London cycle-hire analytics

This project measures what happens to London's cycle-hire demand when the wider transport network is disrupted. It combines a PySpark historical backfill, a daily live-data job, a LightGBM demand model and a Streamlit app. The hosted workflow uses free GitHub Actions, stores outputs as committed Parquet and queries them through DuckDB, so there is no live warehouse to maintain.

Live demo · Engineering notes · Architecture

Analyst investigation

Decision supported: assess whether a verified, source-cited London Underground strike is associated with a material change in cycle-hire demand before treating disruption as an explanatory factor in analyst investigation. The locked ADR-0009 result is an observed association, not causation: the certified evidence reports 1.42× median station-day demand relative to its weather-adjusted expectation, with its existing uncertainty checks. See the certified-evidence note for source-to-consumer lineage.

In plain terms: on a Tube strike day, the typical docking station saw about 42% more hires than expected for that station, that weekday and that weather. "Expected" is a baseline built from the station's own history, not a guess. The result is deliberately framed as an association — strikes are announced in advance, cluster seasonally and coincide with weather, so this does not establish that the strike caused the extra cycling.

The workflow deliberately has two horizons: cited strikes support historical measurement; Line Status and BikePoint snapshots are forward-collected live monitoring. GitHub Actions + committed Parquet/DuckDB is the durable runtime. Airflow is a local portfolio demonstration, not the production scheduler.

This is one of three UK open-data projects on my profile. The other two are england-wales-housing-decision-support (analytics engineering: Dagster, a fully tested dbt project, published dbt docs) and community-energy-flex (a decision system with LP/MILP optimisation and a forecast-versus-actual review). See my GitHub profile for the full project map.

Note

The live demo is on Streamlit's free tier and may take ~30s to wake on the first visit. Journey data is published in bulk with a ~1–2 month lag. The workflow therefore keeps historical measurement separate from live monitoring and does not claim real-time trip prediction (ADR-0006).

Overview

Transport for London has published cycle-hire journeys since 2012, as CSV files in a public S3 bucket. An inventory of that bucket counted roughly 189M trips across 482 files — a dated snapshot taken once, not a claim about what the bucket holds today. The backfill actually built here covers 41.4M journeys in 148 retained files from 2022 to May 2026, spanning five different column layouts, because TfL renamed, dropped and reordered columns between eras.

• Disruption analysis: a weather-adjusted baseline that measures how Tube and rail strikes reshape cycling demand, per station and per day.
• Demand forecast: a LightGBM model that predicts station-level demand and improves the "normal" baseline the disruption effect is measured against.
• Daily live layer: a GitHub Actions job that refreshes line status and dock occupancy into committed Parquet, so the app keeps running with no warehouse and no server.

Full-network strike days reached ~2.3× normal cycling demand. Across 13 source-cited events, the median was 1.42× a weather-adjusted baseline.

What is implemented

• Backfill and reconciliation. PySpark unifies 41.4M journeys (2022–May 2026) across five ordered-header variants. Columns were renamed, dropped and reordered between eras. Every file reconciles raw rows to silver rows plus quarantine (captured warehouse evidence).
• Spark/Python boundary. Spark handles the multi-era backfill; plain Python handles the kilobyte-sized daily API pulls. The boundary is documented in the Spark ↔ Python boundary.
• Disruption estimate. A weather-adjusted baseline measures the strike effect at station × day grain: across 13 source-cited events, disruption days run 1.42× median demand, 95% CI 1.24–1.61 from a cluster bootstrap over event days — 2,000 replicates, resampling the event day rather than the station-day, because the day is the independent unit. A day-of-week-matched placebo over 1,000 random date sets puts the null median at 1.00 and gives a one-sided p < 0.001, and the estimate holds across a sensitivity battery. The certificate fixes claim_class: observed_association (ADR-0009).
• Certified evidence. analysis/certificate.py SHA-256-pins the ADR-0009 text, the five analysis inputs, the analysis code and the result payload into a single certificate ID, so a consumer cannot quietly re-derive the headline number. A drifted input, a changed comparator family or an edited ADR fails verification. Pinning bytes only means something if the bytes are reproducible, so every export imposes a total order over a dbt-proven unique key — three consecutive rebuilds now produce byte-identical artefacts — and the certificate also pins an order-independent content fingerprint, so a failure says whether the data changed or merely its row order (ADR-0014). The dbt exposures name the two consumers and instruct that the Power BI certified cards must not be rebuilt from DAX or affected by date or station slicers (certified-evidence note).
• Forecast baseline. A LightGBM model predicts station-level daily demand. Running it with the disruption flag off supplies a counterfactual "normal" baseline that's ~30% tighter than the median it replaces (~21% lower error on held-out 2026), tracked in MLflow (ADR-0008).
• Read-only questions. The "Ask the data" page has preset Quick answers with no API key; every figure comes directly from a query. It also has an optional bring-your-own-key Claude chat over curated, read-only tools (ADR-0007).
• Free daily runtime. A GitHub Actions job refreshes live line status and dock occupancy into committed Parquet. The app reads it through DuckDB, so it keeps running with no warehouse at all — the Snowflake phase was a one-off build on a free trial, and the workflow outlived it by design rather than depending on it.
• Station footfall context series. Daily rail entry/exit tap counts from TfL's crowding bucket — 1.17M rows over 439 stations from 2019, 5.3 MB as Parquet. It is re-published within days rather than the journey extracts' 1–2 months, so it narrows the lag the workflow has to work around. It is context, not evidence: taps are rail gate events, a different measure over a different population from cycle hire, so nothing joins it to dim_station and it feeds no claim (ADR-0013). The published files overlap and drift their header case between eras; both are handled by name and documented in source contracts.
• Slowly-changing station dimension. dim_station_history is a real SCD2 over BikePoint snapshots: an attribute signature over name, position, dock count, installed and locked state; change detection by lag(); version numbering by running sum; emitting valid_from, valid_to and is_current. A dbt unit test drives three mock snapshot rows through it and asserts two versions, and a singular test fails any station without exactly one current version. Its header records why it is deliberately not bridged to the journey-era dim_station: BikePoint names do not always conform to journey-file names, so a forced join would overstate identity certainty.
• Tested dimensional model. A dbt star schema (17 models) carries 92 data tests — 61 built-in, 22 dbt_utils, and 9 singular tests for the things generic tests cannot express: flow-to-daily reconciliation, journey-calendar completeness, snapshot-log gaps, one current SCD2 version per station, and a source URL on every seeded strike. Two dbt unit tests check model logic against mock rows, and dbt source freshness covers all 8 sources. Alongside it runs a 129-test pytest suite across 17 files: pipeline guards (idempotency, injected errors, an ML leakage guard), the evidence-certificate contract, and the reliability-reference conformance checks. The full DAG runs on both Snowflake (the documented build) and DuckDB. The rebuild reconciles exactly (ADR-0010).

App views

Demand forecast	Ask the data

The Demand forecast page shows predicted vs actual with the lift over the median baseline; the Ask the data page answers plain-English questions with no key required.

Architecture

flowchart TD
    A[retained TfL extracts<br/>148 files · 41.4M journeys] -->|PySpark backfill| S[(Warehouse<br/>silver · 41.4M journeys)]
    API[TfL Unified API<br/>BikePoint + Line Status] -->|Python daily loader| S
    W[Open-Meteo<br/>weather] --> S
    FF[TfL crowding bucket<br/>station footfall] -.context only.-> D
    S --> D{dbt<br/>17 models + 92 tests}
    D --> G[(Gold star schema<br/>+ demand-deviation)]
    G --> ML[LightGBM forecast<br/>MLflow · FastAPI]
    ML --> G
    G --> APP[Streamlit app<br/>+ Ask assistant]
    G --> MCP[MCP server]
    AF[[Airflow<br/>local portfolio demonstration]] -.orchestrates.-> API
    AF -.orchestrates.-> D
    GH[[GitHub Actions<br/>durable runtime]] -.daily runtime.-> API

Loading

Medallion layers: bronze (files/JSON as landed) → silver (typed, deduped, era-unified) → gold (tested star schema + analytical models + the model baseline).

The Spark/Python boundary

Spark handles the backfill because the historical archive is large and its headers changed across eras. Spark's positional CSV reader would misread reordered columns without per-variant, by-name projection. The daily BikePoint and Line Status payloads contain only a few hundred rows, so about 150 lines of requests and executemany are enough. See ADR-0002.

Machine learning

A LightGBM model (ml/) learns daily station-level departures from calendar, weather, disruption and recent-demand lag features. It is used in two ways:

• A sharper baseline. Predicting with the disruption flag off yields a counterfactual "normal demand" that replaces the coarse median in the deviation analysis (demand_deviation_ml).
• A validated forecast. Strict temporal validation: fit on 2022→2024, early-stop on 2025, report held-out 2026, so no window sees its own future. It has to beat two baselines on that held-out window and ml/train.py prints a plain PASS/FAIL on both — a stratified median computed from pre-test data only, which makes it leakage-free by construction, and seasonal-naive. It cuts error ~21% vs the median and ~28% vs seasonal-naive; runs are tracked in MLflow, a pytest feature-leakage guard backs the feature set, and it is served locally via FastAPI. See ADR-0008.

.venv/Scripts/pip install -r ml/requirements.txt
python ml/train.py        # LightGBM + MLflow tracking (local SQLite store)
python ml/predict.py      # -> app/gold_export/predicted_demand.parquet
uvicorn serve:app --app-dir ml --port 8000   # local /predict endpoint

The assistant

The "Ask the data" page only answers questions supported by its tools. It never invents a figure (ADR-0007):

• Quick answers: preset questions answered directly from the gold layer with no API call, so every number is exactly what the query returned (busiest stations, the strike effect, demand trend, and a live "why is this line disrupted now?"). Free for every visitor.
• Ask anything: an optional free-form Claude chat that calls the same curated, read-only tools and reports only numbers a tool produced. It runs on a bring-your-own-key basis, so the owner's Anthropic credits are never spent by anonymous visitors.

A separate read-only MCP server (mcp/) exposes the same gold layer to AI clients through typed tools. It reads committed Parquet through DuckDB, so it needs no warehouse or credentials (ADR-0004).

Tech stack

Layer	Tool
Batch processing	PySpark (Dockerised)
Warehouse	Snowflake (build) → DuckDB + Parquet (durable, free)
Transformation & tests	dbt
Machine learning	LightGBM · MLflow · scikit-learn · FastAPI
Orchestration	GitHub Actions (durable) · Airflow (local portfolio demonstration)
App & AI access	Streamlit · Anthropic SDK · Model Context Protocol
Enrichment	TfL Unified API · Open-Meteo

Quickstart

python -m venv .venv
.venv/Scripts/pip install -r app/requirements.txt   # Windows; use .venv/bin/pip on macOS or Linux
streamlit run app/streamlit_app.py                   # runs on committed Parquet, no warehouse

Verified on Python 3.13. The demo app reads committed Parquet through DuckDB, so it needs no database and runs fully offline — clone and run, nothing to provision. The Quick answers work with no setup; only the optional free-form chat needs your own ANTHROPIC_API_KEY (in .env locally). To reproduce the warehouse build (Spark → Snowflake → dbt), see docs/.

Project structure

ingestion/   API loaders, warehouse loaders, data-export scripts
spark/       multi-era backfill job
dbt/         staging + marts models, tests, seeds
analysis/    the certified ADR-0009 evidence: rigor.py computes it, certificate.py pins it
ml/          demand model: features, LightGBM training (MLflow), batch predict, FastAPI serving
app/         Streamlit app (DuckDB over committed gold Parquet) + Ask assistant
mcp/         read-only MCP server over the gold layer
powerbi/     code-first semantic model over the same Parquet (DAX, Power Query M, TMDL)
benchmark/   constructed reliability-reference fixtures and cross-engine conformance suite
infra/       Airflow (Docker Compose), run scripts, bounded Databricks proof candidate
tests/       pytest suite (feature-leakage guard, Quick answers, tool dispatch), run in CI
docs/        ADRs, architecture and engineering notes

The Power BI layer is written as code rather than clicked together: see powerbi/README.md, which states plainly what has and has not been opened in Power BI Desktop from this repo.

How it stays live

A daily GitHub Actions job (.github/workflows/daily.yml) runs the whole chain in order and stops at the first failure: snapshot live line status and dock occupancy → ingest newly published journey CSVs (journey_increment.py, schema-gated and idempotent) → dbt build and dbt source freshness, which gate delivery → regenerate the certified ADR-0009 evidence → re-verify its certificate → append a run-metadata audit row → commit → ping a dead-man's-switch. A red run auto-opens a GitHub issue. The app's Pipeline health page shows coverage, freshness and the audit trail, including gaps. It needs no warehouse or persistent server and runs on free tiers indefinitely.

Note

Which workflow gates what. The dbt tests gate delivery, not pull requests. They run in daily.yml ahead of the commit step, so failing data is never published. The CI badge at the top of this README is ci.yml, which runs on every push and pull request and covers ruff, the 129-test pytest suite and the cross-engine conformance benchmarks — it does not run dbt.

Station footfall runs on its own weekly trigger (.github/workflows/footfall.yml), not the daily one, because the source is re-published every few days — on a daily schedule it would commit nothing most mornings. It is deliberately narrow: it fetches the rolling file, builds and tests only its own model, and never touches the ADR-0009 certified inputs.

A third scheduled job (.github/workflows/keepalive.yml) pings the app every few hours so visitors are less likely to encounter a cold start. This works around free tier, not a Streamlit limit.

The local Airflow portfolio implementation keeps its alert boundary equally explicit: the daily-ingest, dbt-gate and archive-drift DAGs share a failure callback that always records a critical task-log event. A webhook is opt-in through a local environment variable and is not exercised by this project.

Reliability reference

The separate portable reliability reference uses constructed fixtures and a reviewed JSON oracle to prove replay, replacement, complete-object rejection, and interruption recovery across DuckDB and digest-pinned Spark. CI runs it as a cross-engine parity gate on every pull request: a DuckDB conformance run, a second conformance run inside a digest-pinned apache/spark:4.0.1 container, then a semantic comparison of the two decoded results — all three reports uploaded as artefacts. It does not import application state or alter the living workflow.

A later phase of that work (tier 3) attempted to extend the same suite to Databricks Delta, under a pre-agreed budget of one deployment plus one corrective attempt. It did not succeed, and the write-up below is deliberately kept as the record of that:

T3 attempted a bounded Databricks Delta proof. The gate ended NARROW — the narrowest of the pre-declared outcomes, meaning the attempt was bounded and stopped rather than passed — because the initial deployment and one allowed corrective deployment could not reliably read workspace Python files. The semantic oracle was never reached. Teardown was independently verified, no managed conformance claim is made, and DuckDB/Spark 0.2.0 remains the supported suite.

Download reliability reference v0.2.0

Engineering notes

• ADR-0001: dataset selection, with measured evidence
• ADR-0002: Spark environment and schema-drift handling
• ADR-0003: orchestration sizing and the incremental boundary
• ADR-0004: MCP read-only controls
• ADR-0005: the demo layer and durable hosting
• ADR-0006: the live workflow and the journey-lag boundary
• ADR-0007: curated tool-calling, public preset answers and BYOK chat
• ADR-0008: LightGBM counterfactual baseline with temporal validation
• ADR-0009: claim, design, assumptions, falsifiers and correction log
• ADR-0010: the completed Snowflake-to-DuckDB migration
• ADR-0011: licence-bounded reliability-reference extension
• ADR-0012: the SCD2 station history and the local alert boundary
• ADR-0013: station footfall as a context series, not evidence
• ADR-0014: making the certified inputs byte-reproducible
• Portable reliability reference: recovery protocol, constructed fixtures and DuckDB/Spark parity
• Source contracts: upstream dependencies, failure signals, and the licence/rate-limit position
• Directions not taken: four product directions a 2026-07-27 scan killed, and why
• Snowflake evidence: captured warehouse figures and cost

Limitations

• Associational, not causal. The 1.42× effect is an observed association against a weather-adjusted baseline with stated assumptions. Strikes are announced, seasonal, and weather-entangled, so causal identification is out of scope (ADR-0009).
• Two data horizons. Deep event history exists only for strikes (publicly documented, source-cited); the API-derived log of all disruption types accumulates forward from 2026-07-08 only. Line-level proximity analysis activates once journey extracts cover that window.
• Journey data lags ~1–2 months because of TfL's bulk publishing schedule. Live monitoring and historical measurement are separate claims. Station footfall (ADR-0013) refreshes within days and narrows that window, but it does not close it: tap counts measure a different thing on a different network and cannot stand in for journey data.
• Missed snapshot days are permanent. The API keeps no history; 2026-07-11/12 were lost to a since-fixed crash and are shown as holes on the Pipeline health page, not hidden.
• Increment approximation: arrivals on extract-boundary dates can miss rides that started in the previous file. Departures, the demand measure used here, are exact.
• GitHub Actions cron is best-effort and the free Streamlit tier sleeps; both are accepted, documented trade-offs of the zero-cost runtime.

Attribution

Powered by TfL Open Data. Contains OS data © Crown copyright and database rights 2016 and Geomni UK Map data © and database rights 2019. Weather by Open-Meteo (CC-BY 4.0).