Developer/MigrationTransfer/DataMigration/Fundamentals/data-migration-verification-checksums-reconciliation-and-dry-runs

Data Migration Verification: Checksums, Reconciliation, and Dry Runs

Concept

A one-time data migration — moving records between environments, formats, or systems outside the normal incremental schema-migration flow — has no automatic proof that it worked. The migration job can complete with a clean exit code while having silently dropped rows, truncated values, corrupted encodings, or mismatched a foreign key, and none of that shows up unless verification is treated as a distinct step from the migration itself, not an assumption baked into "the script finished." Verification exists to answer one question with evidence rather than confidence: does the data at the destination actually match the data at the source, in both count and content?

Three verification techniques, layered rather than chosen exclusively, cover this:

Row-count comparison — the cheapest check: does the destination have exactly as many rows as the source (per table, per partition). Catches wholesale drops (a filtered WHERE clause left in by accident, a batch that silently failed partway through) but says nothing about whether the rows that did arrive are correct.
Checksum/hash comparison — computing a hash over each row (or over an entire table/partition) at both source and destination and comparing them. Catches value-level corruption — a truncated string, a timezone conversion applied inconsistently, a numeric field that lost precision — that row counts alone would miss entirely, because the row still exists, just with wrong content.
Full row-by-row diff or targeted aggregate comparison — the most expensive but most complete check: comparing every field, or at minimum running aggregate functions (sum, min, max, distinct-count) on key numeric/date fields between source and target. Reserved for the highest-stakes migrations or a sampled subset of rows, since running it over an entire large dataset is often prohibitively slow.

A dry run — executing the full migration logic against a copy of production (or realistic) data without touching the real destination — is the practice that lets a team catch the failure modes above before they matter, rather than discovering them via one of the checks above after the real cutover has already happened.

Tradeoffs

Technique	Catches	Misses	Cost
Row count only	Wholesale row loss (dropped batches, bad filters)	Any value-level corruption in rows that did transfer	Cheapest — a single `COUNT(*)` per side
Checksum/hash comparison	Value-level corruption within existing rows	New rows, if the hash is computed only over sampled or partial columns	Moderate — requires computing and storing hashes on both sides
Full row-by-row diff	Everything — the ground truth	Nothing, but rarely feasible at full scale	Highest — can dominate the migration's total runtime on large tables
Aggregate comparison (sum/min/max/distinct-count on key fields)	Systemic corruption affecting a field's overall distribution	A small number of individually wrong rows that don't move the aggregate	Low-moderate — a handful of queries per side
Parallel-run comparison (old and new systems live simultaneously, outputs compared)	Behavioral correctness under real live traffic, not just static data correctness	Requires both systems running concurrently, and a defined comparison window	High — operational overhead of running two systems

None of these is a full substitute for the others — row counts and checksums are cheap enough to run on every migration and catch the two most common failure classes; the more expensive full-diff and parallel-run techniques earn their cost only on migrations where the blast radius of an undetected error is large.

When to use / when not to

Row count plus checksum comparison should be the floor for every migration that isn't trivially small and manually verifiable — treat it as mandatory infrastructure, not optional diligence.
Reserve full row-by-row diffing or parallel-run comparison for migrations touching financial, compliance-relevant, or otherwise high-blast-radius data, where the cost of an undetected mismatch materially exceeds the cost of running the more expensive check.
Always dry-run against a copy of real (or realistic) data before running against production — a dry run against synthetic or stale test data will not surface encoding issues, unexpected nulls, or edge-case records that only exist in the real dataset.
Automate reconciliation rather than running it manually — manual row-by-row or spot-check verification doesn't scale past a small migration and introduces exactly the kind of human error the verification step exists to catch.
Don't treat a migration tool's own "success" status (e.g. a managed migration service reporting the job completed) as verification — a completed job and a correct job are different claims, and the tool's completion status answers the first, not the second.

Common pitfall

Verifying row counts and calling the migration validated. Row counts are the cheapest check precisely because they're the weakest one: a migration that drops the last digit of every phone number, corrupts a timezone offset on every timestamp, or truncates a text field at a shorter length than the source produces an identical row count at both ends while every single row is subtly wrong. Teams that stop at row-count parity often don't discover the real problem until it surfaces downstream — a broken report, a customer complaint, a reconciliation failure weeks later — at which point tracing it back to the migration is far harder than catching it with a checksum comparison would have been at the time.

Engineering Lens

The discipline that separates a migration that goes smoothly from one that generates an incident isn't the migration logic itself — most migration scripts are, mechanically, fairly simple. It's whether verification was designed as a first-class, automated step with a real pass/fail gate before cutover, or treated as an afterthought performed informally (or not at all) once the script appeared to run cleanly. The strong answer to "how do you know this migration worked" names the specific checks run (counts, checksums, or a full diff), states what blast radius justified that level of rigor, and confirms it ran against a dry-run copy before the real cutover — not just "the script completed without errors."