Data Engineering Slowly Changing Dimensions (SCDs) 2

Question 1

In a Type 2 SCD, what is the primary purpose of a surrogate key on the dimension table?

Accepted Answer

To uniquely identify each historical version of a dimension record

Answer

Surrogate keys let multiple rows share one natural key while each version remains uniquely identifiable.

Question 2

A Type 1 SCD update is applied to a customer's address. What happens to the prior value?

Accepted Answer

It is overwritten and lost

Answer

Type 1 overwrites the existing value, retaining no history of the previous state.

Question 3

Which columns are most commonly added to support a Type 2 dimension?

Accepted Answer

effective_date, end_date, and is_current flag

Answer

Validity dates plus a current-record indicator are the standard Type 2 tracking columns.

Question 4

What does a Type 3 SCD typically store to track change?

Accepted Answer

A previous-value column alongside the current value

Answer

Type 3 adds a column (e.g., previous_address) to hold one prior value, allowing limited history.

Question 5

Why are surrogate keys preferred over natural keys when joining facts to a Type 2 dimension?

Accepted Answer

They point a fact to the correct dimension version active at event time

Answer

The surrogate key resolves to the specific historical row valid when the fact occurred.

Data Engineering Practice Test