---
title: "Import File Example: Referential Integrity Rule"
slug: "import-file-example-referential-integrity-rule"
updated: 2026-09-21T15:20:44Z
published: 2026-09-21T15:20:44Z
canonical: "docs.validatar.com/import-file-example-referential-integrity-rule"
---

> ## 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: Referential Integrity Rule

## What it checks

One rule, applied to many columns: every value in a foreign key column must exist in its parent table. A row that fails this rule is an orphan, and orphans in a fact table mean revenue, claims, or transactions that no dimension can explain.

The rule's SQL lives on the template. The spreadsheet supplies what varies per column: which parent table and column to look up, and how many orphans are tolerated before the child test fails. That last part matters in practice. Late-arriving dimensions produce a known trickle of orphans in some feeds, and a rule with no allowance would fail every night for a reason nobody intends to fix.

Part of [Template Test Examples: Import File](/docs/template-test-examples-import-file).

## The template

| Setting | Value |
| --- | --- |
| Name | Import File Example - Referential Integrity Rule |
| Level | Column |
| Generation source | Import File |
| Severity | High |
| Quality dimension | Consistency |
| Child test name | `Orphans: {{table.name}}.{{column.name}} -> {{var.parent_table}}.{{var.parent_column}}` |
| 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_orphan_rows}}` |
| Value success criteria | Custom calculation: `testNumericValue <= controlNumericValue` |
| Overall success | Value, tolerance 0 |
| Missing result actions | Fail on both sides |

The test data set SQL:

```
SELECT COUNT(*) AS ORPHAN_ROWS
FROM {{schema.name}}.{{table.name}} c
LEFT JOIN {{var.parent_schema}}.{{var.parent_table}} p
  ON c.{{column.name}} = p.{{var.parent_column}}
WHERE c.{{column.name}} IS NOT NULL
  AND p.{{var.parent_column}} IS NULL
```

Null foreign keys are excluded on purpose. A null is a different defect (a completeness problem) and belongs to a different test.

Variables declared on the template:

| Reference key | Type | Purpose |
| --- | --- | --- |
| `parent_schema` | Schema | Schema of the parent table |
| `parent_table` | Table | Parent table |
| `parent_column` | Column | Column in the parent that the child key must match |
| `max_orphan_rows` | Numeric | Orphan rows allowed before the child test fails. Default 0 |

Nothing on the template is ticked From File. The allowance is a variable referenced directly in the control's Target Value field, the same way the drift templates shipped with Validatar carry their lookback windows. One mechanism for every per-row value, and a declared default to fall back on.

Two metadata links are set on the test data set. `{{schema.name}}.{{table.name}}.{{column.name}}` carries coverage for the foreign key column. `{{var.parent_schema}}.{{var.parent_table}}.{{var.parent_column}}` records the relationship to the parent without counting as coverage for it.

## The import file

`import-file-example-referential-integrity.xlsx`, one sheet, header row plus 6 data rows.

| Header | Meaning |
| --- | --- |
| `schema.name`, `table.name`, `column.name` | The catalogued foreign key column. Column level, so all three are required |
| `test.key` | Unique key, written as `CHILD.COLUMN -> PARENT` for readability |
| `var.parent_schema`, `var.parent_table`, `var.parent_column` | Where the key must resolve |
| `var.max_orphan_rows` | Allowed orphans |

The rows:

| Foreign key column | Parent | Allowed orphans |
| --- | --- | --- |
| `STAR_SCHEMA.FACT_SALES.CUSTOMER_KEY` | `STAR_SCHEMA.DIM_CUSTOMER.CUSTOMER_KEY` | 0 |
| `STAR_SCHEMA.FACT_SALES.PRODUCT_KEY` | `STAR_SCHEMA.DIM_PRODUCT.PRODUCT_KEY` | 0 |
| `STAR_SCHEMA.FACT_PAYMENT.POLICY_ID` | `STAR_SCHEMA.DIM_POLICY.POLICY_ID` | 0 |
| `STAR_SCHEMA.FACT_TRANSACTION.CLAIM_ID` | `STAR_SCHEMA.DIM_CLAIM.CLAIM_ID` | 6,000 |
| `STAR_SCHEMA.FACT_TRANSACTION.PAYMENT_ID` | `STAR_SCHEMA.FACT_PAYMENT.PAYMENT_ID` | 0 |
| `STAR_SCHEMA.DIM_CLAIM.POLICY_ID` | `STAR_SCHEMA.DIM_POLICY.POLICY_ID` | 0 |

The `CLAIM_ID` row shows the allowance in use. Claims arrive in `FACT_TRANSACTION` before `DIM_CLAIM` catches up, so a few thousand orphans are the accepted state and the threshold is set above it.

## What materializes

Six child tests, one per foreign key column, in a folder named after the template. The `PRODUCT_KEY` child, for instance, gets this test script:

```
SELECT COUNT(*) AS ORPHAN_ROWS
FROM STAR_SCHEMA.FACT_SALES c
LEFT JOIN STAR_SCHEMA.DIM_PRODUCT p
  ON c.PRODUCT_KEY = p.PRODUCT_KEY
WHERE c.PRODUCT_KEY IS NOT NULL
  AND p.PRODUCT_KEY IS NULL
```

and a control value of 0. The `CLAIM_ID` child gets a control value of 6000.

## Results on SAMPLE_DW

Verified 2026-09-11.

| Child | Orphans | Allowed | Result |
| --- | --- | --- | --- |
| FACT_SALES.CUSTOMER_KEY -> DIM_CUSTOMER | 0 | 0 | Pass |
| FACT_SALES.PRODUCT_KEY -> DIM_PRODUCT | 254 | 0 | **Fail** |
| FACT_PAYMENT.POLICY_ID -> DIM_POLICY | 0 | 0 | Pass |
| FACT_TRANSACTION.CLAIM_ID -> DIM_CLAIM | 5,045 | 6,000 | Pass |
| FACT_TRANSACTION.PAYMENT_ID -> FACT_PAYMENT | 0 | 0 | Pass |
| DIM_CLAIM.POLICY_ID -> DIM_POLICY | 0 | 0 | Pass |

The 254 sales rows with an unknown product are a real defect in the sample warehouse. They also demonstrate the value of the allowance column: set that row's `var.max_orphan_rows` to 254 and the test passes while the defect is being fixed, then set it back to 0 afterwards.

## Adapting it

**Cover your own star schema.** One row per foreign key. The child column must be in the catalog; the parent named in the `var.` columns only needs to be queryable.

**Parent in another schema or database.** `var.parent_schema` is free text inside the SQL, so `OTHER_DB.OTHER_SCHEMA` works on platforms that allow three-part names.

**Composite keys.** The template joins on one column. For a two-column key, copy the template and add `var.child_column_2` and `var.parent_column_2` to the join, then add those headers to the file.

**Turn the allowance into a percentage.** Change the test SQL to return `100.0 * COUNT(orphans) / COUNT(*)` and treat `var.max_orphan_rows` as a percent. The comparison stays `<=`.

**Pitfall: column level means three required headers.** Leave out `column.name` and the upload fails with `Missing column: "column.name"`.

**Pitfall: case on the parent names.** They are pasted into SQL as written. Snowflake folds unquoted identifiers to upper case, so lower case works there, but match your platform's rules.

## 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: Source to Target Reconciliation](/docs/import-file-example-source-to-target-reconciliation)
- [Import File Example: Change Over Time](/docs/import-file-example-change-over-time)