Where Is Every Truck, Right Now

A medium 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

medium

Seniority

L5

Problem

We operate a last-mile delivery fleet and need to know where every truck is at all times. The operations team wants live tracking on a map and the analytics team needs historical route data for efficiency analysis. Design a pipeline to ingest GPS tracking data from our trucks.

Trucks are moving. Every ping counts.

Business requirements

• Operations watches every truck on a live map; a position has to appear there within seconds of the truck moving.
• Trucks lose connectivity in tunnels and rural areas; when they reconnect and dump buffered events, the route history has to come out right.
• Operations needs to know when a truck enters or leaves a delivery zone; today they're reading positions off the map manually.

Worked solution and explanation

One stream, two clocks, and a tunnel

This is an event-time ordering problem dressed up as a live map. Anyone can wire trucks to a map and a warehouse. What separates candidates is what happens when a truck leaves a tunnel and dumps an hour of buffered pings: if the route is built in arrival order, the truck teleports back and forth in the history. Zone entry and exit is the third requirement hiding in plain sight. If the pipeline does not detect it, an operator is back to comparing positions by eye.

Walk the requirements

Step 1: Fan one source into two cadences

Pings enter truck_gps once, keyed by truck so each truck's pings stay together. A Flink job, live_position_stream, keeps the latest position per truck in live_position_store for the map within seconds. The same pings land untouched in event_lake for a daily Spark job. One cadence for both would leave ops with a stale map, or analytics paying for streaming compute it never reads.

Step 2: Rebuild routes from the raw lake, sorted by event_time

route_builder_by_event_time reads the lake, sorts each truck's pings by event_time, and writes route_warehouse. A tunnel dump lands in the lake with its true timestamps, and the job overwrites every day partition those late pings touch, so each ping goes back in its place however late it arrived.

Step 3: Detect zone crossings in geofence_stream

This job keeps the last known zone per truck and compares it with each new ping. When the zone changes it writes an entry or exit row to zone_alerts, and live_map shows it. Nobody reads coordinates off a screen.

Common follow-up questions

• A truck replays pings from three days ago, after `route_builder_by_event_time` already ran for those days. What lets the routes self-correct? (Tests whether the candidate sees that late pings land in the lake with their `event_time` and that the job rewrites every day partition they touch.)
• Operations adds a new delivery zone while trucks are moving. What does `geofence_stream` absorb, and what does it not? (Tests zone definitions as state the stream reads, and that past crossings are not re-evaluated without a replay.)