Slowly Changing Dimensions (SCDs) Flashcards

A reporting team needs to compare sales by a customer's segment as it was at the time of each sale. Which SCD type supports this?

Answer: Type 2

Type 2 preserves the historical segment valid at each transaction's time.

Which scenario is best served by a Type 1 SCD?

Answer: Correcting a misspelled product name with no need for history

Data corrections where history adds no value are ideal Type 1 overwrites.

What is a key trade-off of Type 2 dimensions?

Answer: Greater storage and load complexity in exchange for full history

Full historical tracking increases row counts and ETL logic complexity.

If a query must always reflect the latest dimension value regardless of fact date, which approach fits?

Answer: Join facts via the natural key to the current Type 2 row (or Type 1)

Current-state reporting uses the active row or a Type 1 attribute rather than historical versions.

Which column would you index to speed up 'current record' lookups in a Type 2 dimension?

Answer: is_current flag (or end_date)

Filtering on is_current or the sentinel end_date is the common current-state access pattern.

A retroactive correction must change history without creating a new version. Which type behavior applies?

Answer: Type 1 overwrite applied to a specific historical row

Overwriting in place corrects an existing version without spawning a new historical record.

Why might a data warehouse store both a Type 1 and Type 2 version of the same attribute?

Answer: To offer current-value reporting and point-in-time history simultaneously

Dual storage (a Type 6 pattern) serves both 'as-is' and 'as-was' analytical needs.