| 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
-
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.
-
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.
-
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.
-
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.
-
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."
-
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.
-
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".
