Question 1

What is a slowly changing dimension?

Accepted Answer

A slowly changing dimension (SCD) is a dimension table whose descriptive attributes change over time - a customer's address, a product's category, an employee's department. The SCD 'type' defines what happens to history when a value changes: whether the old value is overwritten, preserved as a new row, or kept in a separate column.

Question 2

What is the difference between SCD Type 1 and Type 2?

Accepted Answer

Type 1 overwrites the attribute in place, so only the current value exists and historical facts effectively get re-stated against the new value. Type 2 inserts a new row with a fresh surrogate key and effective-date range, preserving the full history so each fact stays joined to the attribute value that was true when it happened. Type 1 is simpler; Type 2 is what you need for accurate point-in-time reporting.

Question 3

When should I use SCD Type 2 versus Type 4?

Accepted Answer

Use Type 2 when history is moderate and you want it inline with the dimension - it keeps point-in-time joins straightforward. Reach for Type 4 (a separate history/mini-dimension table) when a few attributes change very frequently and would otherwise explode the main dimension's row count, so you split the volatile attributes out and keep the core dimension small.

Question 4

Why do Type 2 dimensions cause a join 'fan-out'?

Accepted Answer

Because a Type 2 dimension has multiple rows per natural key (one per historical version), joining it to a fact on the natural key instead of the surrogate key multiplies rows and double-counts measures. The fix is to join facts to the surrogate key captured at load time, or to range-join on the effective-date window - never on the business key alone.

Slowly changing dimensions

Where it's taught

Related topics

Start learning slowly changing dimensions free