What Is a Slowly Changing Dimension? SCD Types Explained

In dimensional data modeling, a slowly changing dimension (SCD) is a dimension table whose attribute values change over time. The "slowly" is relative: customers change addresses, employees change departments, products change categories. These changes happen far less frequently than transactional events, but they do happen — and how you handle them determines whether your historical analysis accurately reflects what was true at the time, or silently rewrites history as source data evolves.

The SCD problem is this: if a customer moves from New York to Chicago in March, and you want to report regional revenue for Q1, which region should their January and February orders be attributed to? The region they were in at the time of purchase (New York), or the region they are in now (Chicago)?

The answer depends on your analytical requirements. SCD types are the set of standard patterns for handling this choice.

SCD Type 1 — Overwrite

In SCD Type 1, when an attribute changes, the old value is overwritten with the new value. The dimension table always reflects the current state; no history is preserved.

A customer's region changes from Northeast to Southeast. The row in dim_customers is updated: region = 'Southeast'. Historical orders that joined to this customer now reflect the new region. The January and February orders are now attributed to Southeast, even though the customer was in Northeast at the time.

**Use when**: Historical accuracy for this attribute is not required. For attributes like customer email address (for sending current communications), correcting an error (a typo in a product name), or attributes that have no historical analytical meaning, Type 1 is appropriate.

**Risk**: Any historical analysis that filters or groups on this attribute is retroactively changed whenever the attribute is updated. If your monthly revenue by region report changes next month because a customer moved, Type 1 is wrong for that use case.

SCD Type 2 — Add a New Row

In SCD Type 2, when an attribute changes, a new row is inserted into the dimension table representing the new version, while the original row is preserved. Each row has metadata tracking its validity period.

The standard Type 2 implementation adds three columns:

- **effective_date** (or valid_from) — the date the row became current

- **expiry_date** (or valid_to) — the date the row was superseded (null or a far-future date for the current row)

- **is_current** — boolean flag indicating whether this is the active row

When a customer moves from Northeast to Southeast, the existing row is updated: expiry_date = '2024-03-01', is_current = false. A new row is inserted: region = 'Southeast', effective_date = '2024-03-01', expiry_date = null, is_current = true.

Historical analysis requires joining fact tables to dimension tables at the correct point in time — a "point-in-time join" that finds the dimension row where the fact table's date falls within the dimension row's validity window:

FROM fact_orders f

JOIN dim_customers c

ON f.customer_key = c.customer_key

AND f.order_date >= c.effective_date

AND (f.order_date < c.expiry_date OR c.expiry_date IS NULL)

This join returns the dimension attributes as they were at the time of each order — January orders join to the Northeast row, post-March orders join to the Southeast row. Historical analysis is accurate.

**Use when**: Historical accuracy is required for the attribute — customer demographics, geographic region, customer segment, product category, employee department. Type 2 is the correct default for dimensions where attributes change and where historical analysis must reflect the state at the time of the transaction.

**Complexity**: Type 2 adds rows to dimension tables (every change creates a new row), complicates joins (point-in-time join logic instead of simple key join), and requires careful handling of the "current" row in dashboards that only need current attributes.

SCD Type 3 — Add a Previous Value Column

In SCD Type 3, instead of adding rows, a column is added to the dimension table for each prior value being tracked. A dim_customers table might add previous_region and region_change_date columns alongside the current region.

When a customer moves, region is updated to the new value, previous_region is set to the old value, and region_change_date records when the change occurred.

**Use when**: You need to track one specific attribute change but not a full history. Useful for "as-of" analysis (report revenue using the region customers had 90 days ago as a comparison period) without the complexity of full Type 2.

**Limitation**: Tracks only one level of history per attribute. If a customer moves twice, only the most recent prior value is preserved. Not appropriate for attributes that change repeatedly or where more than one historical state is needed.

SCD Type 4 — History Table

In SCD Type 4, the main dimension table always reflects the current state (like Type 1), and a separate history table records all changes with timestamps. The main table is clean and fast for current-state queries; the history table is available for historical analysis.

**Use when**: Current-state queries are the dominant use case and must be fast, but occasional historical analysis is also needed. Separating current from historical reduces complexity for the common case.

SCD Type 6 — Combined (2 + 3 + 1)

Sometimes called "Hybrid SCD," Type 6 combines aspects of Types 1, 2, and 3. The dimension table has both the full row-per-change history (like Type 2) and additional columns for the original value and the most recent value on every row. This makes both current-state and historical queries efficient without requiring point-in-time joins for the common case.

**Use when**: Both current-state queries (all customers with current region = Southeast) and historical queries (January orders attributed to region at time of purchase) are frequent and performance is critical. The extra columns add storage overhead but reduce join complexity.

Implementing SCDs in Modern Data Stacks

In dbt, Type 2 SCDs can be implemented using the dbt_utils or dbt_scd packages, or using dbt's built-in snapshot functionality. dbt snapshots capture the current state of a source table at each run and maintain a history table with effective_date, expiry_date, and is_current columns — automating the Type 2 update logic.

dbt snapshots are the standard implementation pattern for Type 2 dimensions in modern ELT pipelines. They run against source staging tables, produce the SCD history table, and downstream mart models join to the snapshot table using the point-in-time join pattern.

Our data architecture practice designs dimensional data models with appropriate SCD patterns for analytical accuracy — contact us to discuss data warehouse modeling for your environment.

A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.