gil lu portfolio
Transit Pulse
SNAPSHOT·warehouse capture —
Collection is complete: eight cities polled from 2026-08-23 to 2026-09-25, all eight scored. Every figure is a warehouse snapshot captured —.
TRANSIT PULSE

Which city runs the most reliable transit?

Eight cities, five countries — agency realtime feeds, 35 service days of them, scored against each agency's own timetable.

Kafka · Spark · Iceberg · Snowflake · dbt · Dagster — the warehouse behind every number, snapshot .

STANDINGS · CLOSED JUDGED DAYS ONLY

Reliability, city by city

A 0–100 composite over every closed judged day, weighted by the service each route runs. Tokyo leads at 98.8, then Zurich 93.6 and Helsinki 92.2; SF Bay closes the table at 60.5. Each service day is scored against the timetable that was live that day.

On-time = arrival within −1 to +5 min of schedule. Excess wait = time beyond the scheduled headway on frequent service. Added (unscheduled) trips count toward volume, never toward on-time.

SHAPE · WHEN, AND HOW LATE

The shape of a service day

Every city is scored in its own timezone — 3am in Helsinki compares to 3am in New York. Click a city chip to isolate it in both charts; hover for exact values.

On-time share by local hour

All closed judged days pooled, scored stop events only — an hour with fewer than 200 scored events is left out rather than drawn from a handful

On-time share by service day

Each city's closed judged days, counted from its first — day 1 is the first day it was judged

Where the delay mass sits

Distribution of arrival delay, 30-second bins, share of each city's scored events — hover a bin for its share

On-time share by mode

Same on-time rule, split by vehicle type; a dot means the city runs no scored service of that mode
FINDINGS · SHARPER QUESTIONS

Four more questions, measured

Same eligibility gate as the standings — closed judged days, routes at or above the completeness floor. Where a feed cannot express something (a network that never emits a cancellation, a schedule without timepoints), the table says so instead of printing a flattering zero.

Does the weekend run better?

On-time share, weekday vs weekend, sorted by the weekend edge

Does the rush hour break the system?

Each city's own seeded peak windows, local time

Who leaves early?

Departures from scheduled timepoints more than 60s ahead, any mode whose schedule marks them — the rider they strand arrived on time

Who cancels, and who can't say?

Cancelled share of scheduled trips; a dash means the feed never emits CANCELED at all
DEEPER · FOUR MORE CUTS

Beyond the on-time share

The same judged days as the standings, cut four more ways. Hover any mark for its numbers.

How late is late?

Delay of scored arrivals: the dot is the median, the bar runs to the 90th percentile, the tick marks the 75th

Does rain slow it down?

On-time share in hours with at least 0.5 mm of rain, against the same hours of the day when it was dry

Does lateness build along the line?

Median delay by how far along its trip the vehicle is, first stop to last

Is reliability even across a city?

Every route with at least 2,000 scored arrivals, one dot each, by on-time share; the shaded band holds the middle 80% of a city's routes
NETWORK · THE WAREHOUSE, DRAWN

Every route, from static GTFS to gold

Route paths from each agency's static feed, stations colored by measured mean delay on the latest full service day, plus every vehicle's position at 08:30 local on the city's last judged day, where the feed publishes positions.

ROUTES · BEST AND WORST

The routes at the extremes

Across all eight cities, minimum 2,000 scored events — enough evidence that a single bad day can't put a route on this list.

Most reliable

Highest on-time share, evidence-weighted

Least reliable

Lowest on-time share — where a schedule is closer to fiction
PIPELINE · FIVE LAYERS

What happens to one poll

Every number on this page started as a single HTTP request to an agency. Eight poller containers held 30 endpoints at a 30-second floor for a month, and each response was archived verbatim before anything interpreted it — so every layer below can be rebuilt from the one above without asking an agency for the same bytes twice.

LayerWhat it holdsVolumeThe rule that governs it
Raw
S3, verbatim
Every poll exactly as the agency returned it — protobuf for GTFS-RT, JSON for ODPT 162 GB
531,745 objects at peak
Immutable and replayable. A 7-day expiry rule held it flat instead of unbounded, and with every feed retired it drains to empty.
Bronze
Iceberg on S3
Decoded envelopes, one row per record, still untyped and undeduplicated 139 GB
119,770 objects
Keeps everything. No watermark, so nothing here can arrive too late to be kept.
Silver
Iceberg on S3
One schema for eight cities: typed, deduplicated, service-date resolved 2.96B rows
48.8 GB · 16 tables
Exact-duplicate drop inside a 2-hour watermark, which is why drains run every 2 hours.
Gold
Snowflake + dbt
Realtime matched to schedule, delays computed, metrics rolled up 422.9M rows
50 models
A metric with no evidence is NULL, never zero. 242 tests run on every build.
Site
static JSON
The snapshots this page reads — ten metric files plus one map per city 18 files
13 MB
Built from gold, committed to the repo, so a deploy is a plain static publish.
FEEDS · WHAT EACH CITY SENDS

Thirty endpoints, eight shapes

No two agencies publish the same thing. Raw volume is each city's seven days of polling to 2026-09-12, while every feed was live, and a feed's size says more about how it is published than about how much service the city runs: Zurich ships a whole national network on every poll, Tokyo ships one JSON array of trains. The vehicle column is what was in motion at once: the most trips in progress in any single poll-minute, by mode, across three weekdays — a trip counts from its first scheduled stop until its last prediction passes, one vehicle each. Where a city also publishes positions, the small print says what those confirm; vehicles reporting with no trip (deadhead, layover, yard) are noted and never folded in.

CityFeedsCadenceRaw / 7dStops servedIn motion at onceWhat it gives
New York
MTA
830s10.5 GB
182,280 obj
989391 subway trains · 7 SIR trains
the subway feed carries no vehicle ids, so a trip is a train; 472 trains hold a trip at the same moment, 81 of them still at their origin
Eight subway feeds, trip updates + positions + alerts, no key required
Boston
MBTA
330s24.4 GB
68,360 obj
7,344600 bus · 57 tram · 51 metro · 38 rail
positions agree: 663 buses report on a trip at the peak, the rest waiting at a terminal; none report without one
Subway, bus, commuter rail, occupancy, and real cancellations — 5,748 on judged routes
Toronto
TTC
330s13.1 GB
65,943 obj
9,2201,395 bus · 176 tram · 1 ferry
positions agree: 1,434 buses on a trip at the peak, plus 769 reporting with no trip at all — deadhead, layover, yard
Every surface vehicle; 225 of 230 scheduled routes observed
Helsinki
HSL
230s16.1 GB
45,567 obj
8,168884 bus · 98 tram · 37 metro · 34 rail
HSL publishes no positions — trips in progress in the prediction feed, one vehicle each
Tram, metro, rail and bus predictions, keyless — but trip_id ships empty on every row
Washington DC
WMATA
630s15.2 GB
136,724 obj
7,696820 bus · 111 metro
positions agree: 950 buses on a trip at the peak, plus 258 reporting with no trip
Rail and bus as separate feed families, every scheduled route observed
SF Bay
511.org
3200s5.7 GB
10,239 obj
19,0221,376 bus · 95 tram · 49 BART · 27 rail · 26 cable car · 9 ferry
positions agree: 1,528 buses on a trip at the peak, plus 622 reporting with no trip; BART publishes predictions, not positions
~30 operators behind one regional feed; ten of them publish schedule and no realtime
Zurich
Swiss OTD
260s / 600s77.5 GB
12,524 obj
9,306700 bus · 144 tram · 103 rail
the Swiss feed carries delays, not positions or times — a trip inside its scheduled span at the poll, one vehicle each
A national feed filtered to a 615-route allow-list — about 7 MB per poll, delays only
Tokyo
ODPT
330s / 60s1.9 GB
56,934 obj
136155 trains · 586 bus
trains from odpt:Train, one record per train in motion; buses from ToeiBus positions no older than 2 min and on a trip
Operator-stated delays for four Toei subway lines, 51 KB per poll

Chicago is the ninth city in the plan and the one that never opened: its GTFS-RT key has never activated. The Train Tracker API does answer on the same key, but it publishes run numbers rather than trip ids, so scoring it would mean building a matcher from scratch — and a score needs 20 judged days this project does not have left.

STORAGE · WHAT EACH CITY PUT IN THE WAREHOUSE

Eight cities, row by row

One card per city, whole history, no eligibility gate — this is the inventory, not the judgment. The ring is the mode mix of the city's finalized stop events; the tiles are what it left in silver and gold; the line under them is its fleet at the busiest minute of three weekdays. The bars are stop events per service day: solid days are judged by the standings, hollow ones are open, truncated, or after the poller was retired.

JOURNEYS · ONE RECORD, END TO END

Follow a single vehicle through the warehouse

One real record from each city, on 11 September 2026, from the bytes an agency returned to the point it becomes a number on this page. Every value below is the actual stored value — the same row read back out of each layer.

New YorkMTA · E train · trip ids encode an origin time
  1. FetchGET the ace feed, one of eight, every 30s → ~132 KB of protobuf archived at nyc/ace/2026-09-11/21/…pb
  2. EnvelopeDecoded to the canonical shape and keyed by city onto transit.trip_updates
  3. SilverTyped into stop_time_predictions. The trip arrives as 106150_E..S71R — MTA encodes the origin time in the id, so it drifts intraday
  4. MatchThe NYC-specific matcher resolves it on route + origin-time to static trip BSP26GEN-E078-Weekday-00_106150_E..S71R, confidence 1.0
  5. GoldStop G06S, sequence 2. Six predictions collapse into one arrival: scheduled 21:43:00, actual 21:43:00, delay 0s → on time. NYC has no feed-supplied delay outside the L, so this was computed against the schedule.
  6. ChartLands in the 17:00 local column of on-time-by-hour, and in NYC's 85.8 composite
BostonMBTA · route 78 · exact trip ids, real cancellations
  1. FetchThree feeds at 30s → ~357 KB per poll at boston/trip_updates/…
  2. EnvelopeSame canonical wrapper; Boston also carries occupancy, which most feeds omit
  3. SilverTrip 78512915, vehicle y2023 — MBTA's realtime ids match its static file exactly, so no inference is needed
  4. MatchStraight equijoin to the same id in the schedule, confidence 1.0
  5. GoldStop 2471, sequence 9, scheduled 00:35:00, actual 00:35:00, delay 0s → on time, from 6 predictions
  6. ChartThe 20:00 local column, and one of the 8.0M scored events behind Boston's 81.1
TorontoTTC · route 340 · a service day that starts at 4am
  1. FetchThree surface feeds at 30s → toronto/trip_updates/…, 13.1 GB a week
  2. EnvelopeAttribution is a licence condition here, carried through to the footer of this page
  3. SilverTrip 128666020, vehicle 8331. TTC sets start_date on zero trips, so the service day is resolved with a −4h cutover rather than the usual noon rule — without it, every overnight trip lands on the wrong date
  4. MatchExact id against the surface schedule, confidence 1.0
  5. GoldStop 5954, sequence 4, scheduled 08:26:40 UTC = 04:26 local, delay 0s → on time
  6. ChartThe 4am column — and part of Toronto's 18.1% early-departure share, the highest in the fleet
HelsinkiHSL · route 1041 · the trip id arrives empty
  1. FetchTwo feeds at 30s, keyless → helsinki/trip_updates/…
  2. EnvelopeNo vehicle-position topic for this city: HSL publishes none
  3. Silvertrip_id is empty on every row. Identity is rebuilt from (route, direction, start date, start time) so the trip still has one stable key across polls
  4. MatchResolved to static trip 1041_20260911_Pe_2_0913, confidence 1.0
  5. GoldStop 1291112, sequence 12, scheduled 06:25:00, actual 06:25:30, delay +30s → on time, from 9 predictions
  6. ChartHelsinki's 2.6% bunching — the lowest in the fleet — is built from arrivals like this one
Washington DCWMATA · Red Line · two schedules, one version
  1. FetchSix feeds — rail and bus each with updates, positions and alerts — at 30s, ~517 KB per bus poll
  2. EnvelopeKeyed onto the same three topics as every other GTFS-RT city
  3. SilverTrip 11836339_20705, railcar 215. DC's schedule ships as two separate zips, loaded under one version so neither mode hides behind the other
  4. MatchExact id, confidence 1.0
  5. GoldStop PF_B11_C, sequence 27, scheduled 14:59:00 UTC = 10:59 local, delay 0s → on time
  6. ChartDC restates a stop 49 times on average before the vehicle arrives. This one was restated 97 times.
SF Bay511.org · SamTrans · thirty operators, one feed
  1. FetchThree feeds at 200s, not 30s — the regional key allows 60 requests an hour
  2. EnvelopeOne feed carries ~30 operators; the operator prefix survives in every id
  3. SilverTrip SM:15252201-155-Blocks-Weekday-62, vehicle 436 — SM: is SamTrans
  4. MatchExact id against the regional schedule, confidence 1.0
  5. GoldStop 335503, sequence 34, scheduled 12:15:33, actual 12:15:33, delay 0s → on time
  6. ChartSF's completeness, 93.2%, is the lowest in the fleet — six operators publish no realtime at all
ZurichSwiss OTD · a delay-only national feed
  1. FetchTrip updates every 60s, alerts every 600s. Each trip-update poll is about 7 MB — the whole Swiss network — 77.5 GB in the week to 2026-09-12
  2. EnvelopeFiltered in silver to a 615-route allow-list, on both the realtime and schedule sides, so no Swiss operator is mislabelled as Zurich
  3. SilverTrip .ojp-91-11-C.1.TA.1450.j26. The feed carries a stated delay and no arrival timestamp, so an actual time has to be constructed rather than read
  4. MatchRealtime and static ids line up for this trip, confidence 1.0. For 45% of Zurich's events they don't, and the trip resolves on route + time at 0.9
  5. GoldStop ch:1:sloid:91299::5, sequence 11. Actual = schedule + stated delay → 20:27:00, delay 0s → on time, method delay_plus_schedule
  6. ChartOne of the 10.7M scored events behind Zurich's 96.4% on-time and its 93.6 composite
TokyoODPT · Toei Mita line · the operator states the delay
  1. Fetchodpt:Train every 30s → a 51 KB JSON array of every Toei train in service, archived at tokyo/trains/2026-09-11/…json
  2. EnvelopeIts own topic, transit.odpt_trains — the record shares no shape with GTFS-RT
  3. SilverTrain Toei.Mita.2031K with odpt:delay = 0 and a fromStation. There is no arrival time in the feed at all
  4. MatchStation URNs map to GTFS stop ids, and the train matches its published timetable …Mita.2031K.Weekday. Arrival is detected by watching fromStation advance along the line across 16 snapshots
  5. GoldStop 226, sequence 2, scheduled 11:52:00, actual = schedule + stated delay → 11:52:00, on time, method odpt_stated
  6. ChartLands in the zero bin of Tokyo's delay distribution, and in its 98.1% on-time and 98.8 composite
METHOD · HOW THE SCORING WORKS

Same rules for every city

One ruler, applied the same way in eight time zones. Each feed is matched to the timetable its agency published for that day, and every threshold below is fixed in code, not tuned per city.

−1 / +5min

On time

An arrival counts when it lands between one minute early and five minutes late against the agency's own schedule.

20days

Before a score

No composite until a city has 20 closed, judged service days. All eight cities cleared it.

50%

Completeness floor

A route whose feed carried fewer than half its scheduled trips is set aside, as is any route under 10 scheduled trips a day.

local

Each city on its own clock

Every hour and service day is read in the city's own time zone, so 3am in Tokyo compares to 3am in New York.

What goes into a score

0–100, four parts, fixed weights
35%Excess wait Minutes riders wait beyond the scheduled gap on frequent service; ten minutes scores zero
30%On time Share of stop events inside −1 to +5 minutes
20%Cancellations Share of scheduled trips the agency cancelled
15%Bunching Share of arrivals less than half a scheduled gap behind the one ahead
SPECS · THE STACK

What it runs on

Five stages from an agency's HTTP response to a row in gold, with Dagster keeping time across all of them.

  1. 01Ingest
    Python pollers on Docker
    30endpoints
    containers
    8, one image
    poll floor
    30 s
    slow lanes
    SF 200 s · Zurich alerts 600 s
    archive
    S3, 7-day expiry
  2. 02Queue
    Kafka, KRaft mode
    4×3topics × partitions
    host
    t4g.small · 48 GB
    compression
    zstd · 50 ms linger
    max message
    900 KB
    retention
    24 h
  3. 03Process
    Spark Structured Streaming
    2 hdrain cycle
    runtime
    EMR Serverless 7.5
    ceiling
    8 vCPU · 32 GB
    per run
    driver + 2 executors
    shuffle
    16 partitions
  4. 04Lake
    Apache Iceberg on S3
    2.96Bsilver rows
    catalog
    Hadoop
    bronze by
    feed · city · date
    silver by
    city · date
    load step
    none — external tables
  5. 05Warehouse
    Snowflake + dbt
    422.9Mgold rows
    compute
    X-Small
    models
    50 over 6 seeds
    layers
    17 · 20 · 13
    tests
    242 every build
OrchestrationDagster on a t4g.medium
  • 2 hsilver drains
  • 11:05 · 23:05dbt chain, UTC
  • hourlyweather
  • Sundaytimetable refresh