--- title: "Create a Template Test" slug: "create-a-template-test" description: "Create a template test: write one test with variables like {{table.name}}, choose the tables it covers, then materialize one child test per table." status: "new" updated: 2026-11-20T21:50:17Z published: 2026-11-20T21:50:17Z canonical: "docs.validatar.com/create-a-template-test" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.validatar.com/llms.txt > Use this file to discover all available pages before exploring further. # Create a Template Test 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](/docs/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](/docs/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](/docs/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: ```sql -- 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: ```sql -- 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](/docs/materializing-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](/docs/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)](/docs/generating-child-tests-for-specific-metadata-objects). **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](/docs/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](/docs/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](/docs/materializing-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](/docs/job-details) and [Schedule a Job](/docs/scheduling-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 - [Template Tests](/docs/template-tests) - [Select Tables and Columns for a Template Test (Metadata Driven)](/docs/generating-child-tests-for-specific-metadata-objects) - [Materialize Child Tests](/docs/materializing-child-tests) - [Compare Source and Target Data](/docs/comparing-to-another-data-set) - Engineers who build template tests from a coding tool can use the [MCP server](/docs/connecting-to-the-validatar-mcp-server) and the [Validatar Agent Kits](/docs/validatar-agent-kits). ## Related - [Template Tests](/template-tests.md) - [Select Tables and Columns for a Template Test (Metadata Driven)](/generating-child-tests-for-specific-metadata-objects.md) - [Materialize Child Tests](/materializing-child-tests.md) - [Create a Standard Test](/create-a-standard-test.md) - [Create a Job](/job-details.md)