Metadata Script Example: Business Rule Catalog

What it checks

A catalog of business rules, one child test per rule. Each rule is a condition that marks a row as breaking it, applied to one table, with an allowance. The rules and the SQL are the same as in Import File Example: Business Rule Catalog.

In practice a rule catalog rarely lives in a spreadsheet for long. It moves into a table that data stewards maintain through a form, a governance tool, or plain INSERT statements. A metadata script is how a template reads that table: when a steward adds a rule, the next materialization adds its child test, with no upload.

To keep the example runnable anywhere SAMPLE_DW is, the script carries the twelve rules inline. Swapping the inline list for your own rules table is a change to one CTE, shown below.

Part of Template Test Examples: Metadata Script.

The template

The metadata script

metadata-script-example-business-rule-catalog.sql:

-- One row per business rule. The list is inline so the example runs anywhere SAMPLE_DW does;
-- in production it usually lives in a table the data stewards maintain, for example:
--   SELECT ... FROM GOVERNANCE.DQ_RULES WHERE IS_ACTIVE
WITH rules AS (
    SELECT column1 AS rule_id, column2 AS table_name, column3 AS rule,
           column4 AS violation_condition, column5 AS max_violations
    FROM VALUES
        ('BR-POL-01', 'DIM_POLICY', 'Policy ends after it starts', 't.POLICY_END_DATE < t.POLICY_START_DATE', 0),
        ('BR-POL-02', 'DIM_POLICY', 'Premium is positive', 't.PREMIUM_AMOUNT IS NULL OR t.PREMIUM_AMOUNT <= 0', 0),
        ('BR-POL-03', 'DIM_POLICY', 'Status is Active, Inactive or Overdue', $$t.POLICY_STATUS IS NULL OR t.POLICY_STATUS NOT IN ('Active', 'Inactive', 'Overdue')$$, 0),
        ('BR-POL-04', 'DIM_POLICY', 'Premium does not exceed the policy amount', 't.PREMIUM_AMOUNT > t.POLICY_AMOUNT', 0),
        ('BR-POL-05', 'DIM_POLICY', 'Record is not modified before it is created', 't.MODIFIED_DATE < t.CREATED_DATE', 0),
        ('BR-CLM-01', 'DIM_CLAIM', 'Claim amount is positive', 't.CLAIM_AMOUNT IS NULL OR t.CLAIM_AMOUNT <= 0', 0),
        ('BR-CLM-02', 'DIM_CLAIM', 'Claim type is a known type', $$t.CLAIM_TYPE IS NULL OR t.CLAIM_TYPE NOT IN ('Collision', 'Comprehensive', 'Liability', 'Personal Injury')$$, 0),
        ('BR-CLM-03', 'DIM_CLAIM', 'Claim date is not in the future', 't.CLAIM_DATE > CURRENT_TIMESTAMP()', 0),
        ('BR-CLM-04', 'DIM_CLAIM', 'Claim is not dated before its policy starts', 'EXISTS (SELECT 1 FROM STAR_SCHEMA.DIM_POLICY p WHERE p.POLICY_ID = t.POLICY_ID AND t.CLAIM_DATE < p.POLICY_START_DATE)', 0),
        ('BR-PAY-01', 'FACT_PAYMENT', 'Payment amount is positive', 't.PAYMENT_AMOUNT IS NULL OR t.PAYMENT_AMOUNT <= 0', 0),
        ('BR-PAY-02', 'FACT_PAYMENT', 'Payment method is a known method', $$t.PAYMENT_METHOD IS NULL OR t.PAYMENT_METHOD NOT IN ('Bank transfer', 'Credit card', 'Debit card')$$, 0),
        ('BR-PAY-03', 'FACT_PAYMENT', 'Record is not modified before it is created', 't.MODIFIED_DATE < t.CREATED_DATE', 0)
)
SELECT 'STAR_SCHEMA'       AS "schema.name",
       table_name          AS "table.name",
       rule_id             AS "test.key",
       rule_id             AS "var.rule_id",
       rule                AS "var.rule",
       violation_condition AS "var.violation_condition",
       TO_VARCHAR(max_violations) AS "var.max_violations"
FROM rules
ORDER BY rule_id

How it works:

• rules holds one line per rule: id, table, plain-language statement, violation condition, allowance. Conditions that contain quotes use Snowflake's $$ quoting.
• test.key is the rule id, so a table with five rules gets five child tests.
• var.rule_id repeats the rule id for the child test name, because test.key is stored in lower case.
• schema.name is fixed to STAR_SCHEMA. Add a schema column to the list if your rules span schemas.

Reading the rules from a table

If your rules live in a table such as GOVERNANCE.DQ_RULES, replace the rules CTE with a query on it:

WITH rules AS (
    SELECT rule_id, table_name, rule_text AS rule,
           violation_condition, max_violations
    FROM GOVERNANCE.DQ_RULES
    WHERE is_active
)

The final SELECT stays the same. If the table lives in a different database or platform from the data under test, choose that data source in Data Source on the Metadata Script tab. The rows are still matched against the catalog of the template's own data source, so table_name must name a catalogued table there.

Retiring a rule (setting is_active to false) makes its child test a candidate for deletion at the next materialization. To keep a retired rule's child test and its result history, clear Delete tests in the materialization dialog.

What it returns

Twelve rows, the same rules and keys as the import file: five for DIM_POLICY, four for DIM_CLAIM, three for FACT_PAYMENT.

Results on SAMPLE_DW

Verified 2026-10-19: 12 children, 10 pass, 2 fail, the same as the import file version.

Compared with the import file

Adapting it

Give rules their own severity in reporting. A row cannot change a child test's severity, which comes from the template. Make one copy of the template per severity, and filter each copy's script on a severity column in the rules table.

Validate the rules table itself. A rule with a broken condition shows up as a child test error, not a failure. A standard test on the rules table (no blank conditions, unique ids, known table names) catches most of these before materialization.

Pitfall: who can write rules can write SQL. The violation condition is pasted into a query that runs with the data source's credentials. Restrict who can write to the rules table the way you would restrict who can edit tests.

Pitfall: a table the catalog does not know. A rule on a table that is missing from the Validatar catalog fails Preview & Validate with Invalid meta object. A job that materializes the template skips that rule and materializes the rest.

Related

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