When should I use Type 1 instead of Type 2?

Use Type 1 when the old value is not analytically meaningful, such as fixing a spelling error or keeping only the current operational state. Use Type 2 when the value at event time matters. The honest answer depends on the question the data must answer and whether future values would distort historical results.

How does a Type 2 join avoid data leakage?

A Type 2 join uses both the entity key and time. The fact’s event or prediction timestamp must fall between the dimension version’s effective start and end. That prevents a training row or backtest from seeing a later classification, plan, tier, or status that was not yet known.

What makes Type 2 dimensions hard to maintain?

The difficult part is keeping version ranges correct as changes arrive. Each tracked change should close the previous row and create a new valid row without overlaps or gaps that break joins. Late-arriving updates, source corrections, and unclear business rules can make the validity timeline messy unless merge logic is explicit.

Slowly Changing Dimensions

Slowly Changing Dimensions are dimension-table patterns for representing attributes that change over time, either by replacing the old value or by keeping dated versions. They let engineers decide whether a fact should see the current attribute value or the value that was valid when the fact happened.

The problem is that many descriptive attributes are not stable. A customer changes address, a merchant moves risk tier, a subscription plan changes, or a product is reclassified. If historical events are joined to today’s dimension row, analysis can rewrite the past. In feature engineering this is worse than a reporting bug: it can leak information that was not available when a prediction would have been made.

Type 1 handles change by updating the existing dimension row. The business key still has one row, and the new attribute value replaces the old one. Type 2 handles change by inserting a new versioned row instead. Each version has an effective start and end time, often plus a current-row marker. A fact joins to the dimension by business key and by checking that the event time falls inside the version’s validity window.

The trade-off is between simplicity and historical correctness. Type 1 is easy to maintain and query, but it cannot answer what was true at an earlier time. Type 2 supports point-in-time joins, but it adds storage, more complex merge logic, and failure modes around overlapping or missing validity windows. A common misunderstanding is that every changing field needs Type 2 history. It depends on whether past values affect reporting, training, backtesting, or auditability.

Engineers meet Slowly Changing Dimensions in warehouses, lakehouse tables, dbt models, feature pipelines, and batch ingestion jobs that merge source-system changes. Practical work includes choosing tracked attributes, detecting changes, closing the previous version, opening the new version, and making downstream joins time-aware. The dangerous shortcut is joining facts only on the entity key, which silently gives old events a value from the future.

Go deeper

Everything DataSlowly Changing DimensionsModel changing attributes using Type 1 and Type 2 slowly changing dimensions for point-in-time feature correctness.Open the lesson

Common questions

When should I use Type 1 instead of Type 2?: Use Type 1 when the old value is not analytically meaningful, such as fixing a spelling error or keeping only the current operational state. Use Type 2 when the value at event time matters. The honest answer depends on the question the data must answer and whether future values would distort historical results.
How does a Type 2 join avoid data leakage?: A Type 2 join uses both the entity key and time. The fact’s event or prediction timestamp must fall between the dimension version’s effective start and end. That prevents a training row or backtest from seeing a later classification, plan, tier, or status that was not yet known.
What makes Type 2 dimensions hard to maintain?: The difficult part is keeping version ranges correct as changes arrive. Each tracked change should close the previous row and create a new valid row without overlaps or gaps that break joins. Late-arriving updates, source corrections, and unclear business rules can make the validity timeline messy unless merge logic is explicit.

Every term