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

Why data goes bad

Q: An analyst finds that a customer list cleaned two years ago now has 23 per cent undeliverable addresses, although no records were added and no system changed. What is the most likely explanation?

Real-world change: people moved and nobody updated the records. Decay produces exactly this pattern. If about a tenth of addresses change each year, two years without maintenance leaves roughly a fifth of the list wrong, and not one new error was entered to cause it.

Q: Two systems both load successfully into the warehouse, and combined revenue for one region is exactly one hundred times too large. What is the most likely cause?

One source stores amounts in cents and the mapping loaded them as dollars. Reading a cent value as a dollar value multiplies every amount by a hundred, so the error arrives as a clean factor rather than a ragged one. A part-finished load leaves revenue short rather than inflated, a missing dimension row drops the facts that reference it out of the join, and duplicated rows do inflate a total without landing on exactly a hundredfold across a whole region. Unit and currency errors load without complaint and produce ordinary-looking numbers, which is why they can run for months before anyone reconciles a total back to the source.

Bad data is rarely one mistake. It enters at three predictable points. At capture, when a field is mandatory but serves no purpose for the person filling it in, so defaults and placeholders get keyed. At integration, when two systems use different identifiers, units, time zones or code lists and a mapping silently loses the difference. And through decay, because the world moves on: people change address, businesses close, products are discontinued. Decay is the one most often forgotten, because nothing breaks. The record stays exactly as correct-looking as the day it was entered while quietly becoming false.

Try it yourself

Capture, integration and decay

Defects enter at three points. At capture, when a form demands what the person cannot supply. At integration, when two systems disagree and the mapping hides it. Through decay, when the world moves and the record does not.

Order records included 12

Rows in the customer table 12

Quarantined rows 2

Rows with an unresolved defect 7

Defect rows in the raw feed6 capture · 3 integration · 1 decay

Capture6 of 14 rows in the feed, 4 still open in the working table

blank mobile number, industry defaulted to the first option in the list, order date keyed as 1 January 1900, postcode outside the reference list

Fix: At the form. Make the field optional where it is genuinely not needed, default it to unknown rather than to a real code, add a plausibility rule at entry, and check the postcode against the reference while the customer is still on the line.

Integration3 of 14 rows in the feed, 2 still open in the working table

one customer arriving with two email-derived ids, an amount landing as a bare numeral with no marker in the string

Fix: In the pipeline. Derive one customer key with a documented matching rule, and parse amounts into cents with the currency held as its own field, which is what lets the bare numeral above be rewritten from a recorded fact rather than a guess.

Decay1 of 14 rows in the feed, 1 still open in the working table

records past the 90 day tolerance on 31 March 2026

Fix: By refresh policy. Nothing is wrong with these rows, they have simply aged past what the decision allows, so the fix is a stated refresh cycle rather than any correction.

Meaning drift, which is none of the three

A complaint form is redesigned in March. One free-text field becomes three tick boxes, so a customer who ticks two now generates two rows. Complaint volume jumps about 30% year on year. Nothing is broken, no value is wrong, and no quality dimension moves. What changed is what a row means.

Fix: Record the change in metadata with an effective date, so the earlier period can be restated or the chart annotated, instead of an operational story being invented to explain a step.

Decay, computed from A(t) = A0 (1 - d)^t at d = 12%. after 1 year 88.0%, after 2 years 77.4%, after 3 years 68.1%. A list can rot to that level with no new error ever entered, which is why one-off cleaning has a short half-life.

Annual real-world change rate d (%)12%

Timeliness tolerance (days)90 days

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

In the raw feed, 6 rows carry a capture defect, 3 carry an integration defect and 1 are past the 90 day tolerance. Rules can standardise and quarantine, and they cannot repair a form. At 12% annual real-world change, a list that starts fully accurate falls to 88.0% after a year and 68.1% after three, without one new error being entered. That is why a refresh policy beats a one-off clean.

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

Nobody sets out to enter wrong data. A contact centre worker with thirty people in the queue and a mandatory industry field they cannot see the point of will choose the first option in the list. That is not carelessness, it is a reasonable response to a badly designed form. Meanwhile the customer list you cleaned last year is rotting on its own, because a tenth of those people moved house and none of them told you.

Before you read on — recall

An analyst finds that a customer list cleaned two years ago now has 23 per cent undeliverable addresses, although no records were added and no system changed. What is the most likely explanation?

Formulas

Decay of an untouched list

A_t = A_0 \, (1 - d)^{\,t}

With an annual rate of real-world change

d

and starting accuracy

A_0

, accuracy falls geometrically. Suppose 12 per cent of customers change address in a year. A list that starts fully accurate is about 88 per cent accurate after one year, 77 per cent after two and 68 per cent after three, without a single new error ever being entered. This is why refresh cycles matter more than one-off cleaning.

Small step errors compound across a pipeline

P(\text{clean end to end}) = \prod_{i=1}^{k} (1 - e_i)

If each of five hand-offs is 98 per cent reliable, the chance a record crosses all five untouched is about 90 per cent. A defect rate of one in fifty per step becomes one in ten overall. Step-level error rates that sound negligible stop sounding negligible once a record crosses several systems.

Worked examples

Scenario

A bank finds that 4 per cent of its business customer records list the industry as agriculture, far above the real share. The field is mandatory on the account opening screen.

Solution

Agriculture is first alphabetically in the code list. Staff opening an account under time pressure, for a field that does not affect the account, take the first option. No individual did anything unreasonable and the aggregate is badly wrong. The fixes belong at the form rather than in the warehouse: make the field optional where it is genuinely not needed, default it to unknown rather than to a real code, or capture it from a source with a reason to be right, such as a business register.

Scenario

A year-on-year comparison of complaint volumes shows a sudden 30 per cent jump in March that nobody can explain operationally.

Solution

The complaint form was redesigned in March. What used to be a single free-text field became three tick boxes, so a customer who ticks two now generates two rows. Nothing is broken and no value is wrong. The meaning of a row changed, and the comparison is now silently comparing two different things. Recording that change in metadata with an effective date is what lets the analyst restate the earlier period or annotate the chart, rather than invent an operational story to explain it.

Common mistakes

✗Bad data is mostly caused by careless staff. Most capture errors are reasonable responses to forms demanding information the person does not need and cannot verify, so the fix is usually a design change rather than training.
✗Once cleaned, a dataset stays clean. Records decay as the world changes, so accuracy falls even when nothing new is entered and no system is touched.
✗Integration errors show up as failures. The dangerous ones do not fail. A currency, unit or time zone mismatch loads perfectly and produces plausible numbers that are wrong by a constant factor.
✗A process change is an operational matter, not a data matter. Changing what a form captures changes what a row means, which breaks comparisons over time unless the change is recorded with a date.

Revision bullets

•Three entry points: capture, integration, decay
•A mandatory field with no local purpose produces defaults, not information
•Silent integration faults (units, currency, time zone, code lists) are the hardest to see
•Accuracy decays geometrically with the annual rate of real-world change
•A form or process change alters what a row means and breaks comparisons over time

Quick check

An analyst finds that a customer list cleaned two years ago now has 23 per cent undeliverable addresses, although no records were added and no system changed. What is the most likely explanation?

Two systems both load successfully into the warehouse, and combined revenue for one region is exactly one hundred times too large. What is the most likely cause?

Connected topics

ETL and ELT Fitness for Use Quality Dimensions Data Cleansing Data Governance

More in Data Foundations

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

Sources

Redman (1998)
Redman, T. C. "The Impact of Poor Data Quality on the Typical Enterprise." Communications of the ACM, 41(2), 79-82, 1998.
Traces poor data quality to routine operational and integration processes rather than to isolated errors.
Rahm & Do (2000)
Rahm, E., & Do, H. H. "Data Cleaning: Problems and Current Approaches." IEEE Data Engineering Bulletin, 23(4), 3-13, 2000.
Classifies defects into single-source and multi-source, and schema-level and instance-level, which is the map used here for capture and integration faults.
DAMA-DMBOK (2017)
DAMA International. DAMA-DMBOK: Data Management Body of Knowledge, 2nd ed. Technics Publications, 2017.
Sets out the practice of managing quality at the point of capture rather than downstream.

How to cite this page

Dr. Phil's Quant Lab. (2026). Why data goes bad. Business Analytics Atlas. https://phucnguyenvan.com/analytics_atlas/concept/ba-why-data-goes-bad