Data cleaning is the work of finding records that are wrong, duplicated, missing or inconsistently formatted, and correcting them so the data can be used with confidence. It is also called data cleansing or data scrubbing, and the three terms mean the same thing in practice.
The definition is short. What it involves is not, so here is what a real cleaning job is made of.
The six problems that cover almost every file
Duplicates
The same customer, account or product recorded more than once, usually under slightly different spellings.
Inconsistent formats
Three date formats in one column, currency stored as text, phone numbers written six ways.
Structural damage
Encoding problems that turn accented names into nonsense, leading zeros stripped from postcodes, values shifted into the wrong column.
Missing values
Blank fields that matter, such as a contact with no email address on a list you are about to mail.
Invalid values
A date in the future where a birth date belongs, a negative quantity, an email with no domain.
Inconsistent categories
California, Calif. and CA. Yes, Y and TRUE. The same fact recorded three ways so it cannot be grouped.
Cleaning, transforming and validating are different jobs
Cleaning corrects values that are wrong or inconsistent, without changing what the data means. Transforming reshapes it for a purpose: pivoting, aggregating, joining, splitting a column into two. Validating checks values against a rule or an external source, such as confirming an address is deliverable.
They get mixed together in conversation, which causes trouble when someone says the data was cleaned and means it was reshaped. Keep them separate and each one becomes checkable.
What a pass looks like in practice
Step five is the one that gets skipped, and it is the one that makes the result defensible. A cleaned file with no record of the changes is a file nobody can audit. See data cleaning best practices for how teams keep that discipline.
- 1 Profile the file. How many rows, what is in each column, how many blanks, how many distinct values.
- 2 Normalize the obvious. Trim whitespace, fold case in the fields where case carries no meaning, repair encoding damage.
- 3 Standardize formats. One date format, one number format, one way of writing a state or a country.
- 4 Find duplicates. Exact first, then fuzzy across name, company and email.
- 5 Review. Look at what the tool wants to change before it changes anything.
- 6 Record what you did. Keep the change log next to the cleaned file.
How you know when a file is clean enough
Clean is not an absolute state, and chasing it as one is how a cleaning job turns into a month. The usable definition is that a file is clean enough when it can answer the question it was pulled for without anybody arguing about the data first.
Three checks tell you most of what you need. Count the distinct values in every column that is supposed to be a category, and see whether the number is close to what it should be: forty nine US states in a column that should hold fifty is a real answer. Count the blanks in the fields the job actually depends on, rather than in every field. Then take a sample of twenty rows and read them, because some kinds of damage only look wrong to a person.
What matters as much as the state of the file is whether you can say how it got there. A cleaned file with a change log attached is one somebody else can check. A cleaned file without one is a file that has to be trusted, and trust is what runs out in the meeting where the number is questioned.
How much of it can be automated
Format and structural problems are highly automatable: a date is either parseable or it is not, and mojibake follows a byte pattern that can be reversed. Duplicate detection is partly automatable, because scoring similarity is mechanical but the decision about whether two similar records are the same real customer is a judgment call.
The useful division is that software proposes and a person disposes. That is how data cleaning software should work, and it is why every change in this data cleaning tool is a proposal you approve rather than an edit that already happened.