Data Migration to Salesforce: The Honest Playbook

Every data migration looks simple in the SOW. "Move the data from System A to Salesforce. Validate. Cutover. Done." Then you start scoping it and discover that System A has 47 inconsistent address formats, 12 picklist values that map to 3 Salesforce values, and a "primary contact" field that's null on 30% of accounts. The two-week project becomes a two-month project, and the cutover date slips because nobody planned for the validation rules to start enforcing on legacy data that never met them.

I've run migrations from HubSpot, Pipedrive, Zoho, custom databases, and Salesforce-to-Salesforce consolidations. The work is the same shape every time. The reason migrations fail isn't because the work is hard — it's because the planning under-estimates the messy parts. This is the playbook I follow now, written in the order I actually do the work.

Step 1: Profile the Source Before Touching the Mapping

The first thing I do — before any field mapping, before any SOW signoff — is profile the source system's data. That means querying every column for:

• Null counts (how many records have this field populated?)
• Distinct value counts (how many unique values exist?)
• Top 20 values (what does the actual data look like?)
• Length distribution (any 5,000-character "notes" fields hiding?)
• Format consistency (are all phone numbers in the same format? all dates?)

This takes a day or two depending on the source. It's the most valuable day of the project. Almost every "surprise" in migrations comes from making assumptions about the source that the data doesn't actually meet.

A migration I ran from a legacy system had 47 distinct values in the "State" column for what should have been Australian states. Two were typos ("Victora", "Quensland"). Five were full company names somehow ended up in the State field. One was a 200-character paragraph. None of this would have surfaced in a typical scoping call. All of it had to be cleaned before mapping made sense.

If you're not allowed to run profiling queries on the source — because of access restrictions, security, or political resistance — that's the project's first risk, not its first compromise. Push back. The data quality directly drives the project length.

Step 2: Field Mapping Document — Be Pedantic

Once the data profile is done, the field mapping document is the next deliverable. This is the contract for the entire migration. It should specify, for every source column:

• Source column name
• Source data type and constraints
• Target Salesforce object and field
• Target data type
• Transformation rule (if any)
• Default value (if source is null)
• What to do with values that don't match the target's allowed values

The transformation rule column is where projects die. A "simple" rule like "Source column 'Status' maps to Target field 'Stage' with these picklist mappings" sounds easy. In practice, the source column has 12 distinct values and the target has 5. Three of the source values are deprecated. Two are typos. One is a word salad nobody remembers entering. The mapping has to make a decision about every one of them, and the wrong decision means data loss after cutover.

I push hard for the client to sign off on the mapping document with specific examples. Not "all 'In Progress' status maps to 'Working'" — but "this specific record id 12345 with Status='In Progress' will appear in Salesforce with Stage='Working' after migration." Concrete examples force the client to actually look at the data.

Step 3: Build the Migration Tooling Around Idempotency

The most underrated property of a good migration tool is idempotency. If you run it twice, the result is the same as running it once. If something fails halfway, you can re-run from the failure point. If you discover a mapping error a week later, you can re-migrate the affected records without rebuilding from scratch.

This means every record needs a deterministic External Id field on the Salesforce side that maps to the primary key on the source. Then the migration runs as upsert operations against that External Id, not insert. Re-running is safe. Recovering from a partial failure is safe. Adjusting a mapping and re-syncing a subset of records is safe.

I've inherited migrations that were insert-only. They produced beautiful data on first run and absolute chaos on the second when somebody re-ran "to fix a bug" and ended up with duplicate accounts in production. Idempotency isn't an architectural luxury; it's basic risk management.

Step 4: Dry-Run in a Sandbox With Production-Like Data

Every migration must be rehearsed in a sandbox before it touches production. The sandbox should have:

• The same Salesforce metadata as production (validation rules, triggers, flows, sharing rules)
• A representative slice of the production data (so existing records exist for reference lookups)
• The same automated user provisioning (so user-owned records have valid owners)

The migration runs in this sandbox first. The output gets validated against expectations. Stakeholders sign off on the sample. Only then does it touch production.

The validation step is where I find the gaps that the field mapping document missed. Validation rules that fire on legacy data that never had to satisfy them. Flows that create duplicate child records when records are bulk-loaded. Required fields that are null on 5% of source records nobody had thought to flag. Each of these gets resolved before production by either fixing the data, adjusting the mapping, or temporarily disabling the rule for the migration window.

A migration where I skipped this step once cost a week of remediation in production after cutover. Validation rules fired on 30,000 imported accounts because the source data didn't meet a constraint that had been added in Salesforce after the source was last synchronised. The fix was to disable the rule, fix the data, re-enable. A weeks' work that the dry-run would have caught in a day.

Step 5: Cutover Plan With a Rollback That Actually Works

The cutover plan is a written runbook that lists, in order, every step from "stop writes to source system" to "users start using Salesforce." Each step has:

• Who runs it
• What it does
• Expected duration
• Validation check
• Rollback procedure if the step fails

The rollback procedure is the part most plans hand-wave. "If something goes wrong, we restore from backup." This is fine for catastrophic failure but useless for the more common case — the migration ran, something looked subtly wrong an hour after cutover, and the question is whether to roll back or push through.

The rollback I include in cutover plans:

• Production records have a marker field set during migration (e.g. Migration_Batch__c = 'CUTOVER_2026_06'). This makes them queryable and deletable as a set if needed.
• The source system stays read-only but operational for at least 2 weeks after cutover. Reverting to it is a fallback option, not a complete loss.
• A pre-cutover Salesforce snapshot is taken, and there's a documented procedure for reverting Salesforce to it (which usually means deleting the migrated records and notifying users).

Most cutovers don't need the rollback. Having it written makes the cutover decision easier when something does look off — you know what the backup plan is, so you can take the call to push through with confidence.

Step 6: Post-Migration QA Is Not "Run a Report"

After cutover, the QA process needs to verify the migration succeeded by sampling. Not "run a count on Account and confirm it matches the source" — that catches the most obvious failure but misses everything subtle. The QA I run includes:

• Random sample of 50 records, manually compared against the source side-by-side
• Reconciliation of every field that should have a non-null value on both sides
• Spot-check of every relationship — does Contact.AccountId resolve to the right Account?
• Validation that any active automation (flows, triggers) didn't mutate the migrated data unexpectedly during import
• Confirmation that user ownership came through correctly (especially for orgs with role hierarchy)

The fields that should match between source and target are explicitly listed in the field mapping document. The QA validates them one at a time. It's tedious. It catches the bugs that "looks right at a glance" misses.

The migrations I run bake all six of these steps in as standard, not optional extras — if you want a second pair of eyes on one, that's the kind of review I do. Skipping any of them is where the same project from a different supplier ends up costing twice as much in remediation a month later.

What Goes Wrong That I Don't Plan For Anymore

A short list of recurring surprises I've stopped being surprised by:

Field truncation on long text. Salesforce text fields have hard limits. Source systems often don't enforce them. A "description" that's 50,000 characters on the source can't fit in a Long Text Area at 32,768 characters. The mapping has to decide: truncate, store in a related Note record, or skip.

Reference loops. Account A references Account B as parent; Account B references Account A as primary contact. Salesforce can't insert either without the other existing. The fix is a two-pass migration — insert all records with no parent, then update with parent references in pass two.

Owner ownership. The source system has 30 users; Salesforce has 12. Records owned by missing users have to be reassigned. The default of "owned by the migration runner" is rarely the right answer; build a mapping for active users and have a fallback owner for the rest.

Date timezone shifts. A "created date" of 2025-06-15 in the source might mean midnight UTC, midnight local, or end-of-day depending on the source system's conventions. Salesforce stores in UTC. Migration without timezone-aware handling produces records that appear to be created the day before or after they were.

What Clients Should Push For

If you're hiring out a migration, the things that signal a serious vendor:

• They run a data profile before quoting (not after).
• Their proposal includes a field mapping document with explicit transformation rules.
• They quote a sandbox dry-run separately — and won't skip it, even on a tight deadline.
• They include the rollback plan in the cutover deliverable.
• They quote post-migration QA as a real line item, not as "included" with no detail.

A vendor who waves any of these off is signalling that the project is going to be billed by the surprise.

The shortest summary: data migrations fail at planning, not at execution. The actual move is mostly mechanical. The work is in profiling, mapping, dry-running, and validating — and that work is where the schedule lives.