Assertions: queries that return no rows when everything is right

A test you can run is worth ten you remember to do

After the load, the casts and the deduplication, you have a clean view. You believe it is right. Next month a new file arrives, the same view runs on it, and something that was true in March is not true in April: a reference appears twice, an amount is negative, a city is spelt a new way. Nothing errors. The report goes out.

The defence is a set of queries, each of which encodes one thing that must be true and returns the rows that violate it. When all of them return nothing, the table passes. When one of them returns rows, those rows are the problem, already found.

The checks worth writing first

The key is unique.

SELECT reference, count(*) FROM payments_clean GROUP BY reference HAVING count(*) > 1;

Required columns are present.

SELECT * FROM payments_clean WHERE reference IS NULL OR amount IS NULL OR paid_on IS NULL;

Values are in range.

SELECT * FROM payments_clean
WHERE amount <= 0 OR amount > 10000000
   OR paid_on < date '2020-01-01' OR paid_on > current_date;

Every reference to another table resolves.

SELECT p.* FROM payments_clean p
LEFT JOIN customers c ON c.id = p.customer_id
WHERE c.id IS NULL;

The categories are ones we know.

SELECT status, count(*) FROM payments_clean
WHERE status NOT IN ('paid', 'refunded', 'failed') GROUP BY status;

Each takes a minute to write and is a permanent record of what “clean” was supposed to mean.

The check that catches the most

None of the above notices when a file is silently half the size it should be. A truncated export, a source system that dropped a branch, a job that ran before the day’s data landed: all pass every check above, because every row that is there is fine. The check that catches them compares this load with the last one:

WITH by_load AS (
  SELECT load_file, count(*) AS n, sum(amount) AS total
  FROM payments_clean GROUP BY load_file
)
SELECT * FROM by_load
WHERE n < 0.7 * (SELECT avg(n) FROM by_load)
   OR n > 1.5 * (SELECT avg(n) FROM by_load);

A load that is less than 70% or more than 150% of the typical size is flagged. The thresholds are arbitrary and should be, because the point is to be asked, not to be blocked. The same shape works for the share of NULL cities, the number of distinct customers, and the total amount. Distribution checks catch the failures that row-level checks are blind to.

Run them as one thing

Put every check in a file, checks.sql, each one labelled, and run the file after every load. A shell loop that runs each query and prints its row count is enough; a check with a non-zero count fails the load. Free tools exist that do exactly this with less typing. dbt, which is open source, lets you declare unique, not_null, accepted_values and relationships tests on a column in a YAML file and runs them as SQL. Great Expectations and Soda Core do the same from Python. They are worth adopting once you have more than a dozen checks; before that, a file of queries and a loop is the same thing without the dependency.

Promote the ones that should never be violated

Some checks are not warnings but laws: a payment reference must be unique, an amount must be positive. Those belong in the table as constraints, UNIQUE (reference) and CHECK (amount > 0), so that a bad row is refused at write time rather than found at check time. The next module builds tables that way. The queries stay useful for the rules that are usually true, such as “a load is about the size of the last one”, which no constraint can express.

The habit

When you find a data problem, write the query that would have found it, add it to the file, and re-run the whole file. A month later you have a test suite that describes every way this data has ever gone wrong, and the next new way is the only one that can surprise you.