Skip to content
All projects

Case study · Data Engineering · Forward Deployed

table-diff: Prove a Data Migration Is Right

Compares a legacy table with its migrated copy by hash, from day to row to cell, and filters out the harmless noise every migration adds.

The problem

After a migration, someone has to prove the new table is the same as the old one. Row counts can match while values are wrong, and a naive row-by-row check flags almost every row because of harmless noise.

Constraints

  • Tables can have millions of rows, so the data must never be pulled into Python
  • Floats drift, CHAR columns get padded and empty strings become NULL after a migration
  • The result has to work as a gate: a pipeline should fail automatically when the copy is wrong

Architecture and decisions

Click a box to see why it was chosen.

table-diff: migration validationInputs
Legacy tableParquet, CSV, DuckDB
Migrated tablesame key, new platform
DuckDBevery pass is SQLNormalize
Noise rulesfloats, spaces, NULLsThree passes
Partition passcount + hash sum per day
Row passmatch by key
Cell passwhich columns changedOutput
ReportMarkdown or JSON
Exit code0 match · 1 diff · 2 error

Why · DuckDB

All three passes are SQL inside DuckDB, so a laptop compares 1,000,000 rows in under 0.1 seconds and memory stays flat.

Results

to compare 1,000,000 rows on a laptop
0.09s
to compare 1,000,000 rows on a laptop
days flagged: raw comparison vs with noise rules
30 → 4
days flagged: raw comparison vs with noise rules
planted bugs found, with nothing else reported
3 of 3
planted bugs found, with nothing else reported
automated tests, CI on Python 3.11–3.13
32
automated tests, CI on Python 3.11–3.13

What I would do next

  1. 01Read from Postgres and Snowflake through DuckDB extensions, and run the partition pass inside the warehouse
  2. 02Tolerances per column, e.g. amount ± 0.01
  3. 03An HTML report with a calendar heat map of partitions