Data cleansing (or data cleaning) is the set of processes used to correct or remove incorrect, incomplete, duplicate or badly formatted data from a dataset. It's the action phase within your data quality management framework: first you profile and detect (data profiling), then you measure with KPIs, and finally you clean. This article covers that third phase in detail.
The five phases of the process
1Detection
You can't clean what you haven't identified. This phase relies on profiling and on the quality rules already defined: nulls where there shouldn't be any, out-of-range values, inconsistent formats, duplicate records. The output should be a concrete list of affected records, not a general impression that "the data is dirty."
2Standardization
Before fixing content errors, formats need to be unified: dates in a single format, consistent upper/lowercase, homogeneous units of measurement, normalized country codes (ES vs Spain vs España). Without this step, the deduplication in the next phase fails, because the system doesn't recognize two records as the same when they're actually identical but written differently.
3Deduplication
Identifying and merging records that represent the same real-world entity. Exact deduplication (same value across all fields) is straightforward; the hard part is fuzzy deduplication, when two customer records have an almost identical name, the same phone number, but a different email. Here it helps to define explicit business rules for which record "wins" in a conflict (the most recent one, the one from the most trusted source, the most complete one).
4Validation and correction
Apply the already-defined quality rules to confirm the corrected data meets expected thresholds. Some errors can be fixed automatically (normalizing a zip code, filling in a country from a zip code); others require human review because an automatic fix could introduce a different error than the original.
5Enrichment
An optional but common phase: filling in missing data using external or internal sources (verifying a tax ID against an official registry, filling in geographic coordinates from an address). Enrichment improves completeness, but introduces an external dependency that also needs monitoring.
Automation vs manual review
Not everything should be automated. Automation makes sense when the correction rule is deterministic and low-risk: normalizing a date format, trimming whitespace, unifying uppercase. It's dangerous when the correction involves a business decision: deciding which customer record is the "correct" one between two duplicates with different data, or inferring a missing value that later feeds an automated business decision. In high-risk AI systems under the AI Act, any automatic correction applied to training data must also be documented: what changed, why, and under what criteria.
Where to clean in the pipeline
| Point in the pipeline | Advantage | Risk |
|---|---|---|
| At the source (before ingestion) | Prevents the error from propagating through the whole pipeline | You don't always control the source system |
| In the staging layer | You keep a raw copy for audit purposes before cleaning | Requires more storage and orchestration |
| In the transformation layer (dbt, Spark) | Cleaning stays versioned alongside business logic | Mixing cleaning with transformation makes debugging harder |
The most robust practice is to clean in staging, always keeping the original raw data. That way you can reprocess if a cleaning rule turns out to be wrong, without depending on re-extracting from the source system.
Tools
For small volumes, OpenRefine is still a solid, visual option. For production pipelines, cleaning usually lives as transformations inside dbt or Spark, built on the rules you already defined with your detection data quality tools.
The most common mistake
Cleaning without fixing the root cause. If the same duplicates arrive every week from the same source system, the problem isn't cleaning — it's the capture process. A good data cleansing program always includes a feedback loop back to the data's origin, not just a recurring patch downstream.