Generate tests from metadata

Turn one working test into one test per table, customer or contract.

Some rules apply to a whole family of things rather than to one of them: every staging table must have a technical timestamp column, every active contract must have a valid price, every fact table must have an index. Writing one test per member of the family by hand does not scale, and the list changes under you anyway.

CAT can write them for you. You define the family as a query, write one test for one member, and mark that test as a template. CAT then produces one test per row the query returns — every time the project is opened or reloaded, so the list of tests is always as fresh as the metadata: a test written today for “every staging table” covers the staging table created next month without anyone touching the project.

For what the properties involved accept, see Templates and Query properties.

When it pays off #

Technical checks:

• every table has a primary key,
• every staging table contains some technical column,
• every fact table has a clustered columnstore index.

Business rules:

• for every contract,
• for every customer,
• for every combination of contract and metric.

Step 1 — define the family as a query #

Ask yourself what the family is and where the list of its members lives. For the examples above that may be:

• the list of tables, SELECT * FROM INFORMATION_SCHEMA.TABLES (potentially WHERE TABLE_SCHEMA = ...),
• the list of columns in some tables (again, INFORMATION_SCHEMA.COLUMNS will do),
• the data in your Customers table.

The list has to live in a data source your project defines. If it does not, add one. The metadata can come from any supported provider — a relational database, a CSV or MS Excel file, a tabular model.

Data sources:
- Name: AeroDWH
  Provider: SqlServer@1
  Connection string: '%AERO_DWH_CONNECTION_STRING%' # environment variable used here

Queries: # here you define the metadata
- Name: staging tables
  Data source: AeroDWH
  Query: |
    SELECT  *
    FROM    INFORMATION_SCHEMA.TABLES AS t
    WHERE   t.TABLE_SCHEMA LIKE 'STAGE%'

Remember — this is just an example. Your metadata may be anywhere and in any format.

Step 2 — write the test for one member #

The easiest and most intuitive way to write generated tests is to write a test for one of the entries first, run it, and only then expand it.

In the example above we have a list of all staging tables. Let us write a test for one of them, STAGE.AIRLINES, checking that the table contains the column SYS_INSERTED_TIMESTAMP:

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

Run it. Once it does what you meant, it is ready to be expanded.

Step 3 — expand it #

You want that test for all of these tables — what the metadata query returns:

Two changes turn the test into a template:

1. Point it at the metadata query with the Metadata property.
2. Replace the hard-coded values with %ColumnName%, using the column names the metadata query returns.

Tests:
- Name: Staging table %TABLE_SCHEMA%.%TABLE_NAME% has a timestamp column
  Metadata: staging tables # don't forget this! It's the link to the metadata query
  Description: |
      Table %TABLE_NAME% in schema %TABLE_SCHEMA%
      must contain column SYS_INSERTED_TIMESTAMP,
      because it is obligatory for all staging tables.
  Data source: AeroDWH
  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

That is it. You now get one test per row returned by the metadata query, each with its own name, description and query. Might look complicated at first sight, but it is trivial in practice — give it a try.

Tips #

• Put the placeholders in the test name too, not only in the query. Otherwise every generated test carries the same name, and a failure tells you nothing about which member of the family broke.
• The column names in %…% are matched exactly as the metadata query returns them, casing included.
• When the metadata query returns no rows, no tests are generated and nothing fails. An unexpectedly short test list is usually a metadata query that filtered everything out.
• The expansion happens on every open and reload, in every tool — a new member of the family gets its test the next time the project is opened, a removed member loses it. Generated tests count towards the plan’s limit on tests per project.
• In CAT Studio you write the metadata query on the Queries page and pick it from a drop-down on the test.