Compare at three levels, from cheap to expensive: row counts, aggregates, then row-by-row differences. Stop when you find a mismatch and zoom into where it is.
Level 1: counts, per partition
SELECT order_date, COUNT(*) FROM src.orders GROUP BY order_date; SELECT order_date, COUNT(*) FROM tgt.orders GROUP BY order_date;
Total counts hide errors that cancel out. Counts per day or per partition show which slice differs.
Level 2: aggregates on key columns
Compare SUM(amount), MIN/MAX of dates, and COUNT(DISTINCT customer_id) per partition. For float columns, compare with a tolerance, not equality, because the two engines may add numbers in a different order and differ in the last digits.
Level 3: find the exact rows
SELECT COALESCE(s.order_id, t.order_id) AS order_id,
CASE WHEN s.order_id IS NULL THEN 'only in target'
WHEN t.order_id IS NULL THEN 'only in source'
ELSE 'different' END AS issue
FROM src.orders s
FULL OUTER JOIN tgt.orders t ON s.order_id = t.order_id
WHERE s.order_id IS NULL
OR t.order_id IS NULL
OR s.row_hash <> t.row_hash;EXCEPT in both directions does a similar job without keys:
SELECT * FROM src.orders EXCEPT SELECT * FROM tgt.orders; SELECT * FROM tgt.orders EXCEPT SELECT * FROM src.orders;
Both must return nothing. One direction alone misses extra rows in the other table.
Row hashes
Comparing 40 columns by hand is tedious. Build one hash per row and compare that:
MD5(CONCAT_WS('|', COALESCE(CAST(col1 AS VARCHAR), '<null>'),
COALESCE(CAST(col2 AS VARCHAR), '<null>')))Two details: handle NULL explicitly (many concatenation functions drop or propagate NULL, which makes different rows look the same), and make sure types print identically in both systems, such as timestamps and decimals with trailing zeros. Most "mismatches" in a migration turn out to be formatting.
What to say at the end
Counts and aggregates give confidence. A key-level full comparison gives proof. On very large tables, do the full comparison on a sample of partitions and the cheap checks on everything. Keep the reconciliation queries so they can run again after each backfill.