Create a Template Test

Prev Next

To create a template test, open a project folder, choose New > New Template Test, write one test definition with variables such as {{schema.name}} and {{table.name}} where the object names go, choose the tables or columns it covers, save it, and materialize it. Materializing creates one child test per table or column.

If you are new to template tests, read Template Tests first. It explains child tests and when a template beats a set of standard tests.

Before you start

  • The same permissions as a standard test: Create Tests on the project, and Create & Edit Tests on the data sources the template reads. See Create a Standard Test.
  • For a Metadata Driven template, the source's tables and columns must be in the catalog. If they are not, ingest them first: see Ingesting Schema Metadata.
  • A working query for one table. Writing the check as a standard test on one table first, then clicking Convert to Template Test on it, is often the quickest start.

The example on this page

The Wholesale Distribution System (SQL Server) has 49 tables, and each one is copied into the Enterprise Data Warehouse (Snowflake) under the name SCHEMA_TABLE in the schema LOAD_SQLSERVER_WORLDWIDEIMPORTERS. The team wants to know, for every table, that the copy has the same number of rows as the source.

The template on this page is Wholesale Row Counts Match the Warehouse, in the Warehouse Reconciliation project. It creates 49 child tests, one per table.

Start the template

  1. In the top navigation, click Projects, open Warehouse Reconciliation, click Tests and open the Wholesale System to Warehouse folder.
  2. Click the arrow on the New button and choose New Template Test.
  3. In Name, type Wholesale Row Counts Match the Warehouse.
  4. In Description, say what a failure means: "A failure means the warehouse copy of a wholesale table has more or fewer rows than the system it was loaded from."
  5. In Quality Dimension, choose Completeness. In Severity Level, choose Critical. Every child test gets these values.
  6. In Target Object Type, choose Table. Choose Column when you want one child test per column instead.
  7. In Object Selection and Definition, choose Metadata Driven.

Object Selection and Definition decides where the list of tables comes from. The template gets a tab for the one you choose:

Choice Tab Use it when
Metadata Driven Metadata Selection Everything you need to pick the tables is in the catalog: names, types, tags, custom field values
Metadata Script Metadata Script A query returns the list, for example a mapping table that pairs each source table with its target
Import File File Selection A person keeps the list in a spreadsheet, often with settings per row such as a tolerance

Write the test definition

The Test Definition tab is built like a standard test, with variables where the object names go. Validatar fills them in for each table when it materializes.

  1. In What do you want to test?, set Where to get the data to Wholesale Distribution System. For a Metadata Driven template, this is also the data source whose catalog the tables are picked from.
  2. In How to get the data, choose Script, and enter the source query below in the script box.
  3. In Metadata Links, enter {{schema.name}}.{{table.name}} and leave its star filled (Impacts Trust Score). Each child test is then linked to its own table in the catalog and counts toward that table's trust score.
  4. In What do you want to compare to?, choose Another Data Set.
  5. Set Where to get the control data to Enterprise Data Warehouse, choose Script in How to define the control data, and enter the warehouse query below.
  6. Click Preview & Initialize Options on each side. Validatar fills in the variables for an object from your selection, runs the query and shows the result. Set Process data as to Single Value on both sides.
  7. In Result Configuration, set Value Success Criteria to Exact Match Only.

The source query, on SQL Server:

-- Rows in this table of the wholesale system. Pass: the same count as its warehouse copy.
SELECT COUNT_BIG(*) AS "VALUE"
FROM "{{schema.name}}"."{{table.name}}"

The warehouse query, on Snowflake:

-- Rows in the warehouse copy of this table, which is named SCHEMA_TABLE.
-- Unquoted, Snowflake reads the name as upper case, the way the copy is stored.
-- Pass: the same count as the wholesale system.
SELECT COUNT(*) AS VALUE
FROM LOAD_SQLSERVER_WORLDWIDEIMPORTERS.{{schema.name}}_{{table.name}}

For Sales.Orders, the child test runs SELECT COUNT_BIG(*) AS "VALUE" FROM "Sales"."Orders" against the source and SELECT COUNT(*) AS VALUE FROM LOAD_SQLSERVER_WORLDWIDEIMPORTERS.Sales_Orders against the warehouse.

Variables

Variable Filled in with
{{schema.name}} The object's schema
{{table.name}} The object's table
{{column.name}} The object's column (Column templates)
{{var.template_test_name}} The template's name
{{test.key}} The child's key, such as sales.orders (see Materialize Child Tests)
{{var.your_key}} A test variable you add

To add your own, click Add Variable under Test Variables and give it a Reference Key, a Variable Type (Schema, Table, Column, Numeric, Boolean or Other) and an optional Default Value. Use it as {{var.your_key}}, for example a tolerance you want to change in one place. A Metadata Script or an Import File can supply a different value for each object. The Open template builder button next to a field shows the field with the variables filled in. For functions such as lower and replace, see Template Test Functions.

Choose the tables

On the tab for your Object Selection and Definition choice, decide which objects get a child test.

Metadata Driven. On the Metadata Selection tab, Use Filters picks every catalog object that matches your rules, so tables added to the catalog later are picked up at the next materialize. Select Objects is a list you pick by name. The example uses one filter, Table Type Equals Table, which selects all 49 base tables and leaves out the three views in the Website schema that are not copied. Click Preview Objects to see the list. See Select Tables and Columns for a Template Test (Metadata Driven).

Metadata Script. On the Metadata Script tab, write a query that returns one row per child test, with columns such as schema.name and table.name, plus var. columns for per-object values. See Generating Child Tests using a Metadata Script.

Import File. On the File Selection tab, download a file with the right headers, fill in one row per child test and upload it. See Generating Child Tests from an Import File.

Name the child tests and choose their folder

On the Child Test Configuration tab:

Setting What it does Example
Use Default Child Test Configuration Names each child {{var.template_test_name}} - {{test.key}} and puts the children in a subfolder named after the template, inside the template's folder
Use Custom Child Test Configuration Lets you set the fields below
Child Test Name The name of each child, with variables {{var.template_test_name}} - {{schema.name}}.{{table.name}}
Child Test Description The description of each child The template's description
Relative Path A folder path under the template's folder. Variables create one folder per value, for example {{schema.name}} {{var.template_test_name}}
Absolute Path A folder path from the project's Tests folder instead Reconciliation/{{schema.name}}
Custom Fields Copy Template Test Values to Child Tests, or Customize Child Test Values with Edit Custom Fields

The example uses a custom name so each child reads Wholesale Row Counts Match the Warehouse - Sales.Orders, and the relative path {{var.template_test_name}}.

Save and materialize

  1. Click Save. The Test Summary panel shows how many tables the selection matches.
  2. Click Materialize Child Tests. The button is available once the template is saved with no unsaved changes.
  3. Check the Statistics in the dialog. The first time, it shows Tests to be created: 49 for the example.
  4. Click Materialize.

When it finishes, the Child Tests tab lists the 49 child tests with their Key, Folder Path and Status. For what the materialize options do on later runs, see Materialize Child Tests.

Run one child first

Before you run all 49, run one and read its result. A mistake in the template is the same mistake 49 times.

  1. On the Child Tests tab, open Wholesale Row Counts Match the Warehouse - Sales.Orders.
  2. Click Run Test.
  3. Read the result. The test value is the source's row count and the control value is the warehouse's.

On the example data this child fails: the warehouse copy of the orders table stopped loading on 23 April 2024, so it has fewer orders than the system. Once one child gives the result you expect, click Run Child Tests on the template to run them all.

Add the template to a job

To run the template every night, add it as a step in a job and tick Materialize child tests before execution and Execute child tests in the step's settings. Each run then materializes first, so a table added to the catalog gets its child test and is tested the same night. See Create a Job and Schedule a Job.

Check that it worked

  • The Test Summary panel shows Last materialized with a date and who did it.
  • The Child Tests tab lists one child per selected table, and the child folder shows them in the folder tree.
  • Each child appears on its table's Tests tab in the Catalog, because of the metadata link.

Do it with Validatar AI (Validatar Cloud, beta). Open the AI panel and ask: "Create a template test in the Warehouse Reconciliation project, folder Wholesale System to Warehouse, that compares the row count of every table in the Wholesale Distribution System with its copy in the Enterprise Data Warehouse. The copy of each table is LOAD_SQLSERVER_WORLDWIDEIMPORTERS._

." Review what it creates before you materialize it, and check the Metadata Selection tab picks the tables you expect. The AI cannot materialize a template, so click Materialize Child Tests yourself. You can undo the change from the chat history.

Troubleshooting

Symptom Cause Fix
The New button is greyed out You do not have the Create Tests permission on this project Ask a project admin
The Metadata Selection tab says metadata cannot be selected No data source is chosen in Where to get the data Choose the data source on the Test Definition tab
Tests to be created is 0 The selection matches nothing, or the catalog has not been ingested Click Preview Objects and adjust the filters; ingest the data source
The template has no child tests after saving Saving does not create children Click Materialize Child Tests
Children error because a table is not found A query builds the table name differently from how the table is really named Run one child, read the error, and fix the naming in the template

Related