Data quality¶

Exercise data-quality-audit-annex.pdf · PDF

You ask the games table a simple question: which is the oldest game? You sort by release date and the answer comes back at once. A game from October 2014. Right behind it, one from April 2004.

2014 before 2004. The query did not fail. There was no red message. The table just handed you a wrong answer with a straight face. That is what dirty data does: it does not crash, it lies quietly. Before you clean anything, you need to know exactly where the dirt is. This page teaches you to look.

Open the chest before you smelt¶

Data quality is how far you can trust a table to answer your question: its values are present, correct, written in one consistent way, and not repeated.

In the crafting game we use all year, Unit 1 was farming and looting: you brought the materials home. Unit 2 is the furnace: preparing them. But nobody smelts blind. First you open the loot chest and look: empty slots, the same item twice, broken items, one sword with absurd stats.

A data audit is that look. You inspect a table, count what is wrong with it and write it down, without changing anything.

Our chest is games: 26,688 Steam games, one per row, collected from the statistics site Steam Spy and left dirty on purpose. You can download the file from the Resources page.

flowchart LR
    A["<b>Audit</b><br>open the chest, count the dirt"] --> B["<b>Decide</b><br>fix, flag or leave"]
    B --> C["<b>Clean</b><br>the furnace: the next lessons"]
    C --> D["<b>Report</b><br>say what you did and what is left"]

Four kinds of dirt¶

Almost everything that can be wrong with a table falls into four groups. Each one has its own way of being found.

The next four sections take them one at a time, on games. Every number below comes from running the query on the class table.

Missing values: the empty slots¶

A missing value is a cell with nothing in it. In SQL it is written NULL, which means "no value", not zero and not an empty word.

You already know the tool from Unit 1. COUNT(*) counts rows. COUNT(column) counts only the rows where that column is not empty. The difference is the number of empty slots.

SELECT COUNT(*) AS total,
       COUNT(price) AS with_price,
       COUNT(*) - COUNT(price) AS no_price
FROM games;

Do the same for every column, in one single SELECT, and you get the full picture:

Always write the percentage next to the count. "23,838 empty scores" sounds like a lot. "89.3% of the column is empty" tells you the column is almost not there.

Missing values in disguise¶

Some empty slots do not look empty. Try these two:

SELECT COUNT(*) FROM games WHERE average_playtime = 0;   -- 25757
SELECT COUNT(*) FROM games WHERE price = 0;              -- 0

average_playtime is 0 in 25,757 rows: 96.5% of the table. Nobody believes that 96.5% of Steam games were played for zero minutes. That zero means "we have no data". It is a missing value wearing a costume.

And price = 0 finds nothing. Not one game costs exactly zero. So where are the free games? They are the 3,095 NULL ones.

Wrong types and formats: text in disguise¶

A data type is the kind of value a column is allowed to hold: whole number, decimal number, text, date. The type decides what you can do with the column.

Ask the database what each column is made of:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'games'
ORDER BY ordinal_position;

Two columns are text and should not be: release_date (a date) and owners (a quantity). This is what broke the query at the top of the page:

SELECT game, release_date FROM games ORDER BY release_date LIMIT 3;

Text sorts like a dictionary, character by character. 8 comes before any letter, and Apr comes before Feb because A comes before F. A date stored as text cannot be sorted, filtered by year or put on a time axis.

It gets worse. A format is the way a value is written down. Most dates here look like Nov 16, 2004. Are they all like that?

SELECT release_date, COUNT(*) AS n
FROM games
WHERE release_date NOT LIKE '___ %, ____'   -- 3 letters, space, day, comma, 4 digits
GROUP BY release_date
ORDER BY n DESC;

42 rows have another shape: 41 give only the month and the year (Oct 2010 three times, Jun 2009 twice...) and one is written the other way round, 8 Oct, 2014. One column, three ways of writing a date.

owners has the same disease. It holds only 13 different values, and all of them are ranges written as text, such as 10,000,000 .. 20,000,000. The most common is 0 .. 20,000, with 17,923 games. Sort it to find the most owned game and you get nonsense:

SELECT game, owners FROM games ORDER BY owners DESC LIMIT 3;

Dota 2, with 100,000,000 .. 200,000,000, loses. As text, 5 beats 1, so "500 thousand" beats "100 million".

Duplicates: the same item twice¶

A duplicate is the same real thing recorded in more than one row.

Is any game in the table twice? Group by name and keep the names that appear more than once:

SELECT game, COUNT(*) AS n
FROM games
WHERE game IS NOT NULL
GROUP BY game
HAVING COUNT(*) > 1
ORDER BY n DESC, game;

74 names come back, covering 149 rows. At the top, Dark Matter, three times. Before you call it a duplicate, look at it:

SELECT number, game, release_date, developer
FROM games WHERE game = 'Dark Matter';

Three developers, three dates. These are three different games that happen to share a name. Delete two of them and you have destroyed real data.

Outliers: the item with absurd stats¶

An outlier is a value far away from all the others. It may be a typing error. It may also be perfectly real.

Prices are in US dollars. Look at the edges of the column:

SELECT MIN(price), MAX(price), ROUND(AVG(price)::numeric, 2) AS mean FROM games;

The average game costs 8.95 and the most expensive one costs 595.99. More than sixty times the average. A typo? Look before you decide:

SELECT game, price, developer
FROM games ORDER BY price DESC NULLS LAST LIMIT 4;

A game-making tool, a video editor, a 3D fashion design program. Steam also sells professional software, and software is expensive. 27 rows cost more than 100 dollars, and they are mostly products like these, not typing mistakes.

The dirt inventory¶

A dirt inventory is the written result of an audit: one line per problem, each with its evidence and a decision.

Every line has four parts. The rule for the third one is strict: no number, no evidence. "There are NULLs" is not an audit. "3,095 NULL in price, 11.6% of the rows" is.

For the decision you have three verbs:

This is the inventory of games. It is also the map of the rest of the unit:

Try it: audit games yourself¶

Open SQL → SQL Lab, choose the database Dades classe and the schema public. Run one statement at a time: select it, then press Run. If you run a whole block, SQL Lab only shows the last result.

Step 1 — Size and types. Run SELECT COUNT(*) FROM games; and then the information_schema.columns query from this page. You should see 26,688 rows and two text columns that should not be text.

Step 2 — Missing values. Write one SELECT with a COUNT(*) - COUNT(column) for each of the ten columns. Your numbers should match the table in "Missing values". Then run the two disguise checks (average_playtime = 0 and price = 0).

Step 3 — Wrong formats. Run the NOT LIKE query and the two owners queries. You should be able to say how many dates have a different shape (42) and why DOOM 3 is not really the most owned game.

Step 4 — Duplicates. Run the HAVING COUNT(*) > 1 query, then look at the Dark Matter rows. Write one sentence: are they the same game?

Step 5 — Outliers. Run the MIN/MAX query and the top prices. Decide, with a reason, whether 595.99 is a typing error.

Key takeaways¶

• A data audit means looking and counting without changing anything. Its only product is a list.
• There are four kinds of dirt: missing values, duplicates, wrong types or formats, and outliers.
• COUNT(*) - COUNT(column) gives the empty cells of a column. Always add the percentage.
• NULL and 0 are different things. A NULL can mean "unknown" (metascore) or carry meaning (a free game), and a 0 can be a missing value in disguise (average_playtime).
• A date or number stored as text sorts like a dictionary and cannot be calculated with.
• Same name is not a duplicate, and an outlier is not an error: look at the row before you decide.
• A dirt inventory has four parts per line: column, kind of dirt, evidence with a number, and a decision (fix, flag or leave).

Quick check¶

Now you know what is dirty, and you have a plan. Time to light the furnace: next, Cleaning: types and formats, where release_date finally becomes a real date.