Skip to content
METRIC OAK

New: the 3-week Data Platform Assessment: fixed fee, priced roadmap. See what's included →

Migration

Data migration reconciliation: how to prove the numbers match

A practical reconciliation approach for migrations to Databricks, Snowflake or Fabric: the levels of checks from row counts to row hashes, tolerances for floats and timezones, tooling such as Lakebridge Reconcile and dbt audit_helper, and business sign-off.

Hemant Walchale

Hemant Walchale
· 3 min read

In most migrations, the time doesn’t go on copying data. It goes on proving the copy is right. Teams that leave reconciliation to the end find all the differences at once, a week before cutover. Teams that reconcile per wave find them while there’s still time to fix them.

The levels of checks

Run them in this order: each level is cheaper than the next, and catches different problems.

Level Check Catches
1. Schema Columns, types, nullability, lengths Missing columns, narrowed types, truncated text
2. Counts Rows per table and per partition (day, month) Missing or duplicated loads
3. Aggregates Sums of key amounts, min and max of dates, per partition Changed values, shifted dates
4. Profiles Distinct counts and null counts per column Case changes, '' becoming NULL, broken keys
5. Row hashes Hash of each row’s values, compared by key Exactly which rows differ
6. Reports The top reports run on both systems Logic and semantic differences in the end product

Levels 1 to 4 run on every table. Row hashes run on important and financial tables. Report comparisons run on the reports the business actually uses.

Row hashes in practice

Compute the same hash on both systems, then compare by key:

SELECT order_id,
       sha2(concat_ws('|', coalesce(cast(customer_id AS STRING), '~'),
                           coalesce(cast(cast(amount AS DECIMAL(18,2)) AS STRING), '~'),
                           coalesce(date_format(order_ts, 'yyyy-MM-dd HH:mm:ss'), '~')), 256) AS row_hash
FROM sales.orders
WHERE order_date = '2026-03-31';

The detail that matters is normalisation. Both sides must format values identically before hashing: the same decimal scale, the same timestamp format and timezone, a fixed placeholder for NULL, and agreed handling of trailing spaces and case. Most “differences” in a first run are formatting, not data.

Tolerances

Agree these up front, in writing, per table:

  • Floating point: compare rounded to an agreed scale, or within a small tolerance.
  • Timestamps: compare in UTC, at an agreed precision (seconds, or milliseconds).
  • Text: decide whether trailing spaces and case count as differences.
  • Known, accepted differences: for example a bug fixed in the new logic. Document them, so they aren’t re-investigated every run.

Tooling

  • Lakebridge Reconcile (Databricks Labs) compares source tables with Databricks tables and reports schema, row and data differences. Check that your source is supported.
  • dbt audit_helper compares two relations and their columns. Handy when both sides are reachable from dbt.
  • Your own SQL driven by a control table (table, partition, check, result, run time). It’s simple, works on any platform pair, and gives you an audit trail.

Whatever you use, store the results. “Reconciled on 31 March, 0 differences” is what the business signs off against.

Make it part of every wave

  1. Reconcile each table as soon as it’s migrated, not at the end.
  2. Run the checks daily during parallel running, because a match on day one can break on day ten.
  3. Get business sign-off per domain, based on the stored results and the report comparisons.
  4. Switch consumers only after sign-off.

The short version

Row counts aren’t proof. Run schema, count, aggregate and profile checks on every table, row hashes on the important ones, and report comparisons on the reports people use. Normalise before hashing, agree tolerances in writing, store results, and reconcile per wave with business sign-off. Related: Snowflake migration gotchas, how long does a data warehouse migration take and the migration estimator.

Sources

Not sure where your own platform stands? Take the free 3-minute data platform scorecard. Need people to do this alongside your team? See senior data engineers for hire. Unfamiliar terms are explained in the data engineering glossary.

Frequently asked questions

Are row counts enough to validate a migration?

No. Matching row counts miss duplicated keys offset by missing ones, changed values, case and NULL differences, truncated text and timezone shifts. Add totals of key amounts, distinct and null counts per column, and row-level comparisons for important tables.

What is row-hash reconciliation?

Computing a hash of the key columns and values for each row on both systems, then comparing hashes by key. It finds exactly which rows differ without moving all the data, and is the standard way to check large tables row by row.

What tools help with migration reconciliation?

Databricks Labs' Lakebridge includes a Reconcile module with schema, row and data reports. dbt's audit_helper package compares relations and columns. Many teams also write their own checks in SQL, driven by a control table.

Who signs off that a migrated table is correct?

The business owner of the data, not only the engineers. Engineers prove that the tables match; owners confirm that the reports they rely on show the same numbers.

Have 30 minutes? Get a senior architect’s view of your data platform.

No sales deck. You talk to the engineer who would lead the work.

Book an architecture review
Hemant WalchaleYou talk to Hemant Walchale, founder145 published guidesProfiles: LinkedIn · Clutch · GoodFirms

Free, takes 20 seconds

Get your starter data platform roadmap

Pick your platform and your main goal. You get a 90-day plan written by senior data engineers: what to do first, next and after.

You’ll also get our monthly email (confirm it in your inbox). Unsubscribe any time. Privacy policy.