Three-tier migration reconciler that proves a data migration moved every row faithfully: counts, order-insensitive column checksums, and exact business aggregates, with checksum-bisection drill-down to the exact divergent rows. A clean 5M-row pair clears in 0.55 seconds.
- "The migration finished" is not "the migration is correct": row counts pass even when 25 dropped rows offset 25 duplicated ones, and this repo demonstrates that exact failure with a test. Checksums catch what counts cannot.
- Sign-off meetings do not accept hash values; they accept "total revenue matches to the cent". Tier 3 compares spec-declared business aggregates exactly (DECIMAL, not float), so the reconciliation report speaks the stakeholder's language.
- When something diverged, "somewhere in 5 million rows" is useless. Checksum bisection localizes the exact divergent rows in a handful of grouped probes, without ever transferring the tables.
Every warehouse consolidation (the kind where ERP, CRM, and finance systems move into a lake or warehouse) ends with the same question: did everything arrive? The honest answer requires comparing two systems that should be identical, cheaply enough to run after every load, and precisely enough to name the divergent keys when they are not.
reconcile-kit runs three tiers, cheapest first: row counts, order-insensitive per-column checksums (SUM of row hashes, so physical row order never matters), and business aggregates declared in a JSON spec with per-aggregate tolerances. The spec file doubles as the migration's reviewable acceptance criteria. On failure, a hash-bucket bisection compares (count, checksum) per bucket and recurses only into buckets that disagree, running an exact multiplicity-aware diff only on leaf buckets known to contain a difference; probe cost scales with divergence, not table size.
Everything is measured against planted ground truth: the bundled simulator copies a source to a target and plants five defect classes (dropped rows, duplicated rows, value drift, null-outs, whitespace mangling) on disjoint key sets, and the test suite asserts every class surfaces in the divergent output with the right kind.
flowchart LR
A["source export<br/>csv or parquet"] --> C
B["target export<br/>csv or parquet"] --> C
S["reconcile spec json<br/>key, columns, aggregates + tolerances"] --> C
C["tier 1: row counts"] --> D["tier 2: per-column checksums<br/>SUM of hash, order-insensitive"]
D --> E["tier 3: business aggregates<br/>exact DECIMAL comparison"]
E -->|all pass| F["MATCH, exit 0"]
E -->|any fail| G["checksum bisection<br/>probe only divergent buckets"]
G --> H["divergent rows with kind:<br/>missing / duplicated / drifted, exit 1"]
| Technology | Role in this project | Why chosen here |
|---|---|---|
| Python 3.10+ | Orchestration, spec handling, bisection control loop | The probes are SQL; the recursion strategy is Python |
| DuckDB | Executes every probe and diff | Grouped scans over 5M rows in milliseconds, embedded; probes are plain GROUP BYs that push down to any warehouse adapter |
| Typer | CLI | Typed options, CI-friendly exit codes (0 match, 1 divergent, 3 error) |
| pytest + pytest-cov | 18 tests, 96% measured coverage | Includes recall-against-ground-truth and both war-story regressions |
| ruff + GitHub Actions | Lint and CI, coverage gate at 90% | Matrix on 3.10 and 3.12 |
Prerequisites: Python 3.10+, pip.
git clone https://github.com/kattakeerthnareddy/reconcile-kit.git
cd reconcile-kit
pip install -e ".[dev]"
# build a labeled defective migration (source, target, spec, ground truth)
rkit simulate --rows 100000
# reconcile; exits 1 and lists divergent rows with kinds
rkit reconcile --source data/generated/source.parquet \
--target data/generated/target.parquet \
--spec data/generated/spec.json \
--json-out report.json
# run the tests
pytest -qA spec for a real migration:
{
"key": "order_id",
"columns": ["customer_id", "amount", "status", "order_date"],
"aggregates": [
{"name": "total_amount", "expr": "SUM(CAST(amount AS DECIMAL(18,2)))", "tolerance": 0.0},
{"name": "distinct_customers", "expr": "COUNT(DISTINCT customer_id)", "tolerance": 0}
]
}Write money aggregates as DECIMAL casts; the war story below is what happens otherwise.
Methodology: python benchmark/run_benchmark.py, two scenarios per scale: a clean pair (the nightly happy path) and a pair with 0.05% of rows corrupted per defect class. Hardware: 2 vCPU, 8 GB RAM container. Raw output: benchmark/results/benchmark_results.json.
| Scale | Clean pair, all tiers | Defective pair incl. drill-down | Divergent rows found | Probes |
|---|---|---|---|---|
| 100k rows | 0.05 s | 0.99 s | 400 | 1 |
| 1M rows | 0.12 s | 52.9 s | 1,000 (capped) | 18 |
| 5M rows | 0.55 s | 103.0 s | 1,000 (capped) | 5 |
xychart-beta
title "Clean-pair reconciliation time (seconds)"
x-axis ["100k", "1M", "5M"]
y-axis "seconds" 0 --> 0.6
bar [0.05, 0.12, 0.55]
The honest limit: drill-down cost scales with how much diverged, not with table size, and that is the design working as intended; the happy path stays sub-second while a heavily corrupted pair (thousands of divergent rows spread across most buckets) pays for many leaf diffs. The 1,000-row output cap keeps incident reports bounded; raise max_keys to enumerate more.
- ADR-0001: three cheap tiers before any row diff. Counts alone are provably insufficient (offsetting drop+duplicate passes tier 1; a test demonstrates it).
- ADR-0002: checksum bisection over a global anti-join, with the two hard-won implementation rules from this build's bugs.
- Live database adapters. The probes are plain grouped SQL, designed to push down to Postgres/Snowflake connections; the adapter layer is deliberately deferred until the file-based workflow proves the probe design. Trigger: the first migration where exports themselves are the bottleneck.
- Schema reconciliation (type widening, renamed columns). The spec pins names; a rename should fail loudly, not be guessed around.
- Continuous CDC comparison. This reconciles snapshots after batch loads; streaming parity is a different tool with watermark semantics.
- No credentials, no network; inputs are local exports, configuration is the spec file.
- Divergent-row output contains real row values by necessity (that is the deliverable); the JSON report should be handled like the data it describes. Logs carry only counts and table names.
- The 1,000-row cap also bounds how much data an incident report can leak into ticketing systems.
| Failure | Detection | Behavior | Recovery |
|---|---|---|---|
| Input file missing/unsupported | InputError before any tier |
Exit 3 with JSON error log | Fix path; .csv/.parquet accepted |
| Key or spec column absent from a side | SchemaMismatch before any tier |
Exit 3, names the columns | Fix spec or re-export |
| Malformed spec | SpecError at load |
Exit 3, names the field | Fix the JSON |
| Float aggregate with zero tolerance | Type check in tier 3 | Warning attached to the aggregate row | Cast to DECIMAL or set a tolerance |
| Massive divergence | Row cap in drill-down | Output truncated at 1,000 rows, flagged | Raise max_keys, or fix the migration first |
| Hash pathology stalling bisection | Depth guard at level 12 | Bucket exact-diffed instead of recursing | None needed; bounded slow path |
Two bugs, both caught by this repo's own verification machinery.
The first: drill-down hung forever on any divergence deeper than one bisection level. The per-level bucket assignment was hash(key, 'lvl', level) % fanout, and a probe showed the smoking gun: every key in a divergent bucket landed in the same child bucket, level after level. DuckDB's multi-argument hash combines argument hashes linearly, so changing the level salt shifts every key's hash by the same constant; modulo the fanout, the bucket translates instead of splitting. The fix routes the salt through string concatenation before hashing, hash(concat(key, ':', level)), which re-mixes properly (verified: constant shift set {0} before, uniform spread after), plus a depth guard that exact-diffs any bucket unsplit by depth 12. Commit 02122e8 carries the diagnosis; the failing tests were committed first.
The second came from the benchmark: at 5M rows the reconciler failed a table against an identical copy of itself, and the "same" SUM(amount) differed between runs in the last few ulps. Floating-point addition is order-dependent, and parallel aggregation reorders it. The demo spec now sums DECIMAL(18,2) (exact), tier 3 compares without float coercion, and a float aggregate with zero tolerance gets an explicit warning. A reconciliation tool that is itself nondeterministic is worse than no tool; both fixes are pinned by regression tests.
- Postgres and Snowflake adapters: each probe is one grouped query per side; the control loop already treats probes as opaque.
- Column-level drill-down (which column, not just which row, for drifted rows) by comparing per-column hashes at the leaf.
- Scheduled mode with a stored baseline, alerting only on new divergence.
- First metric to watch in real use: clean-pair runtime trend; when exports outgrow it, that is the trigger for live adapters.