What is data cleansing?
Data cleansing, also called data cleaning or data scrubbing, is the work of detecting and repairing problems in data that has already been collected. Typical problems include misspelled names, inconsistent formats, impossible values, missing fields, stale entries, and the same customer or product recorded twice. Cleansing can be a one-time project before a system migration or a recurring step in a data pipeline that runs whenever new data arrives.
Cleansing is the counterpart of data validation. Validation checks data as it enters a system and stops bad values at the door, while cleansing repairs the errors already inside. Both serve data quality. Teams that cleanse without adding validation end up fixing the same kinds of errors every month.
Common data cleansing techniques
- Standardization puts values into one format, such as a single date layout, address style, or unit of measure.
- Deduplication uses exact and fuzzy matching to find records that describe the same entity, then merges them.
- Correction against reference data fixes values using a trusted list, such as a postal address database or an official code set.
- Outlier review flags implausible values, such as a meter reading ten times a household’s usual consumption, for a person to check.
- Missing-value handling decides, field by field, whether to fill a gap from another source, flag the record, or exclude it.
Data cleansing in practice
A city water utility prepares to move its customer accounts to a new billing system. The legacy data holds the same service address written several ways, meter numbers that lost their leading zeros in a spreadsheet export, and closed accounts still marked active. The team standardizes addresses against postal reference data and restores meter numbers by matching them to the meter inventory. Suspected duplicate accounts go to customer service staff for review. Every change is logged with the original value, the new value, and the rule that made it, so a disputed bill can be traced back.
What to watch for
- Keep original values and a change log, since automated fixes are sometimes wrong.
- Have people who know the business approve merge rules, because two similar records can be two different customers.
- Take care when cleansing machine learning training data, since removing unusual records can remove the rare cases a model most needs to learn.
- Add field validation to the forms and imports that produced the worst errors, so those errors stop recurring.