Generating Child Tests using a Metadata Script

What the metadata script does

A template test generates its child tests from a list of rows. With Metadata Script as the generation source, you write a query that returns that list. Validatar runs the query when you preview or materialize, and every row it returns becomes one child test.

The rows have the same shape as the rows of an import file: each one names a catalogued object, can carry a test.key that identifies the child test, and can carry var. values that feed the template's variables. The difference is where the list comes from. An import file holds a list someone typed. A metadata script works the list out at the moment you materialize, from whatever the query can read:

• The warehouse's own catalog views. Every table in a schema, every amount column, every column whose name follows a foreign key convention.
• The data. One child test per customer, region, or source system found in a table, so a new value gets its child tests at the next materialization.
• A table someone maintains. A business rule catalog, a source-to-target mapping table, a list of thresholds owned by data stewards.

Use a metadata script when the list can be derived or already lives in a database, and when it changes often enough that keeping a spreadsheet in step would be a chore. Use an import file when a person owns the list and edits it by hand. Use Specific Metadata Objects when catalog filters alone describe the objects and every object gets exactly one child test.

The catalog still matters. Every row must name a schema and table (and a column for column-level templates) that is already in the catalog of the template's data source. The script decides which catalogued objects get child tests, and how many each one gets. It cannot add objects to the catalog.

Worked examples, each with its script, are in Template Test Examples: Metadata Script.

Choose Metadata Script as the generation source

1. Open the template test. On the Test Definition tab, find Object Selection and Definition.
2. Select Metadata Script. A Metadata Script tab appears.
3. On the Metadata Script tab, choose the Data Source the script runs against, and write the script in the editor. The editor starts with a sample script you can copy and adapt.
4. Click Preview & Validate (described below), then save the test.

Which data source runs the script

The script has its own data source, chosen on the Metadata Script tab. It can be the template's data source or a different one, such as a governance database that holds a rules table. Validatar runs the script on that data source's schema metadata connection, the same connection metadata ingestion uses. The data source must be active and assigned to the project.

The script can be SQL or, on a Python (script) data source, Python. A Python script must leave its result in a variable named dataframe. Only the first result set is read.

Whatever data source runs the script, the objects the rows name are always looked up in the catalog of the template's data source.

What the script must return

Name the result columns exactly as below. On platforms that change the case of unquoted names (Snowflake upper-cases them), quote every alias. The dot in the names needs quotes on most platforms anyway.

A minimal table-level script, in Snowflake syntax:

SELECT table_schema AS "schema.name",
       table_name   AS "table.name"
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'STAR_SCHEMA'
  AND table_type = 'BASE TABLE'

Rules for the result

• Lower case, exactly. Preview checks schema.name, table.name, and column.name with case sensitivity. SCHEMA.NAME fails Preview with Missing column.
• Return only columns Validatar recognises. Preview rejects any other column as Invalid column. A var. column must match a variable declared on the template. Declare the variable first, then return the column.
• Return values as text when the format matters. Every value reaches the child test as text. Validatar converts numbers, dates, and booleans using the server's regional settings, so a date that ends up inside SQL, or a decimal that ends up in a name, is safer cast in the script (TO_VARCHAR(x), CAST(x AS VARCHAR)). The shipped examples return every column as text.
• A null becomes empty text, not the default. A var. column that returns NULL gives that child an empty value. The variable's declared default applies only when the script returns no column for the variable at all. Use COALESCE to supply a fallback.
• Keys must be unique. Two rows with the same test.key (or, without test.key, the same object) fail Preview with Duplicate test key. Keys are stored in lower case, so FACT_SALES and fact_sales collide.
• Keep keys and names short. A child test key can be up to 800 characters and a child test name up to 300.

One object, many child tests

test.key is what lets the list stop being one row per catalogued object. Without it, Validatar keys each child test by its object, and a second row for the same object is a duplicate. With it, the key is whatever the script returns, so:

• a table can get one child test per business rule, with the rule id as the key
• a column can get one child test per check, with TABLE.COLUMN: check as the key
• a table can get one child test per segment value, with TABLE: SEGMENT = value as the key

The key is the child test's identity across materializations. Choose a key that stays the same while the row's meaning stays the same. If it changes, the old child test is deleted and a new one is created, and the child loses its result history.

Note: If you change how the script builds its keys and the child test names stay the same, the first materialization deletes the old child tests and reports success without creating the new ones. Materialize a second time to create them, and check the child test count afterwards.

The script is not a template

Validatar sends the script to the data source exactly as written. It is not rendered with Handlebars, so {{var.x}}, {{schema.name}}, or {{project.name}} inside the script reach the database as literal text. The script produces the values that the template's own fields then use.

Preview & Validate

Preview & Validate on the Metadata Script tab runs the script and shows every row it returns, with row numbers. It validates the rows against the template as it is on screen, unsaved changes included, and against the catalog of the template's data source.

The button stays disabled until the script text contains each required column name (schema.name, table.name, and column.name for column level).

A script error (a syntax error, a missing table, a permission problem) is shown as returned by the data source.

Materialize

Click Materialize Child Tests. Validatar runs the script again, compares the rows with the existing child tests, and shows how many child tests will be created and deleted, and how many existing ones are customized or not. Any validation error blocks materialization, and the dialog shows the first one.

How rows turn into child tests:

• The child's identity is its key: test.key if returned, otherwise schema.table or schema.table.column. A row whose key matches an existing child updates that child in place. A child whose key no longer appears is a candidate for deletion.
• The child test name, description, folder path, and every SQL script on the template are rendered per row. {{schema.name}}, {{table.name}}, {{column.name}}, and the catalog attributes such as {{column.data_type}} come from the catalog. {{var.x}} comes from the row. {{test.key}} is the key.
• {{var.x}} is plain text substitution, so a variable can carry a whole SQL statement, a predicate, or a column list that the script assembled.
• Severity and quality dimension are copied from the template. A row cannot change them.

Because the script runs at every materialization, the child tests follow the data. Materialize again, by hand or from a job, and new tables, new columns, new rules, or new segment values get their child tests without anyone editing a list.

Materializing from a job or the API

Warning: A job or API call materializes without the dialog, so nothing stops it for a problem the dialog would catch. If the script returns no rows, every existing child test of the template is deleted when Delete tests is selected, and a job selects it by default. Guard the script so that it never returns an empty result by accident (for example, point it at a source that is loaded before the job runs), or clear Delete tests on the job step.

Rows that name an object missing from the catalog are skipped in a job or API run. The other rows still materialize, and the run reports the last Invalid meta object as its error message.

Metadata scripts in exported test packs

When you export a template test whose generation source is Metadata Script, the script travels with the template, and the script's data source is listed with the pack's other data sources. On import, map it to a data source in your instance, the same way you map the template's own data source. A pack whose script data source is left unmapped does not import.

Nothing else needs uploading. Open an imported template, click Preview & Validate to see the rows on your catalog, then materialize.

Related

• Template Test Examples: Metadata Script
• Generating Child Tests from an Import File
• Generating Child Tests for Specific Metadata Objects
• Understanding Child Test Materialization
• Template Test Functions
• Exercise 11: Create a Source to Target Template Test Using a Metadata Script