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.
| Input | Shape | Lateness and idleness |
|---|---|---|
| `trades` table, via CDC | Insert per fill, update per correction or cancel | p99 4 minutes; quiet overnight |
| `instruments` table, via CDC | Reference data, rare changes | Changes a few times a day |
| Clearing-house settlement messages | A batch per cycle, owned by an outside firm | Silent for hours, then up to 3 hours of messages |
Decisions, piece by piece
| Piece | Choice | Failure it prevents |
|---|---|---|
| Schema: `trades` | Quarantine, key-sticky, version-guarded writes | A single bad migration stalling every account, or replays reverting state |
| Schema: `instruments` | Additive | Stalls over new descriptive columns no money depends on |
| Schema: settlement messages | Block-breaking | A changed amount format posting wrong cash to the ledger |
| Watermark: `trades` | D = 30 s, 2-minute idle timeout, 5 s future cap | Overnight quiet stalling results; a fast clock leaping the watermark |
| Watermark: settlements | End-of-cycle marker from the producer | Guessing completeness for a source that knows it |
| Allowed lateness | L = 1 hour on 1-minute account windows | 288.4 GB of state for 0.065 points |
| Reconciliation | 06:00 the morning after settlement date, trailing 7 days | Audit 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.
- 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?
- Block-breaking enforcement, at the first message in the new format
- The reconciliation delta, at the next morning's pass
- The watermark, because the cycle marker arrives in a new format
- A distributional check on amount, at the first cycle in cents
Walking the 14:12 rename through the system
| Time | What happens | What each consumer sees |
|---|---|---|
| Thu 14:12 | A migration renames `qty` to `quantity` in `trades` | Nothing yet |
| 14:12 to 15:40 | Every `trades` event after the rename lacks `qty` and is quarantined, parked by account, the aggregator's state key | The view marks the 1,900 accounts with a fill since 14:12 stale; 498,100 without one stay correct |
| Thu 15:40 | The parser now maps `quantity`; quarantine replays in source order through the version guard | Flagged accounts catch up, and no state reverts |
| Thu 15:40 | Replayed events stamped 14:12 to 14:38 are past L: the watermark kept advancing on events read before the schema check | Live totals for those minutes stay short |
| Sat 06:00 | The pass recomputes 2026-10-22 from raw landing with the fixed parser | The 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?
- The version guard rejected those events because their source positions were too low
- Key-sticky quarantine discards events older than the idle timeout
- They trail the newest event time by more than D + L, so their windows had closed
- The settlement feed's block-breaking policy paused every table in the pipeline
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.