DoorDash data interview pack

The DoorDash loop

1. SQL

   One row per delivery vs per item. Which metrics fan out if you pick wrong.

2. Streaming

   A courier ping is 8 minutes late. Event time vs the 09:00 dashboard.

3. Features

   Online/offline parity for an ETA model. Warehouse is the wrong 20 ms path.

SQL (2)

1. medium · SQL

   Running total of daily revenue. Why is SUM(SUM(amount)) OVER (...) valid?

   The inner SUM is the GROUP BY aggregation (daily revenue). The window SUM then walks those grouped rows. You need both: group to the day first, then window. A bare SUM(amount) OVER (ORDER BY day) without grouping double-counts every order row.

2. medium · SQL

   A date spine is missing Sundays. What breaks in a 7-day rolling metric?

   The window walks existing days, not calendar days, so Sunday is skipped and Monday’s “7-day” is six days plus a hole. Generate a complete date dimension and LEFT JOIN facts onto it before the window.

Orchestration (1)

1. medium · Dagster

   A late partition arrived. What must not happen?

   A full rebuild of every downstream asset. Partitioned assets + targeted backfill. If your graph cannot backfill one day, it is a batch script wearing assets.

Streaming (1)

1. hard · Kafka

   What is a poison pill, and how do you not stall the whole partition?

   A record that always fails deserialization or sink validation. A naive retry loop blocks that partition forever. Dead-letter the record (with the offset), alert, keep consuming. Know the difference between retryable sink errors and bad payloads.

System design (4)

1. medium · System design

   Design an orders platform for 40 analysts at 9:00 and a 15-minute CDC lag. Sketch the layers.

   OLTP CDC → raw/bronze files → typed silver (MERGE on order_id) → gold at one grain (order or line — pick one) → a serving warehouse or marts for BI. Orchestrate freshness; do not transform 2 TB in the orchestrator. Page on freshness and volume, not only “job succeeded.”

2. hard · System design

   When is a warehouse the wrong serving plane?

   Sub-100ms point lookups, per-user online features, or a product API that is not SQL. Then you need a keyed store or a feature/online path — and a contract with the batch grain.

3. easy · System design

   Streaming dashboard vs a 1-hour mart. How do you choose?

   If the decision can wait an hour, batch is cheaper and simpler. Streaming is for operational actions (fraud, inventory, live ops). Do not stream a CFO dashboard to look modern.

4. medium · System design

   Online store for features must answer in 20 ms. Warehouse p95 is 800 ms. What is the serving path?

   Not the warehouse. Materialize features to Redis/Dynamo/Feast online, with a batch/stream publisher and a mismatch monitor. Warehouse is the offline train path.

Data quality (1)

1. easy · Data quality

   Where should the contract live — producer or warehouse?

   Producer owns the schema and SLA. Warehouse tests are defense in depth. A contract only in dbt is how source teams ship breaking changes on Friday.

AI / ML (3)

1. hard · AI / RAG / MLOps

   Feature store vs “a gold table named features.” What is the extra work?

   Point-in-time correct joins (no leakage), online/offline parity, and a serving path with low latency. A gold table is a start. If training used yesterday’s snapshot and serving uses a live join that can see the label, you shipped leakage.

2. medium · AI / RAG / MLOps

   Online vs offline features for the same model.

   Same definition, two stores, a point-in-time story. If online computes a different join, you shipped skew.

3. hard · AI / RAG / MLOps

   Offline feature table has 40 columns; online store has 12. The model’s AUC collapsed after deploy. First check?

   Training-serving skew: missing features defaulted, or a different join grain online. Diff a production request against the training row for that entity_id and time. Then freeze a schema contract.

Behavioral (1)

1. medium · Behavioral

   A source team broke a contract on Friday. How did you handle the people side?

   Page on the contract test, not the person. Share the failing rows, the SLA, and a dated fix. Then move the contract upstream so warehouse tests are defense in depth. Blame in Slack is the junior tell; a written producer contract is the senior one.

SQL coding labs

Problem statements only. Run them against tests on DataLane; solutions live in the pad.

1. medium · about 12 min · tables: orders

   Cancel rate by country

   Ops wants a cancel rate per country on one row: total orders, cancelled orders, and the ratio. The trap is integer division — cancelled / orders returns 0 in most engines unless you force a float.

   https://www.datalane-data.blog/coding/problems/cancel-rate-by-country/

2. easy · about 8 min · tables: order_items

   Orders with more than one line

   Multi-line orders are the ones that break naive revenue joins, so find them first. Return every order carrying more than one line item, with its line count and total quantity.

   https://www.datalane-data.blog/coding/problems/orders-with-multiple-items/

3. hard · about 18 min · tables: orders

   Second order within seven days

   Retention wants customers whose second completed order landed within 7 days of their first completed order. Return customer_id and the days between those two orders, smallest customer_id first.

   https://www.datalane-data.blog/coding/problems/repeat-within-7-days/

4. hard · about 16 min · tables: orders

   Customers above average order volume

   CRM wants customers whose completed order count is strictly above the average completed-order count across customers who have at least one completed order. Return customer_id and completed_orders, highest volume first.

   https://www.datalane-data.blog/coding/problems/above-average-order-count/