# The Long Route > A package splits, reroutes, and (maybe) arrives. Canonical URL: Domain: Data Modeling · Difficulty: hard · Seniority: L5 ## Problem We run a large e-commerce marketplace. A unit is bought from a vendor, received at one of our fulfillment centers, then picked, packed, and shipped to the customer. A single order often splits across several fulfillment centers, and one outbound truck can carry items from many different orders. Operations needs to see not just where every unit sits right now, but the full path it took to get there. Design the data model. ## Worked solution and explanation ### Why this problem exists in real interviews This is a per-unit lifecycle history dressed up as a logistics schema. The real skill being probed: can you tell that 'see the full path it took' is a demand for an append-only state log, not a status field? Anyone can list the entities. The trick is refusing to put a mutable `status` column on `shipments` and refusing to hang a single `shipment_id` on `order_lines`. Get either wrong and you can answer 'where is it now' but never 'why did this box sit in picking for six hours', and a split order becomes untrackable. > **Trick to Solving** > > Before drawing tables, a strong candidate asks: do we need to know where every unit is right now, or the full trajectory of every unit? The signal is 'the full path it took to get there,' which rules out a status column and demands an event log. > > 1. Dimension vendors, warehouses, and products > 2. Split inbound (vendor) and outbound (customer) lifecycles > 3. Model status changes as rows, not columns > 4. Link order lines and shipments through a junction so orders split and shipments consolidate --- ### Break down the requirements #### Step 1: Identify the entities and their roles `vendors`, `warehouses`, and `products` are stable dimensions. `purchase_orders` and `customer_orders` are headers. `shipments`, `shipment_lines`, and `shipment_events` are the facts that move units through the system. #### Step 2: Split inbound from outbound `purchase_orders` with `purchase_order_lines` track vendor-to-warehouse movement. `customer_orders` with `order_lines` track warehouse-to-customer movement. Two lifecycles, one shared product dimension. #### Step 3: One order splits, one shipment consolidates A single order often splits across fulfillment centers, and one outbound truck carries items from many orders. That is a many-to-many between order lines and shipments, so a single `shipment_id` on `order_lines` cannot express it. #### Step 4: Model status transitions as events `shipment_events` is one row per state change (picked, packed, in-transit, delivered). A status column on `shipments` would erase the history; the event log preserves every transition with a timestamp. #### Step 5: The shipment_lines junction `shipment_lines` is the junction: one row per (shipment_id, order_line_id, quantity_shipped). It supports partial shipments (one order line spread across shipments) and consolidation (one shipment holding many order lines), and it is the join path for per-unit tracking. --- ### The solution Below is one defensible design: separate inbound and outbound lifecycles sharing a product dimension, a `shipment_lines` junction for the split, and `shipment_events` as an append-only state log. Every relationship reads parent to child (a vendor has many purchase orders, a shipment has many events), so the model expresses only 1:N and, through the junction, M:N. > **Why this works** > > The event log is the source of truth for 'where is every unit right now.' Status can be derived as the latest event per `shipment_id`, joined back to order lines through `shipment_lines`. The canonical trade-off is write volume (one row per transition) for full lifecycle auditability. > **Interviewers watch for** > > A strong candidate argues for immutable events within the first two minutes and names the derived 'current status' query. They also catch that an order line splits across shipments and a shipment consolidates many orders, so they reach for a junction rather than a single shipment_id column. Weak candidates put a `status` column on `shipments` and then cannot answer how to compute dwell time per state. > **Common pitfall** > > Two failures show up together. First, mutating `shipments.status` in place, which overwrites the history so SLA diagnostics (why was this box delayed in picking) become impossible. Second, hanging a single `shipment_id` on `order_lines`, which cannot represent a partial shipment or a consolidated truck. --- ### The analysis pattern **Dwell time per state per warehouse** ```sql WITH ranked AS ( SELECT se.shipment_id, se.event_type, se.event_ts, LEAD(se.event_ts) OVER (PARTITION BY se.shipment_id ORDER BY se.event_ts) AS next_event_ts FROM shipment_events se ) SELECT s.warehouse_id, r.event_type, AVG(EXTRACT(EPOCH FROM (r.next_event_ts - r.event_ts)) / 3600) AS avg_hours_in_state FROM ranked r JOIN shipments s ON s.shipment_id = r.shipment_id WHERE r.next_event_ts IS NOT NULL GROUP BY s.warehouse_id, r.event_type ``` --- ### Trade-offs and alternatives **Append-only event log** Perfect trajectory, easy dwell-time queries, idempotent inserts. Cost: deriving current status requires a window function and the event table grows fastest of all. **Mutable status columns plus audit table** Simple current-status queries. Cost: history lives in a second table, state machine violations are possible, and reconciliation between the two tables is manual. --- ## Common follow-up questions - How do you detect out-of-order events arriving from multiple warehouses with clock skew? _(Tests event time versus processing time and whether the candidate uses monotonic sequence numbers.)_ - How would you back out a canceled order while keeping the original shipment events? _(Tests whether cancellation is a new event type or a mutation.)_ - What if a unit is returned from the customer and re-shelved at a warehouse? _(Tests whether the return is a new order in the opposite direction or an extension of the original.)_ - At 10M shipments per day, how do you partition `shipment_events`? _(Tests partition on event_ts and possibly subpartition by warehouse_id.)_ - How do you enforce that a shipment cannot move from delivered back to in-transit? _(Tests whether the state machine lives in the database, the application, or a separate service.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_long_route) - [Data Modeling Interview Questions](https://datadriven.io/data-modeling-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.