Get Help

Complete example

One deliberately exhaustive project file, followed by a walk-through of what each part does.

The example below is deliberately exhaustive: it exercises most of what the file accepts in one document, so the shape of the whole is visible in one place. Read it as a map of what is possible, not as a template to copy.

# ---------------------------------------- data sources defined in this file
Data sources:
- Name: FlightsSystem
  Provider: SqlServer@2
  Connection string: >
    Server=sql-aero-flights-prod.database.windows.net;
    Authentication=Active Directory Service Principal;
    Database=sqldb-aero-flights-prod;
    User Id=%FLIGHTS_TESTING_PRINCIPAL_ID%;
    Password=%FLIGHTS_TESTING_PRINCIPAL_SECRET%;

- Name: PassengersSystem
  Provider: Postgres@1
  Connection string: "%PASSENGERS_CONNECTION_STRING%"

# ------------------------- more data sources, read from another YAML file
Get list of data sources from:
- Provider: Yaml@1
  Connection string: "./DataSources/%ENVIRONMENT%.yaml"
  Query: /Data sources          # the top-level node in that file

# -------------------------------------- queries that drive test templates
Queries:
- Name: all gates
  Data source: FlightsSystem
  Query: |
    SELECT  GATE_ID, GATE_NUMBER
    FROM    DIM.GATES
# queries can be read from external lists as well, the same way as
# data sources and tests: Get list of queries from: ...

# ----------------------------------------------- tests defined in this file
Tests:
- Test suite: Consistency checks
  Test case: flights vs passengers
  Order: 1
  Name: Number of passengers is consistent between systems
  Description: >
    Verifies that the passenger count in the flights system does not
    differ significantly from the passenger count in the passengers system.
  First data source: FlightsSystem
  First query: |
    SELECT  COUNT(*)
    FROM    dbo.Passenger
    WHERE   DeletedFlag = 0
  Second data source: PassengersSystem
  Second query: |
    SELECT COUNT(*) FROM public.passenger
  Expectation: sets match
  Tolerance: 5.0
  Tolerance mode: percent
  Tags: DevOnly

- Test suite: Migration to cloud checks
  Test case: Passengers data
  Name: Check important columns in passengers table
  Description: >
    Verifies the passenger data was migrated completely, including
    non-English characters.
  First data source: PassengersSystem
  First query: |
    SELECT   p.passenger_id, p.first_name, p.last_name, p.passport_number, p.date_of_birth
    FROM     public.passenger AS p
    ORDER BY p.passenger_id
  Second data source: FlightsSystem
  Second query: |
    SELECT   PassengerID, FirstName, LastName, PassportNumber, DateOfBirth
    FROM     Dim.Passenger
    WHERE    IsActive = 1
    ORDER BY PassengerID
  Expectation: sets match
  Key: PassengerID
  Tags: Consistency checks, LongRunning     # several tags: comma- or semicolon-separated

- Name: Actual flights for gate %GATE_NUMBER% are loaded
  Test suite: BusinessTests
  Description: Departures for gate %GATE_NUMBER% are loaded.
  Data source: FlightsSystem
  Query: |
    SELECT  *
    FROM    FACT.DEPARTURES AS d
    WHERE   d.DATE_OF_FLIGHT BETWEEN GETDATE() AND DATEADD(DAY, 14, GETDATE())
            AND d.GATE_ID = '%GATE_ID%'
  Expectation: set is not empty
  Metadata: all gates       # makes this a template driven by the query 'all gates'

- Name: There are no new errors
  Description: Checks there are no unresolved errors in the logging table.
  Data source: FlightsSystem
  Query: |
    SELECT  *
    FROM    Audit.LogIngestError
    WHERE   IsResolved = 0
  Expectation: set is empty
  Maximum errors logged: 50
  Log number of errors: true
  Tags: Monitoring

# ------------------- more tests, read from a database and from a workbook
Get list of tests from:
- Provider: SqlServer@1
  Connection string: data source=localhost;integrated security=true;initial catalog=Testing
  Query: SELECT * FROM [Test] WHERE Skipped = 0
- Provider: Excel@1
  Connection string: MyTests.xlsx
  Query: SELECT * FROM [Sheet1]

# --------------------------------------------------------------------- outputs
Output:
- File: LatestTestResults.xlsx
  Format: xlsx
- File: TestResults/TestResults-{timestamp}.xml
  Format: junit
- Database: FlightsSystem
  Table: Tests.CatTestResults

# --------------------------------------------------------------- root settings
Threads: 2

What each part is

Data sources: — two data source definitions written in this file. Each names a Provider and a Connection string; the Name is what test definitions refer to. FlightsSystem hides two secrets in environment variables, PassengersSystem hides the whole connection string.

Get list of data sources from: — one external list. Further data source definitions are read from another YAML file whose path itself contains an environment variable, so the same project file can be pointed at a different environment. Definitions from this list and the ones written above form one set.

Queries: — a query definition named all gates. A query has a Name, a Data source and a Query. It is not a test and is never evaluated on its own; test templates refer to it by name. Queries can also come from an external list under Get list of queries from:, exactly like data sources and tests — it is not used in this example only to keep it shorter.

Tests: — four test definitions written in this file:

  • the first compares two result sets across two systems and allows a numeric Tolerance;
  • the second compares two result sets column by column, names a Key, and carries two tags — tools filter which tests to run by tag;
  • the third is a template: Metadata: all gates binds it to the query above, so one test is generated per row that query returns, with %GATE_ID% and %GATE_NUMBER% replaced by that row’s column values — see Generate tests from metadata;
  • the fourth uses a single query and the set is empty expectation, and tunes how failures are reported.

Get list of tests from: — two more external lists: one a database query, one a worksheet. Their rows carry the same properties, under the same names, as the entries under Tests: above.

Output: — three outputs: a workbook overwritten on every run, a JUnit XML file whose name carries a timestamp, and a table in one of the data sources defined in this file.

Threads: 2 — a root setting. CAT builds one queue of all tests and evaluates it in two threads. Root settings are plain scalars and can sit anywhere at the root; they are kept last here only so the lists come first.

  • Introduction — the minimal file and the rules every project file follows.
← Introduction
Naming conventions →