Validating a data migration: methods compared

That every object in scope gives the same output on the same input, and that anyone can check it. That sentence has four parts, and each can fail on its own.

“Every object in scope” needs an agreed list. “The same output” needs a defined comparison, with tolerances. “The same input” needs a frozen snapshot that both sides read. “Anyone can check it” needs evidence a person who did not do the work can read.

One limit belongs beside any validation claim. Matching the original shows equivalence on the tested inputs. It does not show that the original was right. Known defects carry over, and should be fixed afterwards as separate, logged changes.

Method. This note compares five families of validation, using the published documentation of the main platform tools (Databricks Lakebridge, Databricks Genie Migrate, Snowflake SnowConvert AI) and the research literature on proving query equivalence. It uses no Novuz customer data.

Summary checks compare row counts and totals between the old and new tables: how many rows, the sum of an amount column, the number of distinct customers.

They are fast and cheap, and they catch gross errors such as a missing load or a duplicated batch. They are also the method most likely to give false comfort. A join that assigns one customer’s balance to another keeps the row count exact. Two wrong rows can add up to the right total.

Table reconciliation compares the old and new versions of a table more closely: the schema, a hash of every row, or row-by-row values matched on key columns.

Databricks’ free Lakebridge tool is a good example of this method. Its Reconcile module offers four report types. A schema report compares column names and data types. A row report compares a hash of each row and needs no join key. A data report compares row and column values using join columns, with mismatch detail per column. An all report runs the data and schema checks together.

Lakebridge’s documentation also shows where the method needs care. For Teradata, row-hash reports need a hash function the user installs. For BigQuery, some types cannot be compared exactly, and some lossy matches, such as a DATETIME column against a timestamp, can be reported as matches and should be checked by hand.

Snowflake’s SnowConvert AI offers a similar data validation step, run from its command line, with results written to Snowflake tables. Databricks’ documentation for Genie Migrate advises running the converted code and comparing results with the source.

Per-object reconciliation goes one level deeper. Instead of comparing the final tables, it runs each converted object (a procedure, a package, a script or a job) and its original on the same frozen inputs, and compares what each one produces.

This is what a migration of logic needs, because it ties every difference to the object that caused it. A failed check points to one procedure and one line, not to a table that thousands of objects touched.

It is also more work to set up. Each object needs inputs that exercise its risky branches, and each comparison needs agreed tolerances. The checks usually run in tiers, cheapest first, and stop an object at its first failure.

In a parallel run, the old and new systems both run in production for a period, and someone compares their outputs as real data flows through.

Parallel runs catch problems no test data predicted, because they see real inputs. They are also the most expensive method. Every month of a parallel run means paying for two platforms and two teams. Without an agreed pass rule, they end when someone gets tired, not when the numbers are proven.

Parallel runs work best when they are short and narrow: one domain at a time, for as long as finance needs to close a period or two, after per-object reconciliation has already cleared the logic.

The strongest possible check would prove that two queries give the same result for every possible input, not just the inputs tested. Researchers have worked on this for decades.

The best-known recent example is Cosette, from the University of Washington, presented at CIDR 2017. Cosette takes two SQL queries and returns “equivalent”, “inequivalent” or “unknown”. When the queries differ, it produces a counterexample input. When they match, it aims to produce a machine-checked proof.

Its authors are clear about the limits. Equivalence for arbitrary SQL queries is undecidable, so no tool can settle every case. Cosette did not yet handle string operations such as LIKE, ORDER BY or floating-point numbers, and some proofs needed human help: in one set of 23 equivalent query pairs, 17 were proven automatically.

Formal methods are valuable research and useful for checking query rewrites. They are not yet a practical way to validate thousands of procedures with loops, temporary tables and error handling. Migrations still rely on testing with well-chosen inputs.

Most migrations need more than one. The table sets out what each proves and when it pays.

In our reading of published guidance and our own experience, the same few mistakes recur.

Tool capabilities change. The descriptions of Lakebridge, SnowConvert AI and Genie Migrate come from their documentation as published in November 2026; check the current version before relying on them.

The comparison of methods is qualitative. We know of no public study that measures how often each method misses errors in real warehouse migrations.