Data Engineering Slowly Changing Dimensions (SCDs) Questions and Answers

Question 1

A data warehouse contains a customer dimension where, for compliance reasons, a complete and auditable history of every customer's address must be maintained.
When a customer moves, the previous address record must be preserved, and a new record with the new address must become the active record.
Which Slowly Changing Dimension (SCD) type is required to meet this specification?

Accepted Answer

SCD Type 2

Answer

SCD Type 1

Answer

SCD Type 3

Answer

SCD Type 4

Question 2

A marketing team asks a data engineer to modify the `DimEmployee` table. They need to track a salesperson's current sales region but also want to know their immediately prior region for short-term campaign analysis. They want to avoid the storage growth and query complexity associated with adding new rows for each change. Which SCD type provides the most direct and efficient solution for this specific requirement?

Accepted Answer

SCD Type 3

Answer

SCD Type 1

Answer

SCD Type 2

Answer

SCD Type 0

Question 3

A data engineer is managing a product dimension table where minor corrections, such as fixing a typo in a product description, occur occasionally. The business requirement is to only store the most recent and accurate version of the product information, and there is no need to retain a history of the incorrect data. Which of the following SCD approaches is the most appropriate and simplest to implement?

Accepted Answer

SCD Type 1

Answer

SCD Type 2

Answer

SCD Type 6

Answer

SCD Type 0

Question 4

When implementing an SCD Type 2 dimension table for employees, a data engineer uses `effective_start_date` and `effective_end_date` columns to track the time period for which each record is valid. When an employee's department changes, which of the following actions must be performed?

Accepted Answer

Update the `effective_end_date` of the current record to the day before the change and insert a new record for the new department.

Answer

Delete the old record and insert a new record with the current date as the `effective_start_date`.

Answer

Update the `department` field in the existing record and set the `effective_start_date` to the current date.

Answer

Add a new column named `previous_department` and populate it with the old department name.

Question 5

In a dimensional model, certain attributes are not expected to change under any circumstances after they are created. An example would be a person's `DateOfBirth` in a customer dimension. Which SCD type is used to classify and handle such fixed attributes?

Accepted Answer

SCD Type 0

Answer

SCD Type 1

Answer

SCD Type 3

Answer

SCD Type 2

Question 6

A data engineering team needs to support two types of analysis on their `Salesperson` dimension: 1) Fast, simple queries to find the current territory for each salesperson. 2) In-depth historical analysis of a salesperson's territory changes over time. Which hybrid SCD approach is specifically designed to satisfy both of these requirements efficiently?

Accepted Answer

SCD Type 6

Answer

SCD Type 4

Answer

SCD Type 2

Answer

SCD Type 1