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.
|Suite||Test Suite||N||Useful for organizing your tests|
|Order||Test Order||N||Order of the test, please not 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
|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.
Test Name) property does NOT have to be uniqiue. But there are four special properties, that form so called Test Full Name. These are:
Name. Combinantions of these four properties has to be unique for every test you define, 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. Examples:
[My test suite].[My test name]
[My test suite]..[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
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.