Fact–dimension keys: Evaluate the best practice.\n“Every key used to join the fact table with a dimension table should be a surrogate key.”

Introduction / Context:
In dimensional modeling (Kimball methodology), dimension tables typically use surrogate keys—integer identifiers generated in the warehouse—to decouple analytics from volatile, reused, or composite source keys. Facts then reference these surrogate keys. The statement claims that every fact–dimension join should use surrogate keys; we evaluate this as a best practice.

Given Data / Assumptions:

• Dimensions may be slowly changing (SCD), requiring type 2 history.
• Source natural keys can change, collide, or be non-integer, harming performance and referential stability.
• Warehouse controls key management for consistency across sources.

Concept / Approach:
Surrogate keys allow multiple historical versions of a dimension row (same natural business key, different effective dates) while keeping facts bound to the correct historical context. They also improve join performance and enable conformance across heterogeneous systems. Exceptions (degenerate dimensions in facts) do not contradict the rule, as degenerate attributes do not require a separate dimension join.

Step-by-Step Solution:

Verification / Alternative check:
Review SCD Type 2 examples: surrogate joins are mandatory to ensure historical accuracy independent of natural key changes.

Why Other Options Are Wrong:

• “Incorrect” ignores the modeling and history advantages.
• Limiting to SCD or snowflake misses broader benefits (performance, conformance).
• Depending on source keys undermines stability and cross-source integration.

Common Pitfalls:
Embedding natural keys in facts; losing history when natural keys change; improper surrogate lookup causing late-arriving facts to mismatch.

Final Answer:
Correct