Data cleaning: a bounded, measurable job, not an open-ended preliminary project
Nobody needs perfect data. What you need are the fields a decision depends on, in a state that can be measured and maintained.
Data cleaning: definition and scope
Data cleaning, also called data cleansing, means finding and fixing defects in a dataset: duplicates, missing values, implausible entries, and inconsistent formats. Afterwards the dataset should be fit for a named purpose, such as delivery, customer communication, or a forecasting model, and it does not need to be flawless for that.
The methods of data cleaning are not new, and that works in your favor, because the foundations have been in print for decades. The theory of record linkage, which asks when two records refer to the same person or company, was set out by Fellegi and Sunter in 1969 [1]. In 2000 Rahm and Do proposed a classification of data defects that separates errors within a single source from errors that only arise when several sources are merged [2].
Data cleaning is easily confused with related terms. Data quality describes a state, measured against criteria such as completeness and uniqueness, whereas cleaning is the work that changes that state. Data preparation is the umbrella term. Besides cleaning, it covers building features for a model and setting up the pipeline, the processing chain that applies the same rules again to every new data load. Finally, master data management sets lasting rules for who creates and changes customer, product, and supplier records. Without such rules, the same defects reappear after cleaning.
Data Quality: Criteria, Measurement, CostsHow data cleaning works
If you want to clean data, you need a question first and a tool second. The process has six steps, and only the fourth is what most people picture when they hear the word cleaning.
| Step | What happens | What it produces |
|---|---|---|
| 1. Define purpose and scope | The business question determines which tables and fields matter, for example the delivery address and delivery terms for logistics | a short list of the fields that will be cleaned and of the fields that will be left as they are |
| 2. Measure the dataset | For each field, duplicates, inconsistent formats, and outliers are counted along with the share of missing values | a finding based on counts rather than an impression |
| 3. Agree rules with the business department | The rules settle which of two customer records wins, which field is mandatory and which value is impossible | a rulebook that someone is accountable for |
| 4. Clean | Duplicates are merged, formats and units standardized, missing values handled by rule, and proven errors corrected | a cleaned dataset and a log of every change |
| 5. Measure again | The same metrics as in step 2 are recorded, and the business department checks a sample | a before-and-after comparison in figures |
| 6. Build it into a pipeline | The rules run as code on every new and every changed record | a dataset that stays clean |
A measurement of our own on a public dataset shows how much an impression misses. In the UCI Adult dataset, the "Workclass" column appears to be 100 percent complete. Only once the question mark is declared a missing value does it emerge that 5.6 percent of the entries are missing. Among 32,561 rows we found 24 duplicates, or 0.074 percent. That share is low but not zero, and without a check it would go unnoticed.
The measurement in detail: our data quality audit across three public datasetsFor step 4 we work with fixed decision rules. Technical duplicates, meaning identical rows from an export that ran twice, are deleted and counted. Business duplicates are never deleted silently but are resolved by a rule agreed with the business department: either the most recent version wins, or the version held in the system of record does. Missing values are only dealt with once it is clear why they are missing.
What your data will look like a year from now depends on step 6. A one-off exercise corrects only what is in the system today, while the causes, such as records created without any check, are still at work the very next day.
Worked example: the cost of uncleaned master data
The following example is a model calculation for a fictitious company and is not based on client figures. A wholesaler of technical products holds 40,000 customer master records and processes 120,000 orders a year. Its management wants to know whether the master data should be cleaned.
| Assumption or calculation step | Value |
|---|---|
| Duplicates in the customer master | 6 percent, 2,400 records |
| Records with a faulty address or faulty delivery terms | 5 percent, 2,000 records |
| Duplicate mailings: two catalog mailings a year at 2.50 euros each to every duplicate | 12,000 euros |
| Wrong deliveries caused by master data errors: 0.8 percent of orders, 960 cases at 45 euros each | 43,200 euros |
| Orders needing clarification in the sales back office: 1.5 percent of orders, 1,800 cases at 20 minutes each and 48 euros per hour | 28,800 euros |
| Total: follow-on costs per year | 84,000 euros |
| One-off review effort for the cleaning: 4,400 review cases at 10 minutes each and 48 euros per hour, around 730 working hours | 35,200 euros |
The rate of 48 euros per hour is an internal fully loaded cost rate. The model calculation assumes that duplicates and faulty records do not overlap and that wrong deliveries and orders needing clarification are separate events.
No cleaning removes every error, and not every wrong delivery originates in the master record. The model calculation therefore assumes that 70 percent of the follow-on costs are avoided. That comes to 58,800 euros a year, set against a review effort of 35,200 euros. Counted from the day the cleaning is finished, the review effort pays for itself in a little over seven months.
This review effort is only part of the total effort, namely the manual work on the review cases in step 4. It does not include measuring the dataset, the rulebook, the second measurement, or the pipeline from steps 2, 3, 5 and 6. Those depend on the state of your systems, extend the payback period, and belong in your own calculation.
| Scenario | Share avoided | Annual benefit | Benefit in the first full year less review effort | Review effort paid back after |
|---|---|---|---|---|
| cautious | 50 percent | 42,000 euros | 6,800 euros | around ten months |
| model calculation | 70 percent | 58,800 euros | 23,600 euros | a little over seven months |
| favorable | 85 percent | 71,400 euros | 36,200 euros | just under six months |
For a rough calculation of your own, count the transactions of one year for each type of error and put a cost on them.
Annual benefit = sum of (faulty transactions per year × follow-on cost per transaction) × share avoided
Payback in months = total one-off effort ÷ annual benefit × 12
The second part of the calculation concerns the period after the cleaning. The wholesaler creates or changes 8,000 customer records a year. For simplicity, the model calculation assumes that errors occur in these records at the same rate as in the old dataset, 11 percent, and that follow-on costs rise with the number of faulty records. On that basis, 880 faulty records are added every year, one fifth of the cleaned volume. Without a check at the point of entry, the annual benefit shrinks by a further 11,760 euros every year and is used up after five years. If the 880 cases are instead checked at the point of entry, that costs 7,040 euros a year at the same rate, and the benefit is preserved. Setting up this check is not included in the 7,040 euros. Both review amounts, the 35,200 euros for the existing records and the 7,040 euros a year, assume that the check flags the faulty records and no others. If it also flags clean ones, the effort rises. Checking all 8,000 new or changed records by hand would cost 64,000 euros a year, more than the annual benefit of 58,800 euros.
When data cleaning pays off and when it does not
Data cleaning pays off when a decision or a process with follow-on costs depends on the data and those costs can be quantified. There are also organisational prerequisites: an agreed way of identifying a customer or product unambiguously across all systems, a business department that decides the rules, and a place where those rules will run automatically in future.
Four false conclusions are easy to draw:
- Clean everything first, then start the project. Only the fields the business question needs are cleaned. The rest can wait until a question calls for them.
- Cleaning is a job for IT. IT carries out the cleaning, but only the business department, such as sales or accounting, can decide which of two customer records is valid.
- The new system will solve the problem. A migration carries the errors into the new system if nobody has defined rules beforehand.
- Unusual values are errors. A bulk order or a machine failure is unusual, but it did happen. If you remove such values, you delete real events from the data.
We advise against cleaning when no decision depends on the fields concerned, when the follow-on costs are low or when the source system is about to be replaced anyway. If it is still unclear how a customer is identified across all systems, the work starts with defining a key. Without a shared key, each system becomes clean on its own terms, and you end up with several versions of the truth instead of one.
A reputable provider is one that names the purpose and the fields concerned in its proposal, measures before and after cleaning with the same metrics and hands the rulebook and the change log over to you. If any of these is missing, you are buying a one-off exercise rather than a lasting state.
Data cleaning in the data science process
In CRISP-DM, the process model that divides a data project into six phases, data cleaning belongs to phase 3, data preparation [3]. In our approach it is the first of three tasks there, ahead of building features and setting up the pipeline. The findings it builds on come from phase 2 and its data quality assessment. In phase 6 we keep the same pipeline running in production, so that a model receives the same cleaned data in day-to-day use as it did in training.
All six CRISP-DM phases at a glance Phase 3: data preparation with decision rules for each defect Phase 6: putting models into operation and monitoring themFrequently asked questions about data cleaning
What is the difference between data cleaning and data preparation?
Data cleaning is the first part of data preparation. It fixes whatever in the dataset is wrong, duplicated, or incomplete. Data preparation goes further: it derives features from the cleaned data that a model can learn from (feature engineering) and builds the processing chain that repeats both tasks with every new data load.
How can duplicate records be cleaned up?
Identical rows are technical duplicates and are deleted. Records that hold the same customer under a different spelling are business duplicates. They are found by matching name, address, and identification numbers and are merged by a rule that the business department sets. If it then remains unclear which key applies in future, the same duplicates will reappear.
What does data cleaning cost, and when does it pay off?
The follow-on costs of faulty transactions per year, multiplied by the share that cleaning avoids, are weighed against the effort. That effort is partly one-off, for the measurement, the rulebook, and the review cases, and partly ongoing, for the checks at the point of entry. If follow-on costs are low or cannot be quantified, the calculation does not add up.
How long does it take to clean master data?
The duration depends on the number of fields the business question requires and on the review cases that cannot be decided automatically. The business department also needs time to agree the rules. A stocktake beforehand makes the fields and review cases visible, so that the effort can be planned.
Can data cleaning be automated?
The execution can be automated, but the decisions cannot. Format checks, the search for duplicates, and the counting of missing values run automatically with every new data load. The business department decides once which of two customer records is valid and whether an unusual value is an error or a genuine event.
Where does data cleaning start?
It starts with a business question and a measurement. The question determines which fields matter, and the measurement shows for each field how many values are missing, duplicated, or implausible. An export of the relevant tables is enough for the measurement, and your data does not need to leave your premises for it.
Sources
- Fellegi, I. P.; Sunter, A. B. (1969): A Theory for Record Linkage. Journal of the American Statistical Association 64 (328), pp. 1183-1210. https://doi.org/10.1080/01621459.1969.10501049
- Rahm, E.; Do, H. H. (2000): Data Cleaning: Problems and Current Approaches. IEEE Data Engineering Bulletin 23 (4), pp. 3-13. https://dbs.uni-leipzig.de/files/research/publications/2000-1/pdf/TBDE2000.pdf
- Chapman, P.; Clinton, J.; Kerber, R.; Khabaza, T.; Reinartz, T.; Shearer, C.; Wirth, R. (2000): CRISP-DM 1.0. Step-by-step data mining guide. SPSS Inc., pp. 10 and 23-25. https://nas.uhcl.edu/boetticher/ML_DataMining/CRISPWP-0800.pdf
Work through your master data figures with us
The number of your faulty transactions and their follow-on costs are enough for an initial assessment of whether cleaning will pay off for you.
Book a free initial consultation