
In short
- A migration can finish with no errors and still lose records, truncate text, shift dates or change totals.
- Prove it with reconciliation: row counts, totals and checksums per table and per group, compared between old and new.
- Rehearse the whole migration on a full copy of production data more than once, and agree in advance what counts as "done".
On this page
The migration script ran over the weekend. No errors. The new system shows 1,284,907 customers, the same as the old one. Everyone goes home. Three weeks later finance notices that every customer whose name had an accent now has a question mark in it, invoice totals are off by a cent on about one order in a thousand, and the 40,000 customers with no country set were quietly given "United States".
"Same number of rows" is the most common way to sign off a migration, and it proves very little. Here is what proves more.
01What goes wrong in migrations
| Problem | Example | How you catch it |
|---|---|---|
| Lost rows | Records with a null in a required column are skipped | Counts per table and per group |
| Duplicates | A re-run after a failure inserts the same rows again | Counts plus key uniqueness checks |
| Truncation | A 255-character address cut to 100 characters | Max length comparisons |
| Encoding | José becomes Jos? or José | Sample checks on non-ASCII text |
| Rounding | Money stored as float, totals drift by cents | Sum comparisons per account or month |
| Time zones | Timestamps shift by hours; dates move to the previous day | Compare dates near midnight |
| Defaults | Missing values silently replaced with a default | Count nulls before and after |
| Broken links | Orders pointing at customers that weren’t migrated | Orphan checks on foreign keys |
02Reconciliation: counts, sums and checksums
Reconciliation means comparing the old and new data in ways that would reveal any of the problems above. Do it per table, and per group within the table, so a problem in one region or month doesn’t hide inside a total that happens to balance.
-- Run the same query on old and new, then compare the results row by row.
SELECT
country,
date_trunc('month', created_at) AS month,
COUNT(*) AS customers,
COUNT(*) FILTER (WHERE email IS NULL) AS missing_email,
SUM(lifetime_value) AS total_value,
MAX(length(address_line1)) AS longest_address
FROM customers
GROUP BY 1, 2
ORDER BY 1, 2;- Counts catch lost and duplicated rows.
- Null counts catch defaults that replaced missing values.
- Sums of money and quantities catch rounding and conversion errors.
- Max lengths catch truncation.
- Checksums (a hash of each row’s important columns) catch any value that changed, row by row, when you need certainty.
03Samples: look at real records
Numbers tell you something is wrong; samples tell you what. Pick records deliberately, not just at random:
- The oldest and newest records.
- Records with accents, emoji, apostrophes and right-to-left text in names.
- Records with the longest values in each text column.
- Records with nulls in optional fields.
- Records created near midnight and on the dates clocks change.
- A handful of real, complex customers the business knows well.
Then open them in the new application, not just the new database. A value can be stored correctly and still display wrongly.
04Rehearse, then rehearse again
- Agree exit criteria first. For example: all counts match per table and month, all money sums match to the cent, zero orphans, 200 sampled records verified by the business.
- Run the full migration on a copy of production, not a small test dataset. Real data contains things nobody expected.
- Reconcile, fix, and run it again from scratch. At least two clean rehearsals before the real one.
- Time it. Know how long the real cut-over will take and plan the downtime.
- Test the rollback. Know exactly how to go back if the real run fails half-way.
05Test the application, not just the tables
Data can be perfectly copied and still not work in the new system. Fields may mean slightly different things, a status code may map to the wrong value, or the new system may calculate something the old one stored. After each rehearsal, run the main user journeys on migrated data, not on fresh test data.
- Log in as migrated users with different roles and check they see the right things.
- Open old orders and check totals, statuses and history display correctly.
- Do something new with old data: refund an old order, renew an old subscription, edit an old address.
- Run the reports the business depends on and compare them with the same reports from the old system.
06After cut-over
Run the same reconciliation queries straight after the real migration, before users start changing data. Keep the old system read-only for a while if you can, so questions like "what did this record look like before?" can still be answered.
Migration sign-off checklist
- Exit criteria agreed in writing before the first rehearsal
- Counts match per table and per meaningful group
- Null counts, money sums and max lengths match
- No orphaned records in any relationship
- Sampled records checked in the new application, including tricky text and dates
- At least two clean full rehearsals on production-sized data
- Rollback tested
- Reconciliation re-run immediately after the real cut-over
Frequently asked questions
What is data migration testing?
Checking that data moved from one system to another arrives complete and unchanged, and that the new system uses it correctly. It covers completeness, accuracy, formats and relationships.
What is data reconciliation?
Comparing source and target data with counts, sums and checksums, usually per table and per group, to prove nothing was lost, duplicated or changed.
Is matching row counts enough?
No. Counts can match while values are truncated, rounded, re-encoded or replaced with defaults. Combine counts with sums, null counts, length checks and samples.
How many rehearsals should a migration have?
At least two full, clean runs on production-sized data before the real one, plus a tested rollback.
What data should I sample after a migration?
Edge cases on purpose: the oldest and newest records, non-English text, the longest values, empty optional fields, timestamps near midnight, and records the business knows well.
Written by
Vihari Panyam
QualityAIQ