Lesson 9 of 11 · 24 min
Data-quality checks before publishing
By the end of this lesson you can write the standard pre-publication checks (volume, duplicates at the grain, orphan keys, null rates, business rules and reconciliation to a trusted total) and combine them into a scorecard query.
A data-quality check is a query whose expected answer you know in advance: usually zero bad rows, or a total that must match another total. Six families cover most needs:
- Volume. Are there as many rows as expected? Is any day missing or suspiciously small? (Late or partial loads.)
- Uniqueness at the grain. If the table is one row per order line, then (order_id, line_no) must never repeat. Duplicates double-count everything downstream.
- Referential integrity. Does every foreign key find its dimension row? An orphan key disappears from inner joins, so its sales vanish from the report.
- Completeness (null rates). What share of a column is NULL? A null rate that jumps from 2 % to 40 % means something upstream broke.
- Business rules. Rules that must always hold:
net_amount = gross_amount − discount_amount, one current row per customer, no Social Commerce sales before its launch. - Reconciliation. Does your report’s total equal a trusted total (the fact table, the finance system, yesterday’s published figure plus today’s load)?
This lesson, its 5 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