Your tool ran once on a normal file. The output looked right. No error, no crash, no warning. Then, a month later, someone notices the totals are off — and by then the wrong numbers have already been used.
I keep running into the same failures while fixing internal business tools. They come in different shapes, but most share one signature: silent. No error, no warning. A value just quietly stops being the value it was.
It doesn't matter who built the tool — a colleague, a contractor, or an AI agent. It fails in the same ways.
Why a clean test file can't catch them
A normal file is too clean. It has no blank cells in the item code column. No item codes with leading zeros. No digits typed full-width. No merged header cells. So the tool passes — and the failures appear later, the first month the real data looks different.
The fix is not to test harder on clean data. It is to test once on dirty data: a file built to contain exactly the shapes that trigger these failures. I built one. It has eight rows, five columns, and a known correct total of 43. That number is the whole trick — you never have to guess whether a total is right.
Here are the seven shapes it catches.
1. 12345 becomes 12345.0
One blank cell in the item code column can be enough. If the tool reads the whole column as decimal numbers, your item code gains a .0. It no longer matches the 12345 in your other files, so lookups fail and duplicates slip through. Cosmetic? Only until a join misses.
2. 00123 becomes 123 — and merges with a different item
Numbers do not have leading zeros. The tool reads the item code as a number, 00123 comes out as 123, and suddenly two different items are one row. Every total broken down by item code is wrong, and nothing tells you a row disappeared into another one.
3. Values that look identical but don't match
123 — the same digits, typed full-width. To a person it is the same number as 123. To many tools it is a different value entirely. The real question is a decision, not a bug: in your work, do full-width and half-width digits count as the same ID? If you haven't decided, ask the person who owns the data. Not deciding is the failure.
4. An empty cell becomes the text nan
A blank quantity comes back as the word nan. The cell is no longer empty — it has a word in it — and any later step that tests "is this cell empty?" now answers no.
5. Rows with a blank category silently vanish from the totals
A row whose grouping column is blank drops out of the breakdown. No error, no warning, just a smaller total. In my test file the correct total is 43. If your output sums to anything else, check the rows with a blank category first.
6. A merged header stops the tool — or empties some rows
Real workbooks grow merged titles. Tools are usually tested on flat, clean sheets. Run the same tool on a sheet whose first two columns sit under one merged title, and it either stops or completes with some rows' values quietly empty.
7. Headers multiply every time you run it
Run a tool that reads a list, appends to it, and saves it — twice. If it treats its own header as data when it appends, every cycle adds one more header row at the top. Once header rows are inside the data, every later step has to dodge them. One run can never show this. You have to run it twice.
The test takes about 15 minutes
Feed the tool one deliberately awkward file, check seven specific places in the output, and — if the tool reads a list, adds to it, and saves it — run it a second time on its own output. Each check has a clear pass and a clear fail, and you write down the exact value that came out — not a paraphrase.
Half the battle is earlier, though — in the request itself. When I hand a build or a fix to an AI agent, I use a six-field request form that pins down the purpose, the scope, and what must never be touched, before any work starts. It's free: The Six-Field Request Form.
But the file is not really the point. The point is the habit: never accept a tool on a clean file. Clean files only prove the tool works on data that was never going to break it.
What's the quietest data corruption you've caught — the one with no error message?
Top comments (0)