The Last Mile

A medium Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
medium
Seniority
L4
Asked in
Amazon

Problem

An order on our platform can ship in multiple packages, each moving separately through the carrier network from warehouse to doorstep. Operations needs to see every order's current status alongside the scan-by-scan history of each package. Design the data model.

Order placed. Now track it to the door.

Worked solution and explanation

Why this problem exists in real interviews

The interviewer is probing whether a candidate separates state from state transitions. An order and a shipment each have a current status, but the history of how they got there is a distinct entity. Getting this right is the difference between a model that answers "where is my package now" and one that answers "why did this package take four days in Memphis".

Break down the requirements

Step 1: Separate entities from events

shipments is the entity (current carrier, current status, tracking number). shipment_events is the event log (every scan, every status change). Two tables, two grains.

Step 2: Choose the order-item grain

order_items is one row per line item. This supports partial shipments, partial cancellations, and backorders without widening the order row.

Step 3: Prefer to derive order status

orders does not need a status column of its own. The overall order state can be derived on read by rolling up its shipments (open until every shipment is delivered). A mutable status column on the order row drifts the moment a single package updates and needs a write on every scan, so the cleaner model derives it.

Step 4: Keep shipment status denormalized for reads

shipments.current_status is a cached projection of that shipment's event log. It costs a small consistency burden in exchange for fast "where is my package" lookups without an aggregate over events.

Step 5: Make events append-only

shipment_events should never be updated. Corrections are new rows with a CORRECTION event type. This is what lets the model survive carrier data quality issues.

The solution

Below is one conceptually sound approach. The split between shipments and shipment_events is the design anchor; the event log is the source of truth and the shipment row is a convenience projection. The overall order state rolls up from its shipments, so orders does not carry its own status column.

`shipment_events` is append-only; `shipments.current_status` is a derived projection for fast reads; orders has no status column, its state is rolled up from shipments
  • customers: customer_id BIGINT PK, email TEXT, created_at TIMESTAMP
  • orders: order_id BIGINT PK, customer_id BIGINT FK, placed_at TIMESTAMP, total_amount DECIMAL
  • order_items: order_item_id BIGINT PK, order_id BIGINT FK, sku TEXT, quantity INT, unit_price DECIMAL
  • shipments: shipment_id BIGINT PK, order_id BIGINT FK, carrier TEXT, tracking_number TEXT, current_status TEXT
  • shipment_events: event_id BIGINT PK, shipment_id BIGINT FK, event_type TEXT, event_timestamp TIMESTAMP, location TEXT
  • customers references orders
  • orders references order_items
  • orders references shipments
  • shipments references shipment_events

The analysis pattern

Shipments with longest dwell in transit
WITH hops AS (
    SELECT
        shipment_id,
        location,
        event_timestamp,
        LEAD(event_timestamp) OVER (PARTITION BY shipment_id ORDER BY event_timestamp) AS next_ts
    FROM shipment_events
    WHERE event_type = 'SCAN'
)
SELECT
    s.tracking_number,
    s.carrier,
    h.location,
    EXTRACT(EPOCH FROM (h.next_ts - h.event_timestamp)) / 3600 AS hours_stuck
FROM hops h
JOIN shipments s ON s.shipment_id = h.shipment_id
WHERE h.next_ts IS NOT NULL
ORDER BY hours_stuck DESC
LIMIT 25

Trade-offs and alternatives

Entity plus event log
Current state is a fast single-row read; full history is reconstructible; corrections are auditable. Cost: dual-write consistency between shipments and shipment_events and a background reconciliation job.
Event sourcing only
Single source of truth, perfect auditability. Cost: every current-state read is an aggregation over the log, and downstream consumers need a projection layer or a stateful view.

Common follow-up questions