Which SCD type uses a separate history table and a surrogate key with the fact table to identify original and historic data?

Which SCD type uses a separate history table and a surrogate key with the fact table to identify original and historic data?

• A. Type 0 SCD
• B. Type 2 SCD
• C. Type 6 SCD
• D. Type 4 SCD

Answer: (D)

This question tests how historical data is managed in slowly changing dimensions. In this approach, history is kept in a separate history table, while the current values stay in the main dimension table. The dimension uses a surrogate key to represent the current row, and the fact table joins to that surrogate key to access the present state. To see how values changed over time, you query the history table, which holds the past versions linked to the same natural key. This separation keeps the main dimension lightweight for current data while preserving full history elsewhere. Other types store history within the same table (Type 2), use a hybrid approach within a single row (Type 6), or maintain no history at all (Type 0).

This question tests how historical data is managed in slowly changing dimensions. In this approach, history is kept in a separate history table, while the current values stay in the main dimension table. The dimension uses a surrogate key to represent the current row, and the fact table joins to that surrogate key to access the present state. To see how values changed over time, you query the history table, which holds the past versions linked to the same natural key. This separation keeps the main dimension lightweight for current data while preserving full history elsewhere. Other types store history within the same table (Type 2), use a hybrid approach within a single row (Type 6), or maintain no history at all (Type 0).