Bikes Before Rush Hour

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

Trip history lands once a day as files from about 40 city operators, each in its own schema, and every hourly station forecast depends on scheduling those files through ingest, cleaning and feature build before it runs. Design the pipeline from raw ingestion to a model-ready feature store that the forecast reads.

Bikes in, bikes out. The city needs to predict demand.

Business requirements

• Operations pre-positions bikes before each rush hour and needs the demand forecast in time for that.
• Dock availability changes minute to minute while trip history only lands once a day, so the supply signal has to be near-real-time even though trip features are batch.
• Corrupt trip records like negative durations and zero-distance test rides must never reach the model's features, though we still keep them in the raw layer.

Worked solution and explanation

What this problem really is

Under the demand-forecast costume, this is two clocks feeding one feature store, plus a filter that has to drop rows from the features without deleting them. Trip history lands once a day as operator files in forty different shapes. Dock availability changes every minute and is worthless once it goes stale. The trap is pulling both through the same nightly batch: the supply feature ends up a day old, and the model pre-positions bikes against docks that emptied hours ago.

Building it

Step 1: Schedule the daily trip files

The operator files are the slow clock, so an orchestrator (Airflow here) owns it. Each hour it picks up whatever files have arrived, lands them untouched in object storage, runs the quality check and kicks off feature build before the forecast fires. Normalizing the forty operator schemas belongs in feature build, after the raw copy exists, so a mapping bug can be fixed and replayed from raw.

Step 2: Stream the dock supply

Dock availability is the fast clock: a snapshot an hour old tells the model a full dock is empty. The feed comes in on Kafka, and a Flink job keeps current supply per station under a minute old, writing into the same feature store. Nothing on this path waits for the trip schedule.

Step 3: Filter corrupt trips between raw and features

About two percent of trips are junk: negative durations from dock clock skew and zero-distance test rides. The raw layer keeps them for audit and reprocessing, so the filter sits after raw_trips and before trip_feature_build. Raw holds everything, the gate drops the bad rows, and only clean trips become features.

The shape that fits

Filter during ingest

This saves one hop, and it silently breaks retention: the corrupt rows never reach storage. When the definition of corrupt changes, there is nothing to reprocess.

Filter after the raw landing

Raw is a faithful copy of what operators sent. The quality gate reads from it and passes only clean trips forward, so the features stay clean and the history stays complete.

Common follow-up questions

• You have forty cities on different file schedules. How do you keep one city's late file from blocking predictions for the others? (Tests whether each city is its own scheduled unit, so feature build proceeds with the cities that finished and flags the one that did not, rather than a single wait-for-all step.)
• A new city onboards with three years of history to backfill. How do you keep that from starving the live hourly run? (Tests whether backfill runs on separate compute from the live pipeline, so a large historical load does not drain what the hourly forecast depends on.)