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

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. 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. That's why the next step compares records, not totals.

Also compare the SUM of a key amount field between source and Salesforce, for example Opportunity Amount by stage. Equal counts with different totals point to changed values. 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.

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 &amp; Pine Co. That's an encoding problem in the load file, and it's exactly what EXACT catches. 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". 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.