Foreign-Key Relationships, Surrogate Keys, Referential Integrity, and Orphan Handling

Learning outcomes

A star schema is only trustworthy if every fact foreign key resolves to exactly one dimension row. Natural source keys are necessary for matching incoming records, but warehouse joins need durable keys under warehouse control. Missing dimension context must be represented explicitly; dropping the fact or letting a null foreign key break the join corrupts analytical totals.

1. Natural keys identify source entities; surrogate keys identify warehouse rows

C001 and P100 are business/natural keys supplied by source systems. They are valuable for matching incoming records, lineage, and reconciliation. They are not ideal as the fact table's dimensional foreign keys because source keys can be reused, reformatted, collide across systems, or later require multiple historical dimension rows.

The warehouse therefore assigns customer_key, product_key, and store_key. Facts join to those surrogate keys. Later SCD chapters can create a new surrogate row for the same natural key without changing the source identity itself.

2. Referential integrity is a semantic invariant

Every non-null dimensional foreign key in fact_sales must match one dimension row. In this local harness SQLite enforces that rule when PRAGMA foreign_keys=ON. Some analytical engines treat constraints as informational or do not enforce them, so the warehouse pipeline must still test the invariant explicitly.

SELECT f.sales_key, f.customer_keyFROM fact_sales f LEFT JOIN dim_customer d  ON d.customer_key=f.customer_keyWHERE d.customer_key IS NULL;SELECT f.sales_key, f.product_keyFROM fact_sales f LEFT JOIN dim_product d  ON d.product_key=f.product_keyWHERE d.product_key IS NULL;SELECT f.sales_key, f.store_keyFROM fact_sales f LEFT JOIN dim_store d  ON d.store_key=f.store_keyWHERE d.store_key IS NULL;

All three result sets must be empty for the canonical fixture.

3. Unknown is data, not absence of a key

Sometimes the fact event is legitimate but descriptive context is unavailable at load time. A null foreign key or a dropped fact hides that condition. The lab reserves surrogate key 0 for a visible Unknown member in each non-date dimension. This permits the fact to load while preserving both the measurement and the fact that context resolution failed.

Unknown and Not Applicable are not always the same business state. Production models often use separate default members. This chapter uses one Unknown member to keep the fixture focused; later designs should choose labels and keys with business users and data-quality owners.

4. Hands-on failure injection — prove why inner-join loss is dangerous

Reuse atlasmart_star.sql and run this isolated failure lab. The extra P999 record is deliberately not part of the canonical Chapter 03 data; it exists only to expose orphan behavior.

import sqlite3from pathlib import Pathconn=sqlite3.connect(":memory:")conn.execute("PRAGMA foreign_keys=ON")conn.executescript(Path("atlasmart_star.sql").read_text())# Isolated staged event: valid sale measurement, product reference not yet known.stage={"order_id":"O9999","line_no":1,"date_key":20260920,"customer_id":"C004",       "product_id":"P999","store_id":"S-MOBILE","quantity":1,"unit_price":30}stage["amount"]=stage["quantity"]*stage["unit_price"]# Wrong: inner lookup returns no product row, so a naive INSERT..SELECT would load 0 rows.product=conn.execute("SELECT product_key FROM dim_product WHERE product_id=?",(stage["product_id"],)).fetchone()print("naive product lookup =", product)assert product is None# Repair: map unresolved context to the governed Unknown member and retain the amount.product_key=0customer_key=conn.execute("SELECT customer_key FROM dim_customer WHERE customer_id=?",(stage["customer_id"],)).fetchone()[0]store_key=conn.execute("SELECT store_key FROM dim_store WHERE store_id=?",(stage["store_id"],)).fetchone()[0]conn.execute("""INSERT INTO fact_sales(sales_key,date_key,customer_key,product_key,store_key,order_id,line_no,quantity,unit_price,extended_amount,source_batch_id)VALUES (?,?,?,?,?,?,?,?,?,?,?)""",(999,stage["date_key"],customer_key,product_key,store_key,stage["order_id"],stage["line_no"],stage["quantity"],stage["unit_price"],stage["amount"],"FAILURE-LAB"))canonical=625new_total=conn.execute("SELECT SUM(extended_amount) FROM fact_sales").fetchone()[0]unknown_total=conn.execute("SELECT SUM(extended_amount) FROM fact_sales WHERE product_key=0").fetchone()[0]print("canonical=",canonical,"after staged exception=",new_total,"unknown_product_amount=",unknown_total)assert new_total == 655 and unknown_total == 30

The naive lookup returns None. A load written as an inner join would silently omit the 30-unit-of-currency measurement. The repaired load maps the unresolved product to key 0, raises the visible total from 625 to 655 for this isolated test, and makes 30 explicitly attributable to Unknown product. A production pipeline should additionally quarantine/alert and later restate the foreign key when the legitimate product member arrives.

Cleanup: delete the disposable lab files; do not merge the P999 event into the canonical course fixture.

5. Let the database reject impossible warehouse keys

PRAGMA foreign_keys = ON;INSERT INTO fact_sales(sales_key,date_key,customer_key,product_key,store_key,order_id,line_no,quantity,unit_price,extended_amount,source_batch_id)VALUES(1000,20260920,104,999999,302,'O-INVALID',1,1,10,10,'FAILURE-LAB');

On the stated SQLite harness, this insert fails with a foreign-key constraint error because product key 999999 does not exist. That failure is desirable. On engines that do not enforce such constraints, the equivalent orphan query must be a load/quality assertion.

6. Controlled failure: use only natural keys in facts

A natural-key-only fact appears simpler: store customer_id and product_id directly and join them later. The problem arrives when a natural key is recycled, two source systems use the same value for different entities, or a Type 2 dimension creates multiple historical rows for one natural key. The fact no longer identifies one warehouse dimension row unambiguously.

The repair is to preserve the natural key in the dimension for matching and lineage while storing the resolved surrogate key in the fact. The key lookup is part of the load contract and must be observable/tested.

7. Production judgment and bridge

Unknown-member handling should be governed like any other data-quality policy: define when it is allowed, how it is labeled, how exceptions are counted/alerted, and whether later restatement is required. “Unknown” must not become a permanent hiding place for broken source mappings.

The next lesson compares the logical star with snowflaked and flat-wide alternatives. The goal is not to crown a universal fastest shape, but to separate semantic maintainability from engine-specific physical performance.

Knowledge check

Summary and next step

AtlasMart facts now have durable warehouse keys and an explicit exception path for unresolved context. Canonical orphan checks return zero rows, while the isolated P999 lab demonstrates why lossless, observable handling matters.

Next: compare star, snowflake, and flat-wide serving shapes without confusing fewer joins with guaranteed correctness or performance.

Authoritative references

• Kimball Group — Dimensional Modeling Techniques — Primary index for facts, dimensions, star schemas, grain, surrogate keys, and related dimensional techniques.
• Kimball Group — Fact Tables and Dimension Tables — Explains fact foreign keys, dimension primary keys, referential integrity, unknown members, and degenerate dimensions.
• Kimball Group — Dimension Surrogate Keys — Explains why warehouse-controlled surrogate keys decouple dimensions from operational natural keys.
• Kimball Group — Nulls in Fact Tables — Explains why fact foreign keys should resolve to explicit default dimension members instead of null.
• Kimball Group — Declaring the Grain — Reinforces that grain precedes dimensions and measured facts.
• Python documentation — sqlite3 — Standard-library execution harness used for deterministic local SQL verification.