An independent study reference written by Dr Phuc V. Nguyen. It is not official subject material — for assessment requirements always follow your subject outline and vUWS.

Data Foundationsintermediate

Data cleansing

Q: An analyst fixes 300 malformed dates by hand in a spreadsheet export, then builds a report from the corrected file. What is the main problem?

The corrections vanish at the next refresh and nobody can reproduce or review what was changed. A manual edit is invisible and unrepeatable. Next month the same file arrives with the same defect, the fix has to be redone from memory, and no reviewer can see which values were altered or on what basis. Encoding the rule in the pipeline solves all three at once.

Q: A deduplication rule is loosened to catch more matches, and the merged customer count falls sharply. Which risk has increased?

Over-merging, combining records that belong to genuinely different people. Loosening the rule raises recall and lowers precision. More pairs are declared matches, so distinct people with similar names in the same area get combined, which corrupts contact history and consent records and is much harder to reverse than leaving a duplicate in place.

Cleansing is the set of operations that makes data usable: profiling to find out what is actually there, standardising formats and units, validating against rules and reference data, resolving duplicates, and deciding explicitly what to do about missing values and outliers. Two disciplines separate a professional job from a spreadsheet fix. Every change is made in the pipeline so it repeats on the next refresh, and every change is recorded, because a cleansing step is an assumption about the world that all later analysis inherits. Cleansing treats the symptom; it does not repair the process that produced the defects.

Try it yourself

Cleansing rules and the matching threshold

A rule may standardise, validate, quarantine, flag or leave a defect unresolved. It may never invent a missing value, never assign a unit or a currency the feed did not record, and never quietly drop a row. Below that sits the matching threshold that decides which customer records are the same person.

Order records included 12

Rows in the customer table 12

Quarantined rows 2

Rows with an unresolved defect 7

At this threshold3 made · 0 wrong · 3 missed

Candidate pair	Email	Name	Postcode	Score	Truth	Outcome
C-2041 / C-2078 Marisa Nolan	0.00	0.85	1.00	0.405	one customer	missed
C-4510 / C-4577 Owen Bright	1.00	1.00	1.00	1.000	one customer	merged, correct
C-4602 / C-4618 Jae Sung	1.00	0.60	1.00	0.880	one customer	merged, correct
C-4731 / C-4733 David Chen	0.00	1.00	1.00	0.450	two customers	left apart, correct
C-4805 / C-4890 Anika Patel	0.00	0.70	0.00	0.210	one customer	missed
C-4912 / C-4915 Sam Lee	0.00	1.00	1.00	0.450	one customer	missed
C-5023 / C-5044 Delta Print	1.00	0.75	0.00	0.775	one customer	merged, correct
C-5108 / C-5140 Ren Okabe	0.00	0.80	1.00	0.390	two customers	left apart, correct

Two pairs agree on exactly the same fields to exactly the same degree, so both score 0.450. One of them is a single customer recorded twice and the other is two people who share a name and a postcode. No threshold separates them, and no reweighting of these three fields separates them either, which is the whole difficulty. The review band is fixed at 0.12 below the threshold. In the order table on this page, the Marisa Nolan pair falls under the review band floor of 0.58, so nothing merges and nobody looks at it, and the customer table still holds two rows for one person.

Match threshold0.70

Rules applied to the raw feed

Missing mobile numbers are always left unresolved. No rule here fills a blank, because a filled blank is an invented fact that everything downstream then treats as evidence. The format rule works the same way. It may rewrite how an amount is written only where the row records its own currency, which every row in this feed does, and a bare numeral on a feed that records no currency would stay flagged, because AUD 1,450 and USD 1,450 are different facts.

Audit log, 10 entries

ORD-1008 STANDARDISE Standardise amount. arrived as "1450" with no currency marker in the amount string, and the row records its currency as AUD in a field of its own, so the rule rewrites the presentation to A$1,450.00 from a recorded fact and the amount itself is unchanged

ORD-1009 QUARANTINE Postcode reference check. postcode 9999 is not in the reference list, row held out of the working table for review, not deleted and not guessed

ORD-1007 QUARANTINE Order date range check. order date 1 January 1900 is a well-formed date and outside the plausible range, so a format rule passes it and this one does not

ORD-1001 LEAVE UNRESOLVED Customer link. pair scored 0.405, under the review band floor of 0.58, so nothing merges and nobody looks at it. The second record of this customer stays in the working table and uniqueness carries the extra copy. At this threshold no pair of different people is merged anywhere in the candidate file.

ORD-1002 LEAVE UNRESOLVED Customer link. pair scored 0.405, under the review band floor of 0.58, so nothing merges and nobody looks at it. The second record of this customer stays in the working table and uniqueness carries the extra copy. At this threshold no pair of different people is merged anywhere in the candidate file.

ORD-1004 FLAG First-option concentration. Agriculture is first alphabetically and holds 25.0% of the working table, above the 20.0% limit, and the value is left exactly as it is

ORD-1005 FLAG First-option concentration. Agriculture is first alphabetically and holds 25.0% of the working table, above the 20.0% limit, and the value is left exactly as it is

ORD-1006 FLAG First-option concentration. Agriculture is first alphabetically and holds 25.0% of the working table, above the 20.0% limit, and the value is left exactly as it is

ORD-1010 FLAG Freshness check. captured 229 days before 31 March 2026, past the 90 day tolerance, a refresh policy is the fix and no rule can invent a fresh value

ORD-1003 LEAVE UNRESOLVED Missing mandatory field. mobile number is blank, no rule here may invent one, so the gap stays visible in the count

At a threshold of 0.70 the linkage makes 3 correct merges, makes 0 wrong ones, sends 0 to review and misses 3 real duplicates. Matching on email alone is precise and under-merges. Adding name and postcode raises recall and starts joining different people who share a common name in one postcode. Merging two real customers is much harder to undo than leaving a duplicate, which is the reason to set the threshold high and send borderline pairs to a person rather than to a merge. The same asymmetry governs missing values. Filling missing income with the column mean asserts that people who declined to answer earn the average and shrinks the spread, so any interval built on it looks tighter than the evidence supports. A missingness flag often carries more information than any guess at the value.

The 14 orders are a stylised fixture written for this widget, not a real extract, and the as-of date is fixed at 31 March 2026. Every row records its own currency, which is what lets a format rule rewrite an amount without assigning one. The business register extract covers 9 of the 14 order records, which are 8 of the 13 customers behind them. The linkage weights, email 0.55, name 0.30 and postcode 0.15, are a stated choice rather than an estimated model, and the fixture also declares which candidate pairs are truly one customer, which is the only reason the merge decisions above can be scored at all.

Why it matters

Cleaning data is closer to editing than to washing. You are not removing dirt from an object that stays the same underneath. You are deciding that this blank means zero, that this record and that record are the same person, that this reading of two hundred degrees is an instrument fault rather than a fire. Each decision could have gone the other way, and each one shapes the answer. Which is exactly why the decisions get written down.

Before you read on — recall

An analyst fixes 300 malformed dates by hand in a spreadsheet export, then builds a report from the corrected file. What is the main problem?

Formulas

Why deduplication needs blocking

P = \dfrac{n(n-1)}{2}

Comparing every record with every other record across 50,000 rows means about 1.25 billion pairs. Blocking first, for instance comparing only records that share a postcode and the first letter of the surname, cuts the candidate set by orders of magnitude, at the cost of missing genuine pairs that disagree on the blocking key.

Mean imputation shrinks apparent variability

s^2_{\text{after}} = s^2_{\text{before}} \times \dfrac{n_{\text{obs}} - 1}{n_{\text{total}} - 1}

Filling gaps with the column mean leaves the mean unchanged and adds nothing to the sum of squared deviations, because each filled value sits exactly at the mean. With 200 observed values in a column of 250, the variance comes out about a fifth too low, which makes any interval built on it look tighter than the evidence supports.

Worked examples

Scenario

A team merges customer records from a retail system and a loyalty app. Matching on email finds 12,000 duplicates. Matching on name and postcode as well finds 19,000.

Solution

Neither figure is right on its own. Email matching is precise and misses people who used a different address for the app, so it under-merges. Adding name and postcode raises recall and starts merging distinct people who share a common name in a dense postcode, so it over-merges. The workable method is to block on postcode, score candidate pairs across several fields, and set the threshold by which error costs more. Merging two real customers is harder to undo than leaving a duplicate, so the threshold leans conservative and borderline pairs go to review.

Scenario

Fifteen per cent of income values are missing in a customer dataset, and an analyst fills them with the column mean so the model will run.

Solution

That choice is not neutral. It asserts that people who did not disclose their income earn the average, and it shrinks the spread of the column so the model looks more certain than it should. Before imputing, check whether missingness relates to anything. If high earners decline to answer more often, mean imputation drags the top of the distribution down and biases every coefficient involving income. Recording missingness as its own flag often carries more information than any guess at the value.

Common mistakes

✗Cleansing is a preliminary chore before the real analysis. The decisions made while cleansing, such as which records are the same person and what a blank means, can move the result more than the choice of model does.
✗Cleansing should always be done in the source system so the data is fixed at the root. Correcting the source is worth doing where it is possible, and analytical corrections still belong in the pipeline where they are versioned and repeatable; a manual edit is lost at the next refresh.
✗Removing outliers improves the data. An outlier can be an error or the most important observation in the file, and deleting it without a reason removes exactly the fraud, fault or breakthrough you might be looking for.
✗Deleting rows with missing values is the neutral option. Dropping incomplete records is safe in general only when values are missing completely at random, meaning missingness is unrelated to both the recorded and the unrecorded values. Under weaker conditions whether it biases the answer depends on the estimand and the model being fitted, and where missingness relates to the outcome, dropping introduces bias while looking tidy.

Revision bullets

•Profile first: counts, distinct values, ranges, patterns, null rates
•Standardise, validate against reference data, then deduplicate
•Blocking makes deduplication tractable and trades some recall for feasibility
•Every cleansing rule is an assumption, so version it in the pipeline and log it
•Missing value handling and outlier removal are analytical choices, not housekeeping

Quick check

An analyst fixes 300 malformed dates by hand in a spreadsheet export, then builds a report from the corrected file. What is the main problem?

A deduplication rule is loosened to catch more matches, and the merged customer count falls sharply. Which risk has increased?

Connected topics

Analytics Workflow ETL and ELT Fitness for Use Quality Dimensions Why Data Goes Bad Sampling Bias Text Mining Pipeline

More in Data Foundations

What Big Data Means Forms of Data Data Warehouse Metadata Star Schema Measures & Dimensions Cloud for Analytics

Sources

Rahm & Do (2000)
Rahm, E., & Do, H. H. "Data Cleaning: Problems and Current Approaches." IEEE Data Engineering Bulletin, 23(4), 3-13, 2000.
A structured account of cleaning steps from profiling through transformation to duplicate elimination.
Fellegi & Sunter (1969)
Fellegi, I. P., & Sunter, A. B. "A Theory for Record Linkage." Journal of the American Statistical Association, 64(328), 1183-1210, 1969.
The probabilistic foundation for scoring candidate record pairs and setting match and non-match thresholds.
Dasu & Johnson (2003)
Dasu, T., & Johnson, T. Exploratory Data Mining and Data Cleaning. Wiley, 2003.
Argues for profiling the data before cleaning it, and for treating cleaning decisions as part of the analysis.

How to cite this page

Dr. Phil's Quant Lab. (2026). Data cleansing. Business Analytics Atlas. https://phucnguyenvan.com/analytics_atlas/concept/ba-data-cleansing