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 executes about 50 million trades a day across 12 million accounts, and the same execution events feed two consumers: the risk desk's intraday account positions and the end-of-day regulatory filing. Design the pipeline that serves both.

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 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

This is one execution stream serving two consumers with opposite correctness budgets, dressed up as a brokerage pipeline. Risk wants positions in seconds and tolerates a briefly wrong number. Compliance wants every trade counted once and a past filing you can rebuild byte for byte. Anyone can draw a stream into a positions table. The trap is reading the regulatory report off that same mutable table. A producer retry then counts a trade twice, and when an auditor asks what you filed in March, the state has been overwritten a hundred times and there is nothing to replay.

Walk the requirements

Step 1: Fork one durable stream into two independent readers

Executions land once in a durable queue. The streaming job and the archive writer each read it at their own pace, so a slow report run never starves risk and a streaming hiccup never delays the filing. Key the topic by account so per-account fills stay ordered, since positions depend on sequence.

Step 2: Serve risk from a live position store

Flink folds each fill into account state and writes an intraday store that the exposure breach dashboard reads. At-least-once is fine here: a duplicated fill nudges a position and the next event corrects it. What matters is that a breach shows up within seconds, while the market is open and the desk can still freeze the account. Forcing exactly-once here buys latency that risk cannot afford.

Step 3: Compute the filing as a deduped batch over the archive

The end-of-day Spark job reads that day's partition of the immutable archive, drops duplicates on execution_id, and lands the aggregate in a warehouse. Exactly-once lives in the dedup, not in a hope that the stream never repeated itself. The input is immutable and the logic is deterministic, so rerunning the same day next year yields the identical report.

Common follow-up questions

• A correction for a trade arrives after the position store applied the original fill but before the end-of-day cutoff. What does each path do? (Tests corrections as appended events: the stream converges by applying it, the batch picks it up from the archive. A correction after cutoff becomes the next day's amendment, never an in-place edit.)
• Volume grows 10x and the peaks get spikier. Where does this design strain first? (Tests capacity reasoning: topic partitions and Flink parallelism scale for the peak, the archive stays cheap, and the daily batch parallelizes per partition. The first strain is write throughput on the position store.)