If a warehouse doesn't enforce foreign keys between fact and dimension tables, why declare them at all?
answer
- the constraint that is never checked
- who catches a dangling fact key?
- declaration is metadata, not a guarantee
- documentation plus an optimizer hint
basics
~20 sA declared but unenforced foreign key is metadata: it documents the join path, feeds BI and lineage tools, and can enable optimizer rewrites. It guarantees nothing — orphan fact rows still load — so referential integrity has to be checked by the pipeline.
solid answer
~50 sWarehouses skip enforcement because validating a constraint per row across a bulk load of hundreds of millions of rows is expensive, loads run in parallel and out of order, and facts legitimately arrive before their dimension rows do. What the declaration still buys you is **metadata**: it tells a human and a BI or lineage tool which column joins to which, and many engines will trust a declared-and-marked-reliable constraint to rewrite a query — for example eliminating a join to a dimension nothing selects from. That trust is the danger: if the data actually contains orphans, a rewrite based on a false constraint can return wrong results. So declare the relationship for documentation and tooling, but treat integrity as something the load enforces: route unmatched business keys to the unknown member so a fact never carries a dangling key, and run a referential check after each load with an alert on the orphan count.
go deeper
Know that in a warehouse a foreign key declaration usually does not stop bad data from loading; the pipeline is what keeps fact and dimension tables consistent.
Explain why per-row constraint checking is a poor fit for bulk columnar loads, and write the anti-join that counts fact rows with no matching dimension row.
Demonstrate the operational side: routing unmatched keys to a reserved member, alerting on the share rather than on zero, and diagnosing whether a spike is load order, a source key format change, or a renumbered dimension.
Decide the house policy on trusted constraints — which rewrites you are willing to let an optimizer make, what test evidence justifies that trust, and who owns the fallout when the data contradicts the declaration.
## The declaration and the guarantee are two different things In an OLTP database a `FOREIGN KEY` is a promise the engine keeps: every insert is checked, and a violating row is rejected. In most analytical warehouses the same syntax is accepted but nothing is verified — the constraint is stored as metadata only, often with an explicit qualifier along the lines of `NOT ENFORCED` or a flag saying the engine may *rely* on it. The word `FOREIGN KEY` in a warehouse DDL is therefore a statement of intent, not a control. ## Why warehouses do not enforce Three reasons, all structural rather than lazy: 1. **Cost.** Enforcement means a lookup per inserted row against another table. On a bulk load of hundreds of millions of fact rows into a columnar, distributed store — where the dimension may live on other nodes entirely — that is a per-row random access on a system designed for large sequential scans. 2. **Load order.** Layers are loaded in parallel and re-run independently. A dimension and its facts are often built by separate jobs; enforcing order between them would serialize the whole warehouse build. 3. **Reality of the data.** Facts genuinely arrive referencing dimension members that do not exist yet, or that never will because the source is dirty. An OLTP database can refuse the row; a warehouse whose job is to report on what actually happened usually cannot. ## What the declaration is still worth - **Documentation that lives next to the data.** Anyone reading the DDL, or any tool crawling the catalog, learns the join path without guessing from column names. - **Tooling input.** BI and lineage tools read constraint metadata to propose relationships and draw the model. Getting these right is why a model diagram in a BI tool matches the star you designed. - **Optimizer rewrites.** Some engines will use a trusted constraint to prove that a join cannot change cardinality and drop it — a view that joins five dimensions still scans one table when a query selects only fact columns. This is a genuine win on wide, view-heavy marts. ## And what it costs when you lie A rewrite based on a constraint the data violates produces *wrong answers*, not errors. If orphan fact rows exist and the optimizer eliminates the dimension join it believed could never drop rows, counts come back too high; the reverse case drops rows silently. Declaring a constraint you have not tested is worse than declaring nothing. ## What you do instead Move integrity into the load and into tests. - **Never leave a dangling key in a fact.** Resolve every business key against the dimension during the load; anything that does not match gets the reserved unknown-member key rather than a value that points nowhere. - **Check after each load.** An anti-join from fact to dimension counting unmatched keys is cheap and catches the whole class: ```sql SELECT COUNT(*) AS orphan_rows FROM fct_order o LEFT JOIN dim_customer c ON c.customer_sk = o.customer_sk WHERE c.customer_sk IS NULL; ``` - **Alert on the trend, not just zero.** In some models a small unknown-member share is expected. What matters is a jump: yesterday 0.2%, today 12% means an upstream key format changed. - **Check the dimension's own key uniqueness too.** An unenforced primary key is equally unchecked, and a duplicated dimension row fans out every fact joined to it — a far more damaging bug than an orphan, because it inflates measures instead of dropping rows. ## Where orphans come from The common causes are worth memorizing because the diagnosis is usually one of them: the dimension load failed or ran late while the fact load succeeded; the source changed the key's format (padding, case, a new prefix) so lookups stopped matching; a dimension was rebuilt and renumbered while facts kept old surrogate values; or a genuine source-data gap where the referenced entity was deleted upstream. ## A workable house rule Declare the relationships, mark them unenforced, and treat the declaration as documentation. Only mark a constraint as trusted or reliable for the optimizer when a scheduled test proves it holds, and wire that test to fail the build. Then the model diagram, the optimizer and the data all say the same thing — which is the entire point of writing the constraint down.
- What is riskier than an orphan fact row: a missing dimension member, or a duplicated one?A duplicate. An orphan fact row disappears from an inner join, so counts come in low and someone eventually notices. A duplicated dimension key fans the join out and *inflates* measures — revenue doubles for the affected members and looks like growth. Test dimension-key uniqueness on every load, not just referential coverage.
- You see the unknown-member share on a fact jump from under 1% to 12% overnight. How do you triage it?Check load order first: did the dimension job fail or run late while the fact job succeeded? If both ran, compare a sample of unmatched business keys against the dimension — a format change upstream, such as new padding, casing or a prefix, is the usual culprit. If the keys look identical, suspect a dimension rebuild that renumbered surrogate values.
saying these in an interview costs you the question
- Claims the warehouse rejects fact rows with unmatched dimension keys
- Declares constraints as trusted without ever testing them
- Thinks declaring a foreign key improves join performance by itself
- Checks only referential coverage, never dimension key uniqueness
- Alerts only when orphan count is non-zero, ignoring the trend