08.09.2023
What is data cleansing?
Data cleansing, also called data cleaning or data scrubbing, is the process of finding and fixing errors, inconsistencies, and gaps in a dataset before anyone relies on it. It sits between collecting data and using it: every report, dashboard, model, and decision downstream inherits the quality of what comes in.
The problem is that bad data does not announce itself. A duplicated customer record looks like a customer record. A sensor stuck at its last valid reading looks like a stable process. Errors surface late, usually in a meeting where two dashboards disagree, and by then the cost of tracing them back is many times the cost of catching them early.
Where dirty data comes from
No dataset starts dirty on purpose. Quality erodes through ordinary causes:
- Manual entry. Typos, swapped fields, free text where a code was expected.
- System integration. Two systems, two conventions: the same customer spelled three ways, the same quantity stored in
kgin one source andlbsin another. - Format drift. Dates as
11.06.2026in one export and2026-06-11in the next; commas as decimal separators here, periods there. - Hardware and sensors. Flatlined values, spikes from faulty instruments, gaps from dropped connections.
- Schema changes. A renamed column or a new mandatory field quietly breaks every pipeline that assumed the old shape.
- Time. People move, companies rename, equipment gets replaced. Data that was correct when recorded becomes wrong by standing still.
What dirty data looks like
| Problem | Example | Typical fix |
|---|---|---|
| Duplicates | The same order ingested twice from a retried API call | Deduplicate on a stable key |
| Missing values | NULL in a required field | Backfill, impute, or drop by a documented rule |
| Inconsistent formats | 11.06.2026 vs 2026-06-11 | Normalize to one canonical format (for dates: ISO 8601) |
| Unit mismatches | Pressure in bar in one system, psi in another | Convert to an agreed unit at ingestion |
| Outliers | A temperature reading of 8,500 Celsius | Investigate first, then correct, cap, or flag |
| Invalid values | An email address without an @, a negative age | Validate against rules; reject or quarantine |
| Irrelevant fields | Columns nobody has queried in years | Trim them from the analytical dataset |
Clean data, step by step
1. Profile before you touch anything
Start by measuring, not fixing. Count nulls per column, list distinct values, check ranges and distributions, and watch row counts over time. Profiling tells you where the problems actually are, which is rarely where you assumed. It also gives you a baseline, so after cleansing you can show what improved.
2. Remove duplicates
Decide what makes a record unique (an ID, a natural key, or a combination of fields) and deduplicate on that. The hard part is near-duplicates: ACME AS and Acme A/S are the same company to a human and two companies to a database. Fuzzy matching helps, but every merge rule should be explicit and reviewable.
3. Handle missing values
There is no single right answer for a missing value, only a right answer per field. Some gaps can be backfilled from another source, some can be imputed from surrounding data, and some should stay NULL, because inventing a value is worse than admitting you do not have one. What matters is that every rule is deliberate and written down, not decided silently by whoever ran the script.
4. Standardize formats and units
Pick one canonical representation for each kind of value: one date format, one text encoding, one unit per physical quantity, one spelling per category label. Then convert at the boundary, when data enters the system, so nothing downstream ever has to guess.
5. Deal with outliers
An outlier is a question, not an error. A reading of 8,500 Celsius is a broken sensor; a sudden doubling of orders might be your best day ever. The rule is simple: investigate before you delete. When an outlier is genuine, keep it and let the analysis handle it. When it is a fault, correct or flag it so the record of what happened survives.
6. Validate against rules
Turn what you learned in the earlier steps into explicit checks: types, ranges, allowed values, referential integrity, freshness. Then run them continuously, not once. A validation rule that runs on every load is the difference between cleaning data and keeping it clean.
7. Document and automate
Write down what was changed, why, and by which rule, and keep the original values recoverable. Then automate the whole sequence. Manual cleansing fixes a dataset; automated cleansing fixes a pipeline.
Cleansing is not a project with an end date. Data degrades the moment it is collected, and a dataset cleaned once by hand is dirty again by next quarter.
Keep it clean at the source
The cheapest place to fix data is the moment it arrives. Validating at ingestion means errors are caught while their context still exists: which source, which batch, which sensor, which change. In operational settings, where data streams in continuously and decisions follow within minutes, that is the only place cleansing can realistically live. The goal is not a heroic annual cleanup, but a pipeline where data is checked, corrected, and trusted by default.