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

Older projects put the number into Second query instead. CAT still reads it from there, for compatibility only: use Expected row count in every new test, also when the definitions live in a database table or a worksheet. Second query as the place for the number may go in a future major version.

Queries #

One: Data source and Query (First data source and First query work too). The expected number comes from Expected row count (or, in older projects, from Second query) — one of them is required. When a test carries both, CAT takes the first of the two it finds in the test and ignores the other without a warning, so give only one. A Second data source on the test is an error.

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 *.