<!-- persona-dash wiki source
     doc_id:   database/chapter-4-data-quality-part-1
     title:    Chapter 4: Data Quality Part 1
     subtitle: Intro, Terminology, Missing data.
-->

## 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**

|Term|Definition|
|----|----------|
|Row|One record in a table|
|Column|One field across every row.|
|NULL|An empty cell. No value was ever stored there. It is different from zero and different from an empty piece of text.|
|Grain|What one row of a table represents. One sale? One month?|
|Aggregate function|A function that takes many rows and returns one number: ```COUNT```,```SUM```,```MIN```,```MAX```,```AVG```.|
|Window Function|A calculation across a group of related rows that keeps every row in the output. ```GROUP BY``` collapses rows, a window function does not.|
|Character Set|The list of characters a system knows about, each with a number. Unicode is the big international one.|
|Encoding|The rule for turning those character numbers into bytes for storage. UTF-8 and latin1 are two different rules.|
|Byte|Eight bits. Storing a number from 0 to 255. Everything in a database is ultimately bytes.|
|Subquery|A query nested inside another query, usually so the outer one can use a column the inner one calculated|

* 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.

```sql
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.

```sql
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. 

```sql
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. 

```sql
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:
```sql
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:

```sql
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.

```sql
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;
```