Model a Ride-Hailing Marketplace

Prompt

“Design the data model for our ride-hailing analytics. Leadership wants to understand marketplace health: trips, revenue, cancellations, driver supply and ratings.”

Business questions (get these first)

1. Completed trips, gross bookings and take rate by city by day/week.
2. Cancellation rate by who cancelled (rider/driver), city and hour of day.
3. Average time from request → accept → pickup → drop-off (where is the funnel slow?).
4. Driver supply hours and utilisation (time on trip / time online) per city.
5. Average rating given to drivers by riders; rating distribution by driver tenure.
6. Revenue by rider’s segment at the time of the trip (riders move between segments).

Clarifying questions

Business processes → fact tables

Bus matrix

ERD

erDiagram
    FCT_TRIPS }o--|| DIM_RIDER : rider_key
    FCT_TRIPS }o--|| DIM_DRIVER : driver_key
    FCT_TRIPS }o--|| DIM_VEHICLE : vehicle_key
    FCT_TRIPS }o--|| DIM_PRODUCT : product_key
    FCT_TRIPS }o--|| DIM_GEO : pickup_geo_key
    FCT_TRIPS }o--|| DIM_GEO : dropoff_geo_key
    FCT_TRIPS }o--|| DIM_DATE : request_date_key
    FCT_TRIPS }o--o| DIM_CANCEL_REASON : cancel_reason_key
    FCT_PAYMENTS }o--|| FCT_TRIPS : trip_id
    FCT_RATINGS }o--|| FCT_TRIPS : trip_id
    FCT_DRIVER_SESSIONS }o--|| DIM_DRIVER : driver_key
    FCT_TRIPS {
        string trip_id PK "degenerate"
        bigint rider_key FK
        bigint driver_key FK "unknown -1 until accepted"
        bigint product_key FK
        bigint pickup_geo_key FK
        bigint dropoff_geo_key FK
        timestamp requested_at
        timestamp accepted_at
        timestamp arrived_at
        timestamp started_at
        timestamp completed_at
        timestamp cancelled_at
        string cancelled_by "rider, driver, system"
        string final_status
        int wait_seconds "accepted to arrived"
        int trip_seconds
        decimal distance_km
        decimal surge_multiplier
        decimal fare_local
        decimal fare_usd
        string currency
    }
    DIM_RIDER {
        bigint rider_key PK
        string rider_id
        string segment "SCD2"
        string home_city
        date signup_date
        date valid_from
        date valid_to
        boolean is_current
    }
    DIM_DRIVER {
        bigint driver_key PK
        string driver_id
        string city "SCD2"
        string tier "SCD2"
        date onboarded_date
        date valid_from
        date valid_to
        boolean is_current
    }
    DIM_GEO {
        bigint geo_key PK
        string h3_cell
        string zone
        string city
        string country
        string timezone
    }

Key design decisions

1. Trips as an accumulating snapshot. One row per request, updated as milestones arrive (MERGE by trip_id). Durations between milestones are precomputed facts → question 3 becomes trivial. Cancelled trips stay in the same table (status + cancelled_by), so cancellation rate = cancelled / requested with no join.
2. Role-playing dimensions: dim_geo as pickup and drop-off; dim_date/time for each milestone (store timestamps in UTC and the city’s local date key, since hour-of-day analysis must use local time).
3. Driver unknown until accepted: driver_key = -1 (“Unassigned”) for requests never accepted, so no NULL FKs.
4. Payments separate from trips. Different grain (tips, refunds and adjustments arrive days later). Gross bookings come from trips; net revenue from payments. Don’t put refunds on the trip row.
5. SCD2 on rider segment and driver city/tier to answer question 6 “as it was”. Fact rows store the version key resolved at request time.
6. Pool trips: multiple riders per vehicle trip. Either grain = rider-trip (one row per rider request, with a shared_trip_id degenerate dimension) or a bridge. Rider-trip grain keeps revenue additive per rider.
7. Utilisation = trip seconds / online seconds per driver per day: needs fct_driver_sessions (interval facts) and trip intervals. Compute in a derived daily driver fact.

Sample queries against the model

-- Q2: cancellation rate by who cancelled, city, local hour
SELECT g.city, EXTRACT(HOUR FROM t.requested_at_local) AS hr,
       AVG(CASE WHEN t.final_status = 'cancelled' AND t.cancelled_by = 'rider'  THEN 1.0 ELSE 0 END) AS rider_cancel_rate,
       AVG(CASE WHEN t.final_status = 'cancelled' AND t.cancelled_by = 'driver' THEN 1.0 ELSE 0 END) AS driver_cancel_rate
FROM fct_trips t JOIN dim_geo g ON t.pickup_geo_key = g.geo_key
WHERE t.request_date_key BETWEEN 20261001 AND 20261031
GROUP BY 1, 2;

-- Q6: completed revenue by rider segment at trip time (SCD2 version key stored on the fact)
SELECT r.segment, SUM(t.fare_usd) AS revenue_usd
FROM fct_trips t JOIN dim_rider r ON t.rider_key = r.rider_key
WHERE t.final_status = 'completed'
GROUP BY r.segment;

Trade-offs

Follow-up questions

Rubric

• Collected business questions before drawing tables
• Identified multiple business processes with distinct grains
• Used an accumulating snapshot for the trip lifecycle
• Role-playing geo and date dimensions; local vs UTC time
• Separated payments (late adjustments) from trips
• SCD2 for attributes needing history; unknown member keys
• Validated the model by writing queries for the questions