## Design and Debug a Point-in-Time Backtest Data Pipeline You own the data pipeline that feeds a quantitative backtest. The inputs include security identifiers, prices, reference data, and time-varying earnings estimates from multiple vendors. Design the pipeline so that a backtest is reproducible, point-in-time correct, and diagnosable when its result changes unexpectedly. ### Constraints & Assumptions - Vendors can disagree, publish late corrections, omit records, or stop updating a series. - Security identifiers can change or be reused, while the economic entity may remain the same. - A historical backtest must use only information that would have been available at the simulated decision time. - Every run must be attributable to exact data, code, configuration, and model versions. ### Clarifying Questions to Ask - Which instruments, frequencies, and corporate actions are in scope? - Does the strategy consume event-time values, publication-time values, or both? - Which vendor fields are licensed for storage, derived use, and redistribution? - What constitutes an outlier for each field, and can a suspicious value be quarantined rather than rejected? ### Part 1 — Detect and Handle Data Quality Failures Explain how the pipeline detects stale data, missing observations, outliers, duplicates, late arrivals, and inconsistent revisions. Describe what is rejected, quarantined, carried forward, or exposed with a quality flag. #### What This Part Should Cover - Field-specific freshness and completeness checks rather than one global rule. - A distinction between genuinely unchanged values and feeds that stopped updating. - Outlier handling that preserves raw evidence and avoids silently rewriting history. - Downstream behavior when required versus optional fields are unavailable. ```hint Define behavior per failure class Missing required prices, a stale optional descriptor, and a suspicious outlier should not all trigger the same fallback. ``` ### Part 2 — Resolve Identifiers and Reconcile Vendors Design identifier resolution across changing tickers and vendor-specific IDs. Then define how a preferred source is chosen by field and how another vendor can backfill gaps without obscuring provenance or creating temporal inconsistency. #### What This Part Should Cover - Stable internal security and entity keys with effective-dated mappings. - Ambiguity, collisions, and unresolved records handled explicitly. - Source precedence that can differ by field, market, or time period. - Per-value lineage showing the selected vendor and any fallback decision. ```hint Preserve both identity time and data time A ticker-to-security mapping and an earnings estimate revision each need their own valid-time history. ``` ### Part 3 — Prevent Look-Ahead Bias Show how earnings estimates and other revised datasets are represented and joined so that a backtest cannot observe a value before it was published and ingested. Include corrections that arrive after the simulated date. #### What This Part Should Cover - Event, publication, ingestion, and validity timestamps where they differ. - Point-in-time or vintage-aware reads using an explicit simulation cutoff. - A policy for late corrections that does not mutate an already sealed run. - Tests that deliberately introduce a future revision and prove it stays invisible. ```hint Query by knowledge time Select the latest version that was actually available by the simulation cutoff, not the latest version known today. ``` ### Part 4 — Diagnose a Sudden Backtest Change A previously stable backtest changes after a routine release. Explain how you determine whether the cause is the model, application code, orchestration, or source data. Include the pipeline components and run metadata needed to make that investigation efficient. #### What This Part Should Cover - Reproduction from a sealed run manifest. - Controlled comparisons that change one dimension at a time. - Intermediate-data checksums and stage-level row/quality metrics. - An orchestration design that records dependencies, retries, and input partitions. ```hint Build a two-by-two comparison Run old and new code against old and new data snapshots so code effects can be separated from data effects. ``` ### What a Strong Answer Covers - Immutable raw inputs, versioned transformations, point-in-time reads, and complete per-value lineage. - Explicit policies for identifier ambiguity, vendor disagreement, and degraded data. - Debugging based on reproducible artifacts and differential analysis rather than intuition. - Tests that cover stale, missing, outlier, revision, and future-information failure modes. ### Follow-up Questions 1. How would you distinguish a valid market jump from a bad outlier without deleting either observation? 2. What happens when the preferred vendor republishes a year of estimates with new values? 3. How would you migrate identifier mappings without changing previously sealed backtests? 4. Which comparison would you run first if only one strategy changed while others using the same data did not?