Data Reconciliation, Explained: Proving Your Data Survived the Move

A nightly job moves the orders table from an on-prem Postgres instance to a cloud warehouse. It has run every night for three months. The orchestrator shows green. The row counts in the job log match, give or take the rows written during the run window. Nobody has filed a ticket. And for roughly six of those twelve weeks, a widened column on the target side silently truncated total_amount for any order over $10,000 — a type cast the migration script got wrong on day one — and every downstream revenue dashboard has been quietly wrong the entire time.

Nothing about that failure shows up in a mover's exit code. The extract step succeeded. The load step succeeded. The count of rows written equals the count of rows read, because truncating a number doesn't delete the row it lives in. A copy job's success signal answers one question — did every row that started the trip arrive at the other end — which is much narrower than "did the data arrive intact." The two get conflated constantly, and the gap between them is where silent data corruption lives.

Reconciliation is the practice of closing that gap: independently proving that source and target agree, after the fact, using a check that has nothing to do with the pipeline that moved the data. It sounds like a formality until you've been the person explaining to finance why Q2 revenue in the warehouse doesn't match the ledger. This post works through what reconciliation checks, the four levels of rigor you can apply, the principles that keep a comparison honest, a full worked example, the metrics that make the practice auditable, and the ways teams get it wrong even when they're trying to get it right.

Reconciliation is a control, not a report

It helps to place reconciliation inside data governance before getting into mechanics. A pipeline that copies data from zone A to zone B is a preventive mechanism at best — it's trying to make the copy correct as it happens. Reconciliation is a detective control: it runs after the fact and produces evidence about whether the copy succeeded, independent of whatever the mover reported. A mover that grades its own homework isn't a control, it's a self-report — and self-reports pass exactly the failure modes the control exists to catch.

That points at the one property a reconciliation check can never give up: independence. If the recon query reuses the same extraction view or transform logic as the pipeline it's checking, any bug shared between the two paths passes cleanly on both sides. A cast that truncates a decimal on the way into the warehouse will also truncate it if the recon check reads the value through the same view that already applied the cast — so the check has to touch both stores through a path that doesn't trust the pipeline to have gotten anything right.

Mechanically, reconciliation is a loop, not a one-shot script. Figure 1 lays out the shape: a recon plane sits beside the data mover — not inside it — and works through the same six stages on every run:

1. Snapshot both sides as of the same cutoff.
2. Compare at whichever level the table warrants.
3. Classify each difference: matched, missing-in-target, extra-in-target, or altered.
4. Report the classification in actionable detail.
5. Remediate — backfill, investigate, or fix the pipeline, depending on the class.
6. Re-run to confirm the gap actually closed — reconciliation stops at "confirmed gone," not "found a problem."

There's also a compliance dimension worth naming, especially inside a regulated shop. When an auditor asks how you know a risk figure in the warehouse matches the ledger it came from, "the ETL job finished successfully" isn't an acceptable answer. What they want is the artifact reconciliation produces: row counts with a timestamp, a hash comparison, exceptions with sign-off. Frameworks like BCBS 239 for risk-data aggregation are built around exactly this expectation — that a bank can demonstrate a reported number traces back to its source without unexplained gaps. Reconciliation logs are that evidence. A green dashboard is not.

Profiling answers a different question than reconciliation

The two words get used interchangeably, and they shouldn't be. Data profiling examines one dataset and describes its statistical shape — how many rows, how many nulls, what the values look like, how they're distributed. It answers the question "what does this data look like?" and needs no second copy to answer it. Reconciliation compares two datasets that are supposed to agree and answers a different question entirely: "are these two copies the same?" Profiling has no notion of a mismatch, because there is nothing to mismatch against; reconciliation has no opinion about whether the data is any good, only about whether both sides hold the same data — faithfully copied garbage reconciles perfectly.

In a zone-A-to-zone-B move, profiling comes first, for three concrete reasons. It establishes the baseline you'll reconcile against — row counts, null rates, and value ranges captured with a timestamp before the mover touches anything. It surfaces the dirty data that will break the move itself: orphaned foreign keys, out-of-range dates, encodings the target engine renders differently. And it tells you how to design the reconciliation — which columns are stable enough to hash, which keys are actually unique, where a tolerance will be needed because the source itself is noisy.

A useful profile covers a handful of metric families, each cheap to compute in a single pass:

• Volume — row count, table count, bytes; the denominators everything else divides by.
• Completeness — null rate and empty-string rate per column; the two are different defects and profiling is where you learn whether your source distinguishes them.
• Uniqueness — distinct counts, duplicate rate, and whether the column you planned to use as a recon key is actually unique (the assumption that most often dies here).
• Distribution — min/max/mean/stddev, percentiles, and top-N frequent values on numeric columns; a lopsided histogram today explains a "mysterious" aggregate mismatch next week.
• Shape and conformance — string length ranges and pattern conformance (do the emails match an email pattern, do the phone numbers fit one format or five).
• Temporal and relational — earliest/latest timestamps, freshness lag, and orphaned-foreign-key rates across related tables.

Tooling-wise you rarely need to build this from scratch. For a one-off look at a table, ydata-profiling (the successor to pandas-profiling) generates the full statistical report from a dataframe. For profiles you want to keep enforcing, Great Expectations and Soda Core turn profile findings into declarative expectations that run in CI; dbt tests do the same natively inside a warehouse project; and Deequ or AWS Glue Data Quality compute constraint checks at Spark scale. Note what none of these are: a reconciliation tool. They each look at one dataset at a time — DMS-style validation and data-diff live on the other side of the fence.

The principles mirror reconciliation's, one step earlier. Profile both zones with the same metric definitions, because a "null rate" computed two different ways is a false alarm generator. Capture the source profile before the move and store it as an artifact — it is the only record of what the data looked like on the day you promised to move it faithfully. Let the profile set the recon tolerances instead of guessing them. And re-profile on a schedule, because a profile is a snapshot with a timestamp, not a fact about the table forever.

The four levels of reconciliation

Reconciliation is not one technique, it's an escalation ladder. Each level catches something the one below it structurally cannot, and each costs more — in compute, in query complexity, or both. Most production strategies run several levels at once, reserving the expensive ones for the rows the cheap ones flag.

Level 1 — existence and count checks. Count the rows in a table on each side (and, one level up, count the tables in a schema). This is the cheapest check you can run and it catches the crudest failure — a table that silently didn't load, a job that died mid-partition, a filter that excluded more rows than intended. It is completely blind to content: a table with the exact right row count can still have every value in it wrong.

Level 2 — aggregate fingerprints. Run cheap aggregate functions per column — SUM, MIN, MAX, AVG on numeric columns, a count of NULL values, a count of distinct values, a sum of string lengths on text columns. This catches numeric drift and truncation at almost the same cost as a count check, since the database computes these in a single pass. Its weakness is what makes it cheap: aggregates can hide errors that cancel out. If one row's amount got truncated down by $50 and another corrupted up by $50, the SUM still matches.

Level 3 — block or bucket hash comparison. Split the table into buckets (a common approach: primary key modulo N), hash the canonicalized contents of each bucket on both sides, and compare the digests. A single altered cell anywhere in a bucket changes that bucket's hash, so this level catches any content difference — not just numeric drift — at roughly the cost of a count check when most buckets match, because a mismatched digest is the only signal that needs investigating. This isn't theoretical — it's how production tools do this. AWS Database Migration Service partitions each table by primary key (10,000 rows per partition by default) and logs failures to a dedicated awsdms_validation_failures_v1 table; it requires a primary key or unique index to partition on. Percona's pt-table-checksum chunks MySQL tables the same way and deliberately lets the checksum queries flow through replication, so a replica's checksum comes from the same statement the primary ran. Datafold's open-source data-diff calls this hashdiff: checksum first, then recursively bisect only the mismatched partitions — the project reports verifying roughly 25 million rows in seconds when the two sides mostly agree, because the expensive part never runs on rows that already matched at the checksum level.

Level 4 — full row-by-row / cell-by-cell diff. Pull the actual rows flagged by level 3 (or, on a small enough table, every row) and compare them cell by cell. This is the only level that names the exact primary keys and columns that disagree, so it's the only one that produces something a human or a backfill job can act on directly. It's also the most expensive — reading and comparing full row content at scale is I/O-bound, which is exactly why levels 1 through 3 exist: to narrow level 4's scope from "the whole table" to "these 4,000 rows in these three buckets."

Principles: how to compare two moving targets

Knowing the four levels doesn't save you if the comparison itself is built wrong. A handful of principles separate a recon check that produces trustworthy signal from one that produces noise nobody reads — get these wrong and the numbers coming out the other end won't mean what you think they mean.

Agree on a consistency cutoff. A source still taking writes and a target still catching up cannot be compared honestly at "now" on both sides — "now" on the source includes rows the target hasn't received yet, and those look like missing data even though nothing is wrong. The fix is a watermark: agree on a cutoff — a timestamp, a log sequence number, a batch id — and reconcile only rows at or before it, on both sides. Rows written after the cutoff are excluded, not counted as a mismatch. Figure 3 shows this as two overlapping timelines collapsing onto the same closed window: the reconciliation only ever looks at the part both sides have already settled.

Normalize before you compare. Two systems that agree on every value can still disagree on every hash, because different engines render the same value differently by default: floats round differently, DECIMAL scales pad or truncate trailing zeros, timestamps render local time on one side and UTC on the other, collation differs on case and accent, a trailing space survives one engine and gets stripped by another, NULL and empty string are the same thing to one system and different things to another. None of these are data errors — they're rendering differences — but hashing raw values without canonicalizing first turns each one into a mismatch indistinguishable from a real one. Convert both sides to one agreed form before comparing, always.

Every comparison needs a key. Without a primary key or a stable business key, you can compare two tables only statistically — counts, sums, distributions — because there's no way to say which row on one side corresponds to which row on the other. Row-level classification into missing / extra / altered is an identity operation: it requires knowing that source row 4471 and target row 4471 are supposed to be the same row before you can say whether they agree.

Partition to localize. Comparing an entire table as one unit tells you the table is wrong, not where. Comparing it per bucket points a mismatch at 10,000 rows instead of 120 million — the mechanism behind level 3's cost advantage, and worth applying even when you're not hashing, just to keep any investigation bounded.

Independence, restated. Worth repeating as a principle, not just an architectural nice-to-have: the recon query should hit both stores directly, with its own minimal logic, rather than reusing the pipeline's transformation code. The value of the check evaporates the moment it shares a bug with the thing it's checking.

Tolerance is a decision, not a default. Some data demands exact agreement — a financial ledger has zero acceptable tolerance, because every cent is somebody's money. Some data tolerates a small mismatch rate — behavioral analytics might treat 0.01% as normal variance from sampling or late-arriving events. Whatever the number is, it's a decision a data steward made and wrote down, not a threshold an engineer picked at 2am to make a noisy report stop paging them.

Worked example: Postgres to warehouse, end to end

Back to the orders table from the intro — roughly 120 million rows, moved nightly from Postgres (zone A) to a cloud warehouse (zone B). Here's a real reconciliation pass against it, level by level, with the exact SQL run on both sides.

First, the cutoff and the count. Both sides get the same watermark — the run's cutoff timestamp — and the count query only touches rows at or before it:

-- Run against BOTH zone A (Postgres) and zone B (warehouse).
-- :cutoff is the agreed watermark for this recon run, e.g. the
-- max updated_at the pipeline had fully committed as of its last run.
SELECT
  count(*)          AS row_count,
  max(updated_at)   AS max_updated_at
FROM orders
WHERE updated_at <= :cutoff;

If the counts disagree here, stop — there's no point computing aggregates or hashes over a set of rows that doesn't even have matching cardinality on both sides. If they agree, move to level 2:

-- Run against BOTH sides, same :cutoff.
SELECT
  count(*)                                AS row_count,
  sum(total_amount)                       AS sum_total_amount,
  min(order_id)                           AS min_order_id,
  max(order_id)                           AS max_order_id,
  count(*) FILTER (WHERE customer_id IS NULL) AS null_customer_id_count,
  sum(length(status))                     AS sum_status_length
FROM orders
WHERE updated_at <= :cutoff;

This is the query that would have caught the truncation bug from the intro within a day, not three months — sum_total_amount would have disagreed by exactly the truncated overflow the first night a >$10,000 order landed. In this walkthrough, assume level 2 comes back clean (the drift is small enough, or offset enough by other rows, to hide inside the aggregate — the whole reason level 2 alone isn't sufficient). Level 3 is where it actually surfaces:

SELECT order_id % 1024 AS bucket,
       count(*) AS row_count,
       md5(string_agg(
         order_id::text || '|' ||
         coalesce(customer_id::text, '') || '|' ||
         to_char(total_amount, 'FM999999990.00') || '|' ||
         coalesce(status, '') || '|' ||
         to_char(updated_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS'),
         ',' ORDER BY order_id
       )) AS bucket_hash
FROM orders
WHERE updated_at <= :cutoff
GROUP BY 1;

Every normalization choice here is doing real work. coalesce(customer_id::text, '') makes a NULL render identically on both sides instead of one engine printing the literal string NULL and the other nothing. to_char(total_amount, 'FM999999990.00') pins the decimal to a fixed format regardless of how each engine's numeric type would otherwise render it. Rendering updated_at AT TIME ZONE 'UTC' removes any chance one side stores local time and the other UTC — without it, every row mismatches for a reason unrelated to the data being wrong. And ORDER BY order_id inside string_agg is not cosmetic: without a deterministic order, the hash would depend on whatever order the database happened to return rows in, making it useless even on identical data.

With bucket digests computed on both sides, a small script compares them and classifies each bucket:

#!/usr/bin/env python3
"""Compare bucket_hash results from zone A and zone B and classify
each bucket as matched, count_mismatch, or hash_mismatch."""
import csv
import sys
from pathlib import Path


def load_buckets(path):
    with open(path, newline="") as f:
        return {
            int(row["bucket"]): (int(row["row_count"]), row["bucket_hash"])
            for row in csv.DictReader(f)
        }


def main(argv):
    if len(argv) != 2:
        print("usage: compare.py <zone_a.csv> <zone_b.csv>", file=sys.stderr)
        return 2

    a = load_buckets(argv[0])
    b = load_buckets(argv[1])
    all_buckets = sorted(set(a) | set(b))

    matched, count_mismatch, hash_mismatch = [], [], []
    for bucket in all_buckets:
        a_count, a_hash = a.get(bucket, (0, None))
        b_count, b_hash = b.get(bucket, (0, None))
        if a_count != b_count:
            count_mismatch.append(bucket)
        elif a_hash != b_hash:
            hash_mismatch.append(bucket)
        else:
            matched.append(bucket)

    total = len(all_buckets)
    match_rate = len(matched) / total if total else 1.0

    print(f"Buckets checked: {total}")
    print(f"Matched:         {len(matched)} ({match_rate:.4%})")
    print(f"Count mismatch:  {len(count_mismatch)} {count_mismatch[:10]}")
    print(f"Hash mismatch:   {len(hash_mismatch)} {hash_mismatch[:10]}")

    if count_mismatch or hash_mismatch:
        return 1
    return 0


if __name__ == "__main__":
    sys.exit(main(sys.argv[1:]))

Say bucket 417 comes back as a hash mismatch. Fetching all ~120,000 rows in it and diffing directly is one option, but on a hot bucket it's often cheaper to re-bucket just that slice with a finer modulus — split it into 1,024 sub-buckets, hash those, and recurse into whichever one still disagrees. Either way, the drill-down's cost stays proportional to the size of the mismatch, not the table — Figure 4 shows this as a bisection tree that only branches where a hash actually disagreed, collapsing every matching subtree without a second look.

A few bisections later, the drill-down bottoms out on the exact rows, and the final recon report for this run looks like this:

Reconciliation report — orders — cutoff 2026-07-16T23:00:00Z
Zone A (Postgres, source):        119,842,003 rows
Zone B (warehouse, target):       119,842,003 rows

Matched:            119,841,844 (99.99987%)
Missing in target:  0
Extra in target:    0
Altered:             159

Root cause: total_amount cast to NUMERIC(10,2) on the warehouse load
step truncates any value >= 10,000,000.00 (the widened source column
allows up to 12 integer digits). All 159 altered rows have
total_amount > $10,000. Ticket DATA-4821 opened; remediation:
backfill from source, widen target column to NUMERIC(14,2).

This is the same bug from the intro. It was invisible to the level 1 count check (no row was ever lost) and survived level 2 (a $10,000+ order is rare enough that the drift didn't stand out against a sum in the tens of millions). Level 3 is what actually found it, at a fraction of the cost of diffing all 120 million rows directly.

Metrics that make reconciliation governable

A reconciliation check that runs once and gets forgotten is a one-time audit, not a control. Turning it into something a data steward can govern means tracking a small set of metrics over time, not just reading pass/fail off the latest run.

Match rate is the foundational number: matched keys divided by total source keys inside the window. Its complement, the mismatch rate, should always be broken down by class rather than one blended figure — the missing-in-target rate, the extra-in-target rate, and the altered rate each point at a different root cause and remediation. Missing rows usually mean a filter or load failure, fixed with a backfill. Extra rows usually mean a duplicate load, fixed by investigating before deleting anything. Altered rows mean a transform bug or stale cache, fixed by patching the pipeline, not by re-running it. Collapsing all three into one number throws away exactly the information that says what to do next.

Reconciliation coverage — the percentage of tables, columns, or rows actually enrolled in a recon check — is the metric most likely to be quietly overstated, because an unenrolled table produces zero recorded mismatches by simply never being checked. Zero mismatches and zero coverage look identical on a dashboard that only shows the mismatch rate.

Timeliness covers two clocks: recon lag, the gap between an event happening in the source and reconciliation verifying it, and the incident-response pair MTTD/MTTR (mean time to detect and resolve a real discrepancy). A check that only runs weekly can have a perfect match rate and still leave a broken pipeline running wrong for six days before anyone notices.

False-mismatch rate — the share of reported mismatches that turn out to be normalization noise rather than real problems — predicts whether people keep trusting the report at all. A check that cries wolf on timezone rendering every run trains readers to stop reading it, functionally the same as no check.

Finally, trend and drift: match rate should be read as a series across runs, not today's single snapshot. A match rate slowly declining over three weeks is a different and more urgent signal than one that dropped once and recovered.

These map onto the standard data-quality dimensions used across the industry (DAMA-style frameworks): completeness maps to the missing- and extra-in-target rates, accuracy and consistency map to the altered rate, timeliness maps to recon lag. Reconciliation isn't separate from data quality management — it's the mechanism that measures several of its core dimensions with hard numbers instead of a survey. Figure 5 shows a single run fanning out into the scorecard a steward actually reviews, rather than the raw pass/fail the pipeline log shows.

How this fails in practice

Most reconciliation failures aren't exotic — the same handful of mistakes, showing up on a different table each time.

The recon is red every morning — and always self-heals

Symptom: the nightly recon reports thousands of "missing in target" rows every run, and by morning the count has dropped to zero on its own. Cause: no consistency cutoff — the check compares the source's live state against a target still catching up on replication lag, so rows written near the end of the window look "missing" purely because they haven't arrived yet. Fix: agree on a watermark and exclude rows past it on both sides.

A thousand mismatches, zero real ones

Symptom: the recon report is red on essentially every run, the mismatch count is large, and every one someone bothers to investigate turns out to be identical data. Cause: a normalization gap — float rendering, timezone formatting, NULL vs empty string, collation — makes the hash disagree on rows that are, by any reasonable definition, the same. Once this happens a few times, people start ignoring the report entirely — the reconciliation equivalent of alert fatigue. Fix: canonicalize both sides before hashing, track false-mismatch rate as its own metric, and treat every recurring noise class as a bug in the recon query.

Counts matched for six months while the data rotted

Symptom: the only recon check running is a row count, it has passed every night for six months, and a downstream dashboard has quietly been wrong the whole time. Cause: count-only reconciliation is structurally blind to content — a widened column, a lossy type cast, or a wrong join key can all leave the row count untouched while corrupting every value in a column. Fix: add aggregate fingerprints and bucket hashes on the columns money or decisions depend on, and escalate to deeper levels automatically on tables flagged as critical.

The recon confirmed the pipeline's own bug

Symptom: the recon check has passed every run since launch, and the same truncation bug from the intro was still found — by an outside audit, months later. Cause: the recon query reused the pipeline's own extraction view, so the same cast that corrupted the load also corrupted what the recon check believed the source value was — both sides agreed, computed by the same buggy logic. Fix: enforce independence — a separate query path against the raw source, ideally under separate credentials — and pair it with a periodic coverage review so new tables get enrolled.

Trade-offs

Depth vs. cost

A full row-by-row diff on every table, every run, at billions of rows is a cluster-sized bill every day. Checksum-and-bisect makes the cost roughly proportional to the differences actually found — the entire reason algorithms like Datafold's hashdiff exist rather than everyone just diffing full tables. The practical answer is to tier tables by criticality: a ledger table justifies level 4 on every run because the cost of missing an error there is unbounded; a clickstream table can run level 1 every pass and level 3 on a sample, since an occasional undetected anomaly there is bounded and small.

Where the comparison runs

Pushing the hashing work down into each engine — computing bucket digests inside Postgres and the warehouse, shipping only the small digests across the network — moves kilobytes instead of terabytes, but demands the same normalization SQL produce identical output on both engines, the same float-and-timezone problem from earlier now baked into two SQL dialects that must agree byte for byte. Pulling raw rows out to a neutral compare engine is simpler and exact by construction, but slower and expensive at scale — and when zone A and zone B sit in different jurisdictions, moving raw rows to a third location just to compare them can run into data-residency requirements the digest-only approach never triggers.

Reconcile the checklist, not just the data

Everything above is a repeatable operational checklist as much as a set of SQL patterns — agree on a cutoff, normalize, escalate through the levels, classify, remediate, track the metrics, re-run. That's exactly the kind of procedure that's easy to get right the first time and easy to let quietly decay the tenth, which is the same problem Agent Skills and slash commands are built to solve for an agent inside Claude Code.

The data-engineer persona pack in Noddle Deck ships skills and commands scoped to exactly this kind of work, carrying the same structure this post walks through — so an agent reaching for them mid-task doesn't have to re-derive the cutoff-and-normalize principles from scratch each time.

noddle-deck pack install data-engineer

References

• Datafold — Data reconciliation: technical best practices
• data-diff technical explanation (checksum + bisection)
• AWS DMS data validation
• AWS blog — Optimize data validation using DMS validation-only tasks
• Percona Toolkit — pt-table-checksum
• Soda — Guide to data quality dimensions
• dbSeer — Data migration validation guide