Test Definition

Details about Test Definitions in CAT

What is a Test Definition in CAT and How to Define It #

When you have defined Data Sources, it is time to create your Tests. You need to tell to CAT somehow what you want to test.

As with data sources, you can also simply define tests directly in your Project file, like this:

Tests:
- Name: Active customers have contracts
  Description: Every active customer has at least one active contract.
  First Data Source: DWH
  First Query: |
    SELECT  CustomerID
    FROM    dbo.Customer as cust
    WHERE   cust.IsActive = 1
    ORDER BY cust.CustomerID
  Second Data Source: DWH
  Second Query: |
    SELECT  DISTINCT c.CustomerID
    FROM    dbo.Contract as c
    WHERE   c.IsActive = 1
    ORDER BY c.CustomerID
  Expectation: sets match # both queries should return the same data

In the example, you instruct CAT to issue two SQL queries against the same data source and compare the results. Your expectation is, that both return the same data. If they will, the test will pass, if not, the test will fail.

Properties of a Test Definition #

Some properties of a test definition are mandatory, some are optional, some are mandatory only when you use a specific Expectation.

Some properties allow other forms, e.g., is is up to you if you use Test Suite or just Suite - see Synonyms columm. See also Naming conventions.

Test Suite, Test Case #

With CAT, you’ll be creating tests on daily basis. Soon, you’ll have a need for organizing your test somehow. Use Test Suite and Test Case properties for a hierarchy for your tests.

Test Name and Uniqueness #

When we sepak about uniqueness, it means on a project level.

The Name (or Test Name) property does NOT have to be uniqiue. But there are four special properties, that form so called Test Full Name. These are: Suite, Order, TestCase and Name. Combinantions of these four properties have to be unique in the project, otherwise the load of tests will end up with an error.

CAT uses full test name e.g., in console when you use PowerShell module. The full test name is created from the mentioned parts, surrounded with square bracket, separated by dots (ignoring empty non-mandatory parts). Examples:

• [My test suite].[My test name]
• [My test suite].[0].[Some test case].[My test name]

First Query, Second Query #

Based on Expectation you provide, CAT expects you to provide also either one or two queries - CAT will execute them against the data source you provide and check if your expectation is met.

E.g., for expectation SetsMatch you need to specify two queries and two data sources - because you want CAT to check the returned data from both queries are the same. For expectation SetIsEmpty you specify one query / one data source, because you want CAT to execute one query and verify it didn’t return any results.

An example of a test with two queries is at the beginning of this article. If you define some other expectations, you can omit the Second Query and Second Data Source completely and you may also omit the First word for better readibility (but you may also leave the First word as is):

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

Timeout #

You can specify timeout for a test. Important things to note:

• Timeouts can be defined only on a test definition level.

• Always specify timeouts in seconds.

• By default, CAT evaluates a test as long as needed.

If you specify a timeout, CAT will stop evaluating your test after the specified amount of seconds elapses, will set the test result to Error and will proceed to the next test.

Expectation Specific Properties #

Some expectations may define additional properties they understand. E.g., SetRowCount expectation wants to know what number of rows you expect. It is implemented that you specify this value in the Second Query properties, but it is counter-intuitive and looks weird in a project file. Therefore, it also checks for an existence of Expected Row Count (and other forms, see Naming conventions), and if it exists, it will use it.

Where to Define Tests #

The easiest and most straight-forward option is to define all your tests in a Project file. The examples above use project files for defining tests.

In some cases, you might want to define tests elsewhere, e.g. in a database table or in MS Excel sheet. You can define tests in ANY implemented provider. E.g., CAT has Postgres@1 provider. That means you can also define your tests (and / or tests ) in a table:

Get List of Tests from:
- Provider: Postgres@1
  Connection string: Server=localhost;Port=5432;Database=testing;User Id=test_user;Password=myPassword;
  Query: SELECT * FROM public.tests;

If the query SELECT * FROM public.tests returns the above mentioned mandatory columns, CAT will create a test from every returned row.

The same way, you can define your tests in MS Excel, ORACLE table or view, another YAML file, CSV file etc.