# The Handoff Canonical URL: Domain: Data Modeling · Difficulty: hard · Seniority: mid ## Problem DoorDash's logistics team is reviewing delivery speed by merchant and market, and a single delivery can be handed from one Dasher to another before the food arrives. They also need each Dasher's total pay, including tips that customers adjust in the hours after delivery. Design a model that supports on-time analysis per merchant and accurate Dasher pay even as assignments and tips change after the order completes. ## Worked solution and explanation ### Why this problem exists in real interviews This is a three-sided marketplace dressed up as a delivery dashboard, and it hides two time-varying truths that candidates love to flatten into static columns. A delivery is not owned by one Dasher, and a Dasher's pay is not final at delivery time. The trap is modeling both as attributes on the delivery row: a single dasher_key and a single tip_amount. Do that and you cannot represent a handoff at all, and the first tip adjustment silently rewrites last week's earnings report. > **Trick to solving** > > Spot the two things that change after the delivery row is born: who is carrying it, and how much they get paid. Both must live outside the delivery row. Assignment becomes a temporal child table; pay becomes an append-only event log. The delivery row itself stays a clean, immutable record of one completed delivery. --- ### Break down the requirements #### Step 1: Declare the delivery grain One row in fact_deliveries equals one delivery: placed_at, promised_at, delivered_at, the merchant, the consumer, the market. On-time rate is computed entirely from these timestamps. Items and pay do NOT belong here, because each would fan the row out. #### Step 2: Separate items from the delivery A delivery has many line items. Putting item rows in the delivery fact multiplies every on-time calculation by the basket size. Keep fact_order_items at one row per item, joined back to the delivery only when you actually need basket detail. #### Step 3: Make assignment temporal An 8 percent slice of deliveries change hands. delivery_assignments holds one row per (delivery, dasher) stint with assigned_at and unassigned_at. The dasher who completed it is the open stint. A static current_dasher_key can exist as a convenience, but the history is the source of truth. #### Step 4: Make pay an event log dasher_pay_events is append-only: base pay, peak-pay promo, initial tip, and later tip adjustments are all separate rows with occurred_at. A tip change at hour 20 is a new event, not an UPDATE. Weekly earnings is a SUM over events in the window, which stays correct no matter when the tip moved. --- ### The reference model The anchor decision: fact_deliveries is immutable and conformed dimensions hang off it, while the two volatile facts (assignment and pay) are modeled as their own grains. This reconciles fast dashboard reads with the reality that both ownership and money settle after the event. > **Why this works** > > On-time rate reads only fact_deliveries (delivered_at vs promised_at), so it is fast and never double counts. Weekly earnings sums dasher_pay_events in the window, so a late tip adjustment lands as a new row and the number self-corrects. A handoff is just two rows in delivery_assignments. ### The earnings query **Per-Dasher weekly earnings, tip adjustments included** ```sql SELECT p.dasher_key, d.full_name, SUM(p.amount) AS weekly_earnings FROM dasher_pay_events p JOIN dim_dashers d ON d.dasher_key = p.dasher_key WHERE p.occurred_at >= date_trunc('week', NOW()) - INTERVAL '1 week' AND p.occurred_at < date_trunc('week', NOW()) GROUP BY p.dasher_key, d.full_name ORDER BY weekly_earnings DESC ``` *Tip adjustments are extra rows in the window, so the sum is always current without any UPDATE.* > **Interviewers watch for** > > A strong candidate names the delivery grain in the first minute, then volunteers that assignment is time-varying and pay settles late, before being asked about handoffs or tip edits. Weak candidates draw one wide order table and only discover the reassignment problem when asked to count handoffs. > **Common pitfall** > > Storing tip_amount as a column on the delivery and updating it when the customer changes the tip. Every historical earnings report that already read that row is now silently wrong, and you have no record of what the Dasher was originally told they earned. Same mistake on assignment: a single dasher_key cannot represent a handoff at all. **Append-only pay events** Pay and tips are rows in dasher_pay_events. * Tip changes are new rows; history is intact * Weekly pay is a SUM over a time window * Slightly more storage and one extra join **Mutable pay column** tip_amount lives on the delivery row. * One UPDATE silently rewrites past reports * No audit of what the Dasher was first promised * Cannot reconstruct pay as of a past date > **How it scales** > > At 2 million deliveries a day, partition fact_deliveries and dasher_pay_events by date and cluster fact_deliveries by market. The on-time dashboard prunes to one day and one market; the earnings rollup scans one week of pay-event partitions. Both stay cheap because neither query touches the item fact. --- ## Common follow-up questions - A customer disputes a delivery and it is refunded three days later. Where does that land in the model? _(Tests whether refunds and clawbacks are modeled as further pay/financial events rather than deletes or updates.)_ - How would you compute, for each handed-off delivery, how many minutes each Dasher held it? _(Tests reasoning over assigned_at and unassigned_at in delivery_assignments instead of a single owner FK.)_ - Merchants change cuisine and market over time. How does that affect the on-time-by-market dashboard? _(Tests SCD Type 2 awareness on dim_merchants so historical deliveries attribute to the market in effect at order time.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the-handoff-doordash-delivery-model) - [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.