---
title: "Import File Example: Business Rule Catalog"
slug: "import-file-example-business-rule-catalog"
updated: 2026-10-20T15:21:45Z
published: 2026-10-20T15:21:45Z
canonical: "docs.validatar.com/import-file-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.

# 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](/docs/template-test-examples-import-file). The same template with the rules returned by a script is [Metadata Script Example: Business Rule Catalog](/docs/metadata-script-example-business-rule-catalog).

## The template

| Setting | Value |
| --- | --- |
| Name | Import File Example - Business Rule Catalog |
| Level | Table |
| Generation source | Import File |
| Severity | High |
| Quality dimension | Validity |
| Child test name | `Rule {{var.rule_id}}: {{table.name}} - {{var.rule}}` |
| 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_violations}}` |
| 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 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:

| Reference key | Type | Purpose |
| --- | --- | --- |
| `rule_id` | Other | The rule's id from the catalog |
| `rule` | Other | The rule in plain language, used in the child test name and description |
| `violation_condition` | Other | SQL condition that is true for a row that breaks the rule |
| `max_violations` | Numeric | Violating rows allowed before the child test fails. Default 0 |

`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.

| Header | Meaning |
| --- | --- |
| `schema.name`, `table.name` | The catalogued table the rule applies to |
| `test.key` | The rule id. Unique across the file |
| `var.rule_id` | The rule id again, for the child test name |
| `var.rule` | The rule in plain language |
| `var.violation_condition` | The condition that marks a violating row |
| `var.max_violations` | Allowed violations, 0 on every row |

The rules:

| Rule | Table | Rule | Violating rows are those where |
| --- | --- | --- | --- |
| BR-POL-01 | DIM_POLICY | Policy ends after it starts | `t.POLICY_END_DATE < t.POLICY_START_DATE` |
| BR-POL-02 | DIM_POLICY | Premium is positive | `t.PREMIUM_AMOUNT IS NULL OR t.PREMIUM_AMOUNT <= 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')` |
| BR-POL-04 | DIM_POLICY | Premium does not exceed the policy amount | `t.PREMIUM_AMOUNT > t.POLICY_AMOUNT` |
| BR-POL-05 | DIM_POLICY | Record is not modified before it is created | `t.MODIFIED_DATE < t.CREATED_DATE` |
| BR-CLM-01 | DIM_CLAIM | Claim amount is positive | `t.CLAIM_AMOUNT IS NULL OR t.CLAIM_AMOUNT <= 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')` |
| BR-CLM-03 | DIM_CLAIM | Claim date is not in the future | `t.CLAIM_DATE > CURRENT_TIMESTAMP()` |
| 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)` |
| BR-PAY-01 | FACT_PAYMENT | Payment amount is positive | `t.PAYMENT_AMOUNT IS NULL OR t.PAYMENT_AMOUNT <= 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')` |
| BR-PAY-03 | FACT_PAYMENT | Record is not modified before it is created | `t.MODIFIED_DATE < t.CREATED_DATE` |

`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.

| Rule | Violations | Result |
| --- | --- | --- |
| BR-POL-01 to BR-POL-04 | 0 | Pass |
| BR-POL-05: DIM_POLICY record is not modified before it is created | 230 | **Fail** |
| BR-CLM-01 to BR-CLM-04 | 0 | Pass |
| BR-PAY-01, BR-PAY-02 | 0 | Pass |
| BR-PAY-03: FACT_PAYMENT record is not modified before it is created | 5,755 | **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 &lt;= 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](/docs/template-test-examples-import-file)
- [Generating Child Tests from an Import File](/docs/generating-child-tests-from-an-import-file)
- [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)