Flying Blind Until Midnight

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

L6

Problem

Design a risk data pipeline that computes intraday margin from live trades and market prices across fixed income, FX and derivatives books, about 2 million trades a day. The desk's risk lead still gets risk only from a nightly batch, and Canadian regulators hold every reported figure to BCBS 239 lineage.

Intraday risk, full lineage. The regulator is watching.

Business requirements

• The trading desk has been flying blind during the day; they want to see positions and risk while the market is open.
• Regulators can ask for the source trade and price behind any P&L or risk number on a report, and the answer can't be 'we'd have to look at the code'.

Worked solution and explanation

A lineage problem in a latency costume

The desk wants margin in seconds. Regulators want every reported number traceable to the exact trade and price that produced it. Anyone can draw a stream processor. What separates candidates is whether the stream keeps its inputs. If the trades and prices it consumed are gone once the number is computed, the fast figure is also the untraceable one, and the audit answer becomes 'we'd have to look at the code.'

Most candidates keep nightly batch for the official numbers and bolt a separate streaming view on the side for traders. That gives two pipelines and two answers, and the intraday view drifts from the nightly book within an hour. Then an auditor asks what the position was at a particular hour, and nobody can answer without rebuilding it. That is the failure BCBS 239 was written to prevent.

Walk the requirements

Step 1: Put the desk on a streaming path

Trade and price events come off Kafka into a stream processor that updates positions and intraday margin as each event arrives. The desk's dashboard reads from a position store the stream keeps current, not from the nightly batch. Whatever engine you pick (Flink, Kafka Streams, Spark Streaming), what matters is sub-minute latency from a trade landing to the trader's screen.

Step 2: Write lineage on the row, not in a wiki

Each computed risk number carries the trade ids and the price snapshot id it was derived from. When a regulator asks where a number came from, the answer is a query against those columns. The trail is queryable because it lives in the data, not next to it.

Step 3: Archive every input the stream consumes

The same trade and price events that feed the stream also land in object storage, partitioned by hour and never overwritten. That archive is what the lineage ids point at. Without it, the ids reference records that no longer exist.

Step 4: Check lineage before a figure is filed

A quality gate sits between the stream and the filed figures and rejects any risk row missing its source ids. A figure that cannot be traced never reaches a regulator report. Filed snapshots go to an immutable table, and corrections become new versions that reference the original instead of overwriting it.

The shape that fits

Common follow-up questions

• A regulator asks for the position at a specific hour last Tuesday and the price quotes that fed it. What query do you run, and against which store? (Tests whether the candidate goes to the archive and the filed snapshots, joined on lineage ids, rather than the position store, which only holds current state.)
• A desk in another country wants Canadian positions in the same view. What changes, and what stays in-region? (Tests whether the candidate keeps row-level data in-region and ships only aggregates across the border.)
• The stream crashes mid-batch and replays from its last checkpoint. How do you stop a trade from being counted twice in the filed figures? (Tests whether the candidate makes the filed write repeatable, so a rerun produces the same figures.)