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:

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

Property Synonyms Mandatory Notes
Suite Test Suite N Useful for organizing your tests
Order Test Order N Order of the test, please note it is only metadata, CAT ignores it at the moment
Test Case N Useful for organizing your tests
Name Test Name Y Name of the test
Description N Extremely useful when the test fails. Be sure to provide descriptively and in detail why this test was created and what it means if you expectation is not met.
First Data Source Data Source Y Name of a data source (the “friendly” name you defined)
First Query Query Y SQL or other kind of query the underlying Provider understands
Second Data Source Y/N Name of a data source (the “friendly” name you defined)
Second Query Y/N SQL or other kind of query the underlying Provider understands
Expectation Y What do you want to verify with the test. See Expectations
Timeout N Maximum timeout in seconds, CAT will stop execution of a test and give it Error result if the execution time is longer
Maximum Errors Logged Maximum Errors Count N How many rows causing a test to fail should be logged.

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):

- 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

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.