diff-table-parity

name	diff-table-parity
description	Compares two tables or query results and diagnoses exactly how they differ — row counts, key set differences, per-column value mismatches — for migration and refactor validation.
when_to_use	When the user must prove two datasets match (or explain why they don't) — validate a data migration, regression-check an ETL change, or confirm a query refactor returns identical results, going past 'counts differ' to where/why/whether-expected.

When to Use

Reach for this when the question is "are A and B the same data, and if not, exactly how do they differ?" Concrete triggers:

• Validating a data migration (old store -> new store) before cutover.
• Regression-checking an ETL/pipeline change: same inputs should yield same outputs.
• Confirming a query/view refactor returns identical results to the original.
• A COUNT(*) already differs and you need where + why, not just the delta number.

NOT this skill: asserting business rules on a single dataset (non-null, ranges, uniqueness, referential integrity) — that is validate-data-quality. This skill always compares two datasets against each other; it never decides whether either is "correct" in isolation.

Steps

1. Pin the comparison contract. Before touching data, write down: the join key(s) that uniquely identify a row in both sides, the column set to compare, and the side names (call them A = baseline/source, B = candidate/target). If a natural key does not exist, find a deterministic composite key — never compare by row position/ordinal, ORDER BY is not a contract.

2. Reconcile schemas first. List columns on each side. Note columns only in A, only in B, and renamed pairs. Decide per column: compare, ignore (e.g. audit timestamps, surrogate auto-IDs), or normalize before compare (cast, trim, round). Comparing a numeric(10,2) against a float8 raw will produce noise — align types now, not after you see mismatches.

3. Row-set diff on the key. Produce three buckets and their counts:

   • keys only in A (dropped/missing in B)
   • keys only in B (added/extra in B)
   • keys in both (candidates for value comparison) Use a FULL OUTER JOIN ... ON a.key = b.key and branch on a.key IS NULL / b.key IS NULL, or set operations (EXCEPT both directions). Capture a sample (10–20) of each non-empty bucket, not just the count.

4. Column-value diff on the intersection. For the in-both keys, compare each chosen column. For every column report: mismatch count, mismatch % of intersection, and 5–10 sample (key, a_value, b_value) triples. Use null-safe comparison (IS DISTINCT FROM, or COALESCE to a sentinel) so NULL-vs-NULL counts as equal and NULL-vs-value counts as a mismatch. Rank columns by mismatch % so the worst offender surfaces first.

5. Classify every diff as expected or defect. A raw mismatch list is not a verdict. Bucket each pattern:

   • Expected/benign: type coercion ("1.0" vs 1), rounding/precision, timezone or timestamptz rendering, whitespace/case/collation, NULL-vs-empty-string, stable-but-different sort. Note the rule and move on.
   • Real defect: value drift, truncation, wrong mapping, dropped/duplicated rows, encoding corruption. These block parity. State the classification rule you applied so it is auditable, don't hand-wave "looks like rounding."

6. Emit a parity verdict with evidence. End with one of: PARITY (zero unexplained diffs), PARITY-WITH-NOTES (all diffs classified expected, list the rules), or FAIL (defects remain, list them with sample rows). Always attach the numbers: total A, total B, only-A, only-B, in-both, and per-column mismatch counts. A bare "tables match" is not an acceptable output.

7. Scale for large tables. Don't pull millions of rows into the agent. Tier the work:

   • Cheap gate: compare COUNT(*) and per-column aggregates (SUM, MIN, MAX, COUNT(DISTINCT)) — if these match, deep diff is often unnecessary.
   • Block hashing: md5/xxhash of concatenated normalized columns per row, then diff the hash sets; only materialize full rows for the keys whose hashes differ.
   • Sampling: if even hashing is too heavy, diff a deterministic sample (WHERE abs(hashtext(key)) % 100 = 0) and report it as a sample, not a proof.

Common Errors

• Float / precision false positives. = on floating point flags rows that are equal to business precision. Round to the contract's scale (round(x, 2)) or compare abs(a - b) < epsilon before declaring a defect. The same trap hits numeric vs double precision and currency stored at different scales.
• Plain = swallows NULL rows. a.col = b.col is NULL (not true) when either side is NULL, so genuinely-differing NULL rows silently vanish from the mismatch count. Use IS DISTINCT FROM everywhere.
• Key collision / non-unique join key. If the "key" is not unique on one side, the join fans out and inflates both counts and mismatches — a 1:N join makes a clean migration look broken. Verify COUNT(*) = COUNT(DISTINCT key) on both sides before trusting any downstream number.
• Non-deterministic ordering treated as a diff. Two queries returning the same set in different order are at parity; comparing by position reports 100% mismatch. Always diff on the key, never on row order, and never assume LIMIT without ORDER BY is stable.
• Collation / encoding / trailing whitespace. 'café' vs 'cafe', 'A ' vs 'A', or different Unicode normalization read as value defects. Normalize (trim, casefold, NFC) per the contract before classifying.
• Comparing across a snapshot skew. If A and B are read at different times and the source is still mutating, "diffs" are just new writes. Pin both reads to the same snapshot/transaction or a frozen extract.

Verify

• Sanity arithmetic holds: only_A + in_both = total_A and only_B + in_both = total_B. If not, the join key is wrong or non-unique — stop and fix step 1/3.
• Re-run on identical input (A vs A) → must report PARITY with zero diffs. If a self-compare shows mismatches, your normalization or null-handling is broken, not the data.
• Every column flagged as defect has at least one concrete sample (key, a_value, b_value) attached; every column dismissed as expected names the classification rule.
• The final verdict is one of PARITY / PARITY-WITH-NOTES / FAIL with the full count table — never ship just a single number or "matches".