Cleaning data: when it's worth it and when it's wasted effort

"First we should clean up the data." It sounds prudent, and it can swallow an amount of hours that's hard to justify afterwards. Real prudence isn't cleaning more — it's deciding well: what gets cleaned, how far, and what gets left exactly as it is. This article is about making that call with judgement, because cleaning for cleaning's sake isn't rigour: it's work with no recipient.

"Clean" doesn't exist; "good enough for this" does

The first trap is treating cleanliness as a state you reach: the immaculate database, fully deduplicated, every field complete. That state doesn't exist, and if it did, it wouldn't last. Data degrades every single day the business operates: customers move, someone types in a hurry, a supplier merges with another. Chasing the perfect database is mopping a kitchen where people are still cooking.

The useful question isn't "is the data clean?" but "is it good enough for what it has to feed?". A mistyped phone number is serious on the list of customers with open orders, and irrelevant in a contact history from years back. Quality isn't a property of the data: it's a relationship between the data and its use. Without a use, there's no quality to argue about.

The criterion: clean what feeds decisions

That leads to the central criterion, which fits in one sentence: you clean what feeds decisions and processes; the rest, you don't. Before touching a single record, ask what actually consumes this data:

• Decisions. The reports leadership genuinely looks at, the figures used to set prices, buy stock or hire.
• Processes. Anything that runs on its own on top of that data: invoicing, shipping, automated notifications. An automated process fed bad data doesn't fail — it executes the error with total efficiency.
• Obligations. Whatever accounting, invoicing or data protection rules require you to keep in order.

If a field feeds none of the three, cleaning it produces exactly nothing. That can sting, because filling that field cost real effort once. But past effort is not a reason — it's the definition of sunk cost.

Three destinations: clean, archive, let die

With that criterion, every block of data ends up in one of three destinations, and all three are legitimate:

• Clean what feeds active decisions and processes. This is where investment pays: deduplicate, complete, correct — against a clear definition of what "correct" means.
• Archive what isn't used but might be requested: accounting history, documents with retention obligations, records of former customers that regulation allows and prudence suggests keeping. Out of the day-to-day operational system, but retrievable if someone asks. Archiving isn't half-hearted cleaning: it's recognising that something has reference value but no operational value.
• Let die the fields nobody has filled in for ages, the lists nobody consults, the parallel databases of projects that no longer exist. Freezing or deleting them — thoughtfully, and within whatever data protection requires — is cleaning too, and it's the cheapest kind there is.

Most cleaning projects that drag on forever do so because they're applying the first destination to data that deserved the second or the third.

Turn off the tap before you mop

There's one check that saves more than any cleaning technique: asking why the data gets dirty in the first place. If the process generating it keeps producing duplicates — a free-text field where there should be a closed list, the same new customer keyed into two systems, nobody owning the customer record — then cleaning isn't a one-off project: it's a recurring expense dressed up as a one-off project. You'll clean, and in a few months you'll be back where you started, with morale slightly worse.

Fixing the source usually pays better than the cleaning itself, and sometimes makes it nearly unnecessary. And there's one condition you can't skip: cleaning requires a definition of "correct". If nobody has decided what a properly registered customer looks like, or which of three duplicate records is the real one, the person doing the cleaning isn't cleaning — they're guessing, record by record, decisions that were never theirs to make.

When a deep clean genuinely pays off

There are moments when cleaning stops being optional and becomes the cheap part of something bigger: before migrating to a new system (moving rubbish into a freshly built house is paying twice), before automating a process that will consume that data with nobody watching, or before a data or AI project that feeds on it directly.

In those cases, the scope comes pre-decided: you clean what the project will use, not the whole database. The project defines which data matters, what "correct" means for each field, and when you're done. A clean-up with no project behind it has none of those three things — which is why it never ends.

Five questions before launching a data clean-up

1. Which decision, process or obligation consumes this data? If the answer is "none", the clean-up ends here.
2. Do we know what "correct" means for each field, and who decides when it's unclear?
3. Have we turned off the tap, or will we be back here in a few months?
4. Which part can we archive or let die instead of cleaning?
5. How will we know we've finished?

If you can answer all five, the clean-up you run will be short, with a recipient and an ending. If you can't, the problem isn't cleaning yet — it's judgement, and that's worth settling before you touch a single record.