The Spreadsheet That Ran Out of Rows — Python Bug Hunt
Modelled on Public Health England's October 2020 reporting failure: nearly 16,000 positive COVID-19 test results were left out of the daily figures, and…
- Language: Python
- Layer: Database
- Difficulty: Medium
- Concepts: Limits, Validation
- Modelled on: Public Health England · 2020
- Visible tests: a small file fits one sheet; 70,000 records are all exported
- Reward: 50 XP for a complete fix
Incident
- Severity: SEV-1 (priority: Critical (data loss))
- Ticket: BUG-XLSROWS
- Reported by: data pipeline
- Time to fix: 20 minutes, the target for a medium incident
Failing checks on the shipped build
- 70,000 records are all exported — no record may be dropped: expected 70000 but got 65535
How to work this incident
- Read the report: write down the expected behaviour and the actual one in a sentence each.
- Reproduce it: run the visible tests and find the failing check that matches the report.
- Read the logs and the failing checks for the first value that is wrong — the earliest wrong value is nearer the cause than the loudest error.
- Follow the data through the query or the repository code; compare the rows it returns with the rows the report expects.
- Fix the cause rather than the symptom, run the visible tests again, and submit against the hidden ones — then write the root cause down.
Briefing
Modelled on Public Health England's October 2020 reporting failure: nearly 16,000 positive COVID-19 test results were left out of the daily figures, and their contacts were not traced, for about a week. Lab results arrived as CSV files and were loaded into Excel templates in the legacy XLS format, which holds at most 65,536 rows per sheet; everything past the limit was silently dropped.
exporter.py turns records into legacy sheets. It stops at the row limit without a word.
Fix export_sheets so every record is exported, split across as many sheets as it takes.
Bug report
BUG-XLSROWS · Priority: Critical (data loss) · Reported by: data pipeline
export_sheets(records, max_rows=None) — max_rows defaults to limits.XLS_MAX_ROWS (65,536):
- returns a list of sheets; every sheet is [limits.HEADER] followed by up to max_rows - 1 records, so no sheet has more than max_rows rows
- every record appears exactly once, in the original order; sheets are filled completely before the next one starts
- no records -> [[HEADER]]
- max_rows < 2 cannot hold a record: raise ValueError
Observed: a 70,000-row lab file produced one full sheet; 4,465 results were never uploaded, with no error.
Logs
[ingest] lab_feed_2020-10-01.csv: 70000 rows
[export] wrote results.xls (65536 rows)
[upload] 65535 results uploadedThe code as shipped
src/pipeline/exporter.py (editable)
limits = bug_require("./limits.py")
def export_sheets(records, max_rows=None):
if max_rows is None:
max_rows = limits.XLS_MAX_ROWS
sheet = [limits.HEADER]
for record in records:
if len(sheet) >= max_rows:
break
sheet.append(record)
return [sheet]
Read-only context: src/pipeline/limits.py.
Open the hunt to start the incident clock, edit the files, run the visible tests and submit against the hidden ones, then write the root cause down. More Python bug hunts.