I counted severe turbulence across 11.5M pilot reports. My first number was 3.4x too low.
FlightFinder ingests PIREPs — the short coded note a crew files from the flight deck about what the air is actually doing up there. 11,460,872 of them in the public archive since 2001.
I wanted one number for a social post: of the reports that mention turbulence at all, how many say severe?
The obvious query is a GROUP BY on the turbulence column. The top of that list looks tidy and well-behaved: NEG, MOD, LGT CHOP, LGT, MOD CHOP, SMOOTH, LGT-MOD, and so on down to rank 20, where SEV sits with 31,622 rows.
So: severe is SEV plus SEVERE plus the two extreme spellings. That is 36,721 rows out of the 4,476,254 that report any turbulence at all. 0.82%. Write the post, ship it.
That number is wrong by 3.4x.
The column is free text. Pilots don't pick from a dropdown, dispatch systems reformat, and 25 years of that gives you 337,727 distinct values in one column. "Severe" alone is written at least these ways, every one of them with four digits or more behind it:
SEV 31,622 · MOD-SEV 30,506 · SEVERE 3,677 · MOD OCNL SEV 1,924 · MDT-SEV 1,677 · SEV CAT 1,042 · MOD TO SEV 1,027 · SEV TURB 1,025
And it keeps going below that: MOD SEV, MOD - SEV, MOD-SEVERE, MODERATE TO SEVERE, CONT MOD OCNL SEV, OCNL SEV, MOD-SEV CHOP. In total 17,758 distinct strings in that column mention severe or extreme. Match on substring instead of equality and the real answer is 126,728 rows — 2.83%, not 0.82%.
The part that actually bothers me is that the wrong number looked completely fine. 0.82% is a plausible rate for severe turbulence. It had a real query behind it and a real 11-million-row dataset behind that. Nothing in the output says "you are seeing a quarter of your matches." If I hadn't gone digging into the tail out of paranoia I would have published it, and nobody would ever have caught it, because who checks?
Three things I took away, in case they save someone else a bad number.
-
A GROUP BY on free text is a spelling census, not a measurement. It tells you the most common way of writing a thing. It does not tell you how often the thing happened.
-
COUNT(DISTINCT col) is the cheapest sanity check there is. One column, 337,727 values, ten seconds to run. If a categorical column holds more distinct values than you could name out loud, you don't have categories. You have prose, and you have to match it like prose.
-
Then audit the matcher for false positives before you trust it. LIKE '%SEV%' also catches SEVERAL, and a value starting with NEG means the opposite of what you're counting. I checked: 25 rows out of 126,753. Noise in this case — but I only know that because I looked, and on a smaller column that check is the whole finding.
This is the same failure I posted about here last week, approached from the other side. That time a column was 100% populated and 74% of it said "unknown". This time a column had no placeholder at all and still couldn't be counted by grouping it. Populated and usable are different properties — and so, it turns out, are countable and counted correctly.
The number that eventually shipped: of 4.5 million pilot reports mentioning turbulence since 2001, 2.8% say severe or worse, and 3,816 — under 1 in 1,000 — say extreme. Which is a much better fact than the one I nearly published, and it cost me one COUNT(DISTINCT) to find out.
The fact that the wrong number looked completely reasonable is the scary part.
Curious whether you now treat any free-text “category” field as untrusted by default, regardless of how clean the top values look.
Not untrusted by default - unquantified by default. The check is cheap enough that there is no excuse for skipping it: count distinct values, then look at what share of rows sits in values below 100 occurrences each. A clean-looking top 20 tells you nothing; the tail is where the answer is.
The two failure shapes need different handling, though. Free text with a long tail is matchable - 17,758 distinct strings in that column mention severe or extreme, and a substring matcher gets you there as long as you audit it for false positives (LIKE '%SEV%' also catches SEVERAL, and a NEG prefix means the opposite of what you are counting).
Sparse is the one you cannot fix with a better matcher. flight_nature in our occurrence table is populated on 14.6% of 283,048 rows. Any trend I compute from it is a trend in which authorities happen to fill that field, not in aviation. No amount of clever matching recovers a value nobody wrote down.
So the working rule is: before a GROUP BY becomes a sentence, write the coverage percentage and the distinct-value count next to the number. If either is embarrassing, the number is a lower bound and gets labelled as one.
The GROUP BY revealing only 0.82% is a masterclass in measurement definition blindness. You measured the distribution of exact spelling matches, not the actual frequency of severe turbulence. GROUP BY free text creates two distinct measurement domains - one counts what people typed, the other counts what actually happened - and you were reporting from the first without realizing it.
The 3.4x gap reveals the hidden boundary: "what column value equals this string" vs "does this report describe severe turbulence." Most teams never discover that boundary exists because the exact-match query returns confidently and looks sensible. The substring match takes real work to justify.
This is why categorical columns measured via exact match are so dangerous - they measure data hygiene, not reality. The sanity check (337k distinct values) caught something was wrong, but only because you trusted it enough to run it. If you'd shipped the 0.82% number, that would be published measurement debt.
Agreed on the domain split, though what stuck with me is that substring matching isn't a fix either - just a cheaper failure. 17,758 distinct strings in that column mention severe or extreme, and the tail below 100 occurrences each is where the odd dispatch reformats live, so 2.83% is really a floor rather than the number.
The habit I've landed on: publish the metric next to COUNT(DISTINCT) of the column it came from. If the second number is large, the first one is a lower bound and should be labelled as one. Cheap to run, and it makes the caveat impossible to forget by the time you write the sentence.