Three-Sided Marketplace Delivery Schema

A hard 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
hard
Seniority
L6

Problem

We run a three-sided marketplace: consumers place orders from merchants, and couriers deliver them. Finance needs accurate revenue reporting. Operations needs delivery latency metrics. One order can be reassigned to a different courier if the first one cancels. Design the data model.

One order. Two deliveries. Revenue counted twice. Where is the bug in your schema?

Worked solution and explanation

Why this problem exists in real interviews

This is grain separation dressed up as a marketplace schema. The real skill: keeping two related but differently-grained processes from corrupting each other, so a reassigned courier cannot double-count GMV and a cancelled attempt cannot null out revenue. Anyone can draw six tables. The tell is putting fact_orders (one row per order, owns revenue) and fact_deliveries (one row per delivery attempt, owns latency) at two separate grains. Get it wrong and one join fans out revenue on every 4% of orders that got reassigned.

Break down the requirements

Step 1: Declare two grains

fact_orders sits at one row per order. fact_deliveries sits at one row per delivery attempt per order. Revenue questions join neither; latency questions filter to completed deliveries.

Step 2: Revenue lives only on fact_orders

subtotal, tip, delivery_fee, and platform_commission all live here. Never on deliveries. A reassignment duplicates a delivery row, not a revenue row.

Step 3: Latency lives only on fact_deliveries

assigned_ts, pickup_ts, and dropoff_ts belong to the attempt. Orders hold only order_placed_ts.

Step 4: Filter cancelled attempts out of averages

A cancelled attempt has a null dropoff_ts. Any latency query filters WHERE delivery_status = 'completed' to avoid contaminating averages.

The solution

Below is one defensible model. The conceptual anchor is grain separation: revenue is order-level, latency is delivery-level, and the two facts never trade places.

Revenue columns live only on `fact_orders`; latency timestamps live only on `fact_deliveries`
  • dim_consumers: consumer_sk BIGINT PK, consumer_nk TEXT, city TEXT, signup_ts TIMESTAMP
  • dim_merchants: merchant_sk BIGINT PK, merchant_nk TEXT, category TEXT, city TEXT
  • dim_couriers: courier_sk BIGINT PK, courier_nk TEXT, vehicle_type TEXT
  • dim_date: date_key INT PK, calendar_date DATE, day_of_week TEXT
  • fact_orders: order_sk BIGINT PK, order_nk TEXT, consumer_sk BIGINT FK, merchant_sk BIGINT FK, date_key INT FK, order_placed_ts TIMESTAMP, subtotal FLOAT, tip FLOAT, delivery_fee FLOAT, platform_commission FLOAT
  • fact_deliveries: delivery_sk BIGINT PK, order_sk BIGINT FK, courier_sk BIGINT FK, attempt_number INT, assigned_ts TIMESTAMP, pickup_ts TIMESTAMP, dropoff_ts TIMESTAMP, delivery_status TEXT
  • dim_consumers references fact_orders
  • dim_merchants references fact_orders
  • dim_date references fact_orders
  • fact_orders references fact_deliveries
  • dim_couriers references fact_deliveries

The analysis pattern

GMV and completed-delivery latency by merchant category
SELECT
    m.category,
    SUM(o.subtotal + o.delivery_fee + o.tip) AS gmv,
    AVG(EXTRACT(EPOCH FROM (d.dropoff_ts - o.order_placed_ts)) / 60.0)
      FILTER (WHERE d.delivery_status = 'completed') AS avg_minutes
FROM fact_orders o
JOIN dim_merchants m ON m.merchant_sk = o.merchant_sk
LEFT JOIN fact_deliveries d
  ON d.order_sk = o.order_sk
 AND d.delivery_status = 'completed'
GROUP BY m.category

Trade-offs and alternatives

Two facts at different grains
Revenue and latency are independently queryable. No double-count risk. Cross-fact queries need an explicit filter to completed deliveries.
Accumulating snapshot on order
One row per order with the latest delivery timestamps upserted in place. Reporting is a single table scan. Reassignment history is lost unless stored on a child audit table.

Common follow-up questions