How TTC Pulse works: from delay logs to route scores

TTC Pulse scores how reliable TTC buses, subways and streetcars have been, by route, station, hour and cause. It joins 1,111,831 delay events from January 2014 to January 2026 with the TTC schedule (GTFS) and with TTC service alerts. The pipeline uses DuckDB and Parquet. The dashboard on this site shows the results, and its Live view adds the current TTC alerts and vehicles.

What the data shows:

• Delays are concentrated. 29 of 284 bus route keys (the top 10%) have 50.1% of all bus delay events.
• Rush hours carry a large share. 42.8% of bus delay events occur on weekdays from 06:00 to 10:00 and from 15:00 to 19:00.
• Mechanical faults are the top bus cause. They cause 234,358 of 723,518 bus delay events (32.4%).
• The first reliability score has a small-sample fault. A route with 10 delay events ranked above the route with the most delay events. I explain the fault and the fix below.

TTC Pulse was a team project at York University (Data Analytics and Visualization, Winter 2026). I wrote the first versions of 33 of the 36 pipeline modules, including the reliability score. When I write "we", I mean the team.

Architecture

The system has two parts. The build part runs locally and makes the data. The request part runs on Cloudflare and serves the dashboard.

• Build time. Python code ingests the three sources and writes a registry with a SHA-256 checksum for each file. DuckDB loads the rows into bronze tables. Then the pipeline writes the silver and gold layers as Parquet files with ZSTD compression.
• Export. The script scripts/export_site_data.py reads only the gold marts. It writes 9 JSON files (129,777 bytes in total) that ship with the site. It also writes one file for each of 308 routes, stations and lines (2.6 MB in total, at most about 13 KB each). The drill-down loads one of these only when a visitor opens that route.
• Request time. A Cloudflare Worker serves the Next.js page. The historical views read the bundled JSON, so the site needs no Streamlit server and no database server. A second route on the same Worker serves the live data. The live layer section below describes it.

Data flow: bronze, silver, gold

Each layer has one job. Bronze keeps the source rows. Silver cleans them and links them to the schedule. Gold aggregates them for the dashboard.

• Bronze keeps each source row as it arrived. It adds three lineage fields: source_file, source_row_id and row_hash. All 1,111,831 delay events keep these fields in silver, so each event traces back to its file and row.
• Silver normalizes dates, routes, stations and causes. The bus route labels are free text, so the normalizer puts each label into one of three tiers. Exact means the full label is a route number. Token means the label starts with a route number or contains LINE n. Fallback means the label has no route number. Then it joins the number to the GTFS route list. 95.8% of delay events (1,065,527) match by the exact tier and 4.2% (46,304) by the token tier. The fact table has no fallback rows.
• Gold aggregates silver into 8 marts for the dashboard. The marts do not keep the row IDs.

The logs hold delays only, not the trips that ran on time. So the dashboard shows counts, not rates.

The reliability score

I wrote the first version of the score in src/ttc_pulse/marts/scoring.py. It combines four inputs into one number:

score = 0.35 z(frequency) + 0.30 z(severity_p90) + 0.20 z(regularity_p90) + 0.15 cause_mix

• Frequency is the number of delay events.
• Severity is the 90th percentile of delay minutes.
• Regularity is the 90th percentile of gap minutes between vehicles.
• Cause mix is 1 minus the sum of squared cause shares. It is a diversity index, not a z-score.

In the ranking mart, each z-score compares one route or station with the others of the same mode and entity type. The window is the 90 days to 31 January 2026. A check script recomputes the stored score from its four inputs for all 893,537 route rows. The largest difference is 2.2e-16.

The small-sample fault

For bus routes, route 28 Bayview South ranks 1st with only 10 delay events. Route 32 Eglinton West has the most delay events (447) and ranks 9th.

8 of the top 10 routes have fewer than 60 delay events. Their severity_p90 is 287 to 471 minutes. With few events, one long delay sets the 90th percentile. The pipeline sets bus delays of 999 minutes or more to null, but values below 999 stay. So a quiet route with one bad day can rank above a route that is late every day.

The fix: weight the score by sample size

On the site, I added an adjusted score:

adjusted = score × n / (n + k)

n is the number of delay events of the route or station in the 90-day window. k is the median n for that mode and entity type. When n is small, the weight is near 0 and the score moves toward 0, which is the mean of a z-score. When n is large, the weight is near 1 and the score stays almost the same. This is a simple credibility weight.

For an even count of entities, the export takes the upper of the two middle values as the median. With the adjusted score, route 32 moves from 9th to 1st. Route 28 moves from 1st to 18th. The limit: k is a rule of thumb. I did not fit it to the data.

Two queries and their results

Both queries run with DuckDB on the committed Parquet files, from the root of the TTC Pulse repository. The results come from TTC Pulse commit d5d2a02.

Query 1: the adjusted bus ranking

WITH r AS (
  SELECT entity_id, frequency AS n, severity_p90, composite_score, rank_position
  FROM read_parquet('gold/gold_top_offender_ranking.parquet')
  WHERE mode = 'bus' AND entity_type = 'route'
),
k AS (SELECT median(n) AS k FROM r)
SELECT r.entity_id AS route, d.route_long_name AS name, r.n,
       round(r.severity_p90, 1) AS severity_p90,
       round(r.composite_score, 2) AS score, r.rank_position AS raw_rank,
       round(r.composite_score * r.n / (r.n + k.k), 2) AS adjusted,
       rank() OVER (ORDER BY r.composite_score * r.n / (r.n + k.k) DESC) AS adjusted_rank
FROM r CROSS JOIN k
LEFT JOIN read_parquet('dimensions/dim_route_gtfs.parquet') d ON d.route_id = r.entity_id
ORDER BY adjusted DESC
LIMIT 5;

The two routes with the most delay events move to the top. Two routes with a high severity_p90 (127 and 162) stay in the top 5, because their raw score is high enough to survive the weight.

Query 2: how many delay events match a GTFS route

SELECT mode, link_status, sum(row_count) AS events,
       round(100.0 * sum(row_count) / sum(sum(row_count)) OVER (PARTITION BY mode), 1) AS pct
FROM read_parquet('gold/gold_linkage_quality.parquet')
GROUP BY mode, link_status
ORDER BY mode, link_status;

Only bus has unmatched events, because only the bus logs use free-text route labels.

The live layer

The Live view on the dashboard calls a server route on the Cloudflare Worker at /api/ttc-pulse/live. The route fetches two TTC feeds:

• the TTC live alert API, https://alerts.ttc.ca/api/alerts/live-alerts
• the GTFS-Realtime vehicle positions feed, https://bustime.ttc.ca/gtfsrt/vehicles

The route caches the result for 60 seconds. The TTC feeds give no delay value for each trip. So the Live view does not compute a live score. It shows the current alerts (significant delays, detours and closures) and the live vehicles. Then it joins each route to its historical rank from the gold marts.

Three details keep the route small and safe:

• A small decoder. The vehicle feed is protobuf. The route reads only the route ID and the position of each vehicle. It does not load a full GTFS-Realtime library. On one feed of 1,166 vehicles, its output matches the official bindings exactly, and it decodes in about 1 to 4 ms.
• A last good copy. The route keeps a second copy for one day. If a TTC feed fails, the page shows that copy with its feed time and a "Feed unavailable" label. The panel is never empty.
• One map for all views. The maps draw the TTC network from the GTFS route shapes, simplified to 42 KB. Vehicles and stations sit on the same projection.

The historical data does not change between exports, so it ships as static JSON. Only the live data needs a server route. We first built the dashboard in Streamlit. For a public link, I compared two options:

I chose static JSON and one live route. The gold marts stop at 31 January 2026, so a live query on them adds no new data. If a TTC feed fails, the historical views still work, because they do not use the live route.

Data quality findings

• Unmatched bus routes. 16,793 bus delay events (2.3%) do not match a GTFS route. The most common raw labels are 56 (2,479 events), 81 (2,441) and 190 (2,341). These events stay in silver with unmatched_review. The route marts have 0 rows with a NULL route.
• Raw cause codes. From 2025, all bus and streetcar cause values are raw codes, for example EFO and MFWEA. These codes have no description in the incident code dimension. The cause charts for 2025 and 2026 therefore show codes, not names.
• One station at one hour. Yonge Station has 2,343 of its 8,229 delay events at 22:00. 2,085 of those have the cause "Miscellaneous Other". This looks like a logging pattern, not a real peak. It needs a check before anyone uses the station ranking.
• Snapshot checksums. 60 of the 234 alert snapshot files do not match the SHA-256 value in the registry. All 60 are smaller than the recorded size.
• Alert loads. The alert parser is not idempotent. A second run appends the same alert rows again. The alert table also has one build time per row (16 April 2026), so it cannot show when an alert started.

Limits and next steps

• The raw files and the DuckDB database are not in the repository. The full pipeline does not run from a clone. The silver and gold Parquet files do, so the queries above run from a clone.
• The repository has no tests and no CI. I would add the number check script to CI first, because it already exits 0 on a correct build.
• The AWS kit (S3, Glue Data Catalog, Athena) is written but has not run on AWS.
• To turn counts into rates, I would join the GTFS trip counts per route and hour.
• I would fit k in the adjusted score from the data, and not use the median.
• I would give the alert parser a unique key on snapshot time, alert and entity, so a second run changes nothing.

Where this lives in the repo

The code is in the TTC Pulse repository:

• Bronze load and lineage fields: src/ttc_pulse/bronze/build_bronze_tables.py
• Bus route matching: src/ttc_pulse/normalization/normalize_bus.py
• Score weights and z-scores: src/ttc_pulse/marts/scoring.py
• Ranking mart and 90-day window: src/ttc_pulse/marts/build_gold_rankings.py
• JSON export for this site: scripts/export_site_data.py
• Number checks: scripts/check_readme_numbers.py
• AWS kit: aws/