Data Diff¶
Compare two datasets — tables, files, or a table before/after a pipeline change — and report schema differences, row-count deltas, key-level mismatches, and per-column drift. The regression test for data work: run it after a transform refactor, a migration, or a reload.
Trigger: /data-diff
How It Works¶
- Identify the two sides and the key — file vs file, table vs table, or before/after the same table (snapshotting the "before" if a re-run will overwrite it). A row key like
(pair, timestamp)enables row-level diffing; without one, only aggregates can be compared. - Schema diff first — missing columns and type mismatches usually explain everything downstream.
- Row counts — total and per partition (per day, per category) so a localized loss doesn't vanish into the total.
- Key-level diff — keys only in A, only in B, and keys in both with differing values (
EXCEPTboth ways, or aFULL OUTER JOIN). Duplicate keys are reported as a finding before joining. - Column drift — differing-row counts per column and numeric stats per side, within an agreed float tolerance.
- Samples and verdict — a few real mismatched rows, then IDENTICAL / IDENTICAL within tolerance / DIFFERENT with exact numbers and a list of what was ignored.
Keyed Diff, Not Frame Equality¶
Bad: one boolean, no diagnosis
Also fails on row order, dtype, and float noise that may be perfectly fine.Good: keyed, directional, tolerant (DuckDB)
WITH a AS (SELECT * FROM 'before.parquet'),
b AS (SELECT * FROM trades)
SELECT
(SELECT count(*) FROM a) AS rows_a,
(SELECT count(*) FROM b) AS rows_b,
(SELECT count(*) FROM (SELECT pair, ts FROM a
EXCEPT SELECT pair, ts FROM b)) AS keys_only_in_a,
(SELECT count(*) FROM (SELECT pair, ts FROM b
EXCEPT SELECT pair, ts FROM a)) AS keys_only_in_b,
(SELECT count(*) FROM a JOIN b USING (pair, ts)
WHERE abs(a.close - b.close) > 1e-9) AS close_mismatches;
Example Report¶
Diff: before.parquet vs trades (key: pair, ts | float tol 1e-9 | ignored: loaded_at)
Schema: identical (12 columns)
Rows: 1,204,331 vs 1,204,317 (14 fewer in B)
Keys: 14 only in A — all pair=USDJPY, date 2023-06-14 | 0 only in B
Values: 0 mismatches within tolerance
Verdict: DIFFERENT — B is missing USDJPY 2023-06-14 (14 rows); all else identical.
Next: re-run /backfill for that date, then re-diff.
Notes¶
- Read-only on both sides; the skill never modifies either dataset.
- Row order is not a difference — diff by key, never by position.
- Tolerance is agreed up front (float epsilon, ignored columns, timezone handling) and everything ignored is listed in the report.
- DuckDB is the default engine because it reads Parquet, CSV, SQLite, and MotherDuck in one session; big tables are aggregated in-database rather than in pandas.
- Pairs with Migrate (verify a table rebuild), Backfill (verify a re-run), and ETL Scaffold (regression-check a transform refactor).
- Connection credentials for both sides come from environment variables and never appear in output or the final report.