Data Migration Testing: How to Prove the Data Is Right

You test a data migration by proving the migrated data is right: the same number of records, the same totals, the same answers to the questions the business asks of it. A migration script that finishes without errors has proved almost nothing. The checks below build confidence in layers, from cheap and broad to slow and specific.

The testing layers at a glance

Layer	What it proves	Example check
1. Counts	Nothing was lost or duplicated	Records per table and per status match source
2. Totals	Values survived the move	Sum of balances per customer matches to the penny
3. Field samples	Transformations are correct	200 random records compared field by field
4. Business rules	The data still makes sense	No invoice without a customer; no future-dated payments
5. Edge cases	The weird records survived	Deleted, merged, and multi-currency records checked by hand
6. User acceptance	People can do their jobs	Real users complete real tasks on migrated data
7. Rehearsal	It works at full size, in time	Full production copy migrated within the cutover window

1. Counts

Count records in source and target for every entity, and break the counts down: by status, by year, by type. A total that matches can hide two categories that are each wrong in opposite directions. Records that are deliberately not migrated should be counted too, so the numbers add up.

2. Totals

Pick the numbers the business cares about, such as balances, amounts outstanding, and quantities, and compare them per customer or per account, not just overall. Overall totals can match while individual accounts are wrong.

3. Field-level samples

Take a random sample and compare every field, source against target, after transformation. Automate the comparison so the sample can be large, and include records created recently, records from years ago, and records that were edited many times.

4. Business-rule checks

Write queries for things that should never be true in the new system: orphaned records, impossible dates, negative quantities, duplicates on fields that should be unique. These catch mapping mistakes that counts and totals miss.

5. Edge cases

Every system has records nobody designed for: test accounts, manual fixes, merged customers, half-deleted data. Find them in the source before migrating (they are usually in the data migration checklist discovery step) and check each type by hand afterwards.

6. User acceptance

Ask the people who use the data to do their normal work on a migrated copy: find a customer, raise an invoice, run the month-end report. They notice things no query will, like a status label that now means something slightly different.

7. Rehearsal

Run the whole migration against a full copy of production at least once, ideally twice, and time it. A rehearsal answers questions no test environment can: does it fit the cutover window, what fails at full volume, and does the rollback actually work?

Agree sign-off before you start

Write down what “correct” means before anyone sees results: which checks must pass, what tolerance is acceptable (often zero for money), and who has the authority to sign off. Deciding that after the numbers come in turns every discrepancy into a negotiation.

After go-live

Keep the source system read-only and available for a defined period, and run the same reconciliation checks again after the first week of real use. Some problems only appear when new data starts mixing with migrated data.

Testing is one part of planning a migration; the rest is in the data migration checklist. If the migration carries real risk, a migration and integration assessment works out what will break before anything moves.