Find problems with set is empty

Most data tests are one sentence: no row may break this rule. Write the query that finds the rows that do, expect it to return nothing — and when it does not, the failure message hands you the culprits.

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:

• Form view
• 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):

• Form view
• 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 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). 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 are for.
• One rule, many tables — “no table in stage has NULL in its key column” is a template over INFORMATION_SCHEMA: one test written, one per table generated. See 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 · Failure message — the reference.
• Smoke tests with set is not empty — the opposite check.