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.

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. Derivatives Atlas. https://phucnguyenvan.com/concept/ba-why-data-goes-bad