The rows that were never counted

In early October 2020, Public Health England announced that 15,841 positive COVID-19 tests had been left out of the daily figures. They covered the eight days from 25 September to 2 October. The cases were real, but they were missing from the figures used to trace their contacts.

What happened

Test results from commercial labs arrived as files and were pulled together into spreadsheet templates. The templates used the old .xls Excel format, which holds at most 65,536 rows. When a file had more results than that, the extra rows didn’t cause an error. They just weren’t there.

This wasn’t a typo or a wrong value. The fault was in the records that didn’t arrive, and a missing record looks exactly like nothing at all.

The cost was time. Contact tracers could only call the contacts of people they knew about, and reporting at the time estimated that as many as 48,000 close contacts were not reached when they should have been.

What was actually wrong

Most data checks look at the records in front of them: is this field filled in, is this value allowed, is this date sensible? Those checks would all have passed here. The question that would have caught it was simpler: did everything that went in come out?

I spent years watching data pipelines in clinical trials, where the same question comes up daily. The checks that mattered most were not clever. How many records arrived today? Is that in line with yesterday, and with last week? Did the count at the end match the count at the start? Most real failures show up first as a number that is quietly too small.

What a check would have shown

A reconciliation, comparing the number of results received from labs against the number that reached the final figures, would have shown a gap on the first day it happened. Even without knowing why, a gap is enough to stop and look.

The fix for the format was trivial: a newer file type, or a proper database. The fix for the blind spot is a habit: count at every hand-off.

Before your own version

Every migration, integration and data load has hand-offs. Before you trust the result:

• Count in, count out. At each step, how many records arrived and how many left? Any difference needs an explanation, even if the explanation is “duplicates removed”.
• Watch the volume over time. A source that normally sends a thousand records a day and suddenly sends six hundred is telling you something, even if all six hundred are perfect.
• Look for limits nobody chose. File formats, page sizes, API batch limits, field lengths. Every system has a ceiling somewhere, and data that hits it is usually cut off without a word.

Sources: Public Health England statement on the delayed reporting of COVID-19 cases, 4–5 October 2020; reporting in The Register and The Conversation, October 2020.