Bird
0
0

Why is it important to have a surrogate key in SCD Type 2 dimension tables?

hard🧠 Conceptual Q10 of 15
dbt - Advanced Patterns
Why is it important to have a surrogate key in SCD Type 2 dimension tables?
ATo uniquely identify each version of a record regardless of natural key changes
BTo speed up query performance by indexing natural keys
CTo avoid storing effective and end dates
DTo prevent duplicate natural keys in source data
Step-by-Step Solution
Solution:

  1. Step 1: Understand surrogate key role and analyze options

    Surrogate keys uniquely identify each record version, even if natural keys repeat due to changes. To uniquely identify each version of a record regardless of natural key changes correctly states this; others confuse performance or data issues.

  2. Final Answer:

    To uniquely identify each version of a record regardless of natural key changes -> Option A

  3. Quick Check:

    Surrogate key = unique version ID [OK]
Quick Trick: Surrogate key uniquely IDs each record version [OK]
Common Mistakes:
MISTAKES
  • Thinking surrogate keys improve speed only
  • Ignoring version uniqueness
  • Confusing with natural key uniqueness

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More dbt Quizzes
Advanced Patterns - Semi-structured data handling (JSON) - Quiz 10hard Advanced Patterns - Metric definitions and semantic layer - Quiz 15hard Advanced Patterns - Metric definitions and semantic layer - Quiz 2easy Advanced Patterns - Why advanced patterns solve complex analytics - Quiz 14medium Governance and Collaboration - Why governance ensures data trust - Quiz 11easy Governance and Collaboration - Group-based ownership - Quiz 3easy Governance and Collaboration - Model contracts and access controls - Quiz 2easy Performance Optimization - Warehouse-specific optimizations - Quiz 7medium Performance Optimization - Clustering and partitioning - Quiz 11easy Performance Optimization - Warehouse-specific optimizations - Quiz 12easy