Metadata Script Example: Referential Integrity Rule

What it checks

Every value in a foreign key column must exist in its parent table. The rule and its SQL are the same as in Import File Example: Referential Integrity Rule.

The difference is who finds the foreign keys. In the file, someone listed six of them by hand. Here the script reads the warehouse's own INFORMATION_SCHEMA and applies a naming convention: a column named <ENTITY>_ID or <ENTITY>_KEY points at the table DIM_<ENTITY> when that table has a column of the same name. A new fact table that follows the convention is covered at the next materialization, with nobody editing a list.

A convention is never perfect, so the script also carries a short overrides list: an orphan allowance for one column, and one false match left out.

Part of Template Test Examples: Metadata Script.

The template

The metadata script

metadata-script-example-referential-integrity.sql:

-- Candidate foreign keys come from the warehouse's own catalog: any STAR_SCHEMA column
-- named <ENTITY>_ID or <ENTITY>_KEY whose table DIM_<ENTITY> has a column of the same name.
-- The overrides list covers what a naming convention cannot know.
WITH candidates AS (
    SELECT c.table_schema, c.table_name, c.column_name,
           p.table_schema AS parent_schema, p.table_name AS parent_table, p.column_name AS parent_column
    FROM INFORMATION_SCHEMA.COLUMNS c
    JOIN INFORMATION_SCHEMA.COLUMNS p
      ON p.table_schema = c.table_schema
     AND p.column_name  = c.column_name
     AND p.table_name   = 'DIM_' || REGEXP_REPLACE(c.column_name, '_(ID|KEY)$', '')
    WHERE c.table_schema = 'STAR_SCHEMA'
      AND REGEXP_LIKE(c.column_name, '.*_(ID|KEY)')
      AND c.table_name <> p.table_name
),
overrides AS (
    SELECT column1 AS table_name, column2 AS column_name, column3 AS max_orphan_rows, column4 AS skip
    FROM VALUES
        ('FACT_TRANSACTION', 'CLAIM_ID', 6000, FALSE),  -- known backlog of claims not loaded yet
        ('FACT_REVENUE', 'POLICY_ID', 0, TRUE)          -- no value matches DIM_POLICY: excluded until its owner confirms what it references
)
SELECT c.table_schema  AS "schema.name",
       c.table_name    AS "table.name",
       c.column_name   AS "column.name",
       c.table_name || '.' || c.column_name || ' -> ' || c.parent_table AS "test.key",
       c.parent_schema AS "var.parent_schema",
       c.parent_table  AS "var.parent_table",
       c.parent_column AS "var.parent_column",
       TO_VARCHAR(COALESCE(o.max_orphan_rows, 0)) AS "var.max_orphan_rows"
FROM candidates c
LEFT JOIN overrides o
  ON o.table_name = c.table_name AND o.column_name = c.column_name
WHERE NOT COALESCE(o.skip, FALSE)
ORDER BY "test.key"

How it works:

• candidates joins INFORMATION_SCHEMA.COLUMNS to itself. For each STAR_SCHEMA column ending in _ID or _KEY, it strips the suffix, prefixes DIM_, and looks for a column with the same name in that table. FACT_PAYMENT.POLICY_ID becomes a candidate because DIM_POLICY.POLICY_ID exists. c.table_name <> p.table_name stops a dimension from matching its own key.
• overrides is the part a person maintains. It holds only the exceptions, not the whole list. FACT_TRANSACTION.CLAIM_ID gets an allowance of 6,000 orphans for a known backlog of claims that load late. FACT_REVENUE.POLICY_ID is left out: none of its values match DIM_POLICY, which suggests that the name follows the convention while the column refers to something else, until its owner says otherwise.
• test.key is CHILD.COLUMN -> PARENT, the format the import file uses.
• var.max_orphan_rows comes from the overrides list, or 0 for every other column.

What it returns

Seven rows: eight candidates, less the one override marked skip.

FACT_SALES has four foreign key columns, so that one table gets four child tests, one on each column.

Results on SAMPLE_DW

Verified 2026-10-19: 7 children, 6 pass, 1 fail.

The PRODUCT_KEY failure is the same real defect the import file version finds.

Compared with the import file

The two lists disagree in both directions. A convention finds the columns nobody remembered to list, and misses the relationships that break the pattern. The overrides and an extra-pairs list (below) close the gap.

Adapting it

Use declared constraints instead. If your platform records foreign keys (INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS with KEY_COLUMN_USAGE on SQL Server and PostgreSQL, sys.foreign_keys on SQL Server), select the candidates from there and skip the naming convention.

Add the pairs a convention misses. UNION ALL a small VALUES list of extra pairs onto candidates, for example ('STAR_SCHEMA', 'FACT_TRANSACTION', 'PAYMENT_ID', 'STAR_SCHEMA', 'FACT_PAYMENT', 'PAYMENT_ID').

Keep the overrides in a table. Stewards can then raise an allowance or exclude a column without editing the template.

Pitfall: every candidate must be in the catalog. The script reads INFORMATION_SCHEMA, but the rows are matched against the Validatar catalog. A table created since the last metadata ingestion appears in the script and fails Preview & Validate with Invalid meta object. Ingest metadata first, or filter on tables the catalog holds.

Related

• Template Test Examples: Metadata Script
• Generating Child Tests using a Metadata Script
• Import File Example: Referential Integrity Rule
• Metadata Script Example: Column Check Suite