{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"validating-a-cleaned-table","title":"Assertions: queries that return no rows when everything is right","url":"https://addaly.com/learn/data-and-sql/validating-a-cleaned-table","position":50,"of":66,"minutes":8,"answer":"Encode each thing that must be true as a query that returns its violations, run the whole file after every load, and include a size-and-distribution comparison with the previous load.","keyIdea":"Encode each thing that must be true as a query that returns its violations, run the whole file after every load, and include a size-and-distribution comparison with the previous load, because row-level checks cannot see a file that is silently half missing."},"body":{"format":"markdown","text":"## A test you can run is worth ten you remember to do\n\nAfter 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.\n\nThe 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.\n\n## The checks worth writing first\n\n**The key is unique.**\n\n```sql\nSELECT reference, count(*) FROM payments_clean GROUP BY reference HAVING count(*) > 1;\n```\n\n**Required columns are present.**\n\n```sql\nSELECT * FROM payments_clean WHERE reference IS NULL OR amount IS NULL OR paid_on IS NULL;\n```\n\n**Values are in range.**\n\n```sql\nSELECT * FROM payments_clean\nWHERE amount <= 0 OR amount > 10000000\n   OR paid_on < date '2020-01-01' OR paid_on > current_date;\n```\n\n**Every reference to another table resolves.**\n\n```sql\nSELECT p.* FROM payments_clean p\nLEFT JOIN customers c ON c.id = p.customer_id\nWHERE c.id IS NULL;\n```\n\n**The categories are ones we know.**\n\n```sql\nSELECT status, count(*) FROM payments_clean\nWHERE status NOT IN ('paid', 'refunded', 'failed') GROUP BY status;\n```\n\nEach takes a minute to write and is a permanent record of what \"clean\" was supposed to mean.\n\n## The check that catches the most\n\nNone 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:\n\n```sql\nWITH by_load AS (\n  SELECT load_file, count(*) AS n, sum(amount) AS total\n  FROM payments_clean GROUP BY load_file\n)\nSELECT * FROM by_load\nWHERE n < 0.7 * (SELECT avg(n) FROM by_load)\n   OR n > 1.5 * (SELECT avg(n) FROM by_load);\n```\n\nA 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.\n\n## Run them as one thing\n\nPut 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.\n\n## Promote the ones that should never be violated\n\nSome 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.\n\n## The habit\n\nWhen 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."},"check":{"question":"A monthly payments file is silently truncated by the exporting system at 50,000 rows out of 80,000. Uniqueness, not-null, range and referential checks all pass. Which check would have caught it, and why do the others miss it?","options":[{"text":"A `count(*) FILTER (WHERE amount IS NULL)` check, because truncation leaves the missing rows present with NULL amounts that the not-null check should have seen.","correct":false,"tempting":"Truncated rows are absent, not present with NULLs; a not-null check can only inspect rows that exist."},{"text":"A referential check against the customers table, because 30,000 customers now have no payments and appear as unmatched rows.","correct":false,"tempting":"That check looks for payments without a customer, not customers without payments, and a customer with no payment this month is normal."},{"text":"A comparison of this load's row count with previous loads, because every row that did arrive is individually valid and only the total reveals the loss.","correct":true},{"text":"A CHECK constraint on `paid_on` restricting it to the current month, because the missing rows fall outside the month and would be rejected.","correct":false,"tempting":"Constraints evaluate rows being written; rows that never arrived are not written and cannot be rejected."}]},"prev":{"slug":"duplicates-and-which-to-keep","title":"Duplicates: finding them, and deciding which row survives","url":"https://addaly.com/learn/data-and-sql/duplicates-and-which-to-keep"},"next":{"slug":"missing-values-honestly","title":"Missing values: NULL, empty, N/A, zero and minus one","url":"https://addaly.com/learn/data-and-sql/missing-values-honestly"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Assertions: queries that return no rows when everything is right', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/validating-a-cleaned-table (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Assertions: queries that return no rows when everything is right","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":5,"slug":"data-that-arrives-dirty","title":"Data that arrives dirty"},"answer":null,"keyIdea":"Encode each thing that must be true as a query that returns its violations, run the whole file after every load, and include a size-and-distribution comparison with the previous load, because row-level checks cannot see a file that is silently half missing.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"a-test-you-can-run-is-worth-ten-you-remember-to-do","runs":[{"text":"A test you can run is worth ten you remember to do"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"the-checks-worth-writing-first","runs":[{"text":"The checks worth writing first"}]},{"t":"p","runs":[{"text":"The key is unique.","b":true}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT reference, count(*) FROM payments_clean GROUP BY reference HAVING count(*) > 1;"},{"t":"p","runs":[{"text":"Required columns are present.","b":true}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT * FROM payments_clean WHERE reference IS NULL OR amount IS NULL OR paid_on IS NULL;"},{"t":"p","runs":[{"text":"Values are in range.","b":true}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT * FROM payments_clean\nWHERE amount <= 0 OR amount > 10000000\n   OR paid_on < date '2020-01-01' OR paid_on > current_date;"},{"t":"p","runs":[{"text":"Every reference to another table resolves.","b":true}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT p.* FROM payments_clean p\nLEFT JOIN customers c ON c.id = p.customer_id\nWHERE c.id IS NULL;"},{"t":"p","runs":[{"text":"The categories are ones we know.","b":true}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT status, count(*) FROM payments_clean\nWHERE status NOT IN ('paid', 'refunded', 'failed') GROUP BY status;"},{"t":"p","runs":[{"text":"Each takes a minute to write and is a permanent record of what “clean” was supposed to mean."}]},{"t":"h2","id":"the-check-that-catches-the-most","runs":[{"text":"The check that catches the most"}]},{"t":"p","runs":[{"text":"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 "},{"text":"is","i":true},{"text":" there is fine. The check that catches them compares this load with the last one:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH by_load AS (\n  SELECT load_file, count(*) AS n, sum(amount) AS total\n  FROM payments_clean GROUP BY load_file\n)\nSELECT * FROM by_load\nWHERE n < 0.7 * (SELECT avg(n) FROM by_load)\n   OR n > 1.5 * (SELECT avg(n) FROM by_load);"},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"run-them-as-one-thing","runs":[{"text":"Run them as one thing"}]},{"t":"p","runs":[{"text":"Put every check in a file, "},{"text":"checks.sql","code":true},{"text":", 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. "},{"text":"dbt","code":true},{"text":", which is open source, lets you declare "},{"text":"unique","code":true},{"text":", "},{"text":"not_null","code":true},{"text":", "},{"text":"accepted_values","code":true},{"text":" and "},{"text":"relationships","code":true},{"text":" 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."}]},{"t":"h2","id":"promote-the-ones-that-should-never-be-violated","runs":[{"text":"Promote the ones that should never be violated"}]},{"t":"p","runs":[{"text":"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, "},{"text":"UNIQUE (reference)","code":true},{"text":" and "},{"text":"CHECK (amount > 0)","code":true},{"text":", 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."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"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."}]}],"check":{"question":[{"text":"A monthly payments file is silently truncated by the exporting system at 50,000 rows out of 80,000. Uniqueness, not-null, range and referential checks all pass. Which check would have caught it, and why do the others miss it?"}],"options":[{"text":[{"text":"A "},{"text":"count(*) FILTER (WHERE amount IS NULL)","code":true},{"text":" check, because truncation leaves the missing rows present with NULL amounts that the not-null check should have seen."}],"correct":false,"tempting":[{"text":"Truncated rows are absent, not present with NULLs; a not-null check can only inspect rows that exist."}]},{"text":[{"text":"A referential check against the customers table, because 30,000 customers now have no payments and appear as unmatched rows."}],"correct":false,"tempting":[{"text":"That check looks for payments without a customer, not customers without payments, and a customer with no payment this month is normal."}]},{"text":[{"text":"A comparison of this load’s row count with previous loads, because every row that did arrive is individually valid and only the total reveals the loss."}],"correct":true,"tempting":null},{"text":[{"text":"A CHECK constraint on "},{"text":"paid_on","code":true},{"text":" restricting it to the current month, because the missing rows fall outside the month and would be rejected."}],"correct":false,"tempting":[{"text":"Constraints evaluate rows being written; rows that never arrived are not written and cannot be rejected."}]}],"order":[3,2,1,0],"why":null},"prev":{"slug":"duplicates-and-which-to-keep","title":"Duplicates: finding them, and deciding which row survives","minutes":8},"next":{"slug":"missing-values-honestly","title":"Missing values: NULL, empty, N/A, zero and minus one","minutes":9}}}