--- title: "Metadata Script Example: Business Rule Catalog" slug: "metadata-script-example-business-rule-catalog" updated: 2026-10-20T15:21:59Z published: 2026-10-20T15:21:59Z canonical: "docs.validatar.com/metadata-script-example-business-rule-catalog" --- > ## 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: 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](/docs/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](/docs/template-test-examples-metadata-script). ## The template | Setting | Value | | --- | --- | | Name | Metadata Script Example - Business Rule Catalog | | Level | Table | | 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-business-rule-catalog): violation count `WHERE {{var.violation_condition}}` on the table aliased `t`, control value `{{var.max_violations}}`, the custom calculation `testNumericValue <= controlNumericValue`, and the same variables | ## 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. | Rule | Violations | Result | | --- | --- | --- | | BR-POL-05: DIM_POLICY record is not modified before it is created | 230 | **Fail** | | BR-PAY-03: FACT_PAYMENT record is not modified before it is created | 5,755 | **Fail** | | The other ten | 0 | Pass | ## Compared with the import file | | Import file | Metadata script | | --- | --- | --- | | Where the rules live | A spreadsheet uploaded to the template | A table, or the script itself | | Adding a rule | Edit the file, upload, materialize | Insert a row, materialize | | Who can add rules | Anyone who can edit the template | Anyone who can write to the rules table | | Child tests | 12 | The same 12, with the same keys | ## 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](/docs/template-test-examples-metadata-script) - [Generating Child Tests using a Metadata Script](/docs/generating-child-tests-using-a-metadata-script) - [Import File Example: Business Rule Catalog](/docs/import-file-example-business-rule-catalog) - [Metadata Script Example: Column Check Suite](/docs/metadata-script-example-column-check-suite)