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

Our operations team runs a bike-share network across dozens of cities. We want to predict hourly demand at each station so we can pre-position bikes before rush hour. The data comes from multiple city systems and external sources in different formats. Design a pipeline that takes in this raw data and produces 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

Five 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, one city's malformed file, and a three-year onboarding backfill must each hurt only themselves. Draw one fan-in DAG on one cluster and the first late operator stalls every city's 30-minute window, while the first new-city backfill starves live predictions.

Step 1: Normalize before anything computes features

Every city and external feed lands raw in raw_landing first, untouched, so a bad parse can be replayed. normalize_common_schema then turns each 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 2: Gate the records, quarantine the bad ones

schema_quality_gate checks every normalized record. Malformed or schema-shifted rows go down an error path to dlq_quarantine while valid rows keep flowing. One city's bad file should never stop the other cities, and nothing is dropped, so someone can fix and replay it.

Step 3: Isolate per city under one orchestrator

per_city_ingest runs one task per city, so a missing feed fails one task and not the run. The orchestrator alerts by city name before the cutoff, and feature compute proceeds with whichever cities arrived. Late cities fall back to the previous window's features, flagged. Reruns are safe because ingest writes through a staging table and features upsert.

Step 4: Give backfill its own path

city_history_archive feeds backfill_job, a separate Spark pool that writes straight to the same feature_store. It never touches the live compute, so a three-year onboarding costs the live cities nothing. Overwriting by partition makes a restarted backfill harmless.

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. A single bad file can fail the whole batch.

Per-city tasks plus a backfill pool

A late feed fails one per_city_ingest task and pages by city name. The backfill pool never competes with live compute. Bad records land in dlq_quarantine and valid ones continue.

Common follow-up questions

• Two cities fail ingest in the same window. What does the orchestrator do and what does the rebalancing team see? (Tests per-city alerts and the fallback to the previous window's features for just those two cities.)
• A city changes its file schema overnight. Where does that surface and how do you replay the quarantined rows? (Tests `schema_quality_gate`, `dlq_quarantine` and replay from `raw_landing` without rerunning healthy cities.)