Late Trade Settlements

Concepts covered: Late-Arriving Data

Here the 4 pieces run as one system. A brokerage serves 2 consumers from the same trades. The live buying-power view shows each account's net unsettled amount and must reflect a fill within 2 minutes. The settlement ledger needs exact totals per account per trade date. US equity trades settle on the next business day (T+1), so trades from Thursday 2026-10-22 settle Friday 2026-10-23.

InputShapeLateness and idleness
`trades` table, via CDCInsert per fill, update per correction or cancelp99 4 minutes; quiet overnight
`instruments` table, via CDCReference data, rare changesChanges a few times a day
Clearing-house settlement messagesA batch per cycle, owned by an outside firmSilent for hours, then up to 3 hours of messages

Decisions, piece by piece

PieceChoiceFailure it prevents
Schema: `trades`Quarantine, key-sticky, version-guarded writesA single bad migration stalling every account, or replays reverting state
Schema: `instruments`AdditiveStalls over new descriptive columns no money depends on
Schema: settlement messagesBlock-breakingA changed amount format posting wrong cash to the ledger
Watermark: `trades`D = 30 s, 2-minute idle timeout, 5 s future capOvernight quiet stalling results; a fast clock leaping the watermark
Watermark: settlementsEnd-of-cycle marker from the producerGuessing completeness for a source that knows it
Allowed latenessL = 1 hour on 1-minute account windows288.4 GB of state for 0.065 points
Reconciliation06:00 the morning after settlement date, trailing 7 daysAudit numbers that depend on the streaming tail

The settlement feed gets the strictest policy. Its volume is small, its consumer tolerates hours of delay, and its producer is an outside firm whose changes no contract in CI can catch first. A stall there costs a page; a silently misread amount column costs a ledger that disagrees with the clearing house. Cancellations that arrive days later are picked up by the trailing 7-day rerun, which replaces each partition again.

Raw landing takes every payload before any schema check, so a fixed parser can rerun over it later. Quarantined events replay into the aggregator in source order.
  • trades CDC (source, order database log)
  • instruments CDC (source, reference table log)
  • Settlement messages (source, clearing house, per cycle)
  • Raw landing (bronze, every payload, every version); idempotency: dedupe on event_id
  • Schema enforcement (quality gate, per-table policy); on error: key-sticky quarantine
  • Quarantine (queue, payload + schema version)
  • Exposure aggregator (transform, D = 30 s, L = 1 hour); freshness: under 2 minutes; parallelism: 3 partitions
  • Provisional exposure (silver, status = provisional)
  • Buying-power view (consumer, live)
  • Daily reconciliation (transform, 06:00 after settlement date); backfill: trailing 7 days; idempotency: replace partition
  • Delta gate (quality gate, events only in pass); alerting: above 0.5%
  • Final ledger totals (gold, status = final)
  • Settlement ledger (consumer, audit-grade)
  • trades CDC -> Raw landing
  • instruments CDC -> Raw landing
  • Settlement messages -> Raw landing
  • trades CDC -> Schema enforcement
  • instruments CDC -> Schema enforcement
  • Settlement messages -> Schema enforcement
  • Schema enforcement -> Quarantine
  • Schema enforcement -> Exposure aggregator
  • Quarantine -> Exposure aggregator
  • Exposure aggregator -> Provisional exposure
  • Provisional exposure -> Buying-power view
  • Raw landing -> Daily reconciliation
  • Provisional exposure -> Daily reconciliation
  • Daily reconciliation -> Delta gate
  • Delta gate -> Final ledger totals
  • Final ledger totals -> Settlement ledger

The clearing house starts sending amount in cents instead of dollars, with the same field name and type. Which piece catches it, and when?

Walking the 14:12 rename through the system

TimeWhat happensWhat each consumer sees
Thu 14:12A migration renames `qty` to `quantity` in `trades`Nothing yet
14:12 to 15:40Every `trades` event after the rename lacks `qty` and is quarantined, parked by account, the aggregator's state keyThe view marks the 1,900 accounts with a fill since 14:12 stale; 498,100 without one stay correct
Thu 15:40The parser now maps `quantity`; quarantine replays in source order through the version guardFlagged accounts catch up, and no state reverts
Thu 15:40Replayed events stamped 14:12 to 14:38 are past L: the watermark kept advancing on events read before the schema checkLive totals for those minutes stay short
Sat 06:00The pass recomputes 2026-10-22 from raw landing with the fixed parserThe ledger gets exact final totals; delta reads 0.10%

The delta of 0.10% sits above the healthy band and below the 0.5% alert, and it can be explained to the decimal: the usual 0.07% tail plus roughly 0.03 points from the quarantined window. The system is auditable because every gap between the live number and the final one traces to a policy someone chose and can defend.

In the rename incident, the 15:40 replay runs through the version guard, yet live totals for 14:12 to 14:38 stay short until the Saturday pass. Why?

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.