Katkat
Back

Design an Incremental Rolling-Metrics Data Pipeline

Jain GlobalData Engineer · Technical Screen
MediumData EngineerTechnical ScreenSystem Design

Last updated

Design an incremental batch data pipeline to compute multi-horizon rolling financial metrics over daily ticker observations, handling bounded historical restatements, atomic publication, and data reconciliation.

Scenario

A quantitative trading firm requires a daily data pipeline to compute multi-horizon rolling statistical metrics (e.g., 5-day, 20-day, 60-day, and 252-day moving averages, realized volatility, and VWAP) across global equities. The pipeline must ingest end-of-day market observations, produce updated feature tables, and support fast reads for quantitative researchers and risk reporting dashboards.

A critical requirement is handling market data corrections: exchanges occasionally restate volume, split adjustments, or settlement prices up to 30 days retroactively. The pipeline must support bounded incremental recomputation while guaranteeing that published results are strictly equivalent to a full historical rebuild.

Requirements

  • Functional Requirements:
    • Ingest daily price/volume updates for 10,000 active tickers.
    • Incrementally compute rolling metrics across multiple time windows (5d, 20d, 60d, 252d).
    • Support retroactive corrections up to K = 30 days back, propagating metric updates down to the current date without requiring a full multi-year recomputation.
    • Provide atomic, idempotent publication: downstream readers must never observe partial or inconsistent rolling states.
    • Ensure reproducible data lineage and a formal verification process proving incremental calculations match cold-start rebuilds.
  • Non-Functional Requirements:
    • Complete the daily incremental run within 20 minutes of market close.
    • Read queries from quantitative analysts (via Spark/DuckDB) and dashboards must achieve sub-second to low-second latency for single-ticker metric histories.
    • Prevent storage degradation (e.g., the small-files problem) over multi-year operational lifecycles.

Scale Assumptions

  • Universe: 10,000 equities.
  • History: 15 years of daily observations (~3,780 trading days, ~38 million base rows total).
  • Daily Restatements: ~1-3% of tickers receive revisions up to 30 trading days back.
  • Target Metrics: 25 statistical features per ticker per window.

What to Cover

  1. Data Architecture & Storage: Choose an appropriate storage format and engine (e.g., Iceberg, Delta Lake, or relational/columnar stores). Detail partitioning and clustering strategies.
  2. Computation Engine: Contrast bounded-window recalculation vs. stateful cumulative state maintenance for algebraic, distributive, and holistic metrics.
  3. Idempotency & Atomic Publication: Detail how pipeline failures and restatements are committed without race conditions or dirty reads.
  4. Reconciliation & Verification: Explain how to systematically prove that incremental state does not drift from a ground-truth full rebuild over time.

Hints

Hint 1

Distinguish between distributive/algebraic metrics (like mean and variance, which can maintain running counters) and holistic metrics (like medians or rank percentiles, which require the entire window).

Hint 2

Modern lakehouse formats with ACID capabilities (such as Apache Iceberg or Delta Lake) allow atomic partition replacement and snapshot isolation to eliminate read-write conflicts.

Hint 3

For bounded restatements, re-reading the maximum window size (e.g., max window + restatement lookback) is often more robust than mutating complex stateful accumulator logs.

Follow-up questions

  • How does your pipeline handle corporate actions (e.g., a 2-for-1 stock split 10 days ago) that require retroactive price scaling across historical observations?
  • If analysts frequently query the last 252 days across all 10,000 tickers simultaneously, how should you optimize file sizing and sorting (e.g., Z-order vs. date-partitioning)?
  • How would you implement an automated canary or shadow-reconciliation job to detect numerical drift between streaming/incremental updates and nightly batch runs?