Eight-Hour-Old Positions

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

Our portfolio analytics platform shows clients their holdings value and gives the risk team intraday P&L that feeds NAV calculations, but positions only refresh in a nightly batch. Design a pipeline that keeps positions and P&L current throughout the trading day.

Positions shift by the second. The math cannot lag.

Business requirements

• Clients see their holdings on the portal; an executed trade has to show up there immediately, not eight hours later.
• Stale or wrong prices flowing into NAV is a regulatory mis-reporting incident; the calculation can't use a price we haven't validated.
• Streaming positions can drift from the custodian's record because of late executions or write failures; the warehouse has to be reconciled against the custodian nightly.
• When a stock splits or pays a dividend, every historical position and price for that security has to be adjusted, or P&L is wrong for everyone holding it.

Worked solution and explanation

What this really is

This is a lambda architecture dressed up as a client portal fix. Anyone can draw a stream from trade executions into a positions store, and that ends the eight-hour lag. What separates candidates is treating the stream as fast but not authoritative. A price from one vendor flows straight into NAV. Nothing checks streamed positions against the custodian. A split leaves history unadjusted. Get that wrong and one bad quote becomes a regulatory mis-report, and one split misstates P&L for every account holding the stock.

Walk the requirements

Step 1: Stream executions into positions_store

trade_executions on Kafka feed a Flink positions_stream that updates positions per account. Clients read positions_store through client_portal seconds after the fill. That fixes the lag and nothing else.

Step 2: Gate prices before the stream reads them

price_vendors land in price_validator, which compares each security's quotes across sources. If they agree within tolerance, a consolidated price goes on. If not, the last validated price is held. positions_stream, and therefore nav_calculation, only ever sees a price that passed.

Step 3: Reconcile against the custodian nightly

A Spark reconciliation_batch joins positions_history to custodian_statement per account and hands the differences to risk_team. Late executions and dropped writes show up the next morning. Without this they surface weeks later, when nobody can say where the drift began.

Step 4: Rewrite only the affected security

corp_action_processor reads each split or dividend and adjusts that one security's positions and price series in positions_history. Never rewriting leaves P&L wrong. Rebuilding everything on every action never finishes. Per-security partitions keep the fix surgical.

The reference design

Common follow-up questions

• Two price vendors agree and a third disagrees past tolerance. What does `price_validator` do, and what does NAV see? (Tests for a quorum or median rule: the majority consolidates and the outlier is excluded.)
• The custodian sends a corrected statement two days late. How does the corrected reconciliation replace the original? (Tests for overwriting by reconciliation date, so a rerun replaces that day's diff instead of appending a second one.)