You are designing a data warehouse that contains a product dimension.
The product dimension contains the original product attributes as well as the most recent set of updated attributes. When a product is updated, the original product attributes must be preserved.
The product dimension design must:
Support a Type 3 Slowly Changing Dimension (SCD) process
Minimize the total storage consumed
You need to design a solution that meets these requirements.
What should you do?
A. Design two tables. One table will hold the original product surrogate key and attributes. The other table will hold the most recent product surrogate key and attributes.
B. Design a table as a Type 2 SCD dimension that holds the surrogate key for each product and its attributes. Limit the number of records per product within the table to two.
C. Design a table to hold the surrogate key for each product and its attributes as well as additional columns to store the original attributes.
D. Design a table to hold the surrogate key for each product and its attributes. Add a column to store an indicator that identifies the record as original data or recent data.
