Slowly Changing Dimensions (SCD)

A "Slowly Changing Dimension" is a dimension whose attribute values change occasionally over time - a customer moves to a new address, a product is renamed, a store is reassigned to a new region etc. The changes are infrequent and unpredictable, unlike facts which arrive constantly.

The "SCD Types" are the standard techniques for deciding what to do when a source value changes: ignore it, overwrite it, or keep the old value alongside the new one.

SCD Types apply to attributes, not tables

Engineers often label an entire table as a "Type 2 dimension", but the type is actually chosen per column. For example, a single dim_customer table can freeze its sign-up date, overwrite its email, and track the full history of its address. This applies three different techniques within the same table.

Type 0: Retain Original

With Type 0, the dimension attribute value never changes. Once it is written, it remains static forever.

Example (Type 0)

Alice signed up in New York (NY) on a specific date. She later moves to California (CA). Her original_state and sign_up_date are Type 0 attributes.

customer_id	name	original_state (Type 0)	sign_up_date (Type 0)
101	Alice	NY	2023-01-15

After the move: The row remains exactly the same. Even if the source system updates her state to CA, the data warehouse intentionally discards that change for the original_state attribute.

Type 0 Attributes vs Pure Type 0 Dimensions

Type 0 is generally applied in two distinct ways:

Type 0 Attributes

Individual attributes are locked when the row is inserted, while other attributes in the same row can still change. Examples:

• original_sign_up_date
• account_creation_branch
• date_of_birth

Pure Type 0 Dimensions

Every column is locked, meaning a row is never updated after insertion. This is common in three scenarios:

• Date and Time Dimensions (dim_date, dim_time): The attributes of a specific date never change. For example, January 1, 2024, will always be a Monday and a public holiday. Once generated, this table is completely static.
• Static Reference Data (dim_country_code, dim_currency): These are lookup tables defining fixed business concepts or external standards. The definition of "USD - US Dollar" does not change over time.
• Immutable Event Profiles: These dimensions record the exact state of an event the moment it happens and cannot be altered. For example, a dim_checkout_context table capturing the device, app version, payment method and promo code used for a specific order.

Error corrections do not count as SCD changes

Type 0 means the real-world value never changes, not that the row is never touched. If a sign_up_date was entered incorrectly, it still gets fixed. Correcting a mistake is not considered a true change because the old value was never accurate to begin with.

Type 1: Overwrite

With Type 1, the old attribute value in the dimension row is directly overwritten with the new value. The attribute always reflects the most recent assignment, meaning this technique intentionally destroys historical context.

While this approach is easy to implement and avoids adding new rows, it comes with a major caveat: history is restated. If you use a Type 1 attribute to group or filter historical data, past events will look as if the new value was always true. Furthermore, you must also ensure that any downstream aggregate tables, OLAP cubes, and materialized views affected by this change are recomputed.

Example (Type 1)

A product manager decides to move the "Smartwatch Pro" from the Accessories department to the Wearables department. With Type 1, we simply overwrite the old department in the existing row.

Before the change:

product_key	sku	product_name	department (Type 1)
8801	SW-P-01	Smartwatch Pro	Accessories

After the change:

product_key	sku	product_name	department (Type 1)
8801	SW-P-01	Smartwatch Pro	Wearables

Notice what did not change: product_key is still 8801, so every existing fact row keeps pointing at this same product. No fact updates are needed - that is what makes Type 1 cheap.

The trade-off is that past Accessories sales are now understated, and Wearables sales are overstated for those same periods. A report run last month and rerun today will show different department totals, with nothing in the warehouse explaining why. There is also no timestamp, so the warehouse can't answer "when" the department changed.

When to use Type 1

• Error Corrections: Overwriting typos or bad data entry. The old value was never true, so there is nothing to preserve. This applies even to attributes that are logically Type 0 - a wrongly entered date of birth is still corrected in place.
• Current-State Only Attributes: Attributes where the business strictly does not care about past values, or where restating history is the explicitly desired outcome. Examples include phone_number, email_address, preferred_language etc.

Type 2: Add New Row (Historical Tracking)

With Type 2, when an attribute changes, a brand-new row is inserted into the dimension table with the updated values. The old row is preserved but marked as expired. This is the primary technique for perfectly preserving history.

This requires the dimension table to use a Surrogate Key (a unique, warehouse-generated ID) as its primary key, because the natural business key (like customer_id) will now appear on multiple rows.

When a new row is created, a new surrogate key is assigned. Fact tables will use this new key for all incoming records until the next change occurs.

WARNING

When a Type 2 change creates a new row, the remaining columns are copied from the old row. If the table also contains Type 1 attributes, any future Type 1 updates must be applied to every row sharing the natural key, not just the current one, to prevent inconsistent data.

Required Metadata Columns

A minimum of three additional columns must be added to a Type 2 dimension to track the timeline:

• effective_date: The date/timestamp when this specific row became active.
• expiration_date: The date/timestamp when this specific row was replaced. For the current active row, this is often set to a far-future date (like 9999-12-31). This is a Kimball best practice because it allows fact tables to join using simple, fast BETWEEN logic. Alternatively, some systems leave this as NULL to represent "no expiration yet", though this requires more complex OR expiration_date IS NULL logic in downstream SQL.
• is_current: A simple Boolean indicator (True / False) that makes it easy to filter queries to only the latest state.

Inclusive End vs Exclusive End

When setting a row expiration value, using an exclusive end is a common best practice. While Kimball recommends this approach for timestamped dimensions, the right choice actually depends on your column type.

• Timestamps: Exclusive, always. An inclusive approach is unworkable because the exact instant before "2024-05-10 00:00:00" has no clear representation. Using "2024-05-09 23:59:59" silently drops sub-second data, and adding milliseconds breaks if your database precision changes. An exclusive end avoids this problem completely. The old row expires at exactly "2024-05-10 00:00:00", and the new row starts at that exact same moment.
• Dates: Inclusive end remains very common. Using consecutive days like "2024-05-09" and "2024-05-10" is unambiguous and reads naturally to business users. It also allows analysts to easily use the BETWEEN operator for queries.

Example (Type 2)

Alice moves from New York (NY) to California (CA) on May 10, 2024. state is a Type 2 attribute.

Before the change:

customer_key	customer_id	name	state (Type 2)	effective_date	expiration_date	is_current
1	101	Alice	NY	2023-01-15	9999-12-31	True

After the change:

customer_key	customer_id	name	state (Type 2)	effective_date	expiration_date	is_current
1	101	Alice	NY	2023-01-15	2024-05-09	False
2	101	Alice	CA	2024-05-10	9999-12-31	True

Notice how the keys work: The natural customer_id (101) stays the same, but the new row gets a new customer_key (2).

Any fact records (like sales) occurring before May 10 were already written using customer_key 1, meaning they will forever roll up to New York. Any new sales from May 10 onward will be written using customer_key 2, rolling up to California. History is perfectly segmented.

When to use Type 2

• Point-in-Time Accuracy: When you need the exact state of an item the moment an event happened. For example, a user who wrote a post as a "Beginner" must stay tied to "Beginner" for that specific post, even after they become a "Moderator."
• Reproducible Reporting: When past reports must never change. If a number was correct last quarter, it must remain exactly the same when someone reruns the report next year. This is often non-negotiable for audits and compliance.
• Tracking Duration: When you need to measure how long something lasted. Because Type 2 adds start and end dates, the dimension itself can answer how long a user stayed in a specific tier or how long a product held a certain price.
• The Default Choice: When you are unsure if history matters. It is much safer to preserve history now than to overwrite it with Type 1 and later realize you destroyed data you needed.

Type 3: Add New Attribute (Limited History)

With Type 3, instead of adding a new row, a new column is added to the existing row to track the previous value. The primary column is then updated with the current value, similar to a Type 1 overwrite.

This technique creates an "alternate reality" for reporting. It allows business users to group and filter fact data by either the current value or the previous value. Because it only preserves a single step of history rather than a complete timeline, this technique is used relatively infrequently.

Example (Type 3)

A sales representative is reassigned from the East district to the North district. district is managed using a Type 3 approach so the business can compare performance using both the old and new organizational structures.

Before the change:

employee_key	employee_id	name	current_district	previous_district (Type 3)
505	E-99	Bob	East	NULL

After the change:

employee_key	employee_id	name	current_district	previous_district (Type 3)
505	E-99	Bob	North	East

Notice that no new rows are created and the employee_key remains unchanged. All existing fact records still point to this single row. Analysts can now run reports showing sales grouped by the new North district, or switch to the alternate reality to see the same sales grouped by the old East district.

When to use Type 3

• Classification Changes: When a business classification or grouping changes, and the business needs to analyze facts using both the current and previous classifications.
• Dual Perspectives, Not Timelines: When you need to preserve a limited number of versions of an attribute (typically the current and previous value), rather than maintaining a complete history of every change. If you need point-in-time accuracy and the ability to determine which value was valid at any past date, use Type 2.

Type 4: Add Mini-Dimension / Current + History

The "Type 4" label has two meanings in practice. In Ralph Kimball's methodology, Type 4 means using a Mini-Dimension for rapidly changing attributes. In some data engineering teams, "Type 4" is instead used to describe separating current data from historical data into two tables.

The Kimball Definition (Mini-Dimension)

This design addresses the "rapidly changing monster dimension" problem, where frequently changing attributes such as credit scores can cause explosive row growth and degraded query performance when Type 2 history is maintained in a large dimension.

Type 4 separates relatively stable attributes from rapidly changing attributes into two dimensions that both connect directly to the Fact table:

• Base Dimension: Holds relatively stable descriptive attributes, such as names, dates of birth, or other attributes that change infrequently. Its primary key identifies the individual entity.
• Mini-Dimension: Holds rapidly changing attributes, often represented as discrete bands or groups, such as age band, income band or credit-score band. Because similar entities share the same attribute combinations, the Mini-Dimension typically contains far fewer rows than the Base Dimension and can have its own primary key, independent of the Base Dimension key. It does not need to identify individual entities.

When a business event occurs, the Fact table records the Base Dimension key to identify the entity and the Mini-Dimension key to capture its rapidly changing profile at the time of the event.

Example (Type 4: Kimball Mini-Dimension)

The following example shows a fact table that stores both Base Dimension Key and Mini Dimension Key:

fact_sales_id	base_customer_key	mini_demographic_key	amount
9901	101 (Alice)	Profile X (Age 20-30, High Income)	$50.00
9902	101 (Alice)	Profile Y (Age 30-40, High Income)	$75.00

The Industry Definition (Current + History Tables)

Some data teams use "Type 4" for a different pattern: separating the latest version of a record from its historical versions.

The Current Table holds only the latest version, while the History Table preserves previous versions. This keeps current-state queries fast but adds pipeline complexity because both tables must remain synchronized.

NOTE

A temporal column is simply a column that stores dates or timestamps to track when a record was valid, active, or occurred.

The History table must have at least two temporal columns to track the period during which each version was effective:

• valid_from: The date/timestamp when this specific version became effective.
• valid_to: The date/timestamp when this specific version stopped being effective.

NOTE

As with Type 2's row-expiration column, whether valid_to is inclusive or exclusive depends on the column's data type and the convention adopted by the data model.

Example (Type 4: Current + History)

Alice moves from New York (NY) to California (CA) on May 10, 2024.

Current Table:

customer_id	name	state
101	Alice	CA

History Table:

customer_id	name	state	valid_from	valid_to
101	Alice	NY	2023-01-15	2024-05-09

Type 5: Mini-Dimension (Type 4 + Type 1)

This is a hybrid approach. The fact table joins to a primary dimension (Type 1) for the current profile, while also joining to a separate historical "mini-dimension" (Type 4) that tracks rapidly changing demographic attributes over time.

• Use Case: When dealing with very large dimensions (e.g., 50 million customers) where tracking history with Type 2 would cause explosive, unmanageable table growth.

Type 6: Hybrid Configuration (1 + 2 + 3)

Type 6 intelligently combines the techniques of Types 1, 2, and 3 (and mathematically, 1 + 2 + 3 = 6).

• Mechanism: A new row is added for historical tracking (Type 2). However, every row for that entity also contains a "current attribute" column. When a change happens, this "current" column is overwritten (Type 1) across all historical rows for that entity. It may also keep a "previous" attribute column (Type 3).
• Impact: It allows users to query historical facts using either the historical attribute value (what was true at the time) or the current attribute value (what is true today), all without complex SQL logic.

Type 7: Dual Dimension Keys

In this advanced hybrid, the fact table contains two foreign keys for the exact same dimension entity.

• Mechanism: One key is a durable business key that always points to the "current" Type 1 dimension profile. The second key is a surrogate key that points to the "historical" Type 2 dimension row that was active at the time the fact event occurred.
• Impact: Similar to Type 6, this allows seamless reporting on either the "as-was" historical state or the "as-is" current state, but it resolves the complexity by leveraging two foreign keys in the fact table rather than constantly overwriting dimension rows.