01 / DefinitionWhat data cleaning is — and what it is not
Data cleaning is correcting or removing values that already exist in a table: folding duplicates, fixing encodings and formats, coercing types, reconciling variants of the same real-world thing. It is distinct from enrichment and extraction, which add values that never existed. AI data cleaning means delegating the judgement-requiring subset of that work — and only that subset — to a model, with the same acceptance discipline any model output needs.
The distinction from extraction is worth keeping sharp, because the two need different
verification. Folding ACME Corp. and Acme Corporation into one
supplier is cleaning — the fact was there twice. Deciding that supplier is a
manufacturer is extraction — the fact was
never there at all. Cleaning verifies that nothing true was lost; extraction verifies
that nothing false was added.
02 / The splitWhat rules should clean, and what a model should
The economics are lopsided and the table should say so. Rules are ~free per row, deterministic, and auditable by reading them. Models are priced per row, probabilistic, and auditable only by checking outputs. So the burden of proof sits on the model: it cleans only what rules demonstrably cannot.
| Cleaning task | Owner | Why |
|---|---|---|
| Dedupe by key | Rule | Equality on URL or id is not a judgement call |
| Whitespace, casing, encodings | Rule | One regex, zero ambiguity |
| Type & format coercion | Rule | "2026-8-13" → date is mechanical; failures should be loud, not guessed |
| Unit normalisation, stated units | Rule | kg→lb is arithmetic |
| Dedupe by meaning | Model | "ACME Corp." vs "Acme Corporation (Shenzhen)" is a judgement |
| Name normalisation, free text | Model | No rule enumerates how humans misspell suppliers |
| Outlier triage | Model | "$1,200,000 for a phone case" — typo or bulk listing? Requires reading |
| Missing-value inference | Neither | That's extraction — or a gap to record honestly, not fill quietly |
Tools that run a model over every row to do what a regex would have done aren't cleaning better — they are adding a probabilistic layer to deterministic work, which means adding a new way to be wrong. If a rule can state the fix, the rule is the fix.
03 / DisciplineThree principles that keep cleaning honest
- Clean derives; it never edits. The raw table is append-only history; the clean table is a derivation from it. Correct in place and you destroy the evidence that would let anyone re-check the correction — or make a different one later.
- Idempotent, because cleaning re-runs. New raw rows arrive; the clean table gets re-derived. Same input, same output, every time — otherwise the clean table's meaning depends on its run history, and nobody can reproduce it.
- Every rejected row gets written down.
115 raw → 62 cleanis only trustworthy with the third number:−53 folded by url. A row that vanishes without a reason is the seed of every "why don't these counts match?" meeting. Rows that were never accepted should still be on the books as refused, with the rule or judgement that refused them.
These three are the difference between a cleaning step and a cleaning edge — something that runs once versus something a pipeline can lean on. They are also, not coincidentally, how the clean edge works in Tablize: mechanical dedupe, stable ids, and a fold count that survives as an audit line.
04 / PracticeFive patterns for a model in the cleaning loop
1 · Dedupe by meaning, propose-then-apply
The model reads candidate pairs (pre-filtered by cheap similarity) and proposes folds with reasons; a rule applies only the folds above a declared confidence, and the rest queue for a person. The model never deletes anything — it nominates.
2 · Canonical naming with a closed vocabulary
Free-text values map onto a declared canonical list ("Guangdong", not four spellings of it). Closing the vocabulary turns an open generation problem into a classification — checkable by enum, refusable when the model invents an entry.
3 · Outlier triage, not outlier deletion
Statistical rules find the weird rows; the model reads each and labels it
typo | legitimate | unclear with a sentence of reasoning. Typos get a
proposed correction; unclear goes to a person. Nothing is dropped on a
z-score alone.
4 · Schema coercion with refusal
When a rule can't parse a value ("about 40ft container"), the model attempts a
structured reading — {qty: 1, unit: "40ft"} — under a declared schema, and
is allowed to answer unparseable. The licence to refuse is what keeps
coerced garbage out.
5 · Variant folding across languages
The same supplier in Latin and CJK scripts, the same product in marketplace-dialect and catalogue-dialect: judgement folds only a model can make, run as batches with pinned answers, exactly like any extraction work.
05 / TrustVerifying cleaned data
A cleaning pass makes claims: nothing true was lost, nothing false was introduced, the fold is reproducible. Claims want checks:
- Arithmetic must close. raw = clean + folded + refused, per run, no remainder. If the counts don't reconcile, the cleaner is lying somewhere.
- Declared checks on model output. Every model-proposed fold or coercion passes type/enum/format rules before it lands — mechanical judgement first.
- Pinned answers for judgement folds. A sample of known pairs (these two ARE the same company; these two are NOT) hidden in the batches, with misses refusing the batch. Same golden-gate logic as extraction, because it is the same risk.
- Spot-read the refusals. The refused pile is where a too-aggressive rule or a hallucinating model shows up first. Ten minutes reading refusals catches what aggregate accuracy hides.
06 / FAQQuestions that come up
Should I use AI for data cleaning at all?
For most cleaning, no — rules are cheaper, faster and reproducible. Reach for a model only on judgement calls: meaning-level dedupe, free-text normalisation, outlier triage. If a rule can state the fix, the rule is the fix.
Cleaning vs extraction — where exactly is the line?
Cleaning corrects what exists; extraction creates what doesn't. Folding two spellings of one company is cleaning; labelling that company a manufacturer is extraction. Keep them separate because they verify differently — cleaning proves nothing was lost, extraction proves nothing was invented.
What should happen to rejected rows?
They stay on the books, with reasons. An honest fold is an audit line — 115 raw, 62 clean, 53 folded by URL — and a refused row you can still point to beats a vanished row every time counts stop matching.
Why does idempotence matter so much?
Because cleaning re-runs forever: new rows arrive, the clean table re-derives. If two runs of the cleaner disagree, your clean table's meaning depends on run history — which is to say it has no meaning. Same input, same output is what makes it a derivation.
How do I verify model-assisted cleaning?
Like any model output: declared checks judge every submission mechanically, pinned answers spot-check the judgement, and the arithmetic (raw = clean + folded + refused) must close. A model's fold proposals deserve exactly the scrutiny its extractions get.
Adjacent reading, same production-line lens: