Source-to-Warehouse Reconciliation Audit Workflow

Reconcile row counts, monetary totals, and checksums between source systems and the warehouse on a schedule, routing mismatches by magnitude and age.

intermediate~30 mindata-pipelinedata-qualityaudit

Objective

Detect and attribute data divergence between operational systems and the warehouse with a standing reconciliation that quantifies every mismatch.

Environment

Access to both source and warehouse query surfaces
A scheduled job slot for reconciliation runs
Owners identified per data domain

Steps

1. Pick the reconciliation keys

Choose the invariants that matter per domain: row counts per day, monetary totals, distinct entity counts, or per-key checksums for sensitive tables.

Expected state: Each domain has two or three named invariants rather than a generic count-everything pass.

2. Run on a fixed schedule with lag awareness

Execute reconciliation after pipeline completion, comparing bounded windows (yesterday) rather than open-ended now, so in-flight rows do not read as mismatches.

Expected state: Comparisons use aligned time windows and account for replication lag.

3. Quantify mismatches precisely

For each failed invariant, compute the exact difference: rows missing, extra, or differing, and the affected window and keys.

Expected state: Every mismatch carries a number, a window, and sample keys, not just a red flag.

4. Route by magnitude and age

Send material mismatches (monetary or entity-level) to an urgent queue with a domain owner; tolerance-level drift goes to a weekly data-quality review.

Expected state: Reconciliation output feeds two queues with explicit entry criteria.

5. Attribute and close each mismatch

Trace each mismatch to its cause class (pipeline bug, late data, source change, timezone edge), fix or accept it, and record the disposition.

Expected state: No mismatch closes without a cause class and a decision recorded.

Expected outcome

A standing reconciliation that quantifies source-versus-warehouse divergence, attributes every mismatch to a cause, and closes each with a recorded disposition.

Verification

The mismatch ledger shows every historical divergence with cause and disposition; an intentionally introduced row deletion is detected in the next run with the exact key.

Cleanup

Prune the mismatch ledger by retention policy while keeping aggregate drift history.

Trust in the warehouse is earned by arithmetic

Consumers trust dashboards until one is provably wrong once. A standing reconciliation of counts, totals, and checksums converts that trust from anecdote to arithmetic, and its mismatch ledger becomes the memory of every divergence.

-- daily reconciliation (window: yesterday, aligned TZ)
SELECT
  s.order_date,
  s.row_count   AS source_rows,
  w.row_count   AS wh_rows,
  s.total_cents AS source_total,
  w.total_cents AS wh_total
FROM source_daily s
FULL JOIN wh_daily w USING (order_date)
WHERE s.row_count IS DISTINCT FROM w.row_count
   OR s.total_cents IS DISTINCT FROM w.total_cents;

-- order_date | src_rows | wh_rows | src_total | wh_total
-- 2026-10-19 |     41207 |    41206 |  8904231 |   8904207  <- 1 row, $0.24
-- disposition: late-arriving row, accepted + backfilled

Windowed comparison kills false positives

Comparing now-to-now between an operational system and a warehouse that lags by design generates permanent phantom mismatches. Bounding both sides to completed windows (yesterday, last hour, closed batch) makes every remaining mismatch real and worth routing.

The mismatch ledger is the pipeline’s memory

Individual mismatches get fixed and forgotten, so the same cause class recurs quarterly. A ledger recording each divergence with cause and disposition reveals patterns — which pipeline stage leaks, which source changes break assumptions — and turns anecdotes into engineering priorities.