Get Help

Track test results over time

A run tells you how the data is today. A table of every result tells you whether it is getting better — and which tests keep failing, which keep you waiting, and who should hear about it tonight. CAT writes that table for you; this guide has the queries to read it.

The pattern

A database output inserts one row per test into a table of yours, as each test finishes — the result, the timing, the message, and the whole definition that produced it (suite, name, data sources, queries, expectation). Every row of one run carries the same ExecutionGuid. Keep the table, and every question about the past is a SELECT.

The output points at a data source the project already has — the warehouse itself is the usual choice, a reporting database works just as well:

Data sources

Name
DWH
Provider
SqlServer@2
Connection string
%DWH_CONNECTION_STRING%

Output

Database
DWH
Table
dbo.CatTestResult
Format
xlsx
File
LatestTestResults.xlsx
Data sources:
- Name: DWH
  Provider: SqlServer@2
  Connection string: "%DWH_CONNECTION_STRING%"

Output:
- Database: DWH
  Table: dbo.CatTestResult
- Format: xlsx
  File: LatestTestResults.xlsx

The second output is optional: the table is for history and reports, the Excel file is for the person who asks “what happened last night” and wants something to open. Every CAT tool writes both — CAT Studio while you develop the tests, CAT CLI or the PowerShell module from a pipeline — so the history starts on the first run, not on the first scheduled one. Database outputs need the Team or Enterprise plan; the Starter and Professional plans write Excel only.

Create the table yourself

CAT creates the table when it may, but a CAT account should not be allowed to. Create it from the script on the database’s page — SQL Server, PostgreSQL, Oracle — and give CAT INSERT on it, nothing more.

Columns are matched by name, so any subset of the script’s columns works, and your own columns are fine when they are nullable or have a default. For reports, keep at least ExecutionGuid, Project, FullName, TestResult, StartedOn, FinishedOn and RawMessage; the rest is there when you want to see the query that failed without opening the project. The full list of what a row carries is on Properties.

Several projects write to one table without a conflict — Project tells them apart, and every query below filters on it. Several environments are best kept as one table per environment (Table: dev.CatTestResult, Table: prod.CatTestResult), with a view that unions them under an Environment literal for the reports.

The reports

Every query is here for each database, against the table from that database’s page: dbo.CatTestResult on SQL Server, public.cat_test_result on PostgreSQL, AERO.CAT_TEST_RESULT on Oracle — change the schema and the project name, nothing else. The text between the queries names the columns as SQL Server spells them; StartedOn is started_on on PostgreSQL and STARTED_ON on Oracle. Each query is a plain SELECT; a table or a view like these is what Power BI, Grafana or Excel reads, and the dashboard is one visual per query.

The last run

What the front page of a dashboard shows: every test of the newest run, the ones that did not pass first.

WITH LastRun AS
(
    SELECT TOP 1 ExecutionGuid
    FROM dbo.CatTestResult
    WHERE Project = 'Flights'
    GROUP BY ExecutionGuid
    ORDER BY MIN(StartedOn) DESC
)
SELECT r.FullName, r.TestResult, r.FinishedOn, r.RawMessage
FROM dbo.CatTestResult r
JOIN LastRun l ON l.ExecutionGuid = r.ExecutionGuid
ORDER BY CASE r.TestResult WHEN 'Passed' THEN 1 ELSE 0 END, r.FullName;

WITH last_run AS
(
    SELECT execution_guid
    FROM public.cat_test_result
    WHERE project = 'Flights'
    GROUP BY execution_guid
    ORDER BY MIN(started_on) DESC
    LIMIT 1
)
SELECT r.full_name, r.test_result, r.finished_on, r.raw_message
FROM public.cat_test_result r
JOIN last_run l ON l.execution_guid = r.execution_guid
ORDER BY CASE r.test_result WHEN 'Passed' THEN 1 ELSE 0 END, r.full_name;

WITH LAST_RUN AS
(
    SELECT EXECUTION_GUID
    FROM AERO.CAT_TEST_RESULT
    WHERE PROJECT = 'Flights'
    GROUP BY EXECUTION_GUID
    ORDER BY MIN(STARTED_ON) DESC
    FETCH FIRST 1 ROWS ONLY
)
SELECT r.FULL_NAME, r.TEST_RESULT, r.FINISHED_ON, r.RAW_MESSAGE
FROM AERO.CAT_TEST_RESULT r
JOIN LAST_RUN l ON l.EXECUTION_GUID = r.EXECUTION_GUID
ORDER BY CASE r.TEST_RESULT WHEN 'Passed' THEN 1 ELSE 0 END, r.FULL_NAME;

The newest run is the one whose first test started last — not MAX(FinishedOn), which a long test of an older run can win. RawMessage is the verdict and the sample rows, short enough for a grid; Message has the description and the queries under it.

Pass rate per day

The trend line. One row per day, passed over everything that ran:

SELECT CAST(StartedOn AS DATE) AS Day,
       COUNT(*) AS Tests,
       SUM(CASE WHEN TestResult = 'Passed' THEN 1 ELSE 0 END) AS Passed,
       CAST(100.0 * SUM(CASE WHEN TestResult = 'Passed' THEN 1 ELSE 0 END) / COUNT(*) AS DECIMAL(5, 1)) AS PassRate
FROM dbo.CatTestResult
WHERE Project = 'Flights'
  AND StartedOn >= DATEADD(DAY, -90, SYSUTCDATETIME())
GROUP BY CAST(StartedOn AS DATE)
ORDER BY Day;

SELECT started_on::date AS day,
       COUNT(*) AS tests,
       COUNT(*) FILTER (WHERE test_result = 'Passed') AS passed,
       ROUND(100.0 * COUNT(*) FILTER (WHERE test_result = 'Passed') / COUNT(*), 1) AS pass_rate
FROM public.cat_test_result
WHERE project = 'Flights'
  AND started_on >= (now() AT TIME ZONE 'utc') - INTERVAL '90 days'
GROUP BY started_on::date
ORDER BY day;

SELECT TRUNC(STARTED_ON) AS RUN_DAY,
       COUNT(*) AS TESTS,
       SUM(CASE WHEN TEST_RESULT = 'Passed' THEN 1 ELSE 0 END) AS PASSED,
       ROUND(100 * SUM(CASE WHEN TEST_RESULT = 'Passed' THEN 1 ELSE 0 END) / COUNT(*), 1) AS PASS_RATE
FROM AERO.CAT_TEST_RESULT
WHERE PROJECT = 'Flights'
  AND STARTED_ON >= SYS_EXTRACT_UTC(SYSTIMESTAMP) - INTERVAL '90' DAY
GROUP BY TRUNC(STARTED_ON)
ORDER BY RUN_DAY;

A nightly schedule makes this one run per day; with several runs a day, group by ExecutionGuid and plot MIN(StartedOn) instead. StartedOn is UTC — a run that starts at 23:30 local may land on the next day.

The tests that fail most

Where the time goes. Every test that did not pass at least once in the last month, the worst first:

SELECT FullName,
       SUM(CASE WHEN TestResult = 'Failed' THEN 1 ELSE 0 END) AS Failed,
       SUM(CASE WHEN TestResult = 'Error'  THEN 1 ELSE 0 END) AS Errors,
       COUNT(*) AS Runs,
       MAX(CASE WHEN TestResult <> 'Passed' THEN StartedOn END) AS LastProblem
FROM dbo.CatTestResult
WHERE Project = 'Flights'
  AND StartedOn >= DATEADD(DAY, -30, SYSUTCDATETIME())
GROUP BY FullName
HAVING SUM(CASE WHEN TestResult <> 'Passed' THEN 1 ELSE 0 END) > 0
ORDER BY SUM(CASE WHEN TestResult IN ('Failed', 'Error') THEN 1 ELSE 0 END) DESC, FullName;

SELECT full_name,
       COUNT(*) FILTER (WHERE test_result = 'Failed') AS failed,
       COUNT(*) FILTER (WHERE test_result = 'Error')  AS errors,
       COUNT(*) AS runs,
       MAX(started_on) FILTER (WHERE test_result <> 'Passed') AS last_problem
FROM public.cat_test_result
WHERE project = 'Flights'
  AND started_on >= (now() AT TIME ZONE 'utc') - INTERVAL '30 days'
GROUP BY full_name
HAVING COUNT(*) FILTER (WHERE test_result <> 'Passed') > 0
ORDER BY COUNT(*) FILTER (WHERE test_result IN ('Failed', 'Error')) DESC, full_name;

SELECT FULL_NAME,
       SUM(CASE WHEN TEST_RESULT = 'Failed' THEN 1 ELSE 0 END) AS FAILED,
       SUM(CASE WHEN TEST_RESULT = 'Error'  THEN 1 ELSE 0 END) AS ERRORS,
       COUNT(*) AS RUNS,
       MAX(CASE WHEN TEST_RESULT <> 'Passed' THEN STARTED_ON END) AS LAST_PROBLEM
FROM AERO.CAT_TEST_RESULT
WHERE PROJECT = 'Flights'
  AND STARTED_ON >= SYS_EXTRACT_UTC(SYSTIMESTAMP) - INTERVAL '30' DAY
GROUP BY FULL_NAME
HAVING SUM(CASE WHEN TEST_RESULT <> 'Passed' THEN 1 ELSE 0 END) > 0
ORDER BY SUM(CASE WHEN TEST_RESULT IN ('Failed', 'Error') THEN 1 ELSE 0 END) DESC, FULL_NAME;

Failed and Error are different problems — the data broke the rule, or the test could not run — so keep them in two columns; a test with thirty errors needs a connection fixed, not a data owner. See Results.

How long a test takes

The one that says which test deserves a Timeout or a cheaper query:

SELECT FullName,
       AVG(DATEDIFF(MILLISECOND, StartedOn, FinishedOn)) / 1000.0 AS AvgSeconds,
       MAX(DATEDIFF(MILLISECOND, StartedOn, FinishedOn)) / 1000.0 AS MaxSeconds,
       COUNT(*) AS Runs
FROM dbo.CatTestResult
WHERE Project = 'Flights'
  AND StartedOn >= DATEADD(DAY, -30, SYSUTCDATETIME())
GROUP BY FullName
ORDER BY AvgSeconds DESC;

SELECT full_name,
       ROUND(AVG(EXTRACT(EPOCH FROM finished_on - started_on)), 3) AS avg_seconds,
       ROUND(MAX(EXTRACT(EPOCH FROM finished_on - started_on)), 3) AS max_seconds,
       COUNT(*) AS runs
FROM public.cat_test_result
WHERE project = 'Flights'
  AND started_on >= (now() AT TIME ZONE 'utc') - INTERVAL '30 days'
GROUP BY full_name
ORDER BY avg_seconds DESC;

SELECT FULL_NAME,
       ROUND(AVG(SECONDS), 3) AS AVG_SECONDS,
       ROUND(MAX(SECONDS), 3) AS MAX_SECONDS,
       COUNT(*) AS RUNS
FROM
(
    SELECT FULL_NAME,
           EXTRACT(DAY FROM FINISHED_ON - STARTED_ON) * 86400
         + EXTRACT(HOUR FROM FINISHED_ON - STARTED_ON) * 3600
         + EXTRACT(MINUTE FROM FINISHED_ON - STARTED_ON) * 60
         + EXTRACT(SECOND FROM FINISHED_ON - STARTED_ON) AS SECONDS
    FROM AERO.CAT_TEST_RESULT
    WHERE PROJECT = 'Flights'
      AND STARTED_ON >= SYS_EXTRACT_UTC(SYSTIMESTAMP) - INTERVAL '30' DAY
)
GROUP BY FULL_NAME
ORDER BY AVG_SECONDS DESC;

A test whose maximum is far above its average is one whose query waits on something — a lock, a load still running — and that is a scheduling problem before it is a test problem. Running the slow ones in their own thread is on Run tests in parallel.

An alert when a test fails

A pipeline already fails the build on a failed test — the pipeline guides show how. The history is for what the pipeline does not do: tell a person who is not watching the pipeline.

The simplest alert needs no scheduler: a stored procedure as the output instead of a table. CAT calls it once per test, with the same values the table would get; the procedure inserts the row, and when the result is not Passed it does whatever the database can do. CAT reads the procedure’s parameters before the run and passes those it recognizes, matched by name, so a procedure may take fewer parameters than the one on its page — as long as what it inserts satisfies the table.

Every block below catches its own errors, and that part is not optional. An error raised by the procedure stops the tests behind it (see the limits), so a mail that cannot go out — Database Mail off, no network ACL, the mail server down — must fail quietly and leave the row saved.

SQL Server sends the mail itself, with Database Mail. Take dbo.SaveCatTestResults from the SQL Server page as it is and add one block after its INSERT:

    IF @TestResult <> 'Passed'
    BEGIN TRY
        DECLARE @Subject NVARCHAR(255) = CONCAT('CAT ', @TestResult, ': ', @FullName);
        EXEC msdb.dbo.sp_send_dbmail
             @profile_name = 'Alerts',
             @recipients   = 'data-team@example.com',
             @subject      = @Subject,
             @body         = @RawMessage;
    END TRY
    BEGIN CATCH
    END CATCH

The project names the procedure instead of the table — Procedure: dbo.SaveCatTestResults — and the account needs EXECUTE on it.

PostgreSQL cannot send mail, so the procedure hands the failure on: a row in an alerts table, which a job (cron, a pipeline, an Azure Function) reads and mails, then sets sent_on. The table:

CREATE TABLE public.cat_alert
(
    alert_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name VARCHAR(700) NOT NULL,
    test_result VARCHAR(30) NOT NULL,
    raw_message TEXT NULL,
    created_on TIMESTAMP NOT NULL DEFAULT (now() at time zone 'utc'),
    sent_on TIMESTAMP NULL
);

Take public.save_cat_test_result from the PostgreSQL page as it is and add one block after its insert:

    if c_test_result <> 'Passed' then
        begin
            insert into public.cat_alert (full_name, test_result, raw_message)
            values (c_full_name, c_test_result, c_raw_message);
        exception
            when others then null;
        end;
    end if;

The project names the procedure instead of the table — Procedure: public.save_cat_test_result — and the account needs EXECUTE on it.

Oracle sends the mail itself with UTL_MAIL, once a DBA has installed it, set SMTP_OUT_SERVER and opened a network ACL to the mail server for the package owner. Take the CAT_TEST_MGMT package from the Oracle page as it is and add one block after the INSERT in its body:

    IF C_TEST_RESULT <> 'Passed' THEN
        BEGIN
            UTL_MAIL.SEND(
                sender     => 'cat@example.com',
                recipients => 'data-team@example.com',
                subject    => 'CAT ' || C_TEST_RESULT || ': ' || C_FULL_NAME,
                message    => DBMS_LOB.SUBSTR(C_RAW_MESSAGE, 4000, 1));
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    END IF;

RAW_MESSAGE is an NCLOB; the mail gets its first 4 000 characters. The project names the package instead of the table — Procedure: AERO.CAT_TEST_MGMT — and the account needs EXECUTE on it.

It runs per test. A run with two hundred failures sends two hundred mails, so decide what an alert is before you write one: a mail only when the previous row of the same FullName was Passed (a new failure, not a known one), a mail for one suite only (@Suite = 'Smoke tests'; c_suite on PostgreSQL, C_SUITE on Oracle), or no mail at all from the procedure — an insert into an alerts table that a job reads every morning and sends as one digest. The procedure is a hook; the policy is yours.

The limits

  • The table grows. One row per test per run: five hundred tests every night are about 180 000 rows a year, and a failed row holds its sample of offending rows in RawMessage and Message. A retention delete on StartedOn once a month is the usual answer; keep the aggregates (the daily pass rate) in a small table of their own if the trend should outlive the rows.
  • The full name is the identity of a test across runs. Rename a test, move it to another suite or change its Order, and its history starts again under the new name. A stable Key on the test is an alternative identity, and it is on the row.
  • Columns are matched by name. Rename a column in the table and CAT stops writing it, without a word; a column CAT does not know must be nullable or have a default, or the first insert fails and stops its thread (next point). The table is read once before the first test, so a change made during a run is seen on the next one.
  • An error in the output stops the tests behind it. When the insert or the procedure fails, the thread that ran the test stops: its remaining tests do not run, and the run ends without a summary (CAT CLI exits with 1). A column CAT cannot fill, a missing permission or an alert that raises an error all do it — which is why the alert blocks above catch their own.
  • Times are UTC. StartedOn and FinishedOn are UTC in every database; convert in the report, not in the table.
  • A killed run has rows. Tests that finished before the run was stopped are in the table — a run with fewer rows than tests is one that did not finish, which the last-run query makes visible when it is compared with the previous run’s count.
← Run tests in parallel
Test Power BI →