Cleaning and organising data: where to start, in practice

The moment a company decides to "get the data in order" is almost always a good moment arrived at in the wrong way: an AI project has run aground, someone opened the real tables, and the discovery of triplicated master records and fields filled with creativity generated the most dangerous request in the repertoire, let's clean everything. Dangerous because "everything" is not a perimeter, and cleaning programmes without a perimeter share the destiny of Monday diets: solemn start, three weeks of rigour, silent abandonment.

Work on data can instead be set up so that it produces visible value in weeks and reusable foundations over years, and the difference sits almost entirely in the first decisions.

The use case dictates the perimeter

The first decision is what not to clean. Company data is a continent, and the part the first AI use cases need is typically a province: the entities feeding that process, the tables the model will drink from, the histories needed for training. Starting there carries two advantages the universal programme will never have, a client waiting for the result (the AI project itself, which funds and justifies the effort) and a verifiable completion criterion, because "data ready for use case X" is a measurable condition, "data in order" is not.

Inside the perimeter, the second decision is the order of works, and it pays to follow the hierarchy of damage. First the key entities, customers, products, suppliers, because every defect there propagates everywhere: deduplicating a customer master and establishing a unique key shared between CRM and ERP is worth, alone, more than months of touch-ups on secondary fields. Then the relationships between systems, meaning the reconciliations somebody performs today by hand in a spreadsheet. Only at the end the cosmetics of descriptive fields, which is the visually gratifying and economically least urgent part.

The four defects you always meet

The pathologies of corporate data are surprisingly repetitive, and recognising them speeds up the diagnosis. Duplicates come first: the same entity recorded several times with minimal variants, "Rossi S.p.A.", "ROSSI SPA", "Rossi Spa (new)", which for a model are three different customers. The remedy has a technical part (matching and merging) and an organisational part that outlives it, the rule on who can create new master records and under which controls, because deduplicating without closing the tap is emptying the sea with a bucket.

The second defect is free-text fields used as a landfill: notes containing structural information (deadlines, amounts, statuses) because the right field did not exist or nobody filled it. Here modern AI paradoxically offers help, since extracting structure from free text is one of its best trades, provided the output lands in governed fields and not in more notes. The third defect is non-random missing values, the holes concentrating in certain periods or certain branches which, fed to a model, become learned bias. The fourth is diverging conventions, dates in three formats, implicit units of measure, encodings every department bent to its habits: the remedy is called a declared standard, and it holds only if someone answers for it.

Cleaning that stays clean

The most expensive technical mistake of the whole endeavour is cleaning the data instead of building the cleaning, meaning producing a one-off tidied dataset that starts getting dirty again the next day. The right shape of the work is the pipeline: validation, deduplication and standardisation rules written as code, versioned, executed at every refresh, with quality metrics coming out at every run and a place where exceptions land in front of a human eye.

The pipeline also changes the economics of the project, because it converts a recurring, invisible cost (the hours analysts and teams spend every month fixing things by hand) into a one-off investment with maintenance, and that recurring saving is often the most solid business case of the whole data programme, more solid even than the AI project that triggered it.

The organisational part remains, and it decides durability: every cleaning rule embeds a business choice (which of the two addresses is the good one? below which threshold is a record discarded?), and those choices belong to the data owners, never to whoever writes the pipeline. Where the data governance we wrote about exists, this handover is a flow; where it does not, cleaning gives birth to it out of necessity, one domain at a time.

If you are about to open a data workstream ahead of AI projects, the way not to waste the first month is knowing in advance which data matters, in which order and with which thresholds, which is exactly the photograph the AI Rating produces on the Readiness dimension: calendly.com/fabiolalli/zerofive, or hello@zerofive.ai. And if the workstream is already open, one control question is worth the entire retrospective: what you are cleaning today, will it still be clean on its own in six months?