SF-Data-Arch Data Migration Practice Question
A financial services firm is migrating 10 million transaction records into a custom object Transaction__c. The legacy system uses a composite key of AccountNumber and TransactionDate to uniquely identify transactions. The target Salesforce org has a unique external ID field Transaction_Key__c. The architect needs to ensure that re-running the migration does not create duplicate records and that updates to existing transactions are applied. Which approach should be used?
⚠ Common exam trap
The trap here is assuming that duplicate rules or custom triggers can replace the need for an external ID and Upsert operation, when they cannot efficiently handle large-scale updates and inserts.
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
✓
Populate Transaction_Key__c with the concatenation of AccountNumber and TransactionDate, then use the Data Loader's Upsert operation with Transaction_Key__c as the external ID field.
Concatenating the composite key into the external ID field and using Upsert allows the migration to match existing records by that key. Upsert updates existing records and inserts new ones, preventing duplicates. This is the most efficient and standard method for handling composite keys in large migrations, as it leverages Salesforce's built-in matching and bulk processing capabilities without custom code.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Create a custom Apex trigger to check for existing transactions before insert and update them if found.
Why it's wrong here
A custom Apex trigger can perform upsert logic, but it is not efficient for 10 million records. Triggers are subject to governor limits and would likely hit CPU time or SOQL query limits. This approach is complex, error-prone, and not scalable. The standard Upsert operation with an external ID is designed for this purpose and handles bulk processing efficiently.
- ✗
Use the Data Loader's Update operation with a SOQL query to fetch existing records and match them by AccountNumber and TransactionDate.
Why it's wrong here
The Update operation requires the Salesforce record ID, not a composite key. To use Update, you would need to first query all existing records and map their IDs, which is impractical for 10 million records due to SOQL limits and performance. This approach is inefficient and does not leverage the external ID field designed for this purpose.
- ✗
Use the Data Loader's Insert operation and rely on Salesforce's duplicate rules to prevent duplicates.
Why it's wrong here
Duplicate rules can alert or block duplicates, but they do not update existing records. Insert will fail for records that already exist if duplicate rules are set to block, or create duplicates if rules are not enforced. This does not meet the requirement to apply updates to existing transactions and may cause partial loads. Relying on duplicate rules is not a substitute for upsert with an external ID.
- ✓
Populate Transaction_Key__c with the concatenation of AccountNumber and TransactionDate, then use the Data Loader's Upsert operation with Transaction_Key__c as the external ID field.
Why this is correct
This is correct because the composite key from the legacy system can be concatenated into a single string that populates the unique external ID field. Upsert uses this field to match existing records, preventing duplicates and allowing updates. This approach is standard for migrations where a natural composite key exists and can be represented as a single string within the 255-character limit.
About these practice questions
Courseiva writes every SF-Data-Arch question from scratch — 222 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. 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.