Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Referential integrity in warehouses

Data quality · Building Quality In

Referential integrity in warehouses

Mediumdata-quality-25
referential-integritydata-warehousingdbtdimensional-modeling

Question

Warehouses don't enforce foreign keys. How do you catch orphan records?

Solution

Cloud data warehouses treat foreign keys as non-enforced metadata used primarily for query optimization. Data engineers catch orphan records by running automated anti-joins between fact and dimension tables and routing missing foreign keys to sentinel records rather than dropping transactions.

Catching missing parent keys in SQL

Because warehouses do not block invalid inserts, pipelines must run explicit integrity assertions during transformation:

  • In dbt, configure a relationships test in the YAML schema file to assert that every foreign key in the fact model exists in the target dimension.
  • In native SQL, run an anti-join query comparing fact and dimension keys to count orphan records before publishing.
SELECT count(*) AS orphan_count
FROM fact_orders f
LEFT JOIN dim_customer c
  ON f.customer_key = c.customer_key
WHERE c.customer_key IS NULL;
-- Expected result: 0

When orphan records appear, dropping them corrupts financial revenue totals. Production pipelines apply two standard mitigation techniques:

  • The unknown member pattern: Replace missing dimension foreign keys with a sentinel key like -1 pointing to a pre-seeded Unknown row in the dimension table. This preserves total revenue metrics while signaling to analysts that customer attribution is unmapped.
  • Late-arriving dimension handling: If an order arrives before the operational database extracts the new customer record, insert a stub dimension row with the natural key and backfill descriptive attributes when the dimension pipeline catches up.

Handling business impact and severity

The severity of orphan records determines whether the pipeline blocks or warns:

  • For general analytics, map missing foreign keys to unknown members and emit a warning alert to investigate upstream extraction lag.
  • For regulatory accounting or invoicing, orphan records represent critical integrity violations that must block the publishing task until parent entities are resolved.
PreviousNext