---
title: "Import File Example: Column Check Suite"
slug: "import-file-example-column-check-suite"
updated: 2026-10-20T15:21:49Z
published: 2026-10-20T15:21:49Z
canonical: "docs.validatar.com/import-file-example-column-check-suite"
---

> ## 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: Column Check Suite

## 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](/docs/import-file-example-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](/docs/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](/docs/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](/docs/metadata-script-example-column-check-suite) 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 &lt;&gt; 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`.

## 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: Business Rule Catalog](/docs/import-file-example-business-rule-catalog)
- [Metadata Script Example: Column Check Suite](/docs/metadata-script-example-column-check-suite)