Slowly Changing Dimensions (SCDs) Flashcards

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

Answer: To uniquely identify each historical version of a dimension record

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

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

Answer: It is overwritten and lost

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

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

Answer: effective_date, end_date, and is_current flag

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

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

Answer: A previous-value column alongside the current value

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

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

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

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

A business wants to keep full history but only queries the current value 95% of the time. Which design helps?

Answer: Type 2 with an is_current flag for fast current-state filtering

The current flag lets common queries filter to active rows while full history remains available.

What is a Type 0 SCD?

Answer: An attribute that never changes once written (retain original)

Type 0 preserves the original value permanently and ignores later source changes.