Reconciliation Passes

Every allowed-lateness budget leaves a tail, and some consumers cannot accept one: a ledger, a regulatory report, an invoice. A reconciliation pass is a batch job that recomputes the streaming output from the complete record after the tail has arrived, compares the 2, writes the corrected version as the version of record, and publishes how far apart they were.

Both paths read the same feed. The stream serves provisional totals within 2 minutes; the pass recomputes from every landed event and replaces the final partition.
  • Trades CDC feed (source, order database log)
  • Raw event landing (bronze, append-only, every event); idempotency: dedupe on event_id
  • Streaming aggregator (transform, 1-min windows, L = 1 hour); freshness: under 2 minutes
  • Live totals (storage, status = provisional)
  • Live dashboard (consumer, reads provisional)
  • Reconciliation pass (transform, daily batch); backfill: trailing 7 days; idempotency: replace partition
  • Delta check (quality gate, delta metric); alerting: delta above 0.5%
  • Final totals (gold, status = final)
  • Ledger and audit (consumer, reads final)
  • Trades CDC feed -> Raw event landing
  • Trades CDC feed -> Streaming aggregator
  • Streaming aggregator -> Live totals
  • Live totals -> Live dashboard
  • Raw event landing -> Reconciliation pass
  • Live totals -> Reconciliation pass
  • Reconciliation pass -> Delta check
  • Delta check -> Final totals
  • Final totals -> Ledger and audit

Decisions that make the pass trustworthy

Computing the delta

The pass and the comparison fit in one query: dedupe the raw events, aggregate at the stream's grain, then full-outer-join to the streaming output so a window missing on either side still shows up.

WITH latest AS (
  SELECT
    trade_id,
    account_id,
    trade_time,
    amount,
    status,
    row_number() OVER (
      PARTITION BY trade_id
      ORDER BY source_position DESC
    ) AS rn
  FROM raw_trade_events
  WHERE trade_date = DATE '2026-10-22'
),
recon AS (
  SELECT
    account_id,
    date_trunc('minute', trade_time) AS window_start,
    sum(amount) AS net_amount
  FROM latest
  WHERE rn = 1
    AND status <> 'CANCELLED'
  GROUP BY
    account_id,
    date_trunc('minute', trade_time)
)
SELECT
  coalesce(r.account_id, s.account_id) AS account_id,
  coalesce(r.window_start, s.window_start) AS window_start,
  coalesce(s.net_amount, 0) AS stream_amount,
  coalesce(r.net_amount, 0) AS final_amount,
  coalesce(r.net_amount, 0) - coalesce(s.net_amount, 0) AS delta
FROM recon AS r
FULL OUTER JOIN stream_minute_totals AS s
  ON r.account_id = s.account_id
  AND r.window_start = s.window_start
WHERE coalesce(s.window_start, r.window_start) >= TIMESTAMP '2026-10-22 00:00:00'
  AND coalesce(s.window_start, r.window_start) < TIMESTAMP '2026-10-23 00:00:00'

Roll those rows up into the day's delta metric: the share of windows whose amounts differ, the summed absolute delta, and the share of events that only the pass counted. The last number has an expected value. With L = 1 hour, about 0.07% of trade events should appear only in the pass, so the metric gets a band, and readings outside it in either direction point to a different problem.

Events counted only by the passReadingLikely cause
0.06% to 0.09%HealthyThe expected tail past 1 hour
2.4%, all from 1 partitionIncidentAn idle partition resumed more than L behind
2.4%, starting at a migrationIncidentKey-sticky quarantine parked keys and was never replayed
0.00%SuspiciousThe pass reads the streaming output instead of raw events
Do
  • Recompute from raw landed events, kept in every schema version
  • Match the stream's keys and dedupe rules exactly
  • Give the delta an expected band and alert on both sides of it
  • Replace whole partitions so reruns leave one copy
Avoid
  • Diff the stream against a copy of its own output
  • Let consumers mix provisional and final rows without a status column
  • Raise L to shrink the delta when the pass already corrects it
  • Treat a zero delta as proof of health

The daily pass reports 2.4% of events counted only by the pass, all stamped 09:58 to 10:26 and all from p2, which went quiet at 09:58 and resumed at 11:28. Other days sit near 0.07%. What happened?

About this lesson

This section is part of the Schema Drift: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.