Data reconciliation

What is data reconciliation?

Data reconciliation is the systematic comparison of two datasets or systems to confirm that they match, and to find where they do not. You put source and target side by side and check whether row counts, totals, and values line up.

The point is deliberately boring: do not trust a copy because it looks fine. A pipeline can silently lose two records, duplicate an amount, or truncate a field while every technical job still reports success.

The term appears in two worlds. Accounting teams reconcile bank statements with the general ledger. Data teams reconcile source and target after a migration, ETL run, ELT model, or CDC process. The question is the same: does my copy still match the original?

Reconciliation in a data pipeline

Every time data moves, something can disappear, duplicate, or change. A migration moves a whole database to a new platform. An ETL or ELT pipeline reads from a source, transforms, and writes to a warehouse or lakehouse. A change data capture stream keeps a copy in sync with the source.

In each case, the risk is quiet. A filter may be too strict. A retry may append duplicate rows. A type conversion may round values. A late-arriving record may fall outside the load window. The pipeline can still finish green.

That is why reconciliation belongs inside the process, not as a panic exercise after a user complains. Google's open-source Data Validation Tool, for example, is built for comparing source and target data across migrations and heterogeneous data stores.

Reconciliation in accounting

Bank reconciliation is the older and more familiar version. You compare bank statement lines with what was booked in the ledger. Each payment, receipt, fee, and transfer should appear on both sides with the same amount. Differences point to missed postings, duplicate entries, timing differences, or fraud.

Data reconciliation uses the same logic with datasets: two independent records of the same reality are compared so the differences become visible.

Common techniques

Start cheap and broad, then go deeper only where a difference appears.

• Row counts
  Source and target should have the same number of rows, adjusted for intentional filters or deduplication. This catches missing loads and duplicate loads quickly.

• Control totals
  Compare sums of important numeric fields: invoice amount, order quantity, balance, payment total. Equal row counts with different totals still mean something changed.

• Column aggregates
  Compare minimum, maximum, sum, average, null count, and distinct count by column. This narrows the difference to a column or field group.

• Hash comparison
  Calculate a hash over the values of a row on both sides. A single changed character produces a different hash. This is useful when totals can hide differences that cancel out.

• Key-by-key comparison
  Join source and target on a stable key and list rows that exist on one side but not the other, or rows where selected fields differ. This gives you the repair list.

A million-row example

Suppose the source system contains 1,000,000 order lines. After the nightly load, the warehouse has 999,998. The run succeeded, the dashboard opens, and nobody will count a million rows by hand.

A row-count reconciliation catches the two missing rows immediately. It does not yet say which rows are missing, so the next step is a key comparison on order line ID. That returns the two order lines absent from the target. Now you can inspect why they were dropped: a null in a required field, a date filter, a failed retry, or a source change.

The dangerous part is that the dashboard total may be only slightly wrong. Small plausible errors often live longest because they do not look broken.

Reconciliation versus testing and observability

Data reconciliation compares two known sides: does target match source?

Data testing checks one dataset against rules: are keys unique, are required fields filled, are values in range?

Data observability watches production data over time and alerts on freshness, volume, schema, distribution, or lineage anomalies.

All three matter. Reconciliation can pass while quality is poor if the source and target contain the same bad value. Testing can pass while two rows are missing if the remaining rows obey the rules. Observability can notice a strange drop in volume but still need reconciliation to prove where the loss happened.

What to watch out for

Unclear transformation logic
Source and target may intentionally differ because the target filters, aggregates, or deduplicates. Document those rules before comparing.

No stable key
Without a reliable key, row-level comparison becomes much harder. This is one reason master data management and good identifiers matter.

Late-arriving records
A source record may arrive after the pipeline window. Decide whether the comparison waits, tolerates a delay, or triggers a backfill.

Hashing messy data
Whitespace, case, date formatting, and decimal precision can produce false mismatches. Normalise values before hashing.

Checks nobody reviews
A reconciliation report is useful only when differences are routed to an owner who can decide whether they are expected or wrong.