SfSkills Domains Docs GitHub

Home / data / external-id-strategy

external-id-strategy

Use this skill when designing, selecting, or troubleshooting external ID fields on Salesforce objects for upsert operations, cross-system record correlation, or idempotent data loads. Trigger keywords: external ID field design, upsert key strategy, cross-system record matching, source system ID mapping, composite key for uniqueness, duplicate insert on upsert, relationship resolution by external ID. NOT for sequencing and running the migration itself — use data/data-migration-planning. NOT for making an inbound API retry-safe end to end — use integration/idempotent-integration-patterns.

Open the skill folder on GitHub

External ID Strategy

This skill activates when a practitioner needs to design or troubleshoot how Salesforce objects carry source-system identity for upsert operations, cross-system record correlation, or idempotent integration and migration patterns. It covers external ID field type selection, uniqueness and case sensitivity semantics, indexing behavior, composite key design, parent-child relationship resolution by external ID, and the Bulk API 2.0 and REST API upsert mechanics that depend on these choices.

Before Starting

Gather this context before working on anything in this domain:

Core Concepts

What an External ID Field Is

An external ID field is a custom field on a Salesforce object marked with the External ID attribute in the field definition. Marking a field as External ID has two automatic platform effects:

  1. Automatic indexing — Salesforce creates a database index on the field without a Support case. This makes upsert match lookups efficient even on large objects (millions of records).
  2. Upsert key exposure — The field becomes available as a match key for REST API upsert (PATCH /sobjects/{SObject}/{ExternalIdField__c}/{value}), Bulk API 2.0 upsert jobs (the externalIdFieldName job parameter), Apex Database.upsert(records, Schema.SObjectField), and Data Loader upsert operations.

Upsert matching behavior is deterministic:

This insert-or-update behavior makes upsert inherently idempotent when the external ID is unique: running the same load twice produces the same end state without creating duplicates.

Field Type Selection and Case Sensitivity

External ID fields can be of type Text, Number, Email, or Auto Number. The choice affects uniqueness enforcement and case sensitivity:

Composite Key Strategy

When no single source field is globally unique across all records, a composite key must be constructed before loading. The standard approach is to concatenate two or more source fields with a stable separator and store the result in a single Text external ID field.

Design rules:

Parent Relationship Resolution via External ID

When loading child records that reference a parent, it is possible to avoid a two-pass load (load parents, query back Salesforce IDs, embed IDs in child CSV) by using the parent's external ID field in a relationship reference column.

In Bulk API 2.0 CSV format, relationship resolution uses the syntax RelationshipName__r.ExternalIdField__c as the column header. For standard relationships:

Salesforce resolves the parent Salesforce Id row-by-row at ingest time using the indexed external ID field. Requirements:

Common Patterns

Pattern: Source System ID as External ID (Single-Object Correlation)

When to use: A single source system owns authoritative records for a Salesforce object type, and each source record has a stable unique identifier (e.g., ERP customer number, legacy CRM contact ID, warehouse SKU code).

How it works:

  1. Create a custom field on the Salesforce object (e.g., Legacy_CRM_Id__c of type Text or Number). Mark it as External ID and Unique. For Text fields with alphanumeric source keys, decide case sensitivity based on the source system's behavior.
  2. On initial load, use Bulk API 2.0 upsert (or Data Loader upsert) with this field as the external ID key. Every source record inserts as a new Salesforce record.
  3. On all subsequent loads, reuse the same upsert — new source records insert, existing records update, no manual deduplication needed.
  4. In downstream queries and reports, use the external ID field as a stable cross-system join key without storing Salesforce Id values in the source system.

Why not use Name or another non-external-ID field: Non-external-ID fields are not exposed as upsert keys. The upsert API rejects them as the match field. Name values are also frequently non-unique and will generate errors.

Pattern: Multi-System Composite External ID

When to use: Records originate from multiple source systems, and no single field is globally unique across all sources (e.g., order numbers restart per region, account IDs repeat across acquired-company datasets).

How it works:

  1. Define the composite key formula: <SystemCode>|<SourceId> (e.g., ERP|10042, CRM2|A-9871, LEGACY|00001).
  2. Create a Text external ID field (e.g., Source_Composite_Key__c), marked External ID and Unique. Enable case-sensitive option if source codes can differ by case.
  3. Produce the composite value in the ETL layer for every record before staging. Validate that no component field is null (null + separator + value produces an inconsistent key).
  4. Load via upsert using Source_Composite_Key__c as the upsert key across all source systems.
  5. Document the formula in the integration spec and enforce it in data quality validation.

Why not create one external ID field per source system: Each object supports up to 25 external ID fields, but proliferating per-source fields complicates upsert logic and wastes the indexed field budget. A single composite key field unifies the strategy and keeps the object model clean.

Pattern: External ID-Based Relationship Resolution (Parent-Child Load)

When to use: Loading related objects (e.g., Account + Contact, Order + Order Line Item) where children reference parents by source system key, and a two-pass load (load parents, query IDs, embed in child file) is impractical or fragile.

How it works:

  1. Ensure the parent object has its external ID field populated before the child load begins.
  2. In the child CSV file, include a column using the relationship syntax as the header: for a standard Account lookup on Contact, use Account.Legacy_CRM_Id__c; for a custom lookup, use ParentCustom__r.Legacy_CRM_Id__c.
  3. Populate the column with the parent's source system key value (not the Salesforce Id).
  4. Submit the Bulk API 2.0 ingest job for children with the appropriate upsert key set on the child object.
  5. Salesforce resolves each parent reference using the indexed external ID field at ingest time — no pre-query required.

Why not query Salesforce IDs first: A two-pass approach works but introduces fragility — pagination errors on large parent queries, Salesforce IDs that differ across sandboxes, and stale ID references if records are deleted and recreated. Relationship resolution via external ID is fully idempotent and sandbox-portable.

Decision Guidance

SituationRecommended ApproachReason
Source key is a pure integerNumber external ID fieldNo case-sensitivity concerns; numeric equality is unambiguous; avoids text padding issues
Source key is alphanumeric, source is case-insensitiveText external ID, Unique (case-insensitive default)Prevents duplicate variants differing only by case from being inserted
Source key is alphanumeric, source treats case as significantText external ID, Unique, case-sensitive option enabled; normalize to consistent case in ETLPreserves case distinctions while avoiding accidental mismatches from inconsistent input casing
Records come from multiple source systemsComposite key in one Text external ID field (SystemCode|SourceId)Single upsert key per object; avoids per-source field proliferation
Child records must reference parents without knowing Salesforce IDsParent external ID populated first; child CSV uses RelationshipName__r.ExternalIdField__c syntaxSingle-pass child load; idempotent and sandbox-portable
Source key is an email address (person record)Email type external IDBuilt-in format validation; case-insensitive uniqueness matches standard email semantics
No natural key exists in source systemGenerate a stable UUID or hash at ETL layer before first load; store in source systemAssign once at migration time; use as external ID on all subsequent loads
Object already at 25 external ID fieldsConsolidate using composite key pattern or remove unused external ID fields25 is the platform limit; exceeding it requires removing an existing external ID field
Need to load the same dataset into multiple sandboxes and productionUse external ID-based loads throughout; never embed Salesforce IDs in load filesSalesforce IDs differ between orgs; external IDs are portable across all org copies

Step-by-step instructions for an AI agent or practitioner working on this task:

  1. Inventory source keys — For each Salesforce object that needs upsert support or cross-system correlation, document the source system's natural key field(s): data type, uniqueness guarantee, maximum length, and case sensitivity behavior. Flag any object where no single field is globally unique.
  2. Select field type and uniqueness settings — Based on the key type and case sensitivity requirements, choose Text, Number, or Email. Always mark External ID and Unique when the field will serve as an upsert key. For Text fields, decide whether to enable the case-sensitive option. Confirm the current external ID field count on each object does not approach the 25-field limit.
  3. Design composite key formula if needed — If no single source field is globally unique, define the composite formula, separator character, and maximum combined length. Validate length does not exceed 255 characters. Document the formula in the integration spec before creating the field.
  4. Create the field via Setup or metadata deployment — Create the custom field through Setup > Object Manager, or deploy it via Salesforce DX metadata (CustomField with externalId: true and unique: true). Confirm the field appears in the object's indexed fields list after creation.
  5. Test upsert behavior end-to-end before full load — Run a small test batch (10–100 records) through the intended upsert path. Verify: (a) first run inserts all records, (b) second run with same data updates all records and creates no duplicates, (c) introducing a row with a duplicate external ID value in the org returns an expected error, not a silent duplicate.
  6. Verify parent relationship resolution if applicable — If child records use relationship column syntax, confirm the parent external ID field is populated, run a small child batch, and verify parent lookups are set correctly on the inserted child records.
  7. Document the strategy and commit — Complete the external-id-strategy-template.md with field API names, types, case-sensitivity settings, composite key formulas, and integration path references. This documentation is required for integration spec audits and onboarding future maintainers.

Review Checklist

Run through these before marking work in this area complete:

Salesforce-Specific Gotchas

Non-obvious platform behaviors that cause real production problems:

  1. Text external IDs enforce case-insensitive uniqueness by default — Unless the case-sensitive option is explicitly checked at field creation, ORDER-001 and order-001 are treated as identical external ID values. If the source system generates keys that differ only by case for different logical records, inserting the second variant will fail with a duplicate error, even though the integration author expected two distinct records. Normalize keys to a consistent case in the ETL layer regardless of the uniqueness setting.
  2. Upsert errors on duplicate values are per-row, not per-job — In Bulk API 2.0, if two Salesforce records already share the same external ID value (possible when the Unique constraint was not set, or was removed after records were created), any upsert attempt for that value returns an error for that specific row while other rows in the batch continue processing. The job status shows partial success. These silent partial failures are easy to miss in large loads — always inspect the job's error file after every bulk upsert run, not just the overall job status.
  3. External ID alone does not prevent duplicates — Unique is a separate checkbox — Creating a field as External ID gives it an index and exposes it as an upsert key, but does not enforce uniqueness. The Unique checkbox must be checked separately. Omitting it means duplicate external ID values can be inserted through the UI, Data Import Wizard, or non-upsert DML. Once duplicates exist, any upsert on that value errors. Always set both External ID and Unique together when the field is intended as an upsert key.

Output Artifacts

ArtifactDescription
external-id-strategy-template.md (completed)Per-object external ID field design decisions, composite key formulas, and integration path references
CustomField metadata XMLDeployable field definition with externalId: true and unique: true (example in templates/)
Integration spec sectionDocuments field API name, type, case-sensitivity setting, composite key formula, and the integration path that uses it for all team members

Dependencies

Package files