--- title: "Designing external IDs before you migrate" url: "https://trailassembly.com/designing-external-ids/" topic: "Data and Integration" published: "2026-09-13T14:11:53+00:00" updated: "2026-10-06T13:54:40+00:00" --- # Designing external IDs before you migrate **In one sentence:** before you migrate anything, give every object one [external ID](https://trailassembly.com/upsert-with-external-ids/) field holding the key from the source system, mark it unique, and load with upsert instead of insert. That single decision is what makes a migration re-runnable, lets you load children without an ID lookup pass, and gives you something to reconcile against when the load half fails at two in the morning. ## What an external ID actually does Ticking External ID on a custom field does two things. It creates an index, so filtering on the field is fast. And it makes the field usable as a match key for upsert and for resolving relationships during a load. The second one is the valuable part. In a Bulk or REST load you can reference a parent by its external ID rather than its Salesforce ID: ```apex LastName,Email,Account.Legacy_CRM_Id__c Okonkwo,a.okonkwo@example.com,ACC-00012 Brand,j.brand@example.com,ACC-00047 ``` No lookup pass, no ID mapping spreadsheet, no dependency on having loaded the accounts in this session. The children can be loaded by someone else on a different day and they still land on the right parents. The field types that support it are Text, Number, Email and Auto Number. There is a cap on how many external ID fields one object can have, and it is generous enough that the limit is not usually what constrains you. Your discipline should be. ## One per object, named after the source The rule: one external ID field per object per source system, named so that the source is obvious from the API name. ```apex Legacy_CRM_Id__c SAP_Customer_Number__c Billing_System_Invoice_Ref__c ``` Not `External_Id__c`. The moment a second system arrives, a generic name means someone has to read the data to work out what the values are, and someone else will start writing a different system’s keys into the same field. Then the uniqueness constraint fails for reasons nobody can explain. ## Unique, and the case-sensitivity choice Mark it unique. An upsert against a non-unique external ID fails the moment two records share a value, and the error tells you there were multiple matches, not which records they were. Unique gives you a case-sensitivity option, and this needs a real decision: - **Case insensitive** treats `ACC-12` and `acc-12` as the same value, so one of them will be rejected as a duplicate. This is what you want for keys that pass through humans, spreadsheets or systems that normalise case. It also means a load cannot accidentally create two records for the same source row because the casing drifted. - **Case sensitive** treats them as different records. Only pick this if the source system genuinely uses case to distinguish keys, which is rare, and usually a sign of a bug on their side rather than a design feature. Default to case insensitive. You can change the setting later, but not while the data violates the new rule, so changing it on a populated field means cleaning up first. One behaviour to know: unique fields allow multiple nulls. A row with a blank external ID in an upsert is treated as an insert, every time you run the file. Run it three times and you get three records. Reject blank keys in the file, before loading. ## Designing the key itself Take the source system’s primary key. Not a name, not an email, not a concatenation of things the business might change. The whole point is stability: the key has to identify the same real thing next year. When the source has a composite key, concatenate the parts with a separator that cannot appear in any of them, and write the format down: ```apex ORDER-2026-004871-LINE-003 ``` ## Making loads re-runnable This is the payoff. An upsert-driven load is [idempotent](https://trailassembly.com/callout-retries-and-idempotency/): run it once, run it five times, the result is the same. That changes how a migration feels. A failed batch is not a cleanup exercise, it is a rerun. A late correction from the source system is not a reconciliation, it is a rerun with a newer file. ## Frequently asked **Can I add an external ID to a field that already has data?** Yes, and marking it unique will fail if duplicates exist. Find them with a grouped query before you tick the box. **Does an external ID improve query performance?** It creates an index, so filtering on it is fast on large objects. That is a genuine side benefit, though it is not a reason to mark arbitrary fields. **What if the source system reuses keys?** Then it is not a key. Add something that makes it unique, such as the entity type or the year, and document why. --- Source: https://trailassembly.com/designing-external-ids/ Trail Assembly — independent Salesforce write-ups.