The Automation That Works Until the Spreadsheet Changes
A small team automates weekly invoice processing: a script reads a CSV exported from an accounting tool, validates required columns (invoice_id, vendor, amount, due_date), matches each row to a purchase order in a shared spreadsheet, and writes results to a status sheet. It has run cleanly for four months. Last week the accounting tool's vendor released an update: the CSV now includes two extra columns at the front (a timestamp and a region code), the amount column now sometimes contains a currency symbol and comma ('$1,234.50'), and due_date switched from YYYY-MM-DD to MM/DD/YYYY. The script failed on 40 of 212 invoices - some rows errored, some matched to the wrong PO because column positions shifted, and 9 rows were silently marked 'matched' with the wrong amount. The team only noticed when a vendor called about a duplicate payment.
1. The script 'validated required columns' but still matched rows to the wrong PO. Explain why the validation failed to protect the data, and classify the three failure types by severity.
Read the worked answer
Why validation failed. The validation almost certainly checked column names or count, not positions or content. When two columns were inserted at the front, the header row may still have contained the expected names (or the check passed on a subset), but the code read by index - column 3 is amount, column 5 is due_date. After the shift, index 3 is now region and index 5 is now something else. The validation said 'columns present' while the parser read the wrong cells. Name-based validation without position-independent access is a false sense of safety.
Severity ranking:
- Silent wrong matches (9 rows) - most severe. No error, plausible output, money moved. These are the ones that cause duplicate payments and erode trust. Silent corruption is worse than a crash.
- Wrong-PO matches from shifted columns - severe. Wrong but detectable if anyone reconciles; still caused bad downstream action.
- Hard errors (the remainder of the 40) - least severe. The script stopped or flagged; a human saw it. Failures that announce themselves are the cheap kind.
The ranking principle: an automation that fails loudly is a nuisance; one that fails quietly is a liability. The design optimized for 'runs without complaining,' which is the wrong goal.
2. Redesign the ingestion step so this class of upstream change produces a visible failure instead of silent corruption. Be specific about parsing, validation, and what happens on failure.
Read the worked answer
Parse by header name, never by position. Build a column map from the header row ({name: index}) and access fields through it. Extra columns become harmless. Missing expected columns raise immediately.
Validate content, not just presence. For every row:
invoice_id: non-empty, matches expected pattern, unique within the file.amount: strip currency symbols and thousands separators, then parse to a decimal. Reject anything that does not parse cleanly. Never coerce silently.due_date: parse against an explicit list of accepted formats (ISO first, then MM/DD/YYYY), and reject ambiguous strings rather than guessing. A date that parses two ways is a data error.vendor: non-empty and present in the known vendor list.
Add a schema fingerprint. Hash the header row and compare to the last known-good hash. A mismatch does not necessarily stop the run, but it logs a warning and routes the file to review - an early tripwire for exactly this event.
Make the run atomic and fail closed. Validate the entire file before writing anything. If any row fails validation, write nothing to the status sheet; instead emit a failure report listing the offending rows and the reason, and notify the owner. Partial writes are how 9 bad rows slipped through.
Reconcile totals. After matching, assert that the sum of matched amounts equals the sum of input amounts (within rounding). A mismatch means rows were dropped or double-counted - a cheap check that catches silent corruption.
Treat upstream format changes as expected events. Pin the expected schema version, alert on change, and require a human to acknowledge a new format before it is processed.