The Spreadsheet That Ran Out of Rows

Medium

· Database

Incident

SEV-1

BUG-XLSROWS

Priority: Critical (data loss) · reported by data pipeline

Time to fix: 20 min

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

  1. Read the report: write down the expected behaviour and the actual one in a sentence each.
  2. Reproduce it: run the visible tests and find the failing check that matches the report.
  3. 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.
  4. Follow the data through the query or the repository code; compare the rows it returns with the rows the report expects.
  5. Fix the cause rather than the symptom, run the visible tests again, and submit against the hidden ones — then write the root cause down.

Inspired by

Public Health England · 2020

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.

Code map

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]

Sign in to edit the file, run the tests and submit a fix

A free account — no card, no trial clock. You come straight back here.

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

  1. Read the report: write down the expected behaviour and the actual one in a sentence each.
  2. Reproduce it: run the visible tests and find the failing check that matches the report.
  3. 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.
  4. Follow the data through the query or the repository code; compare the rows it returns with the rows the report expects.
  5. 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 uploaded

The 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.