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.

The template

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:

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.

The rows:

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.

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
• Generating Child Tests from an Import File
• Import File Example: Source to Target Reconciliation
• Import File Example: Change Over Time