Get Started in 4 Simple Steps

Follow these steps to see the result of your first test in a few minutes.

1. Install CAT

Start PowerShell. If you are not sure whether you have PowerShell 7, check it like this:

PowerShell version

If the number starts with 7, you are ready to install CAT. If not, download and install PowerShell 7 from this URL:

https://learn.microsoft.com/en-us/powershell/scripting/install/installing-powershell-on-windows?view=powershell-7.3#installing-the-msi-package

Download the x64 msi package (not zip!). Run the installer and go through it (next, next, next…)..

Run PowerShell 7 from the start menu:

PowerShell 7 in the Start menu

Be sure you do NOT start Windows PowerShell (PowerShell 5).

Install CAT with this simple one-line command:

Install-Module CAT -AcceptLicense -Force -AllowClobber -Scope CurrentUser

2. Create a Project

Run these commands in PowerShell 7:

Import-Module CAT
cd ~/Documents # or any other folder of your choice
mkdir MyProject # it is best practice to have each project in a separate folder
cd MyProject
New-CatProject MyProject

Change MyProject to something meaningful, if you want.

The command will create MyProject.cat.yaml file in MyProject folder. Find the file and open it with your favourite editor (we recommend Visual Studio Code with YAML extension). The project file looks something like this:

Data Sources:
- Name: DWH
  Provider: SqlServer@1
  # put your connection string here
  Connection string: data source=localhost;integrated security=true;initial catalog=DWH

# let's write our first test
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

3. Write Your First Test

Tweak the data source definition and point it to your data. Try SqlServer@1, Postgres@1, Oracle@1, … Change connection string to your connection string. Give it some meaningful name.

Notice how simple the test definition is. Give another name to your test, change first query and second query to SQL you want to verify.

If you want to compare results of two SELECT statements, use sets match expectation. Tip: in the Second Query you can “hard code” what you expect from the first query, just type something like SELECT 1, 'Something you expect' UNION ALL SELECT 2, 'Something else'.

For many other expectations, like set is empty, set is not empty, set rowcount you don’t specify the Second Data Source nor the Second Query. set row count requires one more property, ExpectedRowCount.

Start with something simple, e.g.:

Tests:
- Name: No new errors in the log table
  Description: Check we don't have any new errors logged
  Data Source: DWH # use your data source name here
  Query: |
    SELECT  TOP (5) *
    FROM    dbo.ErrorLog
    WHERE   LogDate >= DATEADD(day, -5, sysdatetime())
  Expectation: set is empty

Use SQL syntax you are used to. CAT just sends raw SQL queries to the underlying databases (ORACLE, PostgreSQL, …). There is nothing like CAT SQL.

4. Run the Tests

Run this PowerShell command:

Invoke-CatProject

And that’s it. You should see the result of your test.

CAT test result

There is much more CAT can do. You can compare data from two different systems (e.g., SQL Server with Power BI), store results of your tests in MS Excel files or relational databases, generate your tests from metadata and much more. All of that with very simple configuration.

← Introduction
Introduction →