Thirty Cities, One Forecast

A hard Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain

Pipeline Design

Difficulty

hard

Seniority

L5

Problem

Hourly demand forecasts for every station of a bike-share network in 42 cities depend on raw data from each city's systems and from external sources, all in different formats. What pipeline turns that raw data into reliable, model-ready features?

Five cities. Five data formats. One prediction.

Business requirements

• Operations runs predictions every 30 minutes and pre-positions bikes ahead of the next half-hour; the feature pipeline has to be ready in time.
• When one city's data hasn't arrived, the other cities still need their predictions on time.
• Onboarding a new city requires three years of historical features; that backfill can't slow down predictions for the live cities.

Worked solution and explanation

Many formats, one clock, and a backfill that wants your cluster

This is a failure-isolation problem dressed up as a feature pipeline. Anyone can draw sources, Spark and a warehouse. What separates candidates is where the blast radius stops: one city's late feed and one three-year onboarding backfill must each hurt only themselves. Draw one fan-in DAG on one cluster and two things go wrong. The first late feed stalls every city's 30-minute window, and the first new-city backfill starves live predictions.

Step 1: Isolate ingestion per city under one orchestrator

per_city_ingest runs one task per city, scheduled by the orchestrator, so a missing feed fails one task and not the run. Feature compute proceeds with whichever cities arrived, and a late city falls back to its last good features, flagged stale.

Step 2: Normalize before anything computes features

normalize_common_schema turns every city and external format into one schema. Features computed on mixed formats are silently wrong, so normalization is its own stage and not a side effect of feature code.

Step 3: Check the normalized records

schema_quality_gate sits between normalization and feature_compute. That is what makes the features reliable rather than merely present: a renamed column in one city's file is caught before it turns into a zero-demand feature.

Step 4: Give backfill its own path

city_history_archive feeds backfill_job, a separate Spark pool that writes into the same feature_store. It never shares compute with the live path, so building three years of history for a new city costs the live cities nothing.

One fan-in DAG

One late feed holds feature_compute for every city. A new-city backfill shares the cluster, so live runs stutter for hours.

Per-city tasks plus a backfill pool

A late feed fails one per_city_ingest task, and the rest of the cities publish on time. The backfill pool never competes with live compute.

Common follow-up questions

• Two cities miss the same window. What does the orchestrator do, and what does the model read for those two cities? (Tests per-city isolation and the stale-flagged fallback for only the late cities.)
• A city renames a column overnight. Where does that surface, and how do you rerun only that city? (Tests `schema_quality_gate` placement and per-city reruns without touching healthy cities.)
• Training features turn out to include the hour being predicted. What changes in the pipeline? (Tests point-in-time correctness for the historical features `backfill_job` builds.)