Set row count

You expect the query to return exactly the specified number of rows.

Example #

The table must hold data of all five departments:

Tests:
- Name: Data of all departments are present
  Suite: Data quality checks
  Data source: HR
  Query: SELECT DISTINCT department_id FROM work_records
  Expectation: set row count
  Expected row count: 5

The expected number can also go into Second query — handy when the definitions live in a database table and you do not want a column that only one expectation reads:

Tests:
- Name: Data of all departments are present
  Suite: Data quality checks
  First data source: HR
  First query: SELECT DISTINCT department_id FROM work_records
  Second query: 5
  Expectation: set row count

Queries #

One: Data source and Query (First data source and First query work too). The expected number comes from Expected row count or from Second query — one of them is required; both at once is an error. A Second data source on the test is an error as well.

Result #

If the number of rows returned by the query is exactly the expected one, the result is Passed; the message says The expected row count matched the actual row count: 5.

If the number differs, the result is Failed: The expected row count 5 did not match the actual row count 7.

If the underlying provider returns an exception, the result is Error.

Properties it reads #

Remarks #

CAT reads the whole result set to count it — it does not stop early when the count is already exceeded, so the message can report the actual number. Keep the result small when the table is big: SELECT DISTINCT department_id as in the example, not SELECT *.