02 / case study / Aug 2026 – present
NYC subway reliability pipeline
The MTA never records when a train actually arrives. This pipeline infers it.
Python · dbt · DuckDB · GitHub Actions
The short version
The subway tells you when a train is coming. It never tells you when it actually showed up. I built something that works that out, and then uses it to measure how long people really wait on the platform.
The problem
The MTA's live data is a forecast. For every train it lists the stops still ahead and when it expects to reach them, and it rewrites that list every few seconds. When a train arrives, nothing gets written down. Its row just disappears.
So you can't answer the obvious questions. How long will I wait? Is this line worse at night? You'd need a record of arrivals, and there isn't one.
How it works
- Save a copy of the forecast every 30 seconds. All eight of the MTA's feeds, each copy kept exactly as it came.
- Spot what disappeared. If a train was due at a stop and then it's gone from the list, it arrived. The last time it was predicted is when.
- Don't get fooled. A train also disappears when it's cancelled. If it vanished while it was still more than two minutes away, it didn't arrive, so it doesn't count.
- Measure the wait. Arrivals give you the gaps between trains, and the gaps give you how long a rider actually waits.
Each run only works through what's new since the last one, and a test checks that this gives exactly the same answer as redoing everything from scratch.
Why not just average the gaps
Because trains bunch. Six trains an hour sounds like one every ten minutes. But if they come two minutes apart and then eighteen, most people turn up during the eighteen. The average gap is still ten. The average wait isn't. That extra is called excess wait, and it's the number that matches what a bad commute feels like.
What it found
Its first long run was 150 minutes overnight on Labor Day, when trains were about 19 minutes apart. Then it ran through a weekday, 19 and 20 October 2026: 22.7 million predictions and 145,663 arrivals. In the 4 to 7 pm peak, trains came every 6.8 minutes on average, and riders waited 4.1 minutes where evenly spaced trains would have meant 3.4. That is 19% extra, from bunching alone.
It also caught a real disruption: from about 6:08 pm the southbound E had a gap of roughly half an hour, seen at every station from Jackson Heights to 14 Street, a little later at each one.
I also asked whether rain makes the wait worse. On eleven years of the MTA's own figures, it doesn't. That chart is at the bottom of this page.
What's next
Leaving it running around the clock, so the picture covers whole weeks and not one night.
The six steps below are the same thing in more detail, with the real code behind each one.
01 / subway pipeline / 8 feeds / 30s / duckdb / dbt
The six steps, one at a time.
From the MTA’s live feed to the wait a rider actually has. Open a step for a plain explanation of what it does, and the real code that does it.
- gtfs1 2 3 4 5 6 7 S
- gtfs-aceA C E
- gtfs-bdfmB D F M
- gtfs-gG
- gtfs-jzJ Z
- gtfs-nqrwN Q R W
- gtfs-lL
- gtfs-siSIR
Read the pipeline as a table
| Stage | Does | Source file |
|---|---|---|
| 00 / feeds | Eight MTA GTFS-realtime endpoints: gtfs, gtfs-ace, gtfs-bdfm, gtfs-g, gtfs-jz, gtfs-nqrw, gtfs-l, gtfs-si. | – |
| 01 / collect | A small program asks all eight of the MTA's live feeds for the current picture, every 30 seconds. If one feed fails, the other seven still get saved. | ingest/poller.py |
| 02 / store | Each picture is saved as a small file, filed by date and hour, and never edited. If I get the logic wrong later, I can redo it. If I threw data away here, it would be gone. | ingest/poller.py |
| 03 / find the arrivals | The MTA says when a train is expected, never when it arrived. A train that arrives just stops being listed for that stop. | transform/models/intermediate/int_inferred_arrivals.sql |
| 04 / measure the wait | Arrivals give the gaps between trains. Gaps give the wait: the extra minutes a rider spends on the platform because trains came bunched and not evenly spaced. | transform/models/marts/fct_excess_wait.sql |
| 05 / ask about rain | Hourly rainfall in Central Park is lined up against the wait, line by line, to see whether rain makes it worse. | analysis/rain_regression.py |
| 06 / use it | The result is a single database file that one command rebuilds from the saved copies. Every table and column in it is described on a page that's generated automatically, so the description can't fall behind. | – |
02 / rain vs excess wait / snapshot 2026-09-02
Does rain cost a rider time? Not measurably.
I took eleven years of the MTA’s own monthly figures for five lines and checked whether wetter months had longer waits, after allowing for the time of year and the pandemic. They didn’t. Every result includes zero, which is the careful way of saying rain makes no measurable difference.
“No” is a real answer, so it’s shown here and not buried. It’s also the reason for the pipeline above: a monthly average is a blunt tool for a question about the twenty minutes it was raining.
Loading the chart…