Get Help

Templates

A test whose Metadata property names a query is a template: CAT runs the query and makes one test out of every row it returns.

A definition whose Metadata property has a value is not a test. It is a template, and it is never executed as written. Metadata holds the name of a query definition in the same project; the match ignores case.

When the project is opened, CAT runs that query against the data source the query names, and produces one test per returned row. In every property of the template — the name, the description, the queries, all of them — the text %COLUMN% is replaced with that row’s value in the column called COLUMN. A NULL becomes an empty string.

Placeholders are written like environment variables, and both are substituted: environment variables first, then the row’s columns. Column names are matched exactly as the query returned them, including case, so %TABLE_NAME% and %Table_Name% are not the same placeholder.

A template is written by taking a working test for one case and replacing the specific values with placeholders:

Queries:
- Name: staging tables
  Data source: FlightsSystem
  Query: |
    SELECT  TABLE_SCHEMA, TABLE_NAME
    FROM    INFORMATION_SCHEMA.TABLES
    WHERE   TABLE_SCHEMA LIKE 'STAGE%'

Tests:
- Name: Staging table %TABLE_SCHEMA%.%TABLE_NAME% has a timestamp column
  Metadata: staging tables
  Description: |
    Table %TABLE_NAME% in schema %TABLE_SCHEMA% must contain the column
    SYS_INSERTED_TIMESTAMP, which is obligatory for all staging tables.
  Data source: FlightsSystem
  Query: |
    SELECT  *
    FROM    INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_SCHEMA = '%TABLE_SCHEMA%'
            AND TABLE_NAME = '%TABLE_NAME%'
            AND COLUMN_NAME = 'SYS_INSERTED_TIMESTAMP'
  Expectation: set is not empty

If staging tables returns twelve rows, the project holds twelve tests, each with its own name, description and query. The template itself is not among them.

Behavior

A template still needs Name and Expectation. They are read before CAT decides whether a definition is a test or a template, and a definition missing either is rejected either way.

A metadata query that returns no rows produces no tests. That is not an error; the template simply contributes nothing to the project.

Expansion fails the project open when Metadata names a query the project does not define, when that query names a data source the project does not define, or when the query itself fails. Each error names the template, so the definition to fix is identifiable; the unknown-query error also lists every query name the project does define.

Generated tests count towards the plan’s limit on tests per project. A template that expands past the limit is refused with a message that names it and says how many rows the query may return.

CAT Studio can expand a single template on demand, which replaces the tests that template produced earlier. See Queries in CAT Studio.

← Properties
Introduction →