SF-Data-Arch Data Migration Practice Question
A healthcare company is migrating 4 million Patient__c records from a legacy system into Salesforce. The legacy extract includes a field named 'last_modified' that is populated for 99.8% of records, but the remaining 0.2% have a null value. The target external ID field requires a unique, non-null value. What should the data architect do to ensure a successful migration?
⚠ Common exam trap
The trap here is assuming that external ID fields can contain nulls or that nulls can be ignored, when in fact a unique non-null value is required for upsert operations.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Generate a synthetic unique value for the records with null last_modified by using the legacy primary key concatenated with a timestamp, and map that to the external ID field.
The external ID field must contain a unique, non-null value for every record to support upsert and maintain data integrity. Generating a synthetic value from the legacy primary key and a timestamp ensures uniqueness and populates the field for the 0.2% of records with missing last_modified. This avoids data loss and meets the field constraint without modifying the source system or relaxing the Salesforce field definition.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Set the external ID field to allow nulls in the field definition, then migrate the records with null values as-is.
Why it's wrong here
External ID fields can be configured to allow nulls, but the scenario states the field requires a unique, non-null value. Changing the field definition to allow nulls violates that requirement and may break upsert operations that rely on the external ID. It also does not solve the uniqueness problem if multiple records have nulls.
- ✗
Use the standard Salesforce Data Loader's 'Insert' operation instead of 'Upsert' to bypass the external ID requirement.
Why it's wrong here
Using Insert does not bypass the field-level requirement that the external ID be unique and non-null. If the field is required, Insert will still fail for records with null values. Moreover, Insert does not support updating existing records, which may be needed if some patients already exist. This approach does not address the root issue.
- ✓
Generate a synthetic unique value for the records with null last_modified by using the legacy primary key concatenated with a timestamp, and map that to the external ID field.
Why this is correct
This is correct because the external ID field must be populated with a unique, non-null value for every record. The legacy primary key is already unique, and adding a timestamp ensures no collision with other synthetic values. This approach preserves referential integrity and allows upserts. It avoids data loss and meets the field constraint without altering the source system.
- ✗
Exclude the 0.2% of records with null last_modified from the migration and document them as exceptions for manual entry later.
Why it's wrong here
Excluding records is not appropriate when the requirement is to migrate all patient records. This would create data gaps and potentially violate regulatory requirements for completeness. The null values can be handled programmatically, so there is no need to omit records. Manual entry later is error-prone and inefficient for thousands of records.
About these practice questions
One of 222 original SF-Data-Arch practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Salesforce exam blueprint
This SF-Data-Arch practice question is part of Courseiva's free Salesforce certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the SF-Data-Arch exam.