CompTIA DataSys+ (DS0-001)Database Management and MaintenanceMedium
A data engineer is designing a data warehouse and needs to implement a mechanism to track changes to slowly changing dimensions (SCDs) Type 2. They want to maintain a full history of changes for analytical purposes. Which of the following approaches should they use to achieve this?
- AAdd new columns to store previous values.
- BCreate a separate history table for each dimension.
- CAdd new rows for changes, marking old rows as inactive and new rows as active with validity dates.
- DOverwrite the existing record with new values.
Show answer & explanationAnswer & explanation
Correct answer: C. Add new rows for changes, marking old rows as inactive and new rows as active with validity dates.
SCD Type 2 involves creating a new record for each change, preserving the full history. This is typically achieved by adding new rows, marking the previous version as inactive, and using start/end date columns to define the validity period of each record.
Why the other options are wrong
- A. Adding new columns to store previous values is characteristic of SCD Type 3, which stores a limited history (e.g., current and previous value), not a full history.
- B. While a separate history table could store changes, the most common and efficient way to implement SCD Type 2 within the dimension table itself is by using multiple rows and date ranges, rather than managing a separate table for each dimension.
- D. Overwriting the existing record is characteristic of SCD Type 1, which does not preserve history.
Slowly Changing Dimension (SCD) Type 2
A method for handling changes to dimension attributes in a data warehouse by creating a new record for each change, preserving the full history of the attribute.
- Maintains a complete historical record of dimension attribute values.
- Typically uses start_date, end_date, and often a 'current' or 'active' flag.
- Allows for 'as-of' reporting, showing how data appeared at any point in time.
Memory trick: Type 1 forgets, Type 2 remembers all, Type 3 remembers some.