Smoke tests with set is not empty

Before any clever test, the dumb one: is there data? One row is enough, the test costs almost nothing, and when it fails nothing else is worth running. Write it per table, per partition, per day — and let CAT write it for the tables you have not thought of yet.

The pattern #

set is not empty passes on the first row and never reads a second. Make the query cheap to return one row — TOP 1 / LIMIT 1 and a filter that hits an index — and it is the fastest test in the project:

• Form view
• YAML

Tests:
- Name: Customer dimension is loaded
  Suite: Smoke tests
  Data source: DWH
  Query: SELECT TOP 1 1 FROM dim.Customer
  Expectation: set is not empty

- Name: Yesterday's sales arrived
  Suite: Smoke tests
  Data source: DWH
  Query: SELECT TOP 1 1 FROM fact.Sales WHERE SaleDate = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
  Expectation: set is not empty

- Name: Every region sold something this week
  Suite: Smoke tests
  Data source: DWH
  Query: |
    SELECT TOP 1 1 FROM fact.Sales s JOIN dim.Store st ON st.StoreKey = s.StoreKey
    WHERE st.Region = 'EMEA' AND s.SaleDate >= DATEADD(DAY, -7, GETDATE())
  Expectation: set is not empty

Three grains: the table, the latest partition or period, a business slice. The middle one is the one that catches the load that did not run; run it first thing after every load and before the expensive comparisons — a run of sets match tests against an empty table fails loudly for the wrong reason.

Not COUNT(*): a count always returns one row, so set is not empty would always pass — and the count costs a scan. See the warning on Set is not empty.

Every table, without writing every test #

A smoke test per table is the textbook case for a template: write it once for one table, point it at the catalog, and every table — including the one created next month — gets its test on the next open:

• Form view
• YAML

Queries:
- Name: warehouse tables
  Data source: DWH
  Query: |
    SELECT  TABLE_SCHEMA, TABLE_NAME
    FROM    INFORMATION_SCHEMA.TABLES
    WHERE   TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA IN ('dim', 'fact')

Tests:
- Name: Table %TABLE_SCHEMA%.%TABLE_NAME% is not empty
  Suite: Smoke tests
  Metadata: warehouse tables
  Data source: DWH
  Query: SELECT TOP 1 1 FROM [%TABLE_SCHEMA%].[%TABLE_NAME%]
  Expectation: set is not empty

The same idea per partition (sys.partitions, a control table of loaded dates) or per customer. Tag the generated tests (Tags: smoke) and run the smoke suite alone with --includeTags smoke when you only want the quick answer — see Run your tests. How templates work, and what happens when the catalog query returns nothing: Generate tests from metadata.

When “not empty” is not enough #

A table with one stale row passes. Pair the smoke test with one of: a freshness check (set is empty on MAX(LoadedAt) < … — see Find problems with set is empty), a set row count where the count is known (five regions, twelve months), or a count comparison with the source — the first rung of Compare data across systems.

Related #

• Set is not empty · Templates — the reference.
• Which expectation when.