Import File Example: Business Rule Catalog

What it checks

A list of business rules, each one written as a condition that marks a row as breaking the rule. Policies must end after they start. Premiums must be positive. A claim cannot be dated before its policy begins. Most data teams keep a list like this somewhere, in a spreadsheet, a wiki page, or a governance tool. This template turns every rule on the list into its own child test.

A table usually has several rules, so a table gets several child tests. The rule id, not the table, is what tells the child tests apart: it goes in test.key. Add a rule to the file and the next materialization adds a child test. Retire a rule and its child test becomes a candidate for deletion.

Part of Template Test Examples: Import File. The same template with the rules returned by a script is Metadata Script Example: Business Rule Catalog.

The template

The test data set counts the rows that break the rule:

SELECT COUNT(*) AS VIOLATIONS
FROM {{schema.name}}.{{table.name}} t
WHERE {{var.violation_condition}}

The table is aliased t, so a condition can refer to its columns as t.COLUMN and can join out to other tables with EXISTS.

Variables declared on the template:

violation_condition is pasted into the WHERE clause as written. Handlebars runs with no escaping, so quotes, parentheses, and subqueries survive intact.

The import file

import-file-example-business-rule-catalog.xlsx, one sheet, header row plus 12 data rows.

The rules:

DIM_POLICY appears five times, DIM_CLAIM four times, and FACT_PAYMENT three times. Each appearance has its own rule id in test.key, so each is its own child test.

What materializes

Twelve child tests, one per rule, in a folder named after the template. Rule BR-CLM-04 becomes the child test Rule BR-CLM-04: DIM_CLAIM - Claim is not dated before its policy starts, with this test script:

SELECT COUNT(*) AS VIOLATIONS
FROM STAR_SCHEMA.DIM_CLAIM t
WHERE EXISTS (SELECT 1 FROM STAR_SCHEMA.DIM_POLICY p WHERE p.POLICY_ID = t.POLICY_ID AND t.CLAIM_DATE < p.POLICY_START_DATE)

and a control value of 0.

Results on SAMPLE_DW

Verified 2026-10-19: 12 children, 10 pass, 2 fail.

Both failures are the same rule on two tables: rows whose MODIFIED_DATE is earlier than their CREATED_DATE. Audit timestamps that run backwards usually mean a load process set one of them from a different clock or a different source. Because each table has its own child test, the two failures can be triaged and resolved separately.

Adapting it

Bring your own rule catalog. Export your rules to the five columns above and upload. A rule that spans tables belongs to the table it is measured on, with the other tables in an EXISTS or IN subquery.

Tolerate a known problem while it is fixed. Raise var.max_violations on that rule's row, upload, and materialize. Only that child test changes.

Keep the rule id stable. The rule id is the child test's identity. Renaming BR-POL-05 to BR-POL-5 deletes the old child test, with its result history, and creates a new one.

Report on rules, not tables. Every child test name starts with its rule id, so filtering test results by Rule BR-PAY lists one family of rules across every table.

Pitfall: the rule id in two places. test.key and var.rule_id hold the same value. test.key is stored in lower case, which is why the child test name uses the variable.

Pitfall: conditions that return NULL. t.PREMIUM_AMOUNT <= 0 is not true for a null premium, so a null would slip through. Add IS NULL OR when a null also breaks the rule, as the shipped rules do.

Related

• Template Test Examples: Import File
• Generating Child Tests from an Import File
• Import File Example: Column Check Suite
• Metadata Script Example: Business Rule Catalog