A customer aged 150
A club table says one member is 150 years old. Another spent 900 pounds on snacks. Sam appears twice. Two dates are written differently.
Nothing crashed. But every report from this table will be wrong. Spotting these faults is the first step to fixing them.
Key words
| Word | What it means | Example |
|---|---|---|
| Data quality | How well data is fit for its use: complete, correct, consistent. | No gaps or repeats |
| Missing data | A value that should be there but is not. | An empty age |
| Outlier | A value far from all the others. | Age 150 |
| Duplicate | A row that appears more than once. | Sam, twice |
| Inconsistency | The same fact written in different ways. | Y, yes and YES |
| Mismatched type | A value stored as the wrong kind, such as a date as text. | A price in text |
| Unstandardised | Not written to one agreed format. | Two date styles |
| Structural | A problem with the file or layout, not the values. | Wrong file type |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can name the main kinds of poor data quality and spot each one in a table.
Starter: what you already know
Have a go
Starter: what you already know
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Four kinds of missing data
| Kind | Why it is missing | Example |
|---|---|---|
| MCAR | Missing completely at random: pure chance | A page of forms got wet |
| MAR | Missing at random: linked to another value we do have | Younger members skip the spend question |
| MNAR | Missing not at random: linked to the missing value itself | Big spenders hide how much they spend |
| Systematic | A fault that always loses the same data | The form never saved the age field |
The cells students fill in are left blank in this preview.
The other quality problems
| Problem | What it looks like |
|---|---|
| Outlier | Age 150 or spend 900 |
| Abnormal trend or pattern | Sales jump every night at 3am |
| Duplication | Sam on two rows |
| Different formats | Year first and day first dates |
| Inconsistency | Y, yes, YES |
| Mismatched type | A price stored as text |
| Structural | A file that will not open in the tool |
| Unstandardised | yes/no in one file, Y/N in another |
The cells students fill in are left blank in this preview.
Try it: find the faults
-- Duplicates: the same name, age and spend twice.
select name, age, spend, count(*) as times
from visits
group by name, age, spend
having count(*) > 1;
-- Outliers and gaps.
select id, name, age, spend
from visits
where age is null or age > 100 or spend > 100;A common mistake
Warning
Many people delete every row with a gap. If the gaps are not random, deleting them removes a whole group, such as every big spender. Ask why data is missing before you decide what to do.