# Eight-Hour-Old Positions > Positions shift by the second. The math cannot lag. Canonical URL: Domain: Pipeline Design · Difficulty: hard · Seniority: L6 ## Problem Our portfolio analytics platform shows clients their real-time holdings value, but trade executions aren't reflected in positions until the nightly batch runs 8 hours later. The risk team also needs intraday P&L with market prices validated against multiple data vendors before they feed NAV calculations. Design a pipeline that makes positions and P&L available in near-real-time throughout the trading day. ## Worked solution and explanation ### Why this problem exists in real interviews Real-time positions for clients, validated prices for NAV, nightly reconciliation against the custodian, retroactive corporate-action adjustments. The trap is reading prices from one vendor and trusting them, or treating reconciliation as 'we'll spot it later,' or skipping the corporate-action rebuild because it's only one stock and rare. The default reach is a stream that updates positions from trade executions and a nightly batch that fixes anything wrong. Clients see positions update; risk reads NAV from a single price feed and a stale or wrong price flows into the calc. Nights pass without a custodian reconciliation that catches drift; weeks later the warehouse and the custodian disagree by more than tolerance and somebody has to find out where. A stock splits and historical positions for that security stay unadjusted; P&L for everyone holding it goes wrong overnight. > **Trick to Solving** > > Stream positions live; validate prices across vendors before NAV reads them; reconcile against the custodian each night with per-account diff; retroactive rebuild on corporate actions. > > 1. Trade executions stream into a positions store within seconds; clients read from the store. > 2. Each security's price comes from multiple vendors; a validation step compares them and either consolidates or freezes the price when sources disagree past tolerance. NAV reads only validated prices. > 3. Nightly reconciliation joins the streaming positions to the custodian statement and emits per-account differences; the next morning's view shows what reconciled and what didn't. > 4. Corporate actions trigger a partition-overwrite of the affected security's historical positions and price series; the rebuild scope is per-security, not the world. --- ### Walk the requirements #### Step 1: Trade executions to client positions in seconds Trade events flow through a streaming consumer that updates the per-account positions store; clients read from the store within seconds of execution. The 8-hour batch lag is the named problem; the streaming path is the fix. Without sub-minute updates the requirement is unaddressed; without a durable history tier the retroactive rebuild has nothing to act on. #### Step 2: Multi-vendor price validation before NAV reads Prices come from multiple data vendors. A validation step joins each security's quotes from the vendors and consolidates them (median, weighted, or rule-based) when they agree within tolerance; when they disagree past tolerance, the validator freezes the security's price at the last validated value and alerts. NAV reads only validated prices. Reading from one vendor and trusting it is the version where a single bad quote walks into NAV and creates a regulatory mis-reporting incident; multi-vendor validation is the boundary that catches it. #### Step 3: Nightly reconciliation against the custodian, per account Streaming positions can drift from the custodian's official record (late executions, write failures, edge cases). The nightly reconciliation joins the streaming positions to the custodian statement per account and emits per-account differences. The morning view shows which accounts reconciled cleanly and which didn't, with the diff visible. Without the reconciliation, drift accumulates silently; with it, the team sees drift the next morning and acts before clients do. #### Step 4: Corporate actions retroactively adjust history, per security When a stock splits or pays a dividend, the affected security's historical positions and prices have to adjust or P&L for everyone holding it is wrong. The corporate-action processor reads the action, identifies the affected security and date range, and partition-overwrites the historical positions and price series for that security. Other securities are untouched. A 'never rebuild' approach is the version where P&L is silently wrong from the action forward; a 'full rebuild on every action' approach is the version that takes too long; per-security partition-overwrite is the surgical option. --- ### The shape that fits > **What this design gives up** > > The streaming positions path costs more compute than batch; multi-vendor validation needs vendor agreements and a tolerance policy that has to be tuned; nightly custodian reconciliation requires the custodian's statement format to be parsed and partitions overwritten on disagreement; corporate-action rebuilds run an extra batch when actions land. Implementation cost is the price; the win is real-time positions clients can act on, validated prices NAV can use, drift surfaced the next morning, and P&L that doesn't go wrong on a stock split. > **What reviewers check** > > A reviewer looks at the canvas for these properties: > - A streaming path delivers trade executions to client positions within seconds. > - Prices validate across multiple vendors before NAV reads them; failed validations freeze the price rather than feeding NAV. > - A nightly batch reconciles streaming positions against the custodian statement and surfaces per-account differences. > - Corporate actions partition-overwrite the affected security's historical positions and price series. > **The mistake that ships** > > What gets shipped streams trades into a positions store and reads prices from a single vendor for NAV. A bad quote from the vendor walks into NAV and creates a regulatory mis-reporting moment. Streaming positions drift from the custodian over weeks because nobody's running the reconciliation. A stock splits and the affected positions don't adjust; P&L for every account holding the stock is wrong from the action forward. The eventual rebuild adds multi-vendor validation, custodian reconciliation, and corporate-action rebuild , each was reachable up front if 'real-time positions' had been treated as one part of the contract instead of the whole answer. --- ## Common follow-up questions - Two price vendors agree but a third disagrees past tolerance. What does the validator do, and what does NAV see? _(Tests whether the candidate's tolerance policy has a quorum or median rule: agreement among the majority consolidates; the disagreeing vendor's quote is excluded with an alert. NAV sees the validated price; the alert flags the disagreement for the data team to investigate the third vendor.)_ - A custodian sends a corrected statement two days late. What in this design lets the corrected reconciliation replace the prior one for those days? _(Tests whether the candidate sees that the reconciliation warehouse uses partition-overwrite by reconciliation_date so the corrected statement's reconciliation replaces the original day's record. The streaming positions don't change; the custodian-side correction reflects in the diff for that day.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/eight_hour_old_positions) - [System Design Interview Questions](https://datadriven.io/data-engineering-system-design) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.