Data platform, BI, and reporting v1
- Build the nightly batch pipeline: OLTP + event archives → warehouse → BI, finance, and regulatory outputs.
- Keep analytics load off the production database — structurally, not by promise.
The raw materials already exist: trip events (1.7), the ledger (1.8), the archived ping firehose (1.4), onboarding events (1.2). This lesson moves them somewhere queries are cheap and analysts are free.
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.