Get Help

Store definitions in a database

Keep test definitions in database tables instead of YAML: create only the tables you need (often just the tests), pick your platform's script, point the project file at the tables.

A database can serve CAT its definitions the same way a YAML file does: a row is a definition, the columns are its properties. Any statement that returns the right columns works — a table, a view, a stored procedure — so a test base can be generated, versioned and edited with the tools you already use for data. The mechanism is described in Lists; this page gives you tables to start from.

Create only what you need

Three kinds of definition can come from a database — tests, data sources, queries — and each is its own list in the project file. Most projects store only the tests in the database and keep the two or three data sources inline in the project file; create the cat_data_source_definition and cat_query_definition tables only when those definitions should live in the database too (many data sources managed centrally; metadata queries for generated tests kept next to the tests). Nothing forces the three together.

The columns

The scripts below create three tables with the columns CAT reads most. The names are the property names with underscores — CAT matches them by synonym, so TestSuite, test_suite and Test Suite are all fine, and a column CAT does not know is ignored. Add or drop columns freely; the full lists are on the property pages:

Only rows, not tables, are validated: a test row without an expectation fails when the list is read, exactly as the same YAML would.

The scripts are a starting point, not a schema CAT depends on: an is_active flag, an owner column, a last-changed timestamp, a primary key — add what your team needs; CAT reads the columns it knows and ignores the rest.

Point the project file at the tables 

Get list of data sources from:
- Provider: SqlServer@2
  Connection string: "%TEST_REPO%"
  Query: SELECT * FROM test.cat_data_source_definition

Get list of queries from:
- Provider: SqlServer@2
  Connection string: "%TEST_REPO%"
  Query: SELECT * FROM test.cat_query_definition

Get list of tests from:
- Provider: SqlServer@2
  Connection string: "%TEST_REPO%"
  Query: SELECT * FROM test.cat_test_definition WHERE is_active = 1

A WHERE clause is the simplest way to switch tests on and off without deleting them.

Scripts per platform

Pick your database. Each tab creates the three tables; take only the ones you need.

MS SQL Server, Azure SQL Database, Azure SQL Managed Instance. Provider SqlServer@2.

CREATE TABLE test.cat_test_definition (
  test_suite                   NVARCHAR(300)  NULL,
  [order]                      INT            NULL,
  test_case                    NVARCHAR(300)  NULL,
  test_name                    NVARCHAR(300)  NOT NULL,
  description                  NVARCHAR(MAX)  NULL,
  expectation                  VARCHAR(50)    NOT NULL,
  first_data_source            NVARCHAR(200)  NULL,
  first_query                  NVARCHAR(MAX)  NULL,
  second_data_source           NVARCHAR(200)  NULL,
  second_query                 NVARCHAR(MAX)  NULL,
  timeout                      INT            NULL,
  maximum_errors_logged        INT            NULL,
  maximum_sample_column_length INT            NULL,
  log_number_of_errors         BIT            NULL,
  tags                         NVARCHAR(500)  NULL,
  metadata                     NVARCHAR(200)  NULL,
  [key]                        NVARCHAR(500)  NULL,
  sort_data                    BIT            NULL,
  tolerance                    DECIMAL(19, 4) NULL,
  tolerance_mode               VARCHAR(10)    NULL,
  expected_row_count           BIGINT         NULL,
  is_active                    BIT            NOT NULL DEFAULT 1
);

CREATE TABLE test.cat_data_source_definition (
  name              NVARCHAR(200)  NOT NULL,
  provider          NVARCHAR(50)   NOT NULL,
  connection_string NVARCHAR(2000) NOT NULL,
  technology        NVARCHAR(50)   NULL
);

CREATE TABLE test.cat_query_definition (
  name        NVARCHAR(200) NOT NULL,
  data_source NVARCHAR(200) NOT NULL,
  query       NVARCHAR(MAX) NOT NULL,
  description NVARCHAR(MAX) NULL
);

Azure Synapse Analytics, MS Fabric Warehouse: same provider, narrower type surface — use VARCHAR(8000) where the script says NVARCHAR(MAX), VARCHAR instead of NVARCHAR, and drop the DEFAULT (Fabric Warehouse does not accept it); Synapse dedicated pools additionally want a distribution clause.

PostgreSQL. Provider Postgres@1.

CREATE TABLE test.cat_test_definition (
  test_suite                   VARCHAR(300)   NULL,
  "order"                      INT            NULL,
  test_case                    VARCHAR(300)   NULL,
  test_name                    VARCHAR(300)   NOT NULL,
  description                  TEXT           NULL,
  expectation                  VARCHAR(50)    NOT NULL,
  first_data_source            VARCHAR(200)   NULL,
  first_query                  TEXT           NULL,
  second_data_source           VARCHAR(200)   NULL,
  second_query                 TEXT           NULL,
  timeout                      INT            NULL,
  maximum_errors_logged        INT            NULL,
  maximum_sample_column_length INT            NULL,
  log_number_of_errors         BOOLEAN        NULL,
  tags                         VARCHAR(500)   NULL,
  metadata                     VARCHAR(200)   NULL,
  "key"                        VARCHAR(500)   NULL,
  sort_data                    BOOLEAN        NULL,
  tolerance                    DECIMAL(19, 4) NULL,
  tolerance_mode               VARCHAR(10)    NULL,
  expected_row_count           BIGINT         NULL,
  is_active                    BOOLEAN        NOT NULL DEFAULT TRUE
);

CREATE TABLE test.cat_data_source_definition (
  name              VARCHAR(200)  NOT NULL,
  provider          VARCHAR(50)   NOT NULL,
  connection_string VARCHAR(2000) NOT NULL,
  technology        VARCHAR(50)   NULL
);

CREATE TABLE test.cat_query_definition (
  name        VARCHAR(200) NOT NULL,
  data_source VARCHAR(200) NOT NULL,
  query       TEXT         NOT NULL,
  description TEXT         NULL
);

MySQL, MariaDB. Provider MySql@1.

CREATE TABLE test.cat_test_definition (
  cat_test_definition_id       INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  test_suite                   VARCHAR(300)   NULL,
  `order`                      INT            NULL,
  test_case                    VARCHAR(300)   NULL,
  test_name                    VARCHAR(300)   NOT NULL,
  description                  TEXT           NULL,
  expectation                  VARCHAR(50)    NOT NULL,
  first_data_source            VARCHAR(200)   NULL,
  first_query                  TEXT           NULL,
  second_data_source           VARCHAR(200)   NULL,
  second_query                 TEXT           NULL,
  timeout                      INT            NULL,
  maximum_errors_logged        INT            NULL,
  maximum_sample_column_length INT            NULL,
  log_number_of_errors         BOOLEAN        NULL,
  tags                         VARCHAR(500)   NULL,
  metadata                     VARCHAR(200)   NULL,
  `key`                        VARCHAR(500)   NULL,
  sort_data                    BOOLEAN        NULL,
  tolerance                    DECIMAL(19, 4) NULL,
  tolerance_mode               VARCHAR(10)    NULL,
  expected_row_count           BIGINT         NULL,
  is_active                    BOOLEAN        NOT NULL DEFAULT TRUE
);

CREATE TABLE test.cat_data_source_definition (
  cat_data_source_definition_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  name              VARCHAR(200)  NOT NULL,
  provider          VARCHAR(50)   NOT NULL,
  connection_string VARCHAR(2000) NOT NULL,
  technology        VARCHAR(50)   NULL
);

CREATE TABLE test.cat_query_definition (
  cat_query_definition_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  name        VARCHAR(200) NOT NULL,
  data_source VARCHAR(200) NOT NULL,
  query       TEXT         NOT NULL,
  description TEXT         NULL
);

ORACLE. Provider Oracle@1. Column names are quoted to keep their case; unquoted names work just as well, CAT does not mind TEST_SUITE.

CREATE TABLE cat_test_definition (
  "TestSuite"                 NVARCHAR2(300) NULL,
  "Order"                     NUMBER(10)     NULL,
  "TestCase"                  NVARCHAR2(300) NULL,
  "TestName"                  NVARCHAR2(300) NOT NULL,
  "Description"               NCLOB          NULL,
  "Expectation"               VARCHAR2(50)   NOT NULL,
  "FirstDataSource"           NVARCHAR2(200) NULL,
  "FirstQuery"                NCLOB          NULL,
  "SecondDataSource"          NVARCHAR2(200) NULL,
  "SecondQuery"               NCLOB          NULL,
  "Timeout"                   NUMBER(10)     NULL,
  "MaximumErrorsLogged"       NUMBER(10)     NULL,
  "MaximumSampleColumnLength" NUMBER(10)     NULL,
  "LogNumberOfErrors"         NUMBER(1)      NULL,
  "Tags"                      NVARCHAR2(500) NULL,
  "Metadata"                  NVARCHAR2(200) NULL,
  "Key"                       NVARCHAR2(500) NULL,
  "SortData"                  NUMBER(1)      NULL,
  "Tolerance"                 NUMBER(19, 4)  NULL,
  "ToleranceMode"             VARCHAR2(10)   NULL,
  "ExpectedRowCount"          NUMBER(19)     NULL,
  "IsActive"                  NUMBER(1)      DEFAULT 1 NOT NULL
);

CREATE TABLE cat_data_source_definition (
  "Name"             NVARCHAR2(200)  NOT NULL,
  "Provider"         NVARCHAR2(50)   NOT NULL,
  "ConnectionString" NVARCHAR2(2000) NOT NULL,
  "Technology"       NVARCHAR2(50)   NULL
);

CREATE TABLE cat_query_definition (
  "Name"        NVARCHAR2(200) NOT NULL,
  "DataSource"  NVARCHAR2(200) NOT NULL,
  "Query"       NCLOB          NOT NULL,
  "Description" NCLOB          NULL
);

Teradata. Provider Teradata@1.

CREATE TABLE test.cat_test_definition (
  test_suite                   VARCHAR(300)   NULL,
  "order"                      INTEGER        NULL,
  test_case                    VARCHAR(300)   NULL,
  test_name                    VARCHAR(300)   NOT NULL,
  description                  VARCHAR(64000) NULL,
  expectation                  VARCHAR(50)    NOT NULL,
  first_data_source            VARCHAR(200)   NULL,
  first_query                  VARCHAR(64000) NULL,
  second_data_source           VARCHAR(200)   NULL,
  second_query                 VARCHAR(64000) NULL,
  timeout                      INTEGER        NULL,
  maximum_errors_logged        INTEGER        NULL,
  maximum_sample_column_length INTEGER        NULL,
  log_number_of_errors         BYTEINT        NULL,
  tags                         VARCHAR(500)   NULL,
  metadata                     VARCHAR(200)   NULL,
  "key"                        VARCHAR(500)   NULL,
  sort_data                    BYTEINT        NULL,
  tolerance                    DECIMAL(19, 4) NULL,
  tolerance_mode               VARCHAR(10)    NULL,
  expected_row_count           BIGINT         NULL,
  is_active                    BYTEINT        NOT NULL DEFAULT 1
);

CREATE TABLE test.cat_data_source_definition (
  name              VARCHAR(200)  NOT NULL,
  provider          VARCHAR(50)   NOT NULL,
  connection_string VARCHAR(2000) NOT NULL,
  technology        VARCHAR(50)   NULL
);

CREATE TABLE test.cat_query_definition (
  name        VARCHAR(200)   NOT NULL,
  data_source VARCHAR(200)   NOT NULL,
  query       VARCHAR(64000) NOT NULL,
  description VARCHAR(64000) NULL
);

ClickHouse. Provider ClickHouse@1. ClickHouse needs an engine and an ordering key; Nullable marks the optional columns.

CREATE TABLE test.cat_test_definition (
  test_suite                   Nullable(String),
  `order`                      Nullable(Int32),
  test_case                    Nullable(String),
  test_name                    String,
  description                  Nullable(String),
  expectation                  String,
  first_data_source            Nullable(String),
  first_query                  Nullable(String),
  second_data_source           Nullable(String),
  second_query                 Nullable(String),
  timeout                      Nullable(Int32),
  maximum_errors_logged        Nullable(Int32),
  maximum_sample_column_length Nullable(Int32),
  log_number_of_errors         Nullable(Bool),
  tags                         Nullable(String),
  metadata                     Nullable(String),
  `key`                        Nullable(String),
  sort_data                    Nullable(Bool),
  tolerance                    Nullable(Decimal64(4)),
  tolerance_mode               Nullable(String),
  expected_row_count           Nullable(Int64),
  is_active                    Bool DEFAULT true
) ENGINE = MergeTree ORDER BY test_name;

CREATE TABLE test.cat_data_source_definition (
  name              String,
  provider          String,
  connection_string String,
  technology        Nullable(String)
) ENGINE = MergeTree ORDER BY name;

CREATE TABLE test.cat_query_definition (
  name        String,
  data_source String,
  query       String,
  description Nullable(String)
) ENGINE = MergeTree ORDER BY name;

Snowflake. Provider Snowflake@1. Snowflake returns unquoted names in upper case; CAT matches them regardless of case.

CREATE TABLE test.cat_test_definition (
  test_suite                   VARCHAR(300)   NULL,
  "order"                      INT            NULL,
  test_case                    VARCHAR(300)   NULL,
  test_name                    VARCHAR(300)   NOT NULL,
  description                  VARCHAR        NULL,
  expectation                  VARCHAR(50)    NOT NULL,
  first_data_source            VARCHAR(200)   NULL,
  first_query                  VARCHAR        NULL,
  second_data_source           VARCHAR(200)   NULL,
  second_query                 VARCHAR        NULL,
  timeout                      INT            NULL,
  maximum_errors_logged        INT            NULL,
  maximum_sample_column_length INT            NULL,
  log_number_of_errors         BOOLEAN        NULL,
  tags                         VARCHAR(500)   NULL,
  metadata                     VARCHAR(200)   NULL,
  "key"                        VARCHAR(500)   NULL,
  sort_data                    BOOLEAN        NULL,
  tolerance                    DECIMAL(19, 4) NULL,
  tolerance_mode               VARCHAR(10)    NULL,
  expected_row_count           BIGINT         NULL,
  is_active                    BOOLEAN        NOT NULL DEFAULT TRUE
);

CREATE TABLE test.cat_data_source_definition (
  name              VARCHAR(200)  NOT NULL,
  provider          VARCHAR(50)   NOT NULL,
  connection_string VARCHAR(2000) NOT NULL,
  technology        VARCHAR(50)   NULL
);

CREATE TABLE test.cat_query_definition (
  name        VARCHAR(200) NOT NULL,
  data_source VARCHAR(200) NOT NULL,
  query       VARCHAR      NOT NULL,
  description VARCHAR      NULL
);

Any other database

Provider Odbc@1. Use the script of the platform behind the ODBC driver; for a platform not listed here, the PostgreSQL script is plain enough to adapt — keep the column names, swap the types.

For a platform CAT reads but this page has no script for, convert one of the scripts above — the column list is what matters, the types are the nearest your platform has. A prompt that does it in any AI assistant:

Convert this CREATE TABLE script from T-SQL to <your platform>. Keep every column
and column name, keep nullability, map each type to the closest native type
(unbounded text for NVARCHAR(MAX), a boolean or 0/1 type for BIT), and output
only the DDL.

<paste the SQL Server script here>

Then check the result the way you would any generated DDL — run it, insert one test row, point the project file at it and open the project.

← Organize a growing test base
Generate tests from metadata →