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 (e.g., effective_date or valid_from): The date/timestamp when this specific row became active.
• Expiration Date (e.g., expiration_date or valid_to): 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 valid_to IS NULL logic in downstream SQL.
• Current Indicator (e.g., is_current or active_flag): 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)	valid_from	valid_to	is_current
101	C-101	Alice	NY	2023-01-15	9999-12-31	TRUE

After the change:

customer_key	customer_id	name	state (Type 2)	valid_from	valid_to	is_current
101	C-101	Alice	NY	2023-01-15	2024-05-09	FALSE
102	C-101	Alice	CA	2024-05-10	9999-12-31	TRUE

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

Any fact records (like sales) occurring before May 10 were already written using customer_key 101, meaning they will forever roll up to New York. Any new sales from May 10 onward will be written using customer_key 102, 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.

Handling Late Arriving Facts

A late-arriving fact occurs when a fact arrives in the data warehouse after the relevant dimension has changed. The current dimension row may therefore no longer represent the context that was valid when the business event occurred.

For Type 2 Slowly Changing Dimensions, historical versions of dimension members are preserved. When processing a late-arriving fact, the ETL process should identify the dimension row that was effective when the event occurred, rather than simply using the current row.

For example, suppose customer C101 had the following history:

customer_key	customer_id	address	effective_date	expiration_date
101	C101	Hyderabad	Jan 1	Jan 12
205	C101	Bangalore	Jan 12	NULL

A sale for C101 occurred on January 10, when the customer lived in Hyderabad, but the sale record does not arrive in the warehouse until January 15.

Although the current customer record is customer_key = 205 (Bangalore), the sale must reference customer_key = 101 because that dimension version was effective when the sale occurred.

order_id	transaction_date	processing_date	customer_key	sales_amount
ORD-100	Jan 10	Jan 15	101	$500

This ensures the sale is correctly associated with Hyderabad, preserving the dimensional context that existed at the time of the transaction.

Key principle: For a late-arriving fact, use the dimension version that was effective when the business event occurred, not the version that is current when the fact arrives.