Lesson 12 of 13 · 22 min
Validation queries
By the end of this lesson you can express data rules as queries that return the rows (or the number of rows) breaking them, avoid the NOT IN NULL trap in referential checks, and combine checks into a data-quality scorecard.
A validation query is a rule written as SQL. The convention that makes them easy to automate: a check returns the rows that break the rule, so an empty result means pass. A count of those rows is the check's score.
Most checks fall into six families:
| family | question | typical SQL |
|---|---|---|
| volume | did we get the rows we expected? | count(*) per day or per load, compared with yesterday |
| uniqueness | is the key really unique? | count(*) - count(DISTINCT key), or GROUP BY key HAVING count(*) > 1 |
| completeness | are required values present? | count(*) - count(col) (previous lesson) |
| referential | does every reference point to an existing row? | NOT EXISTS / LEFT JOIN ... IS NULL |
| validity and range | are values allowed and plausible? | status NOT IN (...), quantity <= 0, end < start |
| reconciliation | do two sources agree? | totals or balances compared across tables |
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons