A ride-sharing analytics model centers on a core trip transaction fact table at the grain of one row per trip request, capturing both completed rides and cancellations. Surrounding conformed dimensions represent riders, drivers, vehicles, time, and geospatial zones, supplemented by periodic snapshot tables for driver availability and separate payment transaction facts.
Ride-sharing grain and core tables
The central transaction table is fact_trips. Declaring the grain as one row per requested trip is necessary because modeling only completed rides completely blinds the business to search-to-book drop-offs, passenger cancellations, and driver supply shortages:
fact_trips trip_id (degenerate dimension) rider_key driver_key (defaults to -1 Unknown if cancelled prior to driver match) vehicle_key pickup_zone_key dropoff_zone_key request_date_key request_time_key status_key (requested, accepted, arrived, completed, rider_cancelled, driver_cancelled) base_fare_amount surge_multiplier distance_miles duration_seconds driver_wait_seconds rider_wait_seconds estimated_eta_seconds actual_pickup_duration_seconds
Key architectural mechanics for trip lifecycle tracking:
- Cancellations: When a rider cancels before a driver is matched,
driver_keypoints to sentinel-1. Storing detailed cancellation reason codes captures whether riders abandon due to excessive wait times or high surge pricing. - ETA accuracy: Storing both estimated arrival duration and actual pickup duration allows algorithmic dispatch teams to calculate ETA prediction error by city and time of day.
Dimension and metric breakdown
Surrounding conformed dimensions provide context for slice-and-dice operational queries:
dim_driver: Uses SCD Type 2 to track changes in driver home territory, active operating city, vehicle assignment, and background verification status. A connected mini-dimension or banded profile handles volatile driver rating scores.dim_rider: Captures rider signup cohort, default payment method, and corporate versus personal account categorization.dim_geo_zone: Models standardized spatial polygons or Uber H3 hexagonal grid cells for pickup and drop-off locations, enabling neighborhood-level demand heatmaps.dim_vehicle: Stores vehicle make, model, model year, passenger capacity, and vehicle category (such as economy, comfort, or electric).
Driver availability and ride tracking
A trip transaction table alone cannot answer supply questions like how many drivers were online but idle in downtown Seattle during rush hour. To measure marketplace efficiency, construct a companion periodic snapshot table: fact_driver_availability_snapshot (grain: one row per driver per 15-minute interval). This table records driver status flags (offline, available and searching, dispatched to pickup, on active trip) alongside active online minutes. Financial transactions are modeled in a separate table, fact_trip_payments. Decoupling payments from trip requests handles split fares between multiple riders, tips added hours after trip completion, promotional coupon vouchers, and driver toll reimbursements cleanly without duplicating or distorting physical trip counts.