A friend asked me last winter which airline to take from Chicago to New York. He'd been burned twice on that route and wanted to know if one carrier was actually better or if he'd just been unlucky.
I did what anyone would do. I searched. I got booking sites, a Reddit thread from 2019, and a lot of pages that answered a slightly different question very confidently.
None of them knew. Not because the answer is unknowable — because the answer is sitting in a federal database that nobody bothers to open.
The data exists. That's the frustrating part.
Under federal reporting rules, every US airline above a certain size has to file the operational details of every domestic flight it runs with the Department of Transportation. Not a summary. Every flight.
Scheduled departure time and actual departure time. Scheduled arrival and actual arrival. Whether it was cancelled or diverted. And when a flight is late, the airline has to say why, split across five categories: the carrier's own fault, weather, the air traffic system, security, or an aircraft that arrived late from its previous leg.
That last field is the good one. It's the difference between "this route is unreliable" and "this route inherits problems from earlier in the day, so the 7am is fine and the 6pm is a gamble."
Twelve months of this is about seven million rows. It's free. It's public domain. You can download it right now.
And here is what you get when you do:
FL_DATE,OP_UNIQUE_CARRIER,ORIGIN,DEST,CRS_DEP_TIME,DEP_DELAY,ARR_DELAY,CANCELLED,CARRIER_DELAY,WEATHER_DELAY,NAS_DELAY,SECURITY_DELAY,LATE_AIRCRAFT_DELAY
2025-11-14,UA,ORD,LGA,0715,-4.00,-11.00,0.00,,,,,
2025-11-14,UA,ORD,LGA,1150,38.00,52.00,0.00,0.00,0.00,14.00,0.00,38.00
A zip file of that, ten million lines long, split by month, with a PDF explaining the column names. This is a complete, authoritative answer to my friend's question, in a format that guarantees he will never see it.
That gap — between "the answer is public" and "the answer is reachable" — is the whole project.
What I actually built
DelayAtlas is a page for every major US route, airport and airline, rebuilt from the federal filings once a month.
The Chicago–New York question my friend asked now has a page. O'Hare to LaGuardia runs 67% on time. That's one of the worst numbers in the country for a route that size, and the page tells you why: the delays cascade from earlier flights, so the morning departures are meaningfully better than the evening ones, and the gap between the best and worst carrier on that route is smaller than the gap between departing at 8am and departing at 6pm.
He was asking the wrong question. It wasn't the airline. It was the time of day.
That's the kind of answer the raw file contains and no booking site will ever tell you, because booking sites are in a different business.
There's also a live layer — the FAA publishes real-time ground stops and delay programmes, which sit on their own page, quarantined from the historical numbers so the two never get confused.
The part I got wrong
I assumed the hard part would be the data. Seven million rows, join a few tables, compute some averages. It took an afternoon and most of that was discovering that arrival delay is null for cancelled flights, which will silently poison your averages if you don't catch it.
Then I rendered the first version of a route page, and it was garbage:
JFK to LAX
On-time rate: 78.1%. Average delay: 71 minutes. Cancellation rate: 1.4%.This route has an on-time rate of 78.1%. The average delay is 71 minutes. The cancellation rate is 1.4%.
Two hundred and fifty of those isn't a website. It's a spreadsheet with a stylesheet.
And you can't fix it by writing the pages by hand — that's 250 pages, and they'd go stale the moment new data landed. But if you write one paragraph template and fill in the numbers, you get the same sentence 250 times. Same problem, better disguised.
So the real question turned out to be: how do you write prose where the sentence itself changes depending on what the data says?
Writing about numbers you haven't seen yet
The approach that worked: instead of one template per section, a library of thirty-odd paragraph templates, each with a condition attached. At build time every condition is tested against that route's numbers, and only the ones that fire get rendered.
So there's a paragraph that only appears when more than 40% of a route's delay minutes are inherited from late aircraft and the morning-versus-evening gap is wider than eight points. When both are true, the page says the cascade is the story and you should book early. When they're not, that paragraph never exists on that page.
There's another that only fires when weather is unusually dominant, and it makes a different argument: on this route the season matters more than the carrier.
There's one that only fires when a route is unremarkable — no weather story, no cascade, no seasonal pattern. That was the one I forgot to write, and I only found out because of a check I'd added almost as an afterthought: a minimum amount of real prose per page, enforced at build time, with the build failing and printing the URLs that came up short.
✗ prose floor: 3 pages below threshold
/routes/ogg-to-hnl (612 words)
/routes/hnl-to-ogg (598 words)
/routes/koa-to-hnl (571 words)
Hawaiian inter-island hops. Short, punctual, boring — and every one of my interesting templates stayed silent, because nothing interesting was happening. I had to write templates specifically for nothing is happening here, which is a strange afternoon of work and a genuinely useful lesson: generated content degrades at the edges, and the edges are exactly what you don't check when you're spot-checking output.
Two other things mattered more than I expected.
A number alone says nothing. "78.1% on time" is meaningless to someone who doesn't know the national average is 77.4%. Every claim on the site is written against a benchmark — the national figure, the airport's own average, the other carriers on that route. That single constraint did more for readability than any amount of prose polish.
Say when you don't know. Some routes have small samples. Some measures get suppressed. The templates that admit this — this route saw 900 flights, enough to see a pattern but not enough to be confident about small differences between carriers — are the ones that make the rest of the page trustworthy. Hedging isn't weakness in data writing. Unearned confidence is.
The one rule I'd take to any project
There's a check in the build that fails on a list of about thirty-five phrases. "It's important to note." "When it comes to." "A comprehensive guide." "Delve." "Seamless." The build doesn't warn — it stops, the same as a type error.
Those phrases aren't banned because they're ugly. They're banned because they are what you write when you have nothing specific to say. Every time the build failed on one, the fix was to go back to the data and find something concrete, and there was always something concrete available. That's what seven million rows are for.
I'd put that check in almost anything I write with an LLM in the loop now. Not because it catches bad writing — because it catches empty writing, which is a different and more common failure.
Why bother
There's a decent amount of public data in this shape. Federal agencies publish an enormous amount of it and most of it never reaches the people it's about. Hospital inspection records. Vehicle safety complaints. Consumer finance complaints. All public, all authoritative, all effectively invisible because the delivery format is a zip file and a PDF data dictionary.
Turning one of those into something a person can actually read isn't a hard technical problem. Aggregation is solved. The hard part is deciding what each page should say — making sure it tells someone something specific and true that they couldn't have guessed, rather than restating the numbers in sentence form.
My friend still flies Chicago to New York. He books the morning now.
delayatlas.com — built on public US DOT and FAA data, no affiliation with any airline. Happy to talk about the pipeline or the template approach in the comments.
Top comments (0)