Katkat
Back

Design and Debug a Point-in-Time Backtest Data Pipeline

Jain GlobalData Engineer · Technical Screen
MediumData EngineerTechnical ScreenSystem Design

Last updated

This problem tests the ability to architect a robust financial data pipeline that ensures point-in-time accuracy and reproducible research.

The Scenario

You are designing a data pipeline for a quantitative research firm that consumes daily equity data from multiple vendors. Researchers frequently report that their strategy performance metrics (Sharpe ratio, returns) fluctuate significantly when raw data is updated or backfilled. You need to design a robust pipeline that prevents look-ahead bias and ensures reproducible backtests.

Requirements

  1. Point-in-Time Integrity: How do you ensure that if a researcher runs a backtest on a specific date, the pipeline only provides data that was physically available at that time?
  2. Identifier Resolution: Vendors use different tickers and SEDOLs. Design a mechanism to map these to an internal master ID system that handles historical delistings and corporate actions (e.g., mergers, spin-offs).
  3. Data Quality and Versioning: How do you handle cases where Vendor A provides a price correction for a date three months in the past? Define a system to store and lineage these 'as-of' updates.
  4. Regression Isolation: If a strategy's backtest results change after a weekend data refresh, how do you architect the system to quickly isolate whether the change was caused by a code update, a data correction, or a methodology change?

Constraints

  • The system must handle daily updates for ~50,000 global securities.
  • Latency for historical queries should be minimal, but correctness (avoiding look-ahead bias) is the highest priority.
  • You must account for 'stale' data where a vendor might report a price from the previous day due to a holiday or missing feed.

Hints

Hint 1

Consider implementing a bi-temporal data model that stores both the 'valid time' (when the event occurred) and the 'transaction time' (when the data was recorded in the database).

Hint 2

Use a deterministic mapping table for identifiers that tracks the effective date range for every ticker-to-master-ID relationship.

Hint 3

For regression isolation, maintain immutable snapshots of the data pipeline outputs (or 'Golden Sets') that allow researchers to re-run the exact previous version of the data.

Follow-up questions

  • How would you handle a scenario where an earnings estimate data point is retroactively revised by the vendor? Does this change your 'as-of' date logic?
  • If a new vendor source is added, what verification process would you implement to ensure it doesn't introduce look-ahead bias?
  • How would you implement a 'Circuit Breaker' pattern in the pipeline to detect if incoming data contains outliers that could corrupt backtest results?