Messy data comes in six distinct kinds. A language model is genuinely good at four of them and cannot fix the other two — not because the technology is immature, but because those two are not data problems at all. Telling them apart before you start is the difference between a project that works and one that produces confident, invisible nonsense.
Everyone agrees that messy data is the thing standing between a small business and anything useful with AI. Almost nobody says which kind of messy, and the kinds are not interchangeable. Two of them get worse when you point a model at them.
The six kinds
The same thing written six ways
AI fixes this wellAcme Corp
ACME Corporation
acme corp.
Acme Corp (Mumbai)
Acme
ACME CORP LTD
Six rows, one customer. Every total you calculate is wrong by however much is hiding in the other five spellings. This is the single most common form of mess in a small business, and it is the one where a language model clearly beats the alternatives — it handles abbreviation, word order, misspelling and local variation together, which rules and fuzzy-match libraries do badly.
How to do it safely: have it output a proposed canonical name alongside the original, never in place of it, and review the groups it formed rather than the individual rows. Reviewing 40 groups is quick; reviewing 4,000 rows is not.
Near-duplicates that are not exact duplicates
AI fixes this wellTwo records for one person, differing by a middle initial, an old address, or a typo in a phone number. Your database sees two customers. Your business has one. Deduplication by exact match finds none of these, which is why most businesses believe they have already deduplicated.
The catch: false merges are far more expensive than missed ones. Two genuinely different people with the same name and the same street get merged, and you have destroyed data rather than cleaned it. Set the threshold to under-merge, and route the ambiguous pairs to a person.
Inconsistent formats for the same fact
AI fixes this well03/04/2026 — which is the month?
4 March 2026
2026-03-04
Mar 4, 26
next Tuesday — somebody typed this
Dates, phone numbers, currencies, units, addresses. Here a model is useful mainly because it copes with the handful of entries that are not a format at all — the free text somebody typed into a date field — which is exactly where conventional parsing breaks and the whole job stalls.
Do it as a two-pass job: rules for the 95% that match a known pattern, the model only for the residue. Cheaper, faster, and far easier to audit than sending everything through a model.
Values that are wrong but plausible
AI helps, with rulesAn order dated 2016 in a 2026 file. A quantity of 1,000 where someone meant 100. A price in the wrong currency. None of these breaks anything, which is why they survive for years and quietly distort every report built on them.
A model is good at flagging these, because judging whether a value is implausible in context is a language-shaped task. It should never silently correct them — a corrected value you did not see is worse than a wrong value you can find. Output a flag and a reason; let a person decide.
Worth knowing: the standard statistical approach here — flag anything more than two standard deviations from the mean — fails badly on real operational data, because the outliers are already in the data skewing the mean they are measured against. That is its own article.
Two systems that disagree, and no agreed answer
AI cannot fix thisYour invoicing system says the customer is on a 15% discount. Your CRM says 10%. Both were entered by someone with a reason. Which is correct?
There is no signal in the data that answers that question. The information needed to resolve it does not exist in either system — it exists in a decision somebody made, possibly verbally, possibly two years ago. A model asked to reconcile the two will pick one, fluently, and give you a reason that sounds like analysis. It is a coin toss with a paragraph attached.
This is not a data problem wearing a disguise. It is a governance problem: nobody has decided which system is authoritative for which field. That decision takes an afternoon and a person with the authority to make it, and no amount of technology substitutes for it.
What to do: write down, field by field, which system wins. One page. Then the reconciliation becomes mechanical and you do not need a model for it at all.
Nobody knows what the column actually means
AI cannot fix thisstatus — active? paid? shipped?
flag_2
amount — gross or net? which currency?
date — ordered, shipped, or invoiced?
old_ref — still populated by something
The person who built the spreadsheet left in 2023. The column is still being filled in. Three people use it and two of them are using it differently.
This is the most dangerous kind of mess, and the one a model makes actively worse. Ask it to work with an ambiguous column and it will infer a plausible meaning and apply that meaning with perfect consistency across every row. You now have a clean, confident, internally coherent dataset built on a guess — and no inconsistency left to warn you. The mess was the warning signal, and you have removed it.
What to do: find the person who knows, and write it down. If nobody knows, the honest answer is that the column cannot be used for a decision until somebody decides what it means going forward. That is a real finding, not a failure.
The four a model fixes all have the same property: the correct answer is recoverable from the data itself. Six spellings of Acme are all evidence of one Acme. A date in five formats is still one date.
The two it cannot fix have the opposite property: the information needed is not in the data at all. It is in a decision nobody made, or a definition nobody wrote down. Those are organisational problems, and they are solved by a person with authority and an afternoon — not by a tool.
Can AI clean messy data?
Partly. A language model handles four of the six kinds well — name variants, near-duplicates, inconsistent formats, and values that are wrong but plausible. It cannot resolve the two that are organisational: two systems that disagree with no agreed answer, and columns nobody can define. Those need a decision, not a tool.
This matters more for an AI project than it ever did for reporting. Messy data has always made reporting unreliable, and businesses have lived with that for decades, because a wrong dashboard is usually visibly wrong. Somebody notices the revenue number cannot be right.
Feeding the same data to a language model changes the failure mode in two ways that matter:
| A dashboard on messy data | A model on messy data | |
|---|---|---|
| How it fails | Visibly. The total looks wrong. | Invisibly. The output is fluent and specific. |
| What it does with gaps | Shows a blank or a zero | Fills them with something plausible |
| Who catches it | Whoever knows the business | Nobody, unless you built a check |
| Scale of the error | One report | Every row it touched |
A model will never tell you that your data was too ambiguous to answer the question. It will answer anyway. Every input problem becomes an output that looks exactly like a correct one.
So the check is not optional and it is not a phase-two item. You need a set of records where you already know the right answer, and you need it before you point a model at the rest.
What I look at in the first hour
Given a spreadsheet or a database export and no context, these five questions sort the fixable from the organisational faster than anything else:
- How many distinct values are in the column that should identify a customer? Compare it to how many customers the business thinks it has. The gap is kind 01 and kind 02, and it is usually larger than anyone expects.
- Which columns are completely empty, and which are empty only recently? A field that stopped being filled in six months ago marks a process change nobody documented.
- Which two columns should agree and do not? That is kind 05, and it tells you where the governance gap is.
- Can anyone define every column in one sentence? Every column that fails is kind 06, and it is off limits for decisions until it is defined.
- Is there a known-correct subset anywhere? A month somebody reconciled by hand, an audited quarter. That is your test set, and finding one already in existence saves days.
Note that three of those five questions are about the organisation, not the data. That ratio is roughly right, and it is why “clean the data first” so often stalls: half the work is decisions, and decisions do not get assigned to the person who was handed the spreadsheet.
The order to do this in
- Decide the two organisational questions first. Which system is authoritative per field, and what each column means. One page each. Nothing downstream is trustworthy until these exist, and they cost you no technology at all.
- Build the test set. A few hundred rows where you know the right answer. Without this you cannot tell cleaning from damage.
- Rules for the regular mess. Formats, obvious duplicates, known patterns. Cheaper and more auditable than a model.
- A model for the residue. The variants, the near-duplicates, the free text, the implausible values — the cases where a rule cannot be written.
- Flag, do not fix, anything judgement-shaped. Output the proposal and the reason; let a person approve.
Most teams start at step 4 because it is the interesting one, and spend the saved time twice over on step 1 afterwards.
I fix expensive data problems for small and mid-sized businesses — and a good share of that work turns out to be kinds 05 and 06, where the answer is a decision rather than a tool. If your data is in the way of something you want to do, tell me what you are trying to get to, or see how I work.
