Salesforce data migration testing: how to reconcile record counts and field values before go-live

AI-Assisted

By Richard C, AIL Studio

The data load finished with no errors, so the migration worked, right? Not necessarily. "No errors" means Salesforce accepted what it was sent. It doesn't prove that every source row was sent, or that the values arrived the way they left. Before go-live you want evidence, object by object, that you can hand to whoever signs off.

This post walks through four checks in a spreadsheet: record counts, field values on an external key, nulls and truncation, and a sign-off table.

1. Reconcile record counts per object

For each object, get the row count from your source export (or the source system's own query) and the count in Salesforce:

SELECT COUNT() FROM Account
SELECT COUNT() FROM Account WHERE External_Key__c != null

The second query is the more useful one. It counts only records that came from the migration, not ones users created by hand in the sandbox.

Before comparing, subtract the records you left out on purpose (for example inactive users or old cases) from the source count, so you compare against what you meant to load.

Put the counts side by side. Difference is =C2-B2, and Status turns it into words:

=IF(D2=0,"Match",IF(D2<0,"Short "&-D2,"Extra "&D2))

A match isn't proof that nothing is wrong: one missing record plus one duplicate still matches. Totals (below) catch changed amounts; step 2 then finds which records changed.

Compare totals

Counts tell you how many records arrived. Totals tell you whether the money did. For each object that has an amount field, sum it on both sides:

• Opportunity: Amount
• Opportunity Product (OpportunityLineItem): TotalPrice
• Account: AnnualRevenue, if you migrated it

SELECT StageName, SUM(Amount) FROM Opportunity
WHERE External_Key__c != null GROUP BY StageName

Run the same grouping on the source export (a pivot table works) and put the two totals side by side. Grouping by stage tells you where a difference sits. In a multi-currency org, SOQL returns a grouped SUM(Amount) in the org's default (corporate) currency. For per-currency totals, export Amount and CurrencyIsoCode and sum them in a pivot on both sides. If you compare converted totals instead, both sides must use the same exchange rates.

Pass rule: the totals must match exactly, per object and per group. The only allowed difference is one you can trace to rounding, for example a source with four decimals loaded into a field with two. Write that down on the sign-off sheet. Any other difference, even when the counts match, means a record is missing, duplicated or changed. Go to step 2 to find it.

2. Compare field values on an external key

Export the same fields from both sides into two tabs, Source and Target, each starting with the external key (the ID from the old system that you loaded into an External ID field). Salesforce record IDs are new after the load, so the external key is the only thing you can match on.

On a Fields tab, pull each target value next to its source row:

=IFERROR(INDEX(Target!$B$2:$B$100,MATCH($A2,Target!$A$2:$A$100,0)),"(missing)")&""

The &"" at the end matters. Without it, an empty target cell comes back as 0, and a blank phone number looks like a real value.

Then compare with EXACT, which is case-sensitive (a plain = isn't):

=IF(B2="(missing)","Missing",IF(EXACT(Source!B2,B2),"OK","Changed"))

For a large object, don't compare every row by hand. Take a sample, say 100 records across different record types and owners, and widen it if anything turns up.

3. Catch nulls and truncation

Two failures hide inside "Changed", and they're worth naming because they have different fixes.

Null in target: the source had a value and the target is blank. It's usually an unmapped field or a picklist value that was rejected.

=IF(B2="(missing)","Missing",IF(AND(Source!C2<>"",D2=""),"Null in target",IF(EXACT(Source!C2,D2),"OK","Changed")))

Truncated: the target is the start of the source value, cut short. The field length in Salesforce is shorter than the source column.

=IF(B2="(missing)","Missing",IF(EXACT(Source!D2,F2),"OK",IF(AND(LEN(F2)<LEN(Source!D2),LEFT(Source!D2,LEN(F2))=F2),"Truncated","Changed")))

In the example, Cedar & Pine Co arrived as Cedar & Pine Co. That's an encoding problem in the load file, and it's exactly what EXACT catches.

4. Sign off per object

Finally, one row per object, counted from the checks above:

• Missing: =COUNTIF(Fields!C2:C9,"Missing")
• Changed: =COUNTIF(Fields!C2:G9,"Changed") (do the same for "Null in target" and "Truncated")
• Ready?: =IF(B2<>"Match","Not ready",IF(C2="n/a","Counts only",IF(C2+D2+E2+F2=0,"Ready","Not ready")))

"Counts only" is an honest status. The formula gives it when the totals match but the value checks are marked n/a because nobody has compared values yet. Don't let it get rounded up to "Ready".

Next checks

Counts and field values are the first pass. For row-level problems, see how to find missing and duplicate records on an external key and how to catch Contacts attached to the wrong Account.

These formulas were checked in LibreOffice Calc. INDEX/MATCH, EXACT and COUNTIF exist in Excel and Google Sheets, but I haven't tested them there, so check them once in your own copy.

For the wrong-parent check, RecordBridge is a browser tool that compares your Account, Contact and Opportunity exports: RecordBridge.