What SQL data reconciliation actually means

Data reconciliation is the practice of confirming that two datasets which are supposed to represent the same underlying reality actually agree -- an old system and its replacement after a migration, a BI dashboard and the database it reads from, dev and prod after a deploy, or the same report re-run a day apart. It's less "find the one bug in this query" and more a repeatable discipline: a fixed set of checks you run every time data moves, gets copied between systems, or gets recomputed.

The 4-step reconciliation framework

Each step catches errors the previous one is structurally blind to. Skipping straight to the row count -- the single most common shortcut -- is also the single most common way a broken migration gets signed off as fine.

1. Row count check. The cheapest possible sanity check: does COUNT(*) match on both sides? Useful as a first pass, but as the live example below shows, it can pass cleanly while several real problems sit underneath it.

2. Control-total check. Compare a key aggregate -- SUM(amount), SUM(quantity) -- between the two datasets. Faster than a row-level diff on a huge table, but it has the same blind spot as the row count: a value that's too high on one row and too low on another by the same amount will still sum to a matching total.

3. Key-existence diff. Find rows that exist on one side but not the other, by LEFT JOINing in both directions and filtering for a NULL on the far side:

key-existence-diff.sql
-- rows in source but missing from target
SELECT s.order_id FROM orders_source s
LEFT JOIN orders_target t ON s.order_id = t.order_id
WHERE t.order_id IS NULL;

4. Column-level diff. For rows that exist on both sides, compare the actual values. On a narrow table, compare columns directly; on a wide table, hash the business columns into one value per row and compare hashes instead of dozens of individual comparisons -- covered in full in Row-Level Diffing in SQL: Which Columns Changed.

Advertisement

Live example: the row counts matched (live example)

Seven orders migrated from an old system (orders_source) into a new one (orders_target). Step 1 of the framework above -- the row count check -- passes without a hitch: 7 rows on each side. That alone would satisfy a reconciliation check that stops too early. Run the count-only check first, then the full diff query, and see what it was hiding.

The row count matched because one order that was genuinely lost during the migration (5004) was offset in the total by one unrelated extra order (5008) that landed in the target table from somewhere else -- two completely unrelated problems that canceled out at COUNT(*). The full diff query also surfaces a third, separate issue the count could never have shown: order 5003 exists correctly on both sides, but its amount changed from 899.99 to 950.00 in transit.

Key takeaways

  • Reconciliation is a four-step framework, not a single query -- row count, control total, key-existence diff, column-level diff.
  • A matching row count is the weakest possible signal: missing and extra rows can cancel out at COUNT(*) while the data itself is wrong.
  • A matching control total has the identical blind spot -- errors that offset each other in the aggregate still pass.
  • One LEFT JOIN in each direction, combined with a value comparison, surfaces missing rows, extra rows, and changed values in a single query.

Challenge: find what the count hid

Same source/target migration dataset used in the live example above. Three tasks -- confirm the row counts really do match, find the order that vanished, and find the order whose amount changed in transit.

Related deep dives in this series

This guide is the framework -- each of the six articles below is a full deep dive into one specific reconciliation problem, with its own live example.

SQL Data Reconciliation: Why Two Tables Don't Match

EXCEPT, MINUS, and FULL OUTER JOIN -- find exactly which rows and columns differ, with a real reconciliation example.

Why Two SQL Queries Return Different Row Counts

JOIN fan-out, WHERE vs JOIN condition placement, and implicit NULL exclusion -- the usual suspects behind a count mismatch, explained.

Why Doesn't My Dashboard Number Match the Database?

Caching lag, timezone cutoffs, hidden BI filters, and aggregation-grain mismatches, with a real revenue example.

Comparing Dev vs Prod Data: Spotting Environment Drift

Schema drift checks, row count comparisons, and checksum-based row diffing across environments.

Row-Level Diffing in SQL: Which Columns Changed

Column-by-column CASE diffing and row hashing to pinpoint exactly what changed in a record, not just that it did.

Why Yesterday's Report Total Changed

Late-arriving data, backdated corrections, and re-run vs incremental recompute -- root-causing metric drift.