--- title: "Find problems with set is empty" description: "The workhorse expectation — write the rule as 'find the violations', get the offending rows in the failure message; the settings that control how many, the cost, and the tricks" url: "https://docs.justcat.it/how-to-guides/test-patterns/find-problems-with-set-is-empty/" --- # Find problems with set is empty ## The pattern State the rule as a *violation query*: every row it returns is a problem. Orphans, duplicates, nulls where none may be, values out of range, a dimension member that appeared out of nowhere, a fact older than its dimension row: **Properties** Name : No duplicate customer emails Suite : Data quality Data source : DWH Query : ```sql SELECT Email, COUNT(*) AS Rows FROM dim.Customer WHERE IsCurrent = 1 GROUP BY Email HAVING COUNT(*) > 1 ``` Expectation : set is empty Name : Every fact row has a date in the calendar Suite : Data quality Data source : DWH Query : ```sql SELECT TOP 20 f.SaleId, f.DateKey FROM fact.Sales f WHERE NOT EXISTS (SELECT 1 FROM dim.Date d WHERE d.DateKey = f.DateKey) ``` Expectation : set is empty **YAML** ```yaml Tests: - Name: No duplicate customer emails Suite: Data quality Data source: DWH Query: | SELECT Email, COUNT(*) AS Rows FROM dim.Customer WHERE IsCurrent = 1 GROUP BY Email HAVING COUNT(*) > 1 Expectation: set is empty - Name: Every fact row has a date in the calendar Suite: Data quality Data source: DWH Query: | SELECT TOP 20 f.SaleId, f.DateKey FROM fact.Sales f WHERE NOT EXISTS (SELECT 1 FROM dim.Date d WHERE d.DateKey = f.DateKey) Expectation: set is empty ``` Select the columns that identify the row and show the problem — the key and the offending value — not `*`: that is what you will read in the message. ## What comes back when it fails The message says `No row was expected, but at least 1 row exists.` and shows a **sample of the offending rows** — by default one. Three test properties shape it (all on [Test properties](https://docs.justcat.it/reference/tests/properties/ "Test properties")): | Property | Use it for | |---|---| | `Maximum errors logged` | how many rows the sample shows, `0`–`50`. `0` when logs must not carry data; `5`–`20` when you will want to see a pattern, not one row. CAT stops reading the result once it has that many. | | `Maximum sample column length` | longer cells in the sample when the interesting part of a text is past character 25 | | `Log number of errors` | the **total** count of offending rows in the message (`… exactly 25 rows exist.`) and on the output as `Number of errors`. Reads the whole result — use it when something consumes the number, not routinely. | **Properties** Name : No orders without a customer Data source : DWH Query : SELECT OrderId, CustomerKey FROM fact.Orders WHERE CustomerKey IS NULL Expectation : set is empty Maximum errors logged : 20 Log number of errors : true **YAML** ```yaml - Name: No orders without a customer Data source: DWH Query: SELECT OrderId, CustomerKey FROM fact.Orders WHERE CustomerKey IS NULL Expectation: set is empty Maximum errors logged: 20 Log number of errors: true ``` `Output details` (experimental, SQL Server output) writes *every* offending row to the output, not only the sample — for the rare case where the test is also the extract of what to fix. ## The cost, and the trick that removes it With the defaults CAT closes the connection after the first row — the test costs as much as the query costs to return its first row. So the cost is in the query, and the usual advice applies: filter early, index what you filter on, and **don't compute what you don't need** — `SELECT COUNT(*) FROM t` and then expecting it empty is the classic mistake (one row always comes back, with the count in it — see the [Set is empty](https://docs.justcat.it/reference/tests/expectations/set-is-empty/ "Set is empty") page); a `TOP`/`LIMIT` on the violation query is free insurance. ## Tricks * **Thresholds instead of zero tolerance** — when a few violations are normal, put the threshold in the query: `HAVING COUNT(*) > 50`, or a percentage (see [My expectation does not exist](https://docs.justcat.it/how-to-guides/test-patterns/my-expectation-does-not-exist/ "My expectation does not exist")). The test stays binary; the number is in the message. * **Freshness** — `SELECT MAX(LoadedAt) FROM … HAVING MAX(LoadedAt) < DATEADD(HOUR, -6, SYSUTCDATETIME())`: empty when the load ran, one row with the stale timestamp when it did not. * **Self-describing rows** — add a literal column that says what is wrong when a query checks several rules at once: `SELECT 'negative amount' AS Problem, OrderId, Amount FROM … WHERE Amount < 0 UNION ALL SELECT 'future date', …`. * **Across systems** — the violation query can read two systems when the provider allows it (a linked server, a lakehouse with both); when it cannot, that is what [`sets match` and `contains`](https://docs.justcat.it/how-to-guides/test-patterns/compare-data-across-systems/ "Compare data across systems") are for. * **One rule, many tables** — "no table in `stage` has NULL in its key column" is a [template](https://docs.justcat.it/reference/tests/templates/ "Templates") over `INFORMATION_SCHEMA`: one test written, one per table generated. See [Generate tests from metadata](https://docs.justcat.it/how-to-guides/organize-and-run-tests/generate-tests-from-metadata/ "Generate tests from metadata"). * **Description is part of the test** — write *why* the rule exists in `Description`; it travels into the failure message and the outputs, where the person reading it at 6 a.m. needs it. ## Related * [Set is empty](https://docs.justcat.it/reference/tests/expectations/set-is-empty/ "Set is empty") · [Failure message](https://docs.justcat.it/reference/tests/failure-message/ "Failure message") — the reference. * [Smoke tests with set is not empty](https://docs.justcat.it/how-to-guides/test-patterns/smoke-tests-with-set-is-not-empty/ "Smoke tests with set is not empty") — the opposite check.