A
Advait Trivedi
Guest
The first time my team compared our migrated data against the original, only 86% of records matched on the field that mattered most. By the final run, 99.9994% of 12.3 million records matched, and we could explain every record that did not.
Nothing in the gap between those two numbers involved moving data. All of it was verification work. That is the part of a migration almost nobody plans enough time for, and it is the part I want to walk you through.
I have spent two decades building and leading engineering teams, most of that time in digital marketplaces for health insurance. In that world, a record is rarely just a record. It can be a person's decision about whether they want to be contacted. When we moved data off a legacy Oracle monolith into microservices backed by PostgreSQL and
Here is what I learned, written so you can apply it to your own migration.
Most migration conversations are about movement: dual writes, change data capture, replication tools, cutover plans. Those problems are well understood, and the tooling is good.
Correctness is harder, for a simple reason. The team that writes the comparison report is usually the same team that wrote the migration. They share one understanding of the data, so they tend to make the same mistake twice. If the migration mishandles an empty timestamp, the check probably mishandles it in the same way, and the two happily agree with each other. You get a green checkmark on a migration that is wrong.
So before I let anyone open a tool, I ask the team to agree on what "correct" means. For me it comes down to three questions.
One more habit before the steps: start testing early, on a small slice of data. As the data grows, every test gets slower, and every failure gets harder to trace. Also pick the fields you compare with care. When we migrated the history of users' consent, the timestamp was the critical field, because it decides which consent choice is the latest one.
Load the source data and the migrated data into a single staging environment, such as a warehouse like Snowflake. Every test then runs against the same data set, and nobody argues about whose copy is right.
Then resist the urge to look at individual records. Count first. I sort every record key into one of four piles:
The size of each pile tells you the shape of the problem before you spend an hour on a single record. Only then do I pull samples from each pile and dig into them. Open source tools such as Google Cloud's Data Validation Tool and data-diff work the same way: summary first, details second.
This loop of writing a query, running it, reading the result, and refining it is also where an AI coding agent earned its keep for us. An agent can read the legacy data access code, work out the matching queries for the new tables, run them in the sandbox, and drill from summary counts down to the offending records. What used to take hours per cycle took minutes. I keep the plan, the business rules, and the final verdict with a human.
Your data warehouse probably already holds a version of the data you are migrating, often a table fed by an event stream such as Kafka. It is sitting right there, already loaded, and it is very tempting to compare against it.
Don't. That table is a copy, and copies lose things. A message may never have arrived. The copy may have been built by older logic. The only thing you can trust is the system of record itself, meaning the database the application actually writes to.
My one rule here: a mismatch count only ever goes to zero or to "explained." It never just gets smaller because someone ran a cleanup script.
A mismatch tells you that two systems disagree. It does not tell you which one is wrong. It is tempting to treat the old system as the truth and overwrite the new one to match. That only works if both sides mean the same thing by the same value.
Here is an example of how this goes wrong. Picture an old system with a marketing field where "Y" means the person opted in. The new system has a do-not-contact field where "Y" means the opposite. A naive comparison sees "Y" on both sides and reports a perfect match. A naive fix copies "Y" across, and you end up contacting people who asked you not to.
I also treat the match rate as a debugging tool instead of a score. On a hard field, it climbs in steps, and each step should line up with one cause you found and fixed. In my experience, the causes come in four groups, and it pays to fix them in this order:
If you chase logic bugs while measurement problems are still in the numbers, you will lose days on records that were never broken. Our climb from 86% to 99.9994% went through these four groups in exactly this order.
One warning: if your match rate jumps straight to "great" on the first try, be suspicious. More often than not, it means your check cannot see the problem.
Migrations often have to pick one value from several: the latest status, the current address, the last consent a customer gave. The usual approach is to sort by timestamp and take the newest.
That breaks when two records share the exact same timestamp, and in large data sets this happens far more than you would guess. Batch jobs and bulk imports write many rows in the same instant. When nothing else decides the order, the database returns whichever row it reaches first, and that can change from one run to the next. Your migration can quietly pick the wrong value, and your check can disagree with your migration for no real reason.
Two kinds of mismatch are usually false alarms, and they create so much noise that they bury the real defects. Handle both from day one.
The first is time zones. Our old database stored local Pacific time while the new one stored UTC. Timestamps that were truly identical looked seven hours apart. Converting both sides to a common time zone before comparing cleared 99.9% of our timestamp mismatches in one move.
The second is empty timestamps. Some old records had no "last updated" value at all. We chose not to carry that gap into the new system. We filled it the way the application itself would have: use the audit timestamp if there is one, fall back to the record's creation date, then to the profile's creation date. A migration is a chance to improve data quality, and your verification should expect that improvement instead of flagging it as an error.
Most verification happens after the data has moved. By then, a bad record is already in production, and fixing it means another migration. I prefer a set of checks that must pass before anything is written. If one fails, the load stops.
This matters most when the load is not a clean one-time copy into an empty system, which describes most real migrations: data moved in batches, jobs that get re-run, a target that already has live users writing to it.
Data engineers know this idea as the Write-Audit-Publish pattern: write to a staging area, check it there, and publish only if every check passes.
This step tests the migration script itself. Run it several times against a small shadow copy of the source and target databases. Dry runs reveal logic errors and missing fields before they touch real data. AI agents are useful here too, for generating shadow data that exercises every scenario the script should handle.
For the data sets that matter most, I go one step beyond a one-way comparison. After the target is loaded, export it back into the staging area and compare it against the original source. This catches a different kind of failure: data that was written correctly but lost some information on the way.
Then write the verdict down. Store the results of each validation in a table you can query later. Six months from now, someone will ask whether the migration was checked. A record you can show beats a memory you have to defend.
If you take nothing else from this article, take this.
Moving the data is the visible part of a migration, and it is where most of the planning goes. The trust your customers place in you depends on the other part. Budget time for it, give it an owner, and do not accept a green checkmark until someone can explain how it could have been red.
Nothing in the gap between those two numbers involved moving data. All of it was verification work. That is the part of a migration almost nobody plans enough time for, and it is the part I want to walk you through.
I have spent two decades building and leading engineering teams, most of that time in digital marketplaces for health insurance. In that world, a record is rarely just a record. It can be a person's decision about whether they want to be contacted. When we moved data off a legacy Oracle monolith into microservices backed by PostgreSQL and
DocumentDB, "the rows arrived" was never going to be a good enough answer. I needed to be able to say "the rows are correct" and show my work.Here is what I learned, written so you can apply it to your own migration.
Why a Green Checkmark Can Lie
Most migration conversations are about movement: dual writes, change data capture, replication tools, cutover plans. Those problems are well understood, and the tooling is good.
Correctness is harder, for a simple reason. The team that writes the comparison report is usually the same team that wrote the migration. They share one understanding of the data, so they tend to make the same mistake twice. If the migration mishandles an empty timestamp, the check probably mishandles it in the same way, and the two happily agree with each other. You get a green checkmark on a migration that is wrong.
So before I let anyone open a tool, I ask the team to agree on what "correct" means. For me it comes down to three questions.
- Is it complete? Every record that should exist in the new system does. Nothing was quietly dropped on the way.
- Is it faithful? Each value means the same thing on both sides. Matching text is not enough, as you will see in a moment.
- Is it repeatable? The migration gives the same result every time it runs. If it picks between two possible values by chance, it is wrong even on the days it happens to pick the right one.
One more habit before the steps: start testing early, on a small slice of data. As the data grows, every test gets slower, and every failure gets harder to trace. Also pick the fields you compare with care. When we migrated the history of users' consent, the timestamp was the critical field, because it decides which consent choice is the latest one.
Step 1: Put Both Sides in One Place
Load the source data and the migrated data into a single staging environment, such as a warehouse like Snowflake. Every test then runs against the same data set, and nobody argues about whose copy is right.
Then resist the urge to look at individual records. Count first. I sort every record key into one of four piles:
- Match: both sides agree.
- Mismatch: both sides have the record, with different values.
- Missing: the record never made it to the new system.
- Target newer: the new system has a legitimately more recent value than the source.
The size of each pile tells you the shape of the problem before you spend an hour on a single record. Only then do I pull samples from each pile and dig into them. Open source tools such as Google Cloud's Data Validation Tool and data-diff work the same way: summary first, details second.
This loop of writing a query, running it, reading the result, and refining it is also where an AI coding agent earned its keep for us. An agent can read the legacy data access code, work out the matching queries for the new tables, run them in the sandbox, and drill from summary counts down to the offending records. What used to take hours per cycle took minutes. I keep the plan, the business rules, and the final verdict with a human.
Step 2: Compare Against the Real Source, Not a Handy Copy
Your data warehouse probably already holds a version of the data you are migrating, often a table fed by an event stream such as Kafka. It is sitting right there, already loaded, and it is very tempting to compare against it.
Don't. That table is a copy, and copies lose things. A message may never have arrived. The copy may have been built by older logic. The only thing you can trust is the system of record itself, meaning the database the application actually writes to.
Two actions follow from this.
- Name the true system of record for each data set, in writing, and compare against that.
- Keep two separate buckets for two different failures. "A message never arrived" and "the migration wrote the wrong value" have different fixes and different owners. If one number blends them, nobody can act on it.
Step 3: Explain Every Mismatch Before You Fix Anything
My one rule here: a mismatch count only ever goes to zero or to "explained." It never just gets smaller because someone ran a cleanup script.
A mismatch tells you that two systems disagree. It does not tell you which one is wrong. It is tempting to treat the old system as the truth and overwrite the new one to match. That only works if both sides mean the same thing by the same value.
Here is an example of how this goes wrong. Picture an old system with a marketing field where "Y" means the person opted in. The new system has a do-not-contact field where "Y" means the opposite. A naive comparison sees "Y" on both sides and reports a perfect match. A naive fix copies "Y" across, and you end up contacting people who asked you not to.
What I ask my teams to do.
- Before correcting anything, confirm what each value means on each side, not just what it looks like.
- Trace at least one real record from end to end, through its history and the code that produced it. One fully understood example is worth more than a thousand counted ones.
- Decide which side is right based on that evidence. Never assume the source is right just because it came first.
- Be strictest with high-stakes fields such as consent, money, and access.
I also treat the match rate as a debugging tool instead of a score. On a hard field, it climbs in steps, and each step should line up with one cause you found and fixed. In my experience, the causes come in four groups, and it pays to fix them in this order:
- Measurement problems. You are comparing against a stale or lossy copy. These show up as records "missing" in bulk, often clustered around a time window such as an outage.
- Comparison problems. The values are equal but written differently, such as time zones, empty values, and upper and lower case. These share an obvious pattern, like a fixed time offset.
- Logic problems. The migration or the check calculates the wrong value. These follow a rule, such as tied timestamps or one category of record.
- The leftovers. A small number of records, each with a written explanation of why it is fine.
If you chase logic bugs while measurement problems are still in the numbers, you will lose days on records that were never broken. Our climb from 86% to 99.9994% went through these four groups in exactly this order.
One warning: if your match rate jumps straight to "great" on the first try, be suspicious. More often than not, it means your check cannot see the problem.
Step 4: Settle Ties With a Business Rule.
Migrations often have to pick one value from several: the latest status, the current address, the last consent a customer gave. The usual approach is to sort by timestamp and take the newest.
That breaks when two records share the exact same timestamp, and in large data sets this happens far more than you would guess. Batch jobs and bulk imports write many rows in the same instant. When nothing else decides the order, the database returns whichever row it reaches first, and that can change from one run to the next. Your migration can quietly pick the wrong value, and your check can disagree with your migration for no real reason.
What to do
- Before trusting any "take the latest" logic, count how many keys have more than one record at the newest timestamp. If the answer is not zero, you need a tiebreaker.
- Make the tiebreaker a business decision. A row ID makes the result repeatable, but it does not make it right. Ask what the business would want when it cannot tell which record came first. For anything touching safety, compliance, or customer choice, my answer is that the most restrictive value wins. An opt-out beats an opt-in. A suspended account beats an active one.
- Apply the same rule everywhere the value is calculated: the migration, the checks, and any downstream reports.
- Count the affected records before and after. "It looks better now" is not evidence.
Step 5: Assume the Two Systems Disagree About Time.
Two kinds of mismatch are usually false alarms, and they create so much noise that they bury the real defects. Handle both from day one.
The first is time zones. Our old database stored local Pacific time while the new one stored UTC. Timestamps that were truly identical looked seven hours apart. Converting both sides to a common time zone before comparing cleared 99.9% of our timestamp mismatches in one move.
The second is empty timestamps. Some old records had no "last updated" value at all. We chose not to carry that gap into the new system. We filled it the way the application itself would have: use the audit timestamp if there is one, fall back to the record's creation date, then to the profile's creation date. A migration is a chance to improve data quality, and your verification should expect that improvement instead of flagging it as an error.
Step 6: Stop Bad Data Before It Loads.
Most verification happens after the data has moved. By then, a bad record is already in production, and fixing it means another migration. I prefer a set of checks that must pass before anything is written. If one fails, the load stops.
This matters most when the load is not a clean one-time copy into an empty system, which describes most real migrations: data moved in batches, jobs that get re-run, a target that already has live users writing to it.
The gates I use.
- Predict the result before you load. Work out how many new records and how many updates you expect, then compare the actual numbers afterward. If you made no prediction, you cannot tell whether the result is wrong.
- Make key checks hard stops. No record may have a missing key and no key may appear twice. A single failure halts the load, because a bad key can overwrite or duplicate whatever it matches.
- Spot-check calculated values against the raw source tables, not against the pipeline's own intermediate tables.
- Never let older data overwrite newer data. If the target is live, some records may have changed since you extracted them. The load should only add records, or update one when the incoming value is actually newer.
- Save the results of every gate, for every run.
Data engineers know this idea as the Write-Audit-Publish pattern: write to a staging area, check it there, and publish only if every check passes.
Step 7: Rehearse on a Shadow Copy.
This step tests the migration script itself. Run it several times against a small shadow copy of the source and target databases. Dry runs reveal logic errors and missing fields before they touch real data. AI agents are useful here too, for generating shadow data that exercises every scenario the script should handle.
Step 8: For Critical Data, Make the Round Trip
For the data sets that matter most, I go one step beyond a one-way comparison. After the target is loaded, export it back into the staging area and compare it against the original source. This catches a different kind of failure: data that was written correctly but lost some information on the way.
Then write the verdict down. Store the results of each validation in a table you can query later. Six months from now, someone will ask whether the migration was checked. A record you can show beats a memory you have to defend.
The Checklist I Use Before Calling a Migration Verified
If you take nothing else from this article, take this.
Design the comparison
- Compare against the true system of record, not a downstream copy.
- Work out expected values from the business rules, independently of the migration code.
- Keep transport loss, migration errors, and "target is legitimately newer" in separate buckets.
Normalize before comparing
- Convert time zones to a common zone.
- Handle empty timestamps with a clear fallback that mirrors what the application does.
- Line up casing, category values, and any flags that mean opposite things on each side.
Make results repeatable
- Give every "latest wins" rule a tiebreaker that means something to the business.
- Use the identical tiebreaker in the migration, the checks, and downstream jobs.
Gate the load
- Predict record counts and the split between new records and updates.
- Treat missing keys and duplicate keys as hard stops.
- Spot-check a sample against raw source tables.
- Make sure backfills cannot overwrite newer live values.
Prove it and keep the proof
- Dry-run the migration script against a shadow copy.
- Confirm record counts hold steady at every hop.
- Save validation results where they can be queried later.
Moving the data is the visible part of a migration, and it is where most of the planning goes. The trust your customers place in you depends on the other part. Budget time for it, give it an owner, and do not accept a green checkmark until someone can explain how it could have been red.