Introduction

Using Expectations, you define what you expect from the returned data.

In every test definition, regardless from where you load them (from database, from file), you need to specify you Expectation.

Every test runs either one or two queries. You need to tell CAT what you expect from them, such as it returns nothing, it returns at least one row, both queries return the same data etc.

Example:

Tests:
- Name: Dimension Currency is not empty
  Suite: Smoke tests
  Description: Checks the Currency dimension table is not empty. If yes, something went wrong with load to DWH, data in this table are expected.
  Data Source: DWH
  Query: SELECT * FROM Dimension.Currency
  Expectation: set is not empty

See the expectation row - you tell CAT, that you expect the query will return at least one row.

As with almost anything in CAT, you can follow your own naming convention. The example will work with set is not empty, Set Is Not Empty, SET_IS_NOT_EMPTY, SetIsNotEmpty, … See Naming conventions.

In some test definitions you define only one query / data source pair - Query, Data Source (First Query and First Data Source also works in that case). In other test definitions, you define two these pairs, in that case also provide Second Data Source and Second Query. This depends on the Expectation you provide in the test definition.

Why’s that? Let’s say you provided sets match expectation - it compares two sets of data and the test passes if they are the same. Logically you need run two queries. On the other hand set is not empty expectation works only with one - you leave the Second Data Source and Second Query empty or you omit it completelly. If you provided two, it would be confusing what should happen - do both need to be empty? Or at least one? CAT fails the test with an error if you provide two queries when only one is expected.

🐱

Important: When you provide two queries, they CAN be from different providers. That way you can easily compare data across systems, e.g. MS SQL Server with PostgreSQL or ORACLE with data in a Power BI semantic model.

In the Overview there is a column with expected number of queries.

CAT executes every test you define, and for each of them it runs the queries against the data sources. Then it compares your expectation with the actual data and either marks the test as Passed or Failed. If there is an error during the execution of a test, has result Error.

For full list of defined expectations and how they work, see Expectations Overview.