Salesforce Data Mapping: Avoid Migration Errors Before You Load

The Short Answer

Data mapping isn't a mere field-to-field translation spreadsheet; it's the definitive document where an organization decides the meaning of every data point it brings into Salesforce. Nearly every loading error that appears technical – incorrect date format, unrecognized picklist value, broken relationship – originates from an unmade business decision.

The effective process goes like this: First, determine which entities will be migrated. Next, identify the business owner for each entity. Then, define which fields have a genuine consumer. Only then should you write conversion rules. When the process starts in reverse, the technical team quietly makes business decisions, which only surface three months post-Go Live when a revenue report doesn't add up.

For background on overall migration planning, refer to Salesforce Data Migration Guide.

Three Types of Gaps Data Mapping Reveals

The semantic gap is the most costly because it doesn't cause loading failures. The data successfully loads, automation runs on it, and reports show inaccurate yet plausible numbers. Structural gaps cause load failures and are therefore discovered early. Quality gaps are only discovered if acceptance thresholds are defined in advance.

The Layer of Meaning: Data Dictionary Before Mapping Spreadsheet

Before mapping field to field, create a data dictionary that defines key entities: what an Account is, what differentiates a Lead from a Contact in this organization, and when an Opportunity closes. These definitions are concise – two lines per entity – but they enable dispute resolution by decision, not by vote.

The simple test: Ask three people from three different departments to define "customer" independently. If the definitions vary, the migration will transfer three different truths into the same table.

Anatomy of a Proper Mapping Row

Each row in the spreadsheet should answer seven questions: from which source object and field, to which Salesforce object and field, what is the data type and length, what is the conversion rule, what happens with a blank value, what is the default value, and who approved it. A row missing any of these columns will resurface as a question during a late-night cutover load.

Three operational rules that prevent issues:

• No silent conversions. Any value the system "corrects" automatically must be logged as an exception.
• Default values are business decisions. The person setting Country = IL as a default should be accountable for region-based reports.
• External IDs before anything else. For each entity, retain an External ID from the source system. Without it, there's no reconciliation and no second run.

Transformations: Where Mistakes Occur

The transformations that cause the most damage are often the simplest ones. Dates without time zones shift records by a day; names that are trimmed and capitalized inconsistently create new duplicates right after old ones were cleaned; amounts converted to a uniform currency at a daily exchange rate lead to reporting discrepancies with the ERP.

Rule: All numerical or monetary transformations are validated by comparing totals, not by comparing individual records. Identical counts do not confirm accuracy.

For those still pondering the source of truth, refer to Source of Truth in the Organization, and for the cutover window itself, see Cutover and Reconciliation.

Scenario: Service Company with Two Source Systems

A service organization with 90,000 customers undertook a migration from two systems: an older billing system and a service system acquired with a subsidiary. The first mapping spreadsheet was marked "ready" within two weeks—340 fields mapped.

During the first rehearsal run, 97% of records were loaded. The issue emerged during reconciliation: the total balances in Salesforce were 4.1% lower than in the ERP. The reason wasn't a failed load, but rather that all records with negative balances (credits) were mapped to a field with a validation rule preventing negative values—and were silently set to zero.

The fix involved two levels: an explicit conversion rule for credits, and a policy change requiring any rule that zeroes out or truncates a value to generate an exception record. In the second run, the number of exceptions rose to 1,900, which was progress: the exceptions were visible instead of hidden. The third run reduced exceptions to 40 documented cases, and only then was the cutover date set.

Common Risks and Prevention Actions

How to Measure Success

The last metric prepares for the next cycle: it shows how much effort was unnecessary and guides the scope of future migrations.

Organizations preferring professional guidance in building their mapping do so as part of Integrations and Data Services.

Checklist Before the First Load

• ☐ Concise data dictionary for each key entity, approved by business
• ☐ List of fields with a defined consumer; others for archiving
• ☐ External ID for every entity being migrated
• ☐ Complete value mapping table, including handling of unrecognized values
• ☐ Explicit rule for every blank value and every default value
• ☐ Every conversion rule generates an exception record instead of a silent correction
• ☐ Reconciliation script: count, sum, relationship, manual sampling
• ☐ Agreed-upon exception threshold above which cutover will not proceed
• ☐ Version and sign-off on the mapping spreadsheet
• ☐ Rollback plan and rerun procedure

Professional Resources

• Salesforce Data 360 — https://www.salesforce.com/data/
• Salesforce Data 360 Architecture — https://architect.salesforce.com/docs/architect/fundamentals/guide/data-360-architecture.html
• HPI Pro – Integrations and Data — https://hpi.pro/integrations-data