Every Scan, Every Parcel, Every Pin Code

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

How would you keep shipment state accurate when tracking scans from warehouses, vehicles and delivery points across 18,000 pin codes in India arrive out of order, so a delivery scan can land before the origin hub's departure scan? The ops team also has to catch parcels moving in the wrong direction.

Out for delivery. Delivered. Except the events arrived backwards.

Business requirements

• Customers expect their tracking page to reflect a new scan within minutes; today they call support saying their package is missing.
• When a shipment hasn't moved in too long, ops needs to know quickly; today they only find out when customers complain.
• Routing decisions have to be made on every scan at high volume; the lookup can't slow down scanning.

Worked solution and explanation

Why this problem exists in real interviews

Out-of-order tracking scans are a stateful-streaming problem in disguise: customers want the latest state in minutes, ops wants the absence of state (stuck parcels), and routing wants a lookup fast enough not to slow scanning. The trap is treating each scan as it arrives without a per-parcel state machine keyed by AWB. Get it wrong and a delivery scan that lands before the origin scan flashes 'delivered' on a parcel that never shipped.

The first reach is a stream that upserts each scan into a tracking table by parcel id, keeping the latest by event-time. But if the delivery scan arrives first the customer page briefly contradicts itself; ops queries 'not moving' on a slow batch and learns of stuck parcels hours after customers do; and routing reads pin-code reference from the OLTP on every scan until it buckles.

Walk the requirements

Step 1: Stream into a per-parcel state machine that respects event-time

Scan events flow through a queue into a stream processor keyed by AWB. For each parcel the processor holds an event-time-ordered list of scans and emits the resulting state to the tracking store, so an out-of-order delivery scan arriving before the origin scan doesn't flash 'delivered'. Without stateful streaming the customer view is whichever scan landed last, not the parcel's real progress.

Step 2: Stuck-shipment detection as a watermark timer on the stream

The same per-parcel state arms a watermark-driven timer that fires when no scan arrives inside the operational window, emitting a 'stuck' exception that reaches ops as an alert. Because it's event-time driven, a delayed batch of scans doesn't trigger false alerts. A periodic batch query for 'no recent scan' lags by the batch interval and floods the store on every load.

Step 3: Routing reference in a cache the stream reads, not the OLTP

Routing lookups run on every scan at peak, so the stream reads pin-code routing from an in-memory cache with a tens-of-milliseconds budget instead of the OLTP. The same lookup tells the stream when a scan lands off the parcel's expected route, and that misroute goes to ops through the same exception path as a stuck parcel.

The shape that fits

Common follow-up questions

• A scanner is offline for hours and dumps its buffer on reconnect. What reorders the events, and what does the customer see during the flush? (Tests event-time semantics: buffered scans are inserted in event-time order and the customer state converges to the final state rather than flickering through each buffered scan.)
• Ops wants the stuck threshold to vary by route (rural tolerates longer than urban). Where does that config live and what changes in the stream? (Tests seeing per-route thresholds as state the stream reads alongside the routing lookup, arming each parcel's timer by its route rather than a hardcoded interval.)