Model the order lifecycle of a food-delivery marketplace — placement to prep to pickup to drop — for three stakeholders who want contradictory truths, while one courier carries two strangers' dinners on a single run. A complete working through: the three grains, the bridge that makes stacking honest, the SLA invariant, streaming ingestion, payout close, and the dashboard.
This prompt sounds like a CRUD app and is in fact a marketplace with three irreconcilable points of view — and the moment you allow one courier to carry two orders at once, the naïve schema lies to at least one of them.
"Model the order lifecycle for a food-delivery platform — placement, restaurant prep, courier pickup, delivery — serving customers (on-time SLA), couriers (earnings), and restaurants (operations), with native support for stacked orders where one courier delivers several orders on a single dispatch. How do you scope it?"
The trap is the word order. It invites a single fact table — one fat row per order, with the customer columns, the restaurant columns, and the courier columns all sitting side by side — and for a while it works. Then the dispatch engine, hunting for efficiency, hands one courier two orders bound for the same block. Now there is no honest place to write the courier's pay. Split it across the two order rows and you have invented a fiction; the courier was never paid "per order," they were paid for a route. Write it once and double-count it on read. The single fact has no grain that fits all three readers, because there is no single event they are all watching.
A weak answer reaches for one table and patches the stacked case with a nullable batch_id and a prayer. A strong answer notices that the prompt names three actors who each live at a different grain — and that the only clean model is to give each their own fact and a bridge to reconcile them. So before any tables, the frame for the session:
Scope is the first scored dimension, so name it. In scope: the order/dispatch/bridge core, the state-event log that powers stage attribution, mutable tips, and the analytics that serve each side. Out of scope, said explicitly: the real-time dispatch optimizer (the routing brain that decides which orders to stack — treated as an upstream service that hands us a dispatch plan), menu and catalog, payments rails, fraud, and the customer-facing map. The caveat: the model must not preclude a courier picking up a third order mid-route, so stacking is N-ary from day one, never hard-coded to two.
Then the envelope math, volunteered. Marketplace-shaped numbers at national scale:
| Quantity | Estimate | Consequence |
|---|---|---|
| Orders per day | 25,000,000 | ≈ 290/s average, ~1.5 K at the dinner peak |
| State events per order | ~7 | placed, confirmed, ready, picked_up, delivered + retries |
| State-event writes | ≈ 2,000/s peak | The append-only firehose that shapes the telemetry tier |
| Batch rate (stacked share) | ~35% of dispatches | The bridge is the common case, not an edge case |
| Tip-mutation window | 24 h post-delivery | Economics must be append-only; no row is final on delivery |
| Active couriers at peak | ~600,000 | Sizes the dispatch fact and the earnings rollup |
| On-time SLA target | ≥ 92% of orders | The single number the customer org is graded on |
Read the table and the architecture is half-decided. The volume is trivial — 290 orders a second is a rounding error against the ride-sharing firehose. The pressure here is not throughput; it is reconciliation. One in three runs is stacked, every tip can move for a day after the fact, and three orgs will write conflicting SQL against the same data unless the grains are kept clean. The rest of this article follows the money through the bridge.
Two write paths, one reconciliation. Orders and dispatches are born in separate services at separate tempos; the bridge is stamped the instant the dispatcher commits a plan; the state-event log is the spine that lets anyone reconstruct what happened and when.
Three properties of this picture do most of the interview's work. First, the order plane and the dispatch plane never share a row — the customer's experience and the courier's pay are written by different services and only ever joined, never merged, so neither reader can corrupt the other's grain. Second, the bridge is the only place stacking is expressed, and it is written in a single transaction whose invariant is arithmetic: the per-leg payout shares must sum to exactly the dispatch payout. Third, the event log is dashed into everything because it owns no truth of its own — it is an append-only narration that lets the stage-latency job, finance, and disputes each reconstruct the timeline without locking a hot row.
Each grain owns its own numbers; the bridge owns only the relationship. A late delivery is a fact of fct_orders and never touches courier pay. A generous tip is a fact of fct_orders that flows to the dispatch through the bridge's leg_payout_share, but the order row remains the source of record. When the two disagree — and after a 24-hour tip edit they will — the order is authoritative for the customer, the dispatch is authoritative for the courier, and finance restates from the tip-lock boundary. No reader is ever forced to filter another reader's rows.
The schema falls out of the grain question. Customer truth: fct_orders. Courier truth: fct_dispatches. The treaty between them: brg_dispatch_orders. The narration that explains everything: fct_order_state_events. Around them, conformed dimensions, SCD2 where history has legal weight.
One row per order, born at placement and filled in as the order moves. The five lifecycle timestamps are nullable until reached — a ready_ts of NULL is not missing data, it is an order the kitchen has not finished. The two columns that earn their keep are tip_locked_ts (the immutability boundary; NULL means the tip can still change) and terminal_state, which records not just that an order ended but who ended it — the cancellation source is the difference between a refund and a restaurant warning.
The dispatch is the courier's shift-segment — accept to last drop — and its grain is deliberately blind to how many orders rode along. The count lives in total_orders as a convenience denormalization, but the truth of which orders, in which sequence, at what per-leg distance and pay, lives only in the bridge. A single dispatch carrying one order produces one bridge row with leg_sequence = 1; a stacked dispatch carrying three produces three. Stacking is not a flag bolted onto a 1:1 table — it is the cardinality of the bridge.
Every state transition lands in an append-only table, one row per change, never updated. This is what turns "the order was 18 minutes late" into "the restaurant prep took 22 minutes" — the difference between a complaint and an action. It also keeps the order row small and contention-free: payments, the SLA watchdog, and analytics are all downstream consumers of this log, not writers of fct_orders.
The dimensions are conformed and mostly SCD2, because history here has consequences. dim_restaurants versions its hours and menu so a dispute six weeks later reads against the menu that was live then. dim_addresses is SCD2 for the same reason a refund is a legal artifact — you must be able to prove where the order was going on the night it failed. dim_couriers versions vehicle type and rating tier; dim_h3_cell is SCD1 because a hexagon's geometry does not have a history worth keeping.
The correctness of the whole model lives in one arithmetic identity and one immutability boundary. The identity: the bridge's per-leg shares sum to the dispatch payout. The boundary: tip_locked_ts. Everything else is reporting.
A stacked dispatch is dangerous precisely because the same dollars are visible from two grains. The courier sees payout_usd on the dispatch; the customer's order contributes a tip that flows into that payout; an analyst joining the two can double-count or lose money in the seams. The guard is a closing transaction that refuses to commit a dispatch whose legs do not reconcile — RULE Nº 1 made executable.
A stacked dispatch loops through PICKED_UP → DELIVERING once per leg, but reaches CLOSED exactly once — and CLOSED is the only state in which money is written. Closing is where the invariant is checked: the sum of the legs' leg_payout_share_usd must equal the dispatch's payout_usd to the cent, or the close aborts and the dispatch is held for review. This is the food-delivery equivalent of a ledger that must sum to zero: the route's pay is conserved across its legs.
The second half of the invariant is temporal. A tip is mutable for 24 hours, which means a dispatch can close and its payout can still move. The model handles this without mutating closed facts: the tip edit is an append to the order's economic history, the dispatch's payout_usd is recomputed by the close job's idempotent re-run, and the moment the 24-hour window expires the order's tip_locked_ts is stamped. After that instant the order is frozen and finance restates the courier's earnings from the locked figure. Before the lock, every reader knows the number is provisional because the column tells them so.
Three programs carry the write path: the state-event ingester that narrates every order, the bridge writer that stamps a stacked plan in one transaction, and the leg-share allocator that turns a route's pay into per-order credit. Each is small; the judgment is in what they refuse to do.
Every transition is an immutable row. The ingester is idempotent by construction — a redelivered event with the same (order_id, state) is dropped, not double-written — so the upstream bus can be at-least-once without polluting the timeline. It refuses two things on principle: it never updates a prior state row, and it never advances fct_orders directly. The order row is updated by a separate projection that reads this log; the log is the source of truth.
The dispatch optimizer (upstream, out of scope) hands us a plan: one courier, an ordered list of orders, a per-leg distance. The bridge writer's whole job is to commit the dispatch row and every bridge row together, so the system is never observed in a state where a dispatch exists without its legs. It refuses partial writes — a plan with a gap in leg_sequence, or a duplicated order, is rejected before any row is touched.
When a dispatch closes, its payout must be split across the legs. The allocation is not arbitrary: base pay follows distance (the courier earned the miles), and each order's own tip is credited to its own leg (the tip belongs to the customer who gave it). The allocator's one discipline is that the parts must reconstitute the whole — it allocates by rounding down and assigns the residual cent to the longest leg, so the sum is exact and the § 04 guard always passes.
One carve-out, always stated: the allocator never decides whether to stack. Which orders share a route is the optimizer's call, made on live road conditions and SLA risk; the allocator only divides money after the fact. Keeping the economic split downstream of the routing decision is what lets the marketplace re-tune its batching policy without ever touching the payout code.
Two derived layers carry the slow loop. Stage latency turns the event log into per-stage durations and a whose-fault verdict. The payout close, run nightly and again at the 24-hour tip-lock boundary, freezes earnings and proves the bridge still balances.
Stage latency is a streaming projection over fct_order_state_events. For each order it pairs adjacent transitions — CONFIRMED→READY is prep, READY→PICKED_UP is pickup wait, PICKED_UP→DELIVERED is the drive — and writes them to a wide latency row that the restaurant and customer dashboards both read. The craft is in the attribution: a late order is not interesting; a late order with a 22-minute prep stage is an action item for a specific kitchen. The job is windowed on event time with a watermark, because a tablet that reconnects after a dead zone will deliver a READY event minutes after the wall clock moved on, and processing-time windows would blame the courier for the restaurant's Wi-Fi.
The payout close is the slow loop's other half — an idempotent batch that can be run any number of times and converges to the same answer. It recomputes each open dispatch's payout from the current tips, reruns the allocator, re-asserts the § 04 invariant, and — for any order whose delivery is now more than 24 hours old — stamps tip_locked_ts. Idempotence is the whole game: the close runs nightly for fresh dispatches and a second time after the lock window, and because every step is a recomputation rather than an increment, a retried or double-fired close never moves a courier's pay twice.
The facts are where the system explains itself — once per grain. Three queries an interviewer loves, because each serves a different stakeholder and each carries a classic SQL pattern on its back.
The customer org's defining query. Decompose the order's lifecycle into stages and let a CASE ladder name the bottleneck. The pattern is conditional aggregation's cousin — per-row classification — turning five timestamps into a single actionable verdict.
The courier org's defining query, and the one that justifies the entire bridge. Compare earnings per hour for batched versus single dispatches. If stacking does not beat single by a clear margin, the optimizer should stop offering it — the whole economic case is in this GROUP BY is_batched.
The restaurant org's defining query. Prep-time p50/p95 per restaurant per day, beside the restaurant-sourced cancellation rate. The pattern is percentile aggregation paired with a FILTERed count — one pass yields both the typical kitchen and its tail, plus how often it bailed entirely.
A senior design ends with observability, because every clean grain above is invisible without it. The dashboard watches the three stakeholders separately — each has its own definition of "healthy" — and one tile watches the seam between them.
Read the amber tiles together and the dashboard narrates a Friday dinner rush from the operator's chair: demand spiked, kitchens fell behind (prep p95 at 34 minutes, restaurant_slow owning 61% of the late orders), so on-time dipped and delivery time stretched — while couriers kept earning, stacking kept paying its 1.56× premium, and the seam stayed honest. The single most important tile is the boring one: bridge drift at zero. Everything else is a tunable; that one is the law.
Strip the dinners away and the question was testing five judgments, each of which generalizes far beyond food delivery: