## Design an Incremental Rolling-Metrics Data Pipeline Design a production pipeline that computes rolling-window metrics for approximately 1,000 tickers over about 900 days of historical observations. The initial load must compute the full history, while daily runs should avoid recomputing unaffected results. Analysts and a dashboard need fast access to the latest values and selected historical ranges. Describe the computation strategy, data model, partitioning and indexing choices, columnar storage layout, orchestration, and serving path. Explain how the pipeline handles late or corrected observations, schema changes, retries, and validation. Compare full recomputation with incremental processing and state which events should trigger each approach. ### Constraints & Assumptions - Each ticker normally has one observation per trading day, but values may arrive late or be corrected. - Several rolling windows may be computed from the same underlying series. - A failed run can be retried and must not create duplicate or partially published results. - Dashboard reads are more frequent than source-data updates. ### Clarifying Questions to Ask - Which rolling functions and window definitions are required, including minimum-period behavior? - Must corrected history be visible immediately, and how far back can corrections occur? - What latency is expected for daily publication and dashboard reads? - Do consumers need point-in-time reproducibility or only the latest corrected view? ```hint Find the invalidation range A correction on date d affects only output dates whose windows include d; use that fact to bound recomputation. ``` ### What a Strong Answer Covers - A full bootstrap followed by incremental computation that reads the new data plus the necessary window overlap. - Deterministic invalidation for late data and corrections, with versioned inputs and idempotent publication. - A schema that identifies ticker, observation date, metric, window, value, source version, and computation version. - Columnar files or tables partitioned to avoid tiny files and excessive partition scans, with clustering or indexes aligned to read patterns. - Data-quality checks, lineage, backfill controls, atomic publish semantics, and a serving layer suited to dashboard access. ### Follow-up Questions - How does a correction 30 days in the past affect a 20-day and a 60-day rolling metric? - Would you partition by ticker, date, or both, and how would you avoid thousands of tiny files? - How would you prove that an incremental run produces the same results as a clean full recomputation?