JOURNAL
Published on
THE DIRTY DATATHAT BREAKSAN AUTOMATION
Code does exactly what you told it, including on a wrong value.
THE ANSWER
An automation fails on the data, not on the code. The same client spelled 4 ways. Dates in 3 formats. Required fields empty for years. Duplicates nobody merged. Data quality gets sorted first: pick one source of truth, normalise one column at a time, shut the front door.
AI-generated image
IN SHORT
Count the empty fields before you write the quote.
No identity key, no deduplication — only opinions.
Clean the 10 fields the automation touches, nothing else.
THE POINT
NOT THE CODE, THE DATA
When an automation doesn’t hold, the company assumes it was built badly. Almost always it does exactly what it was asked, on data nobody had looked at. A person doing the job by hand absorbs the mess quietly: “Smith Ltd” and “SMITH LIMITED” are one company. An automation doesn’t know, doesn’t understand and doesn’t remember. It sends 2 emails to the same client. (The flaw was already there; the automation only made it fast.)
THE CASES
FIVE MESSES I ALWAYS FIND
These aren’t extreme cases. They turn up in the first half hour, in any record built by busy people.
The same client spelled 4 ways
Smith Ltd, Smith Ltd., SMITH LIMITED, Smith Ltd – head office. To a person that is one company. To an automation it is 4 clients, with 4 histories and 4 sends.
Dates in 3 formats
Day-month-year in the forms, month-day-year in an export from an American tool, free text saying “end of month”. The day a system reads 03/04 the wrong way round, nothing looks broken. Only the order changes.
Required fields empty for years
The field exists, the form asks for it, and half the rows don’t have it. Back then it could be skipped. An automation that trusts that field discards the rest in silence.
Duplicates nobody ever merged
The contact typed in by sales, and the same one from the website form. They have sat side by side for 3 years: one has the phone, the other the history. A person picks the right one by eye; an automation sees 2 people.
Fields used for something else
Notes holding the bank details, a “city” field holding the county. A case status written into the title, because there was no column for it. (These are the hardest to find before they break something.)
BEFORE
SIX STEPS ON THE DATA
This work comes before the automation, always. The order matters: normalise before choosing a source of truth and you clean 2 records that keep drifting apart.
Choose the source of truth
For each piece of information, one place that wins. The client record lives in the back office, the sales status lives in the CRM. Where 2 systems both have the right to be right, the difference never gets settled.
Count before you judge
How many rows have the field empty, how many date formats exist, how many names repeat. That is 3 questions and a morning. After it you know whether this is an annoyance or a clean-up for the quote.
Define the identity key
What makes 2 rows the same thing: the VAT number, the company number, the normalised email. Without a declared key every deduplication is an opinion, and the next person will hold another one.
Normalise one column at a time
One written rule per column, applied across the whole record, with the original column kept alongside. If the rule was wrong you find out by comparing, not by rebuilding.
Merge duplicates by a rule
Which row wins. Which field survives from the loser. What happens when both hold a different value and neither is newer: written first, applied afterwards.
Shut the front door
Validation where the data goes in. Required fields that are genuinely required, one date format, dropdowns instead of free text. Skip it and the clean-up lasts 6 months, then starts again.
AI-generated image
THE HISTORY
NOT EVERYTHING NEEDS CLEANING
A total clean-up is the commonest way of never starting. You don’t need a perfect record: you need the fields the automation touches to be right. If the job covers active clients from the last 2 years, older history stays as it is. The question is which 10 fields have to be true for this to work. (It is nearly always 10, not 300.)
IF YOU SKIP IT
WHAT IGNORING IT LOOKS LIKE
- The symptom
- The automation doesn’t stop: it keeps running and produces plausible, wrong results. That is worse than an error, because nobody goes looking.
- When it surfaces
- At the first duplicate message to a client. At the first report that won’t reconcile with the back office. At the first deadline read the wrong way round.
- Who pays for it
- The people who had stopped checking because the system was supposed to. They go back to checking everything, and the automation becomes extra work.
- In the quote
- Clean-up is a line of its own, with its own estimate, inside the published ranges. It isn’t an unforeseen event: it is the part you see by looking at the data first.
Automating a wrong value only means getting it wrong faster, across more rows.
QUESTIONS
- How much does data quality matter to an automation?
- It decides whether the thing works at all. Code applies your rule to whatever rows it finds: inconsistent rows give an inconsistent result, and say nothing about it. Looking first costs a morning.
- Do I have to clean everything before starting?
- No, and it isn’t worth it. You clean the fields the automation reads or writes, on the rows it will actually use. The rest stays as it is, described honestly.
- Do I do the clean-up, or do you?
- We settle the rules together: only the people who know the work can say which duplicate row is right. I apply them, repeatably and reversibly, with the original kept beside the normalised value.
- Can’t AI sort the data out on its own?
- It can propose matches and normalisations across volumes you would never review by hand. But which row is true stays a human decision. A wrong match accepted without review is one more piece of dirty data.
If you are weighing up an automation and don’t know what state the data is in, look there first. This work sits inside every automation I build.