Mark to Market

A medium 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
medium
Seniority
mid
Asked in
Fidelity

Problem

A brokerage platform processes about 2 billion trade execution events a day, and two teams read them: risk wants intraday position dashboards that stay within a few seconds of the market, while regulatory reporting needs every trade counted exactly once in the end-of-day books. Trades arrive out of order and some are corrected hours after execution, so the design has to reconcile late and amended fills without double-counting or dropping them.

Business requirements

Worked solution and explanation

Two correctness budgets, one log

This is a lambda architecture dressed up as a brokerage dashboard. The skill being probed is whether you can fan one replayable log out to two paths with opposite contracts. Risk will forgive a position that is off by a fill for a few seconds. Regulators will not forgive a fill counted twice. Anyone can draw a stream processor. The trap is letting one aggregate serve both readers. It is never fast enough to feel live or exact enough to file, and a retry or a late amendment lands in the filed books as a reportable error.

The obvious answer is a stream that aggregates fills into a positions table, with the end-of-day report taken as a snapshot of that table at the close. Two facts in the prompt break it: fills arrive out of order, and some are amended or busted hours later. A correction after the close never reaches the snapshot, and a producer retry double-counts a fill.

Building it

Step 1: Land every fill on a replayable log first

Out-of-order arrival and hours-late corrections mean no downstream store can be the source of truth, since it only reflects what arrived before it ran. A Kafka topic keyed by execution id lets the stream and the batch consume the same events independently, and lets the batch replay a whole trading day on demand.

Step 2: Stream intraday positions into a serving store

Flink aggregates fills with sub-minute freshness into Redis, and the risk dashboard reads Redis. A position briefly off by one trade hurts nobody on the desk. Making this path exact is what makes it slow, and it still would not be safe to file.

Step 3: Reconcile the books in a daily replay

After the close, a Spark job rereads the full day off the log, anchors every aggregation on execution time rather than arrival time, dedups on the stable execution id and applies amendments and busts against the original id. A quality gate checks counts against the log, then an Airflow-scheduled daily task upserts the settled books into Snowflake. Because it replays, a late fill still lands in the right trading day.

One replayable Kafka log feeds two paths: Flink into Redis for the live risk view, and a daily Spark replay with dedup, a check and an Airflow-scheduled upsert into the Snowflake books.
  • trade_fills (source, Kafka)
  • intraday_positions (transform, Flink)
  • position_store (storage, Redis)
  • risk_dashboard (consumer, Grafana)
  • eod_dedup_reconciliation (transform, Spark)
  • reconciliation_check (quality gate, Great Expectations)
  • daily_books_upsert (transform, Airflow)
  • regulatory_books (storage, Snowflake)
  • reporting (consumer, Tableau)
  • trade_fills -> intraday_positions
  • intraday_positions -> position_store
  • position_store -> risk_dashboard
  • trade_fills -> eod_dedup_reconciliation
  • eod_dedup_reconciliation -> reconciliation_check
  • reconciliation_check -> daily_books_upsert
  • daily_books_upsert -> regulatory_books
  • regulatory_books -> reporting
  • Ingest: trade_fills
  • Intraday path: intraday_positions, position_store, risk_dashboard
  • End-of-day path: eod_dedup_reconciliation, reconciliation_check, daily_books_upsert, regulatory_books, reporting

Common follow-up questions