Metadata Script Example: Column Check Suite

What it checks

Several checks on each column, from one column-level template. Amount columns should never be null or negative. Date columns should never be null or fall before 1900. The checks and the SQL are the same as in Import File Example: Column Check Suite.

The file version types out 24 rows, one per column and check. This script writes two short lists instead (which columns, which checks per kind of column) and lets the join produce the 24 rows. The column list comes from INFORMATION_SCHEMA, so a new amount or date column in a covered table gets its checks at the next materialization.

Part of Template Test Examples: Metadata Script.

The template

Setting	Value
Name	Metadata Script Example - Column Check Suite
Level	Column
Generation source	Metadata Script
Script data source	The template's own data source (SAMPLE_DW)
Everything else	Identical to the import file version: failing-row count `WHERE {{column.name}} {{var.failing_condition}}`, control value `{{var.max_failing_rows}}`, the custom calculation `testNumericValue <= controlNumericValue`, and the same variables

The metadata script

metadata-script-example-column-check-suite.sql:

-- The columns come from INFORMATION_SCHEMA: amounts (FLOAT, named *_AMOUNT) and business dates
-- (TIMESTAMP, named *_DATE, audit columns left out). Each column is joined to every check for its
-- kind, so one column produces one child test per check.
WITH columns_in_scope AS (
    SELECT table_schema, table_name, column_name,
           IFF(data_type = 'FLOAT', 'amount', 'date') AS kind
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = 'STAR_SCHEMA'
      AND table_name IN ('DIM_CLAIM', 'DIM_POLICY', 'FACT_PAYMENT', 'FACT_TRANSACTION')
      AND column_name NOT IN ('CREATED_DATE', 'MODIFIED_DATE')
      AND (   (data_type = 'FLOAT' AND ENDSWITH(column_name, '_AMOUNT'))
           OR (STARTSWITH(data_type, 'TIMESTAMP') AND ENDSWITH(column_name, '_DATE')))
),
checks AS (
    SELECT column1 AS kind, column2 AS check_name, column3 AS failing_condition
    FROM VALUES
        ('amount', 'is never null', 'IS NULL'),
        ('amount', 'is never negative', '< 0'),
        ('date', 'is never null', 'IS NULL'),
        ('date', 'is not before 1900', $$< '1900-01-01'$$)
)
SELECT c.table_schema AS "schema.name",
       c.table_name   AS "table.name",
       c.column_name  AS "column.name",
       c.table_name || '.' || c.column_name || ': ' || k.check_name AS "test.key",
       k.check_name        AS "var.check",
       k.failing_condition AS "var.failing_condition",
       '0'                 AS "var.max_failing_rows"
FROM columns_in_scope c
JOIN checks k ON k.kind = c.kind
ORDER BY "test.key"

How it works:

columns_in_scope reads INFORMATION_SCHEMA.COLUMNS for four tables and keeps two kinds of column: FLOAT columns named *_AMOUNT, and TIMESTAMP columns named *_DATE. The audit columns CREATED_DATE and MODIFIED_DATE are left out. Each column is labelled amount or date.
checks lists the checks for each kind, with the condition that marks a failing value.
The join on kind gives every column every check for its kind. Twelve columns times two checks is 24 rows.
test.key is TABLE.COLUMN: check, so the two checks on one column have different keys and become two child tests on that column.
This is a column-level template, so the script returns column.name as well as schema.name and table.name.

What it returns

Twenty-four rows, the same columns, checks, and keys as the import file. The two rows for DIM_CLAIM.CLAIM_AMOUNT:

`table.name`	`column.name`	`test.key`	`var.failing_condition`
DIM_CLAIM	CLAIM_AMOUNT	DIM_CLAIM.CLAIM_AMOUNT: is never negative	< 0
DIM_CLAIM	CLAIM_AMOUNT	DIM_CLAIM.CLAIM_AMOUNT: is never null	IS NULL

Results on SAMPLE_DW

Verified 2026-10-19: 24 children, all pass, the same as the import file version.

Compared with the import file

	Import file	Metadata script
The columns	Typed per row	Read from `INFORMATION_SCHEMA` by data type and name
The checks	Repeated for every column	Listed once per kind of column
A new date column in a covered table	Unchecked until someone adds two rows	Checked from the next materialization
Child tests	24	The same 24, with the same keys

Adapting it

Cover the whole warehouse. Remove the table_name IN (...) filter, or replace it with a schema filter. Every amount and date column then gets its checks. Expect the child test count to grow with the warehouse, and read the warning about empty results in Generating Child Tests using a Metadata Script before materializing from a job.

Add a kind of column. Label text columns code when their name ends in _CODE, and add checks such as code, is never blank, = ''.

Use the catalog's own data types. On a platform with a different type system, adjust the data_type tests: DECIMAL or NUMERIC for amounts on SQL Server and PostgreSQL, DATETIME2 or TIMESTAMP for dates.

Pitfall: the type filter decides coverage silently. A column stored with an unexpected type (an amount kept as text, for example) simply does not appear. Compare the Preview & Validate rows with what you expected to cover.

Pitfall: changing a check's name re-keys it. The key includes the check name. Renaming is never null to has no nulls deletes and recreates the child test on every column, with its result history.