External IDs and Upsert

Why External IDs and Upsert Matter
In almost every real-world Salesforce implementation, Salesforce is not the only system of record. Organizations integrate with ERP systems, marketing platforms, legacy databases, and data warehouses. Each of those systems has its own unique identifiers for customers, products, and orders. The challenge is keeping records synchronized without creating duplicates or losing track of which Salesforce record corresponds to which external record. External IDs and the Upsert operation are the primary tools Salesforce provides to solve this problem. On the Advanced Administrator exam, this topic sits within the Advanced Data and Analytics Management domain, and questions typically test whether you understand how to load and maintain data from outside sources efficiently and accurately.

What Is an External ID?
An External ID is a custom field on a Salesforce object that has the External ID attribute checked. Its purpose is to store a unique identifier that originates from a system outside of Salesforce. Key facts to remember:

• Only Text, Number, Email, and Auto Number field types can be marked as External IDs.
• A single object can have up to 25 External ID fields (the limit was raised from 7; the exam may reference either, so understand that multiple External IDs per object are allowed).
• External ID fields are indexed automatically, which improves performance for lookups, SOQL queries, and reports that filter on that field.
• Marking a field as an External ID does not automatically make it unique. You must separately check the Unique attribute if you want Salesforce to enforce uniqueness. However, for Upsert to work reliably, the values should be unique.
• External ID fields are searchable from the global search sidebar.
• External IDs can be case-sensitive or case-insensitive when combined with the Unique attribute. Text External IDs can be set as case-sensitive unique, meaning ABC123 and abc123 are treated as different values.

What Is Upsert?
Upsert is a combination of Update and Insert. It is a single data operation that evaluates each incoming record and decides whether to create a new record or update an existing one. The decision is based on matching against either the Salesforce Record ID or an External ID field.

The logic works like this:
• If a record with a matching External ID value exists, Salesforce updates that record.
• If no record with that External ID value exists, Salesforce inserts a new record.
• If more than one record matches the External ID value, Salesforce returns an error for that row because it cannot determine which record to update. This is why uniqueness is critical.

How Upsert Works in Practice
Consider a scenario where an organization imports customer data weekly from an ERP system. Each customer in the ERP has a Customer Number. An administrator creates a custom field on Account called ERP_Customer_Number__c, marks it as External ID and Unique. During the weekly load, the admin uses the Data Loader Upsert function and selects ERP_Customer_Number__c as the matching field. Existing accounts get updated with any changed data, and new ERP customers become new Account records. No duplicates are created, and the admin never needs to export Salesforce IDs and map them back manually.

Upsert and Relationships: Linking Child Records Without Salesforce IDs
One of the most powerful and frequently tested capabilities is using External IDs to establish lookup and master-detail relationships during a load. Normally, to load Contacts and associate them with Accounts, you would need the 18-character Salesforce Account ID in your Contact file. With External IDs, you can instead reference the parent's External ID value. For example, when loading Contacts via Data Loader, you can map the Account relationship using Account:ERP_Customer_Number__c. Salesforce looks up the Account with that External ID and sets the lookup accordingly. This eliminates a major pain point in data migrations and is a classic exam scenario.

Tools That Support Upsert
• Data Loader – Supports Insert, Update, Upsert, Delete, Hard Delete, and Export. Upsert is available and you choose the External ID field in the wizard. Data Loader handles up to 5 million records.
• Data Import Wizard – Supports matching on Salesforce ID, Name, Email, or External ID for Accounts, Contacts, Leads, Solutions, Campaign Members, and custom objects. The wizard handles up to 50,000 records. When you choose to match by External ID, the import effectively performs an upsert.
• API (SOAP, REST, Bulk) – All expose an upsert call that accepts an External ID field name.
• Flow – Flow does not have a native upsert element, but Get Records followed by conditional Create/Update logic replicates the behavior. Apex supports upsert natively.

Common Pitfalls and Edge Cases
• Blank External ID values: If the External ID column is empty for a row, Upsert will insert a new record every time because there is nothing to match. Always ensure your source data populates the key.
• Duplicate matches: If the External ID field is not marked Unique and duplicates exist, Upsert fails for those rows with an error indicating multiple records found.
• Formula fields cannot be External IDs: Only the four supported data types qualify.
• Standard fields: You cannot mark standard fields as External IDs. Only custom fields qualify. The Salesforce Record ID is the default match key when no External ID is specified.
• Changing the External ID attribute: You can add or remove the External ID attribute on an existing field, but if you also add Unique and existing data contains duplicates, the save will fail until duplicates are resolved.
• Picklist and Checkbox fields cannot be External IDs.

Exam Tips: Answering Questions on External IDs and Upsert

Tip 1 – Look for integration keywords. When a question mentions a legacy system, ERP, data warehouse, external database, or a unique identifier from another system, the answer almost always involves creating a custom External ID field. The phrase without creating duplicates is a strong signal that Upsert is the correct operation.

Tip 2 – Know the field types cold. If a question asks which field types can be External IDs, the answer is Text, Number, Email, and Auto Number. Eliminate any option that includes Picklist, Checkbox, Formula, Date, or Lookup.

Tip 3 – Distinguish Upsert from Update and Insert. If the scenario requires creating new records AND updating existing ones in a single load, choose Upsert. If only existing records need changes and Salesforce IDs are available, Update is sufficient. If all records are brand new, Insert is correct. The exam often includes distractors where Insert would cause duplicates.

Tip 4 – Remember the relationship trick. If a question describes loading child records (Contacts, Opportunities, custom child objects) and the source file does not contain Salesforce IDs for the parent, the answer is to use the parent object's External ID in the relationship mapping. You do not need to export parent IDs first.

Tip 5 – Unique vs. External ID are separate checkboxes. A question may describe Upsert failures due to multiple matching records. The fix is to enforce uniqueness on the External ID field and clean up duplicate values. Do not assume External ID alone guarantees uniqueness.

Tip 6 – Match tool to volume and object. If the record count exceeds 50,000 or the object is not supported by the Data Import Wizard (such as Opportunities, Cases, or Products), Data Loader is the correct tool. For small loads on supported objects, the Data Import Wizard with External ID matching is acceptable.

Tip 7 – Indexing is a bonus benefit. Questions about improving report or query performance when filtering on a custom identifier field can be answered by marking that field as an External ID, since it becomes indexed. This is a less obvious use that sometimes appears on the exam.

Tip 8 – Watch for blank-key traps. If a scenario says duplicates keep appearing even though Upsert is used, consider whether the External ID values in the source file are blank or inconsistent (for example, leading spaces or inconsistent casing on a case-sensitive field).

Tip 9 – Eliminate manual workarounds. Answer choices that suggest exporting Salesforce IDs, building VLOOKUP spreadsheets, or manually deduplicating after the load are rarely correct when an External ID and Upsert approach is available. The exam rewards the most efficient, scalable, declarative solution.

Summary
External IDs give Salesforce a way to recognize records by identifiers from other systems, and Upsert uses those identifiers to intelligently insert or update in one pass. Together they prevent duplicates, simplify integrations, enable relationship mapping without Salesforce IDs, and improve query performance through indexing. Master the supported field types, the uniqueness requirement, the tool limits, and the relationship mapping technique, and you will be well prepared for any question on this topic in the Advanced Administrator exam.