Data reconciliation: a SQL example and an exception workflow

Two tables can contain the same number of rows and still disagree about which orders exist or how much they are worth. Data reconciliation compares records across systems, identifies differences, and follows those differences through investigation and resolution.

It is useful after a migration, a transformation, or a delayed load. IBM's data reconciliation overview describes comparison and discrepancy resolution as a process that complements data validation. Here, we will build a small comparison and define what should happen when it finds a mismatch.

Define what should match

Before writing a join, agree on the scope of the comparison. An order table with one row per order will not reconcile directly with an order-line table. A source captured at noon may differ legitimately from a target captured ten minutes earlier.

Write down the matching key, row grain, extraction cutoff, and fields to compare. Decide how to handle deleted records, late updates, timezones, and currency units. Preserve the original values when applying normalization so an investigator can trace a difference back to its source.

A reconciliation query reports disagreement. It should not decide that one side is authoritative simply because it is named source.

A runnable SQL reconciliation example

This self-contained SQLite query compares two illustrative snapshots. Order IDs are unique and non-null. Amounts use integer cents, and every amount is in USD. The example deliberately includes a value mismatch, a missing order, an unexpected order, and a null-versus-zero difference.

WITH
source_orders(order_id, amount_cents) AS (
  VALUES (101, 12000), (102, 2500), (103, 900), (105, NULL)
),
target_orders(order_id, amount_cents) AS (
  VALUES (101, 12000), (102, 2600), (104, 900), (105, 0)
),
all_keys AS (
  SELECT order_id FROM source_orders
  UNION
  SELECT order_id FROM target_orders
),
comparison AS (
  SELECT
    k.order_id,
    s.amount_cents AS source_amount_cents,
    t.amount_cents AS target_amount_cents,
    CASE
      WHEN s.order_id IS NULL THEN 'target_only'
      WHEN t.order_id IS NULL THEN 'source_only'
      WHEN s.amount_cents IS NOT t.amount_cents THEN 'value_mismatch'
      ELSE 'match'
    END AS result
  FROM all_keys AS k
  LEFT JOIN source_orders AS s ON s.order_id = k.order_id
  LEFT JOIN target_orders AS t ON t.order_id = k.order_id
)
SELECT * FROM comparison
WHERE result <> 'match'
ORDER BY order_id;

Expected output:

SQLite's IS NOT provides a null-aware comparison: null differs from zero, while two nulls compare alike. Check the equivalent operator in your warehouse before porting this query. The SQLite expression reference documents this behavior.

The query is read-only and uses inline sample data. It does not connect to a source system or change records.

Check keys before comparing values

The example assumes one row per key. Duplicates can multiply joined rows and make the output misleading. Before applying the comparison to real tables, check each side for duplicate or null keys. For an existing table named source_orders, use:

SELECT order_id, COUNT(*) AS row_count
FROM source_orders
GROUP BY order_id
HAVING order_id IS NULL OR COUNT(*) > 1;

Run the same check on the target. Stop and resolve any unexpected results rather than dropping duplicates until the query looks clean. If repeated keys are valid, choose a composite key or aggregate both datasets to the same documented grain.

For large datasets, compare a bounded partition first. Record which partitions and fields were checked; a passing sample is not proof that the entire dataset reconciles.

Report coverage alongside the differences

For the synthetic example above, the source has four rows and the target has four rows. Their union contains five distinct keys. One key matches, two have different amounts, and two appear on only one side. Reporting just "four rows on each side" would hide every discrepancy.

If you report a mismatch rate, define its denominator. Here it is four discrepant keys divided by five distinct keys across both snapshots, or 80%. That describes this fixture only; it is not a benchmark or an estimate of overall data accuracy. Two null amounts count as equal under this query, even if another quality rule says an amount is required.

Use these checks before declaring a run reconciled:

A schema check, extraction-completeness check, and reconciliation result are separate pieces of evidence. Keep all three with the run.

Reproduce the example and its edge cases

Download the Python example suite, inspect it, and run it locally with Python 3.10 or later:

python3 data_quality_examples.py

It uses the standard library and an in-memory SQLite database. It reads no credentials, contacts no services, and includes 13 tests covering both this article and the data-contracts article. The reconciliation cases check the displayed output, equal values, equal nulls, offsetting errors, invalid keys, incomplete extraction, and empty inputs.

The wrapper rejects unconfirmed extraction and unexpected empty inputs before running the SQL. Its extraction_complete input is a caller-supplied assertion, not an automatic completeness detector. In a real pipeline, derive that decision from the extraction job and expected coverage.

The inline SQL is the comparison step only. It cannot detect a missing upstream partition, a wrong extraction cutoff, or a field that was never selected. The sample has no performance benchmark, warehouse adapter, tolerance rules, or automated repair. Those require validation against your data and execution environment.

Investigate differences before correcting them

Classify each discrepancy using evidence from the load and the business record. A missing order could be a delayed arrival, a filtered record, or a failed write. Those causes require different repairs.

Keep the comparison run ID, input snapshots, query version, and discrepancy counts together. Store sensitive row-level evidence in an appropriately restricted system. A notification can link to that evidence without copying the entire dataset into a message.

If the problem is late upstream data, follow the dbt source freshness guide. If recovery requires historical processing, use the Airflow backfill guide to define that execution scope. Reconciliation should run again after the repair.

Route unresolved exceptions to a person

Automate corrections only when a documented rule identifies the intended result. Ask for review when the evidence is ambiguous or proceeding with a known discrepancy changes what a consumer can rely on.

A useful review request states the affected dataset, comparison cutoff, discrepancy type, downstream impact, and proposed next step. Ask a specific question, such as whether a named report may proceed with an identified missing partition. An approval for that report should not authorize overwriting source records.

ActionBox can collect the human decision while your comparison job and repair process remain responsible for the data. Its hosted decision architecture separates the decision from the execution outcome. Use the verified callback workflow if a response will resume work.

Record an approved exception as an exception. Keep the failed comparison and the reason for proceeding visible, then assign the follow-up repair. Approval does not make the datasets equal.

Check the workflow before using it on a release

Test matching data, one-sided records, unequal values, null values, and invalid keys. Also test a failed extraction: an empty or missing input must not quietly become a successful reconciliation.

For each run, report what was compared, what differed, and what remains unresolved. If the mismatch reflects an unclear producer-consumer expectation, document it in a data contract before the next delivery.

Verification notes

Examples checked September 7, 2026 with Python 3.11.9 and SQLite 3.45.1 using synthetic fixtures. The downloadable suite reports 13 passing tests. No production datasets, warehouse performance measurements, or customer results are represented here. Linked official documentation supports the tool-specific behavior; the workflow recommendations are this article's design guidance.

Create a free Source · Design the human review step