Chapter 4: Data Quality Part 1

Intro, Terminology, Missing data.

Part of Database · page 4 of 5

Data Quality Part 1

A data anomaly is a discrepancy between the data and the facts. This article is the query toolkit for proving they exist in a database.

You find an anomaly by counting something and comparing the count to what we expect. Reading rows one at a time will show us a few obvious problems and hide everything else.

Example: A member table has 500 rows. You run a count of distinct member Ids and get 487. Those two numbers cannot both be right if every row is a different member - so 13 rows are duplicates.

TERMINOLOGY DEFINITIONS

• How many? - COUNT(*),SUM(condition). Finds missing data and overloaded categories.
• How many distinct? - COUNT(DISTINCT x),GROUP BY ... HAVING. Finds duplicates and mixed grain.
• Does this match that? - Joins, round trips, range tests. Finds orphaned FKs, mojibake, and impossible values.

Profiling a Table

Profiling means measuring a table's basic shape - how many rows, how many are empty, how many different values, before we go looking for specific problems.

SHOW TABLES;
DESCRIBE tutorial_library.member;
SHOW CREATE TABLE tutorial_library.member;

The third one prints the statement that built the table, including which encoding each text column declares. If a column says latin1 and the data arrived as UTF-8, we have found the cause of our mojibake.

SELECT
COUNT(*) AS rows_total,
COUNT(last_name) AS present,
COUNT(*) - COUNT(last_name) AS missing,
COUNT(DISTINCT last_name) AS different_values,
MIN(CHAR)LENGTH(last_name)) AS shortest,
MAX(CHAR_LENGTH(last)_name)) AS longest
FROM tutorial_library.member;

The shortest of - means there are empty pieces of text in the column - stored, but with no characters in them.

A longest of exactly 50 in a column declared VARCHAR(50) usually means values were cut off when they were written. The data looks complete and is wrong.

Example:

Write one query that profiles three columns at once - first_name, last_name and email - returning the row count plus a missing count for each, One row of output.

SELECT
COUNT(*) AS rows_total,
COUNT(*) - COUNT(first_name) AS missing_first, 
COUNT(*) - COUNT(last_name) AS missing_last,
COUNT(*) - COUNT(email) AS missing_email,
COUTN(DISTINCT email) AS different_emails
FROM tutorial_library.member;

If different_emails is well below COUNT(email), you have also just found duplicate email addresses without meaning to.

Missing Data

NULL means the cell is empty. Nothing was ever there.

Empty string, written as '' means something was stored, a piece of text with zero characters in it. Someone submitted a form and left the box blank.

Sentinel Value means a real-looking value used to represent "missing". Like 'N/A' or 'Unknown', '-', '1900-01-01'. Someone decided how to record a gap.

All three display as a gap in a result grid. They mean different things, and merging them destroys that information.

SELECT
COUNT(*) AS rows_total,
SUM(email IS NULL) AS nulls,
SUM(email = '') AS empty_strings,
SUM(TRIM(email) = '' AND email <> '') AS whitespace_only,
SUM(LOWER(TRIM(email)) IN ('n/a','na','unknown','none','null','-')) AS sentinels
FROM tutorial_library.member;

In MySQL, a true/false expression evaluates to 1 or 0. So, SUM(email IS NULL) adds up the 1s and gives you the count of NULLs. TRIM() strips spaces from both ends of a piece of text.

In PostgreSQL, we have to use the long version:

SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END)

Guessing a list of sentinels only catches the ones we thought of, so a good trick is to count every value and read the top of the list:

SELECT email, COUNT(*) as n
FROM tutorial_library.member
GROUP BY email
ORDER BY n DESC
LIMIT 25;

In columns where nearly every value should be unique, anything appearing 400 times is a sentinel. This is how we would find 39.8283, -98.5795 sitting under a few hundred rows of a geocoded dataset.

NOTE: Comparing anything to NULL gives NULL, never true and never false. So, WHERE email <> 'x' drops every row ehere email is NULL - those rows appear in neither your matches nor your non-matches. Whenever a filter returns fewer rows that expected, check where the NULLs went.

Example 2:

You suspect a numeric column year_of_birth uses 0 or 9999 to mean "unknown". Return each implausible value with its row count, treating anything outside 1900-2026 as suspect.

SELECT year_of_birth, COUNT(*) AS n
FROM tutorial_library.member
WHERE year_of_birth NOT BETWEEN 1900 AND 2026
GROUP BY year_of_birth
ORDER BY n DESC;