Microsoft
70-467 · Question #78
70-467 Question #78: Real Exam Question with Answer & Explanation
The correct answer is A: Use a surrogate key.. Surrogate keys are the industry-standard primary key for dimension tables in data warehouse design, decoupling the warehouse from source system volatility.
Question
You need to choose the appropriate key to use when designing a dimension table based on the Customer table. What should you do?
Options
- AUse a surrogate key.
- BUse a natural key.
- CUse the CustomerNumber column as the key.
- DConcatenate the CustomerName and CustomerNumber columns and use the concatenated
- EUse the CustomerName column as the key.
Explanation
Surrogate keys are the industry-standard primary key for dimension tables in data warehouse design, decoupling the warehouse from source system volatility.
Common mistakes.
- B. Natural keys from source systems can change, be reused, or contain nulls over time, making them unreliable as stable, permanent dimension identifiers.
- C. Using CustomerNumber ties the dimension directly to the source system business key, which cannot support slowly changing dimension scenarios if that value is ever updated or reassigned.
- D. A concatenated key of CustomerName and CustomerNumber is inefficient for joins, grows unnecessarily large, and still inherits the instability problems of both source system natural keys.
- E. CustomerName is not guaranteed to be unique across all customers and can change due to legal name changes or data entry corrections, making it unsuitable as a primary key.
Concept tested. Surrogate key design for dimension tables in star schema
Reference. https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
Community Discussion
No community discussion yet for this question.