What it checks
Several checks on each column, from one template. An amount column should never be null and never be negative. A date column should never be null and should not fall before 1900. Instead of one template per check, this template takes the check from the file: each row names a column and one check, and a column listed twice gets two child tests.
This is the column-level counterpart of the Business Rule Catalog. At column level the catalogued object is a column, so without test.key a column could have only one child test. With test.key set to TABLE.COLUMN: check, it can have as many as the file lists.
Part of Template Test Examples: Import File. The same template, with the columns read from INFORMATION_SCHEMA by a script, is Metadata Script Example: Column Check Suite.
The template
| Setting | Value |
|---|---|
| Name | Import File Example - Column Check Suite |
| Level | Column |
| Generation source | Import File |
| Severity | Medium |
| Quality dimension | Validity |
| Child test name | Check: {{table.name}}.{{column.name}} {{var.check}} |
| Child folder | Relative, {{var.template_test_name}} |
| Test data set | Script on the template's data source, single value, numeric (below) |
| Control data set | Numeric value: {{var.max_failing_rows}} |
| Value success criteria | Custom calculation: testNumericValue <= controlNumericValue |
| Overall success | Value, tolerance 0 |
| Missing result actions | Fail on both sides |
The test data set counts the rows that fail the check:
SELECT COUNT(*) AS FAILING_ROWS
FROM {{schema.name}}.{{table.name}}
WHERE {{column.name}} {{var.failing_condition}}
The column name comes from the catalog. The file supplies only the rest of the condition (IS NULL, < 0), so the same check text works for every column it is applied to.
Variables declared on the template:
| Reference key | Type | Purpose |
|---|---|---|
check |
Other | Name of the check, used in the child test name |
failing_condition |
Other | The part of the WHERE clause after the column name |
max_failing_rows |
Numeric | Failing rows allowed before the child test fails. Default 0 |
The metadata link {{schema.name}}.{{table.name}}.{{column.name}} gives each child test coverage on its column, so every check shows on that column in the catalog.
The import file
import-file-example-column-check-suite.xlsx, one sheet, header row plus 24 data rows: 12 columns with 2 checks each.
| Header | Meaning |
|---|---|
schema.name, table.name, column.name |
The catalogued column. Column level, so all three are required |
test.key |
TABLE.COLUMN: check. Unique per row, so one column can appear once per check |
var.check |
The check's name |
var.failing_condition |
The condition after the column name |
var.max_failing_rows |
0 on every row |
The checks:
| Kind of column | Check | Failing condition |
|---|---|---|
| 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' |
The columns:
| Table | Amount columns | Date columns |
|---|---|---|
DIM_CLAIM |
CLAIM_AMOUNT |
CLAIM_DATE |
DIM_POLICY |
POLICY_AMOUNT, PREMIUM_AMOUNT |
POLICY_START_DATE, POLICY_EFFECTIVE_DATE, POLICY_END_DATE, POLICY_RENEWAL_DATE |
FACT_PAYMENT |
PAYMENT_AMOUNT |
PAYMENT_DATE |
FACT_TRANSACTION |
TRANSACTION_AMOUNT |
TRANSACTION_DATE |
The two rows for DIM_CLAIM.CLAIM_AMOUNT:
table.name |
column.name |
test.key |
var.check |
var.failing_condition |
|---|---|---|---|---|
| DIM_CLAIM | CLAIM_AMOUNT | DIM_CLAIM.CLAIM_AMOUNT: is never null | is never null | IS NULL |
| DIM_CLAIM | CLAIM_AMOUNT | DIM_CLAIM.CLAIM_AMOUNT: is never negative | is never negative | < 0 |
What materializes
Twenty-four child tests, two per column, in a folder named after the template. Check: DIM_CLAIM.CLAIM_AMOUNT is never negative gets this test script:
SELECT COUNT(*) AS FAILING_ROWS
FROM STAR_SCHEMA.DIM_CLAIM
WHERE CLAIM_AMOUNT < 0
and a control value of 0.
Results on SAMPLE_DW
Verified 2026-10-19: 24 children, all pass. Amounts in the sample warehouse are complete and never negative, and no date is missing or earlier than 1900.
A suite that passes still earns its place. It runs every night, and the day a load writes a negative payment or a null claim date, the failing child names the exact column and check.
Adapting it
Add a check. Add rows with the new check for each column it applies to. A range check for an amount column, for example, uses NOT BETWEEN 0 AND 1000000 as its failing condition.
Allow a few failures. Raise var.max_failing_rows on one row. A column that is allowed to be null for some records but not most can carry an allowance on its null check.
Turn the count into a rate. Change the test SQL to SELECT 100.0 * COUNT_IF({{column.name}} {{var.failing_condition}}) / NULLIF(COUNT(*), 0) and treat var.max_failing_rows as a percent.
Too many rows to type. Twelve columns times two checks is already 24 rows, and a real warehouse has hundreds of columns. The metadata script version reads the columns from INFORMATION_SCHEMA and joins them to the checks for their kind, so new columns are covered at the next materialization.
Pitfall: conditions that need the column twice. The template writes the column name once, before the condition. A check such as "is trimmed" (COLUMN <> TRIM(COLUMN)) needs a different template, or a variable that holds the whole predicate.
Pitfall: keys that differ only in letter case. Keys can hold any text, spaces and punctuation included, but they are stored in lower case. Two rows whose keys differ only in case collide, and the upload fails with Duplicate test key.