Full-Fledged Reconciliation of Very Large Datasets

Catches schema drift — a column silently added, dropped, or retyped between source and target — before it corrupts every check downstream.

-- metadata comparison, not data comparison
SELECT COALESCE(a.column_name, b.column_name) AS column_name,
       a.data_type AS source_type, b.data_type AS target_type
FROM source_db.information_schema.columns a
FULL OUTER JOIN target_db.information_schema.columns b
  ON a.table_name = b.table_name AND a.column_name = b.column_name
WHERE a.table_name = 'ORDERS'
  AND (a.data_type <> b.data_type OR a.column_name IS NULL OR b.column_name IS NULL);

Row counts and control totals, computed per partition — the short-list generator for every phase after it.

SELECT COALESCE(s.load_date, t.load_date) AS load_date,
       s.row_count AS source_count, t.row_count AS target_count,
       s.row_count - t.row_count AS variance
FROM (SELECT load_date, COUNT(*) AS row_count FROM source_orders GROUP BY load_date) s
FULL OUTER JOIN (SELECT load_date, COUNT(*) AS row_count FROM target_orders GROUP BY load_date) t
  ON s.load_date = t.load_date
WHERE s.row_count <> t.row_count OR s.row_count IS NULL OR t.row_count IS NULL;

Anti-join for missing/extra rows, plus an independent duplicate check — because duplicates can mask missing rows inside a matching total.

-- in source, missing from target
SELECT s.id FROM source_orders s
LEFT JOIN target_orders t ON s.id = t.id
WHERE t.id IS NULL AND s.load_date = :flagged_date;

-- duplicate keys within one side
SELECT id, COUNT(*) FROM source_orders
GROUP BY id HAVING COUNT(*) > 1;

Hash every in-scope column per row and compare — one fingerprint instead of an N-column OR chain.

SELECT s.id
FROM source_orders s
JOIN target_orders t ON s.id = t.id
WHERE s.load_date = :flagged_date
  AND HASH(COALESCE(s.col1,'~'), COALESCE(s.col2,'~'))
   <> HASH(COALESCE(t.col1,'~'), COALESCE(t.col2,'~'));

Side-by-side column diff — but only for the small set of IDs Phase 4 already flagged, so it's cheap even on huge tables.

SELECT s.id, s.col1 AS src_col1, t.col1 AS tgt_col1
FROM source_orders s
JOIN target_orders t ON s.id = t.id
WHERE s.id IN (:ids_flagged_in_phase4);

Applied inside every comparison above, not as a separate pass:

-- NULL-safety
COALESCE(col, '~NULL~')
-- numeric tolerance, never exact equality on decimals
ABS(a - b) > 0.01
-- timezone / precision normalisation
DATE_TRUNC('second', ts)
-- case & whitespace
TRIM(UPPER(col))

What turns the method from theoretically correct into actually runnable in a nightly job:

Partition pruning on every query · row-level phases scoped only to flagged partitions/keys · fully parameterised (table, key, tolerance, excluded columns as config) · version-controlled · wired into CI/CD · structured report per run (partitions checked, variance type, sample flagged rows, pass/fail).