How I check that a data migration arrived intact

A migration job that finishes without errors has proved one thing: it ran. It has not proved that every row arrived, that the totals still add up or that the reports people read the next morning say the same thing they said yesterday. Since 2009, most of my work has been moving banking and insurance data from one place to another, and the part I spend the most care on is the proof.

Compare the new load with the old one

When I led the move of more than 100 warehouse processes from Pentaho to Azure Data Factory at a large Caribbean bank, the old processes kept running while the new ones were built. That gave us something better than a specification to test against: the real output of the system we were replacing.

So every migrated load was checked against the old load with validation and profiling scripts. Same source, same day, two results, and a script that says whether they match. Writing those scripts once and running them on every batch also cut manual testing by 40%.

Check in batches, not at the end

The 100+ processes did not go live at once. With the scrum master and the product owners we turned them into epics and sprint tasks, and each batch went live and was checked before the next one started.

A problem found in a batch of five processes has five places to hide. The same problem found after a hundred processes go live at once has a hundred, and by then the morning reports already depend on it.

Make the differences visible

Some records will not match, and that is normal: source data has errors, edge cases and rules that only someone in the business remembers. The mistake is forcing those records through so the totals look clean.

In a monthly billing and reconciliation pipeline I built for a health organization in Puerto Rico, the process produces three things: the billing, the reconciliation report and a list of records that do not match. That list goes to a person for review. A short exceptions list that someone reads is worth more than a perfect-looking total that nobody can explain.

Keep a person where the decision is

The same pipeline is started from an application screen, because a user reviews and confirms the results along the way. The automation does the heavy work; the person who answers for the numbers decides what gets billed.

What I look for

• The new output matches the old output, checked by a script, not by eye.
• Each batch is proven before the next one starts.
• Records that do not match end up on a list, not in the total.
• The person who owns the numbers sees the exceptions before anything is final.

None of this is complicated. It just has to be planned from the first day, because a migration you cannot prove is a migration you will be asked to redo.