Ridehail Analytics

Work in progress. This is a long-running project I’m building in the open, one layer at a time.

The idea

Real ridehail data is hard to come by, so I’m building the whole thing: a simulated ridehail service with drivers, customers, vehicles, rides, fares and ratings, and then the data platform you’d build around it. The goal is to cover the full stack of a data team’s work — modelling, generating data, batch and streaming pipelines, a warehouse, and an app that puts the numbers in front of people.

Architecture

There are two paths for the data:

• Batch: Postgres → Airflow → dbt → ClickHouse, for historical analytics.
• Real-time: Kafka → Flink → ClickHouse, for live analytics on rides in progress.

Postgres is the transactional source of truth, with foreign keys and strong consistency. ClickHouse is the analytical store, a columnar database where aggregations over millions of rows come back fast. A FastAPI backend reads from both, and a React app serves role-specific views for admins, drivers and riders.

What’s built so far

A simulated ridehail service

Every entity is a Python dataclass that maps straight onto a Postgres table. They also serve as the schema for the future Kafka event payloads. Rides follow a real lifecycle: REQUESTED → ACCEPTED → EN_ROUTE_TO_PICKUP → ARRIVED_AT_PICKUP → IN_PROGRESS → COMPLETED, with a path to CANCELLED from the early stages.

There are two generators, both built on Faker:

• Batch produces historical records. About 8% of rides are cancelled, weighted 55% by the customer, 35% by the driver and 10% by an admin. Fares and ratings exist only for completed rides.
• Real-time keeps a pool of rides in flight at once and emits their events in wall-clock order. Each active ride holds a pre-computed, time-sorted event queue, and a min-heap always surfaces whichever ride has the next event due:

while heap:
    active = heapq.heappop(heap)
    yield active.pop()

    if active.done:
        yield active.fare
        yield from active.ratings
        heapq.heappush(heap, self._new_active_ride())
    else:
        heapq.heappush(heap, active)

When a ride finishes, its fare and ratings go out straight away and a new ride takes its slot, so the stream runs indefinitely.

Loading 2.5 million rides

The historical load is 2.5 million rides across 500 drivers and 5,000 customers. Inserting that row by row would be painfully slow, so the loader uses psycopg3’s COPY … FROM STDIN protocol in chunks of 10,000 rides, which keeps memory bounded however large the batch.

A web app with role-based views

A FastAPI backend and a React + TypeScript frontend. Everyone signs in on the same page and lands on the view for their role:

• Admin: platform totals and rides by status.
• Driver: earnings, rating, vehicle and recent rides.
• Customer: spend, rating and recent rides.

Auth is a stateless JWT, but every request is still checked against the accounts table, so deactivating an account takes effect immediately. The API also starts if ClickHouse is down, and its health check reports the service as degraded rather than failing outright.

One-command infrastructure

docker compose up brings up Postgres, ClickHouse, a one-shot migration job, the API and an nginx container that serves the React build and proxies /api. The database ports are bound to 127.0.0.1, so local tools can reach them while they stay closed to the network on a server. Only the web app’s port is public.

The warehouse model

The warehouse is a star schema, with one fact table, fact_rides, holding every fare measure and rating score, and dimension tables for driver, customer, vehicle, location (used for both pickup and dropoff), date and time. High-frequency ride events skip the warehouse and go straight to the real-time pipeline.

What’s next

• A Kafka producer for the real-time generator, and Flink jobs to enrich and window the events before they land in ClickHouse.
• The ClickHouse schema, Airflow DAGs for the Postgres → ClickHouse export, and dbt models.
• Porting the live ride map (currently a Streamlit prototype) into the admin view.
• Surge zones with admin controls, and richer views: live parameters for admins, telemetry for drivers and trip tracking for riders.
• Hosting the whole stack.