Implementing Analytics Solutions Using Microsoft Fabric — Question 106

You have a Fabric tenant that contains a warehouse.

You are designing a star schema model that will contain a customer dimension. The customer dimension table will be a Type 2 slowly changing dimension (SCD).

You need to recommend which columns to add to the table. The columns must NOT already exist in the source.

Which three types of columns should you recommend? Each correct answer presents part of the solution.

NOTE: Each correct answer is worth one point.

Answer options

• A. a foreign key
• B. a natural key
• C. an effective end date and time
• D. a surrogate key
• E. an effective start date and time

Correct answer: C, D, E

Explanation

The correct answers are C, D, and E because a Type 2 slowly changing dimension requires tracking the history of changes. Therefore, it needs an effective start date and time (E) and an effective end date and time (C) to manage the validity of records, as well as a surrogate key (D) to uniquely identify each version of the customer data. Options A and B are incorrect since they pertain to keys that are typically derived from the source and do not meet the requirements for a Type 2 SCD.