Counted Once, Remembered Forever
A hard Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.
- Domain
- Pipeline Design
- Difficulty
- hard
- Seniority
- mid
- Asked in
- Fidelity
Problem
A retail brokerage processes about 50 million trade executions a day across 12 million accounts, and the risk desk needs account positions updated within seconds so it can freeze accounts that breach exposure limits while the market is still open. The same execution events feed an end-of-day regulatory report where every trade must be counted exactly once, and that report has to be reproducible byte-for-byte months later when an auditor asks what was filed. Design the pipeline that serves both consumers.
Business requirements
- The risk desk says it cannot wait for an overnight job. If an account blows through its exposure limit at 11am, they need to see it and freeze the account within seconds, not the next morning.
- Compliance says the regulatory report must count every execution once and only once. A duplicate or a dropped trade in the filing is a reportable error.
- When an auditor asks six months later what we filed on a given day, we have to reproduce that exact report from the underlying data, not approximate it from today's state.
Worked solution and explanation
Why this problem exists in real interviews
This is one execution stream serving two consumers with opposite correctness budgets: the risk desk wants positions within seconds and tolerates approximation, while compliance wants a report that counts every trade exactly once and can be reproduced months later. The trap is the design that reads the regulatory report straight off the live position store. That store is mutable, it overwrites account state on every fill, and the moment an auditor asks what you filed in March you have nothing to replay because the state has moved on a hundred times since.
The whiteboard answer streams every execution into a positions table, shows it to risk, and runs a nightly SELECT over that same table to produce the filing. It looks clean until two things happen. A producer retries and the same execution gets counted twice in the report, because nothing deduped on a stable id. And six months later the replay request comes in and the only source of truth is a table that has been mutated continuously since, so the past filing simply cannot be reconstructed. Two of the three requirements have quietly failed.
Walk the requirements
Step 1: Fan the same executions to both paths through a durable queue
A durable queue at ingest lets the streaming risk path and the batch path each consume the executions independently, so a slow report run never starves risk and a streaming hiccup never delays the filing. Partition by account so per-account fills stay ordered, which matters because positions are sequence-dependent, while leaving cross-account order free so you keep throughput at the open and close peaks that run several times the daily average.
Step 2: Serve risk from a streaming position store, approximate but live
The streaming processor folds each execution into account state and upserts an intraday store the risk desk reads. At-least-once delivery is acceptable here: a duplicated fill briefly nudges a position and the next event corrects it, and what matters is that a breach surfaces within seconds so an account can be frozen while the market is open. Forcing exactly-once on this path buys nothing risk cares about and adds latency it cannot afford.
Step 3: Compute the filing as a deterministic batch over the immutable archive
The end-of-day batch reads the day's partition from the append-only archive, dedups on the stable execution id from the matching engine, and aggregates the filing into the warehouse. Exactly-once lives here, in the dedup, not in a hope that the stream never duplicated. Because the input partition is immutable and the logic is deterministic, re-running the same day next year produces the identical report. That is the property an audit actually tests.
The shape that fits
- execution_ingest (source, Kafka); parallelism: account-partitioned
- event_archive (storage, S3 append-only)
- position_stream (transform, Flink); freshness: real-time
- intraday_positions (storage, Cassandra); freshness: < 1min; idempotency: upsert
- risk_desk (consumer, Risk dashboard); freshness: < 1min
- eod_regulatory_batch (transform, Spark); freshness: < 24h; idempotency: dedup_on_execution_id
- regulatory_warehouse (storage, Snowflake); freshness: < 24h
- compliance_filing (consumer, Regulatory report); freshness: < 24h
- execution_ingest -> event_archive
- execution_ingest -> position_stream
- position_stream -> intraday_positions
- intraday_positions -> risk_desk
- event_archive -> eod_regulatory_batch
- eod_regulatory_batch -> regulatory_warehouse
- regulatory_warehouse -> compliance_filing
- Ingest: execution_ingest, event_archive
- Streaming risk: position_stream, intraday_positions, risk_desk
- Batch regulatory: eod_regulatory_batch, regulatory_warehouse, compliance_filing
Common follow-up questions
- A correction for a trade arrives after the streaming store already applied the original fill but before the end-of-day cutoff. What does each path do? (Tests whether the candidate treats corrections as appended events: the stream converges by applying the correction to account state, and the batch includes it because it reads the archive at cutoff. A correction after cutoff becomes the next day's amendment, never an in-place edit.)
- Volume grows 10x and the open and close peaks get spikier. Where does this design strain first, and what do you change? (Tests capacity reasoning: queue partition count and streaming job parallelism scale for the peak, the archive stays cheap because it is partitioned object storage, and the daily batch is embarrassingly parallel per partition. The strain point is the streaming position store's write throughput at peak.)