--- title: "Metadata Script Example: Column Check Suite" slug: "metadata-script-example-column-check-suite" updated: 2026-10-20T15:22:01Z published: 2026-10-20T15:22:01Z canonical: "docs.validatar.com/metadata-script-example-column-check-suite" --- > ## 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. # 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](/docs/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](/docs/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](/docs/import-file-example-column-check-suite): 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](/docs/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. ## Related - [Template Test Examples: Metadata Script](/docs/template-test-examples-metadata-script) - [Generating Child Tests using a Metadata Script](/docs/generating-child-tests-using-a-metadata-script) - [Import File Example: Column Check Suite](/docs/import-file-example-column-check-suite) - [Metadata Script Example: Business Rule Catalog](/docs/metadata-script-example-business-rule-catalog)