Slowly changing dimension (SCD)

A slowly changing dimension (SCD) is a data warehouse dimension whose attributes, such as a customer’s address or income band, change over time. SCD types specify how the warehouse records those changes.

Example

For illustration, suppose a customer’s income band is recorded as $40,000–$59,999 when a loan is approved, then changes to $60,000–$79,999 later. If Type 1 overwrites the old band, a report on last year’s approvals shows today’s band, not the band the decision saw.

How the common SCD types handle lending data

A lender corrects a customer’s mailing address. Type 1 replaces the stored address, so the earlier address is lost.

If a customer moves, Type 2 adds a row for the new address with valid-from and valid-to dates. The earlier row remains to represent the earlier period.

A lender might keep a customer’s previous contact preference in a separate column beside the current one. That is Type 3, which keeps one earlier value.

Why Type 2 history matters for credit analysis

Suppose, for illustration, a loan decision is dated 15 March and the customer’s income band changes on 1 June. A Type 2 join can select the row whose valid dates cover 15 March, so the analysis uses the band recorded for that decision date.

A month-end analysis can match that date to the row valid then. This keeps the comparison tied to the attributes that applied at that point, rather than applying today’s attributes to earlier periods.

Questions we get asked

Does a Type 2 SCD need a current-row flag?

Not necessarily. Type 2 uses valid-from and valid-to dates, and a current-row flag is often included.

What if a Type 2 value changes back to an earlier value?

Type 2 adds a new row for each change, so a return to an earlier value is represented by another dated row. The earlier row remains for its original period.

What does the valid-to date mean in a Type 2 row?

It marks the end of the period represented by that row. Read it with the valid-from date to find which version applies on the date being analyzed.

Type 1 overwrites the old value, so history is lost, and the lending data warehouse architecture article explains how warehouse layers preserve data history and decision records.

Related topics: