Set is Empty

You expect the provided query didn't return any results.

Example #

This expectation is simple and self-explanatory - you expect the provided query didn’t return anything.

For example, if you log errors into a table and you need an automated way to check no errors are being added:

Tests:
- Suite: Smoke Tests
  Name: Table dbo.Errors is empty
  Data Source: DWH
  Query: SELECT * FROM dbo.[Errors]
  Expectation: set is empty

Result #

If no rows are returned, the test result will be Passed.

After reading at least one row, CAT marks the test as Failed.

If the underlying provider returns an exception, the result will be Error.

Remarks #

If the table or view you are testing might be huge (well, you don’t expect that, but if under some circumstances might be), consider some optimization techniques. The syntax depends on the underlying provider. If you are not experienced, try to search for e.g., “best way to check table is empty in ORACLE”.

Common Confusion #

More Rows in the Error Message #

When Maximum Errors Logged is set to 1 (default) or 0, CAT closes the connection to the underlying provider immediately after it receives first row. This is intentional, CAT is meant to be an “indicator” that points to problems, not a tool that returns all rows that do not satisfy a rule. You can raise Maximum Errors Logged to a higher value. Maximum is 50 - it is enough to spot patterns in erroneous data and fix the problems.

Log Number of Errors #

For each test with set is empty expectation you can set Log Number of Errors to true or false. Default is false. If this setting is set to true, CAT will not stop the evaluation immediatelly after it retrieves the first row. It will not even stop when it reaches the Maximum Errors Logged (this only narrows the number of rows in the error message, you cannot have 2 billions of rows there). CAT will scan the entire set and enrich the error message with information about total erroneous rows. This feature is in preview! It can be significantly changed or even replaced in future versions. This feature is off by default.

You can instruct CAT to compute the toatal number of erroneous rows like this:

Tests:
- Name: There are no new errors in the error log
  Data Source: DWH
  Query: |
    SELECT  *
    FROM    Audit.ErrorLog
    WHERE   ThrownOn > DATEADD(DAY, -14, SYSUTCDATETIME())
  Expectation: set is empty
  Maximum Errors Logged: 5
  Log Number of Errors: true # default is false

Let’s say the query returns 25 rows. With the settings above, CAT will output to the error message first 5 rows (example of errors), but will retrieve all the data and will enrich the error message with information No row was expected, but exactly 25 rows exist.. The setting is also on the output, if you export test results into a database, just add Number of Errors column and CAT will save the results there.