Dimensional Modeling Example: Ride-Hailing Trips and Drivers

This is a complete dimensional model for a ride-hailing service like Uber or Ola, and a harder model than retail: trips have a multi-stage lifecycle, every trip has two locations, prices include surge, drivers are measured by how they spend their time, and shared rides carry several riders in one vehicle. The service runs about 20 million trip requests a day in 600 cities across many time zones. Analysts need hourly freshness for operations and exact daily numbers for finance.

1. Requirements

Data realities: trip events stream in from apps and can arrive minutes late or out of order; fares are finalised after drop-off and sometimes adjusted days later (refunds, disputes); riders and drivers change cities and vehicles; times must be reported in each city's local time.

2. Bus matrix

3. Trips (transaction fact)

Grain: one row per trip request, whether it completes or is cancelled. Choosing requests rather than completed trips keeps cancellations and unfulfilled requests in the same table, so conversion (completed ÷ requested) is a simple ratio of two sums.

create table fact_trip (
  request_date_local_key  int not null,  -- dim_date, city-local calendar
  request_date_utc_key    int not null,  -- dim_date, for global reporting
  request_time_of_day_key int not null,  -- dim_time_of_day (minute grain, local)
  rider_key               int not null,  -- version valid at request time
  rider_profile_key       int not null,  -- mini-dimension: rating band, trip-count band
  driver_key              int not null,  -- -2 = "No driver assigned" for unfulfilled requests
  pickup_zone_key         int not null,  -- role-playing dim_zone
  dropoff_zone_key        int not null,  -- role-playing dim_zone; -2 when never dropped off
  product_key             int not null,
  trip_status_key         int not null,  -- completed, rider cancelled, driver cancelled, unfulfilled
  trip_flags_key          int not null,  -- junk: surge band, airport, scheduled, accessibility
  payment_method_key      int not null,
  promotion_key           int not null,
  currency_key            int not null,
  trip_id                 varchar(36),   -- degenerate dimension
  is_completed            smallint,      -- 0/1 counters make rates sums
  is_cancelled            smallint,
  distance_km             decimal(8,2),
  duration_min            decimal(8,2),
  base_fare_local         decimal(12,2),
  surge_amount_local      decimal(12,2), -- additive; the multiplier itself is non-additive
  tolls_fees_local        decimal(12,2),
  gross_booking_local     decimal(12,2),
  gross_booking_usd       decimal(12,2), -- converted at the request-date rate
  driver_payout_usd       decimal(12,2),
  surge_multiplier        decimal(4,2)   -- kept for display and banding only; never summed
)
partition by (request_date_utc_key);

Surge: a non-additive number

A surge multiplier of 1.8 can't be summed or averaged meaningfully across trips. Store the surge amount (fare attributable to surge) as an additive fact, so surge revenue share is sum(surge_amount) / sum(gross_booking). Put a surge band (none, 1-1.5×, 1.5-2×, 2×+) in the junk flags dimension for grouping, and keep the raw multiplier only for display.

Two locations, one dimension

Pickup and drop-off both point at dim_zone through role-playing views (dim_pickup_zone, dim_dropoff_zone). A zone is a hexagonal geospatial cell (an H3 cell, for example) with a flattened hierarchy: zone, neighbourhood, city, region, country, plus attributes such as airport flag and time zone. Raw latitude and longitude stay in the lake; the warehouse groups by zone.

Time zones

A trip at 23:30 in Mumbai and one at 23:30 in New York are both "late evening" for local operations but fall on different UTC days. Store both a local date key and a UTC date key, plus a local time-of-day key. City dashboards use local keys; global finance closes the day on UTC.

4. Dimensions

5. Trip lifecycle (accumulating snapshot)

Grain: one row per trip request, updated at each milestone: requested, matched to a driver, driver arrived, rider picked up, dropped off, or cancelled. It answers the operational questions that the transaction fact can only answer with painful self-joins over events.

create table fact_trip_lifecycle (
  trip_id                 varchar(36) primary key,
  pickup_zone_key         int not null,
  product_key             int not null,
  driver_key              int not null,
  requested_ts            timestamp,     -- precise times for ordering and lags
  matched_ts              timestamp,
  arrived_ts              timestamp,
  picked_up_ts            timestamp,
  dropped_off_ts          timestamp,
  cancelled_ts            timestamp,
  requested_date_key      int not null,  -- and one date key per milestone ("Not yet" row until then)
  dropped_off_date_key    int not null,
  seconds_to_match        int,
  seconds_driver_en_route int,           -- rider wait time
  eta_quoted_seconds      int,
  eta_error_seconds       int,           -- actual arrival minus quoted ETA
  seconds_on_trip         int,
  cancellation_stage      varchar(20),   -- before match, before arrival, after arrival
  is_completed            smallint,
  is_cancelled            smallint
);

Events arrive late and out of order, so the load is an idempotent MERGE on trip_id that only fills a milestone if its timestamp is newer or still empty. Keep the raw event stream in the lake: the snapshot is a derived view of it and can be rebuilt after a bug.

6. Driver activity (periodic snapshot)

Grain: one row per driver per hour per zone they were online in. Drivers are measured by how they spend time, which is state, not events.

create table fact_driver_activity_hourly (
  hour_date_local_key  int not null,
  hour_of_day_key      int not null,
  driver_key           int not null,
  zone_key             int not null,
  product_key          int not null,
  minutes_online       decimal(6,2),  -- additive within the hour; sum across hours for totals
  minutes_on_trip      decimal(6,2),
  minutes_en_route     decimal(6,2),
  minutes_idle         decimal(6,2),
  trips_completed      int,
  earnings_usd         decimal(12,2),
  primary key (hour_date_local_key, hour_of_day_key, driver_key, zone_key, product_key)
);

Minutes are additive, unlike balances, so weekly online hours are a sum. Utilisation is a ratio: sum(minutes_on_trip) / sum(minutes_online), never an average of hourly percentages. Only hours with activity are stored, which keeps the table to roughly drivers online × hours rather than every driver for every hour.

7. Incentive coverage (factless fact)

Driver incentives ("complete 20 trips this weekend in zone X for a bonus") need a record of who was eligible, not just who earned it. A factless coverage table, fact_incentive_eligibility (date, driver, zone, promotion), lists eligible driver-zone-days. Joined with driver activity, it answers "did eligible drivers go online more than ineligible ones?", which the payout records alone can never show.

8. Shared rides: one vehicle, several riders

A pooled trip carries several riders, each with their own pickup, drop-off and fare. Forcing that into fact_trip at one row per vehicle trip makes the rider multivalued. The cleaner fix is to change the grain: fact_trip is one row per rider request, which already gives every rider their own fare and locations. A small fact_vehicle_trip (one row per vehicle journey, with distance driven and occupancy) carries the driver-side measures, and both share trip_group_id as a degenerate dimension. No bridge table is needed, and no fare is ever double counted. See hierarchies and bridge tables for when a bridge is the right tool instead.

9. Questions the model answers

-- Request-to-completion rate and cancellations by stage, per city and day (local time)
select z.city, d.full_date,
       sum(t.is_completed) * 1.0 / count(*)          as completion_rate,
       sum(case when l.cancellation_stage = 'after arrival' then 1 else 0 end) as cancelled_after_arrival
from fact_trip t
join fact_trip_lifecycle l on l.trip_id = t.trip_id  -- same grain (one row per request), so this join is safe
join dim_zone z on t.pickup_zone_key = z.zone_key
join dim_date d on t.request_date_local_key = d.date_key
group by z.city, d.full_date;

-- Median rider wait by pickup zone during the evening peak
select z.neighbourhood, percentile_cont(0.5) within group (order by l.seconds_driver_en_route) as median_wait_s
from fact_trip_lifecycle l
join dim_zone z        on l.pickup_zone_key = z.zone_key
join dim_time_of_day h on extract(hour from l.requested_ts) = h.hour   -- or a stored time-of-day key
where h.day_part = 'Evening peak' and l.is_completed = 1
group by z.neighbourhood;

-- Surge share of gross bookings by city and product, last month
select z.city, p.product_name,
       sum(t.surge_amount_local) / nullif(sum(t.gross_booking_local), 0) as surge_share
from fact_trip t
join dim_zone z    on t.pickup_zone_key = z.zone_key
join dim_product p on t.product_key = p.product_key
join dim_date d    on t.request_date_local_key = d.date_key
where d.month = '2026-09' and t.is_completed = 1
group by z.city, p.product_name;

-- Driver utilisation by city, last week
select z.city, sum(a.minutes_on_trip) / nullif(sum(a.minutes_online), 0) as utilisation
from fact_driver_activity_hourly a
join dim_zone z on a.zone_key = z.zone_key
join dim_date d on a.hour_date_local_key = d.date_key
where d.week = '2026-W39'
group by z.city;

10. Follow-up questions and answers

Common mistakes on this problem

• Modelling only completed trips, which makes cancellation and conversion analysis impossible.
• Averaging surge multipliers or hourly utilisation percentages instead of summing additive parts.
• One date key in UTC only, so city reports split evenings across two days.
• Joining a driver activity snapshot to trips directly, mixing an hourly grain with a per-trip grain.
• Storing pickup and drop-off as two different dimensions instead of one zone dimension in two roles.
• Updating the original trip row for refunds, so finance can't reproduce a closed day.