You are designing a data warehouse that contains a customer dimension. The customer dimension contains the original customer attributes as well as the most recent set of updated attributes. When a…

Question

Options

• ADesign two tables. One table will hold the original customer surrogate key and attributes.
• BDesign a table as a Type 2 SCD dimension that holds the surrogate key for each customer
• CDesign a table to hold the surrogate key for each customer and its attributes.
• DDesign a table to hold the surrogate key for each customer and its attributes as well as

• A
  8% (4)
• B
  15% (7)
• C
  4% (2)
• D
  73% (35)

Explanation

A Type 3 Slowly Changing Dimension stores both the current value and the original (or previous) value of each changing attribute in the same row by adding extra columns - for example, 'CurrentCity' and 'OriginalCity'. This approach preserves history without creating new rows, minimizing storage consumption. Option D describes exactly this: a single table holding the surrogate key, the current attributes, and additional columns for the original attributes. Option B describes a Type 2 SCD, which inserts an entirely new row per change, consuming significantly more storage. Option A uses two separate tables, which adds join complexity and storage overhead. Option C omits the original-attribute columns, failing the preservation requirement. Type 3 is the most storage-efficient SCD pattern that still preserves the original values.

No community discussion yet for this question.