Data cleaning is the process of detecting, correcting or removing inaccurate, inconsistent, duplicated and incomplete records in a dataset so that it is fit for analysis, reporting or machine learning. In data processing, it is the step that sits between ingestion and transformation, and it decides whether everything downstream is built on facts or on artifacts of how the data was collected.
The task is also called data cleansing or data scrubbing. Practitioners commonly report that preparing and cleaning data takes a large share of analytical project time, often estimated at half or more, because errors are diverse, context-dependent and rarely documented. Good cleaning is therefore systematic, repeatable and recorded, not a series of manual spreadsheet edits.
What data cleaning corrects
Data cleaning targets defects that make data misrepresent reality. These defects are usually grouped by the data quality dimension they violate, which also suggests how to detect them.
| Quality dimension | Typical defect | Example |
|---|---|---|
| Validity | Value outside the allowed type, range or format | Age of 212, date of 31 February |
| Completeness | Missing values in required fields | Order without a customer ID |
| Uniqueness | The same entity recorded more than once | Two customer rows for one person |
| Consistency | Conflicting values across fields or systems | Country Germany, currency USD by default |
| Accuracy | Valid format but wrong value | Correctly formatted but outdated address |
| Standardization | Same meaning in different representations | NY, N.Y. and New York |
Accuracy defects are the hardest to find, because the value passes every format check. Detecting them requires an external reference, such as a postal database, a second source system or a manual sample audit, while validity and uniqueness defects can usually be caught with automated rules.
The data cleaning workflow
A reliable data cleaning workflow moves from measurement to correction to verification, and it treats every change as a documented rule rather than an ad hoc edit. The typical sequence looks like this:
- Profile the data: compute distributions, null rates, distinct counts, minimums and maximums for every column.
- Define rules: write explicit validity, range, format and cross-field constraints with the domain experts.
- Standardize representations: trim whitespace, unify case, encodings, units, date formats and codes.
- Handle duplicates: match records exactly or with fuzzy similarity, then merge or remove them.
- Treat missing values: drop, flag or impute them according to why they are missing.
- Review outliers: separate measurement errors from genuine but rare observations.
- Validate and log: rerun the profile, compare counts before and after, and record every rule applied.
The order matters. Standardization should precede deduplication, because “ACME Corp.” and “acme corporation” cannot be matched reliably until case, punctuation and legal suffixes are normalized. Likewise, imputing missing values before removing duplicates can distort the statistics used for imputation.
Core techniques and when to apply them
Each data cleaning technique fits a specific defect and a specific risk. Choosing the wrong one can introduce more bias than the original error, so the decision depends on the volume of affected records and on how the data will be used.
| Technique | Addresses | Main risk |
|---|---|---|
| Rule-based validation | Invalid types, ranges and formats | Rules that are too strict reject valid edge cases |
| Standardization and parsing | Inconsistent representations | Over-normalization loses meaningful detail |
| Exact and fuzzy matching | Duplicate records | False merges of distinct entities |
| Deletion of rows | Irreparable records | Biased sample if deletion is not random |
| Imputation | Missing values | Underestimated variance and false certainty |
| Outlier capping or winsorizing | Extreme values | Removal of real, important events |
For outliers, a common screening rule flags values outside 1.5 times the interquartile range beyond the first or third quartile, or more than three standard deviations from the mean in roughly normal data. These rules only flag candidates for review; deleting every flagged value automatically removes exactly the rare events, such as fraud or equipment failure, that analysts often need most.
Data cleaning compared with related processing steps
Data cleaning overlaps with several neighboring steps in data processing, and teams often confuse them. The distinction matters because each step has a different goal and a different owner.
| Step | Primary goal | Relationship to data cleaning |
|---|---|---|
| Data cleaning | Make records correct and consistent | The umbrella process |
| Deduplication | One record per real entity | A specialized subtask of cleaning |
| Imputation | Fill in missing values | One option for treating missing data |
| Data transformation | Reshape data for a target model | Follows cleaning, changes structure |
| Feature engineering | Create predictive model inputs | Builds on cleaned data, adds new variables |
| Data validation | Check data against rules | Detects problems that cleaning then fixes |
In machine learning, a strict rule applies: cleaning parameters must be learned on training data only. Computing an imputation mean or an outlier threshold on the full dataset before splitting leaks information from the test set and inflates measured performance.
Common mistakes in data cleaning
The most damaging mistakes in data cleaning are those that silently change the meaning of the data. They rarely raise an error, and they often surface only when a report or a model behaves unexpectedly months later.
- Overwriting raw data: always keep an immutable original so that every cleaning step can be reproduced or reversed.
- Cleaning only downstream: fixing values in a report while the source keeps producing errors guarantees recurring work.
- Treating zero and null as equal: a missing measurement and a measured zero carry different information.
- Assuming missingness is random: values missing for a reason, such as unanswered income questions, bias any naive treatment.
- Undocumented manual edits: one-off corrections cannot be audited and are lost at the next data load.
The best defense is to express cleaning as versioned, tested code or declarative rules in the pipeline, with before-and-after metrics stored for every run. This turns cleaning from a craft into an auditable process.
Stripped to the essentials
Data cleaning means fixing mistakes in a collection of information before anyone relies on it. Typical mistakes are typos, blanks, duplicates and values that make no sense.
Picture a shared address book that many people have edited for years. Some friends appear twice. Some phone numbers are missing a digit. One person is listed as born in the year 2090. Cleaning the address book makes every entry trustworthy again. The same idea applies to a company’s sales records or a hospital’s patient files. Clean data leads to correct answers.
Where it is applied
An online retailer wanted to measure repeat-purchase rates from 2.4 million order records collected over five years. Initial profiling showed that about 7% of customers appeared under two or more IDs, mostly because guest checkouts used slightly different spellings of the same email address and name.
The data team lowercased and trimmed email addresses, standardized street abbreviations and then matched customers on email plus postal code, with a fuzzy name comparison as a tie-breaker. They also found 1,900 orders with negative quantities, which turned out to be returns logged in the wrong table, and moved them to the returns dataset instead of deleting them.
After cleaning, the measured repeat-purchase rate rose from 18% to 23%, because many returning customers had previously been counted as new ones. Every rule was stored in the pipeline, so the next monthly load was cleaned automatically.