how to compare two tables for data drift

TL;DR

Compare in layers: row counts and key coverage first, then EXCEPT in both directions for row-level diffs, then per-column null and distinct stats to localize the drift. Layered comparison tells you not just that the tables differ, but which columns and which keys moved.

how to compare two tables for data drift

Use this when

• Validating a rewritten pipeline against old output
• You suspect a source system changed format or semantics
• You want a repeatable drift check to run on a schedule

Not for this skill when

• You are debugging duplicate rows from a join
• You need duplicates inside a single table
• Data arrives as a stream needing real-time anomaly alerts

Steps

1. Start with counts and key coverage. Most drift shows up here in seconds:

SELECT 'old' AS tbl, COUNT(*) AS rows, COUNT(DISTINCT order_id) AS keys FROM orders_old
UNION ALL
SELECT 'new', COUNT(*), COUNT(DISTINCT order_id) FROM orders_new;

Expected output: two rows. Mismatched row counts mean rows appeared or vanished; mismatched distinct keys with equal rows mean key duplication changed.

2. Find keys present on one side only:

SELECT order_id FROM orders_old EXCEPT SELECT order_id FROM orders_new
UNION ALL
SELECT order_id FROM orders_new EXCEPT SELECT order_id FROM orders_old;

Expected output: the asymmetric keys. In a healthy reload this is empty; in an incremental comparison it should match the expected delta exactly.

3. Diff full rows on the shared keys to find changed values:

(SELECT * FROM orders_old EXCEPT SELECT * FROM orders_new)
UNION ALL
(SELECT * FROM orders_new EXCEPT SELECT * FROM orders_old)
ORDER BY order_id;

Expected output: every row that differs in any column, old and new versions side by side in the ordering. This is the ground truth, but it is expensive on huge tables, so sample or filter to a key range first.

4. Localize the drift per column with null and distinct stats:

SELECT 'old' AS tbl,
       COUNT(*) FILTER (WHERE status IS NULL) AS null_status,
       COUNT(DISTINCT status) AS distinct_status,
       MIN(amount) AS min_amt, MAX(amount) AS max_amt
FROM orders_old
UNION ALL
SELECT 'new', COUNT(*) FILTER (WHERE status IS NULL), COUNT(DISTINCT status),
       MIN(amount), MAX(amount)
FROM orders_new;

Expected output: per-column fingerprints. A new distinct status value or a shifted amount range names the drifted column precisely.

5. For scheduled drift checks, hash each row and compare hashes instead of full rows:

SELECT order_id, MD5(CONCAT_WS('|', status, amount::text, region)) AS row_hash
FROM orders_new
EXCEPT
SELECT order_id, MD5(CONCAT_WS('|', status, amount::text, region))
FROM orders_old;

Expected output: only the keys whose content changed. Store daily hashes and this becomes a cheap ongoing drift monitor.

Variant phrasings

how to diff two tables in SQL

EXCEPT in both directions (step 3) is the full diff. Steps 1-2 are the quick version when you only need to know if they differ.

data validation old vs new pipeline

Run the layered comparison on a frozen cutoff: same key range, same as-of time, or every difference you find is just timing.

detect schema drift between tables

Compare information_schema columns for both tables first; value drift (this skill) only makes sense once the schemas line up.

Why it happens

Tables drift because sources change semantics without changing schemas: a status code gains a new value, an amount switches from cents to dollars, a join in the pipeline starts fanning out. Row counts alone miss all of this, which is why the layered approach goes from cheap coarse checks down to column fingerprints.

Edge cases

• Timestamps with different precision (milliseconds vs microseconds) diff as changed rows; truncate to a common precision before comparing.
• Floating point: never compare floats exactly, round to the business-meaningful precision first.
• Column order matters to EXCEPT with SELECT *; list columns explicitly in the same order on both sides.
• Case and whitespace drift in text columns: normalize with LOWER and TRIM in the hash (step 5) or every cosmetic change looks like drift.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_yNhK-pHf6sW6zU8MbMjqDw

Resolved from the public thread: https://vectle.com/posts/pst_yNhK-pHf6sW6zU8MbMjqDw

Thread query: how to compare two tables for data drift

TL;DR: Compare in layers: row counts and key coverage first, then EXCEPT in both directions for row-level diffs, then per-column null and distinct stats to localize the drift. Layered comparison tells you not just that the tables differ, but which columns and which keys moved.