Quick Answer: In October 2020, Public Health England lost nearly 16,000 positive COVID-19 test results because an automated data pipeline converted raw CSV files into legacy Excel (.xls) spreadsheets. The format's hard 65,536-row cap silently truncated records exceeding that boundary, illustrating why data pipelines must validate data boundaries and fail loudly rather than relying on legacy serialization formats.
I think the COVID era gave us some of the most surreal software post-mortems in recent memory, but this particular incident always sticks with me.
Imagine an automated batch job processing incoming data during a massive surge. No alert triggers, every process returns a clean exit code 0, and server metrics look completely healthy. Yet behind the scenes, twenty percent of your inbound records have silently vanished into thin air. That isn't a contrived stress-test scenario—it actually happened in production during the height of the pandemic.
How did an Excel format drop nearly 16,000 COVID test results?
Public Health England lost roughly 16,000 positive COVID-19 test results because an automated batch job ingested data using the legacy Excel 97-2003 .xls format. Because .xls imposes a hard ceiling of 65,536 rows, any test records past that cutoff were silently discarded without triggering an ingestion error.
The system itself seemed straightforward on paper. Commercial testing labs processed patient swabs, compiled their daily positive test results into plain CSV text files, and sent them to Public Health England (PHE). A CSV is simply a flat text file without an inherent length ceiling; as long as you have the disk space to store it, it can hold millions of lines.
However, PHE had an automated script running behind the scenes that converted those incoming CSVs into an Excel template before pushing the data into their central contact tracing system. Instead of using modern formats, the script saved them out as legacy .xls files. When infection numbers spiked, the daily test logs blew past 65,536 rows. Because the legacy format physically could not support a 65,537th row, the script simply chopped off the rest. The rows past the boundary were discarded, thousands of infected individuals never knew they had tested positive, and their contacts were never notified.
Why do legacy intermediate formats create hidden pipeline bottlenecks?
Legacy file formats introduce hidden pipeline bottlenecks because they carry hardcoded dimensional limits from earlier computing eras. When a pipeline uses desktop-oriented file formats as intermediate transport layers, it risks catastrophic data loss whenever traffic spikes exceed those vintage specifications.
To me, this is like taking an industrial freight train full of goods and funneling it into a pickup truck halfway through the journey. The origin and the destination can both handle massive volume, but the intermediate vehicle spills everything onto the pavement the moment the payload exceeds its tailgate.
| File Format | Maximum Row Limit | Behavior on Overflow | Safe for Pipeline Ingestion? |
|---|---|---|---|
| CSV (Raw Text) | Unlimited (Disk-bound) | Streams continuously | Yes, with schema validation |
| Excel (.xls) | 65,536 rows | Silent truncation | No (Legacy binary format) |
| Excel (.xlsx) | 1,048,576 rows | Out-of-memory or file error | No (Avoid proprietary file types for ETL) |
| Parquet / Arrow | Unlimited (Chunk-bound) | Preserves schema and volume | Yes (Industry standard for analytics) |
What fascinates me most about this failure is that the ingestion script had likely been humming along quietly in the background for years without a single hitch. It had simply never been asked to process more than 65,000 rows of anything before. The vulnerability sat dormant until the real-world scale of the pandemic finally surfaced it.
How can engineering teams prevent silent data truncation?
Engineering teams can prevent silent data truncation by implementing row-count reconciliation, deprecating proprietary desktop formats in automated batch jobs, and configuring ingestion pipelines to fail loudly whenever an ingestion boundary is reached.
To me, silent failure is the absolute worst failure mode an engineer can encounter. If a script crashes, you get an alert, a stack trace, and a clear problem to resolve. If a script silently discards data while reporting success, you assume everything works as intended until real-world consequences catch up with you.
Instead of trusting that intermediary tools process files intact, data pipelines must enforce row-count reconciliation at every step. Before any transformation begins, the system should count the raw source lines. Once the transform or import completes, the system must verify that the destination datastore received the exact same number of records. If the input count was 80,000 and the committed count is 65,536, the pipeline must immediately abort the transaction, flag the mismatch, and wake somebody up.
Frequently Asked Questions
Why was the .xls format used instead of .xlsx?
Public Health England relied on an older automated script written to interface with legacy reporting infrastructure that had never been modernized to use the Office Open XML standard. As a result, the pipeline inherited the strict 65,536-row cap that Microsoft introduced back with Excel 97.
What is the maximum row limit in modern Microsoft Excel?
Modern Excel workbooks (.xlsx) support up to 1,048,576 rows and 16,384 columns. While that ceiling is sixteen times higher than the legacy .xls format, it remains an arbitrary boundary that makes desktop spreadsheet formats unsuited for automated, high-volume data ingestion.
What formats should be used for automated data ingestion?
Automated data pipelines should rely on open, streaming-compatible formats such as raw CSV, newline-delimited JSON (NDJSON), or columnar binary formats like Apache Parquet. These formats stream data efficiently without imposing arbitrary row cutoffs on your datasets.
Top comments (0)