--- title: "Metadata Script Example: Referential Integrity Rule" slug: "metadata-script-example-referential-integrity" updated: 2026-10-20T15:21:53Z published: 2026-10-20T15:21:53Z canonical: "docs.validatar.com/metadata-script-example-referential-integrity" --- > ## 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. # 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](/docs/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](/docs/template-test-examples-metadata-script). ## The template | Setting | Value | | --- | --- | | Name | Metadata Script Example - Referential Integrity Rule | | Level | Column | | Generation source | Metadata Script | | Script data source | The template's own data source (SAMPLE_DW) | | Everything else | Identical to [the import file version](/docs/import-file-example-referential-integrity-rule): the orphan-count SQL, control value `{{var.max_orphan_rows}}`, the custom calculation `testNumericValue <= controlNumericValue`, and the same variables | ## The metadata script `metadata-script-example-referential-integrity.sql`: ``` -- Candidate foreign keys come from the warehouse's own catalog: any STAR_SCHEMA column -- named _ID or _KEY whose table DIM_ 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`. | Foreign key column | Parent | Allowed orphans | | --- | --- | --- | | `DIM_CLAIM.POLICY_ID` | `DIM_POLICY.POLICY_ID` | 0 | | `FACT_PAYMENT.POLICY_ID` | `DIM_POLICY.POLICY_ID` | 0 | | `FACT_SALES.CUSTOMER_ID` | `DIM_CUSTOMER.CUSTOMER_ID` | 0 | | `FACT_SALES.CUSTOMER_KEY` | `DIM_CUSTOMER.CUSTOMER_KEY` | 0 | | `FACT_SALES.PRODUCT_ID` | `DIM_PRODUCT.PRODUCT_ID` | 0 | | `FACT_SALES.PRODUCT_KEY` | `DIM_PRODUCT.PRODUCT_KEY` | 0 | | `FACT_TRANSACTION.CLAIM_ID` | `DIM_CLAIM.CLAIM_ID` | 6,000 | `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. | Child | Orphans | Allowed | Result | | --- | --- | --- | --- | | FACT_SALES.PRODUCT_KEY -> DIM_PRODUCT | 254 | 0 | **Fail** | | FACT_TRANSACTION.CLAIM_ID -> DIM_CLAIM | 5,045 | 6,000 | Pass | | The other five | 0 | 0 | Pass | The `PRODUCT_KEY` failure is the same real defect the import file version finds. ## Compared with the import file | | Import file | Metadata script | | --- | --- | --- | | How foreign keys are found | Listed by hand | Naming convention over `INFORMATION_SCHEMA` | | What a person maintains | Every row | Only the exceptions | | Child tests | 6 | 7 | | Found by the script, not in the file | | `FACT_SALES.CUSTOMER_ID`, `FACT_SALES.PRODUCT_ID` | | In the file, missed by the convention | `FACT_TRANSACTION.PAYMENT_ID -> FACT_PAYMENT` (the parent is a fact table, not `DIM_`) | | 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](/docs/template-test-examples-metadata-script) - [Generating Child Tests using a Metadata Script](/docs/generating-child-tests-using-a-metadata-script) - [Import File Example: Referential Integrity Rule](/docs/import-file-example-referential-integrity-rule) - [Metadata Script Example: Column Check Suite](/docs/metadata-script-example-column-check-suite)