TTC delay history, schedules, route metadata, and live alerts arrive in different formats, which makes reliable route and station analysis difficult.
Approach
Our team built a reproducible DuckDB and Parquet pipeline, linked delay records to GTFS entities, and served the analytical marts through a multi-page Streamlit dashboard.
Result
The platform turns 1 million+ normalized delay events into route, station, time-pattern, cause, and live-alert views with explicit data-quality checks.
How it works
Step 1 of 5: TTC delay data + GTFS + live alerts
Highlights
Co-developed a Python ingestion and normalization pipeline joining TTC delay data with static GTFS and live service alerts.
Processed over 1 million delay events into DuckDB and Parquet data marts, with checks for unmatched route and station mappings and reproducible Streamlit views.
Data workflow
Processed over 1 million bus and subway delay events into query-ready DuckDB and Parquet marts.
Linked historical records to static GTFS routes and stations, with QA for unmatched and ambiguous mappings.
Added GTFS-RT service-alert ingestion and validation alongside the historical data.
Structured the pipeline as raw, bronze, silver, and gold layers for repeatable rebuilds.
Dashboard focus
Ranked recurring route and station hotspots using frequency, severity, regularity, and cause mix.
Built views for monthly trends, weekday-hour patterns, cause signatures, and entity drill-downs.
Compared captured live alerts with historical reliability signals and surfaced coverage limits.
Kept metric definitions, lineage, and data-quality caveats visible in the interface.
Live dashboard
The dashboard runs on this site at /demos/ttc-pulse. It reads small files exported from the gold marts of the repository.