Data platform, BI, and reporting v1
The raw materials already exist: trip events (1.7), the ledger (1.8), the archived ping firehose (1.4), onboarding events (1.2). The next step moves them somewhere queries are cheap and analysts are free.
At first glance, it seems reasonable to run the nightly report against production primaries because that is where the freshest data lives. The choice optimizes the common case by weakening the boundary that matters. Analytics may be late but must not consume transactional capacity, mutate ride truth, or publish a partially validated reporting set.
The prime directive: analysts never query production. One analyst's accidental cross join on the trips table is a self-inflicted outage. Nightly, an ETL orchestrator extracts from read replicas and the object-store archives, transforms into a dimensional model — fact tables (fact_trips, fact_payments, fact_sessions) and dimensions (dim_city, dim_driver, dim_rider, dim_date) — and loads a columnar warehouse. Data is checked (row counts, nulls, ledger sums to zero) before publication; a broken dashboard erodes trust almost as fast as a broken app.
On top of the warehouse: BI dashboards (city GMV, trips, active drivers, surge hours, cancellation rates — refreshed daily), finance outputs (revenue recognition from the ledger, driver tax documents, PSP reconciliation), and regulatory reports — many cities require trip counts, service-area coverage, and safety statistics on a schedule; these are legally mandated functional requirements, and they're why we kept immutable event trails everywhere. Product experimentation v1 also lives here: assignment logs plus next-morning outcome analysis.
flowchart LR
subgraph PROD ["Production — untouched by analysts"]
RR[("Read replicas")]
AR[("Object store — ping + event archives")]
end
RR --> ETL["Nightly ETL — orchestrated, checked"]
AR --> ETL
ETL --> DQ{"Quality gates — counts, nulls, ledger sums to 0"}
DQ -- "pass" --> WH[("Columnar warehouse — facts + dimensions")]
DQ -- "fail" --> HOLD["Hold publication + page data on-call"]
WH --> BI["📊 BI dashboards — city ops, exec"]
WH --> FINR["💰 Finance — rev rec, tax docs, PSP reconciliation"]
WH --> REGR["🏛️ Regulatory reports per city"]
WH --> EXP["🧪 Experiment analysis"]
Worked example: a driver's weekly earnings statement
sequenceDiagram
autonumber
participant CRON as Orchestrator (Sunday 02:00)
participant WH as Warehouse
participant LED as Ledger (via replica)
participant GEN as Statement generator
participant S3 as Object store
participant C as Comms service
CRON->>WH: aggregate week per driver — trips, hours, gross
CRON->>LED: authoritative sums — earnings, tips, adjustments, take
GEN->>GEN: cross-check warehouse vs ledger (must match to the cent)
GEN->>S3: render PDF statement, signed URL
GEN->>C: notify driver — statement ready
Note over GEN: mismatch → halt, page finance-eng. Never send a wrong number.
Timezones will corrupt your metrics quietly: "trips per day" must be the city's local day, not UTC, or every dashboard is wrong by a rush hour. Model time in dim_date per city from day one — retrofitting timezone correctness into a year of aggregates is misery.