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.
- 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
- Source of truth: Read raw landed events, including those the stream dropped as too late, or an authoritative outside record. The streaming output can only be compared against, never recomputed from.
- Settle horizon: For the trades feed a 24-hour budget already reaches 99.995%, so a pass a day after the window closes sees nearly everything.
- Same grain, same rules: Compute at the stream's keys, account and window start, and dedupe on event_id. A pass that counts duplicates the stream removed reports a delta that does not exist.
- Idempotent write: Replace the partition for each recomputed day, so a rerun or the 7-day trailing backfill leaves one correct copy.
- Explicit authority: Label every row. Consumers that need audit-grade numbers read only final rows.
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 pass | Reading | Likely cause |
|---|---|---|
| 0.06% to 0.09% | Healthy | The expected tail past 1 hour |
| 2.4%, all from 1 partition | Incident | An idle partition resumed more than L behind |
| 2.4%, starting at a migration | Incident | Key-sticky quarantine parked keys and was never replayed |
| 0.00% | Suspicious | The 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?
- p2 was dropped by the idle timeout, and its backlog resumed more than D + L behind
- p2 went idle and stalled the job watermark, so no partition's windows ever closed
- The pass double-counts p2's events because raw landing retried during that time
- A producer clock on p2 ran ahead and pushed honest events behind the watermark
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.