Slowly Changing Dimensions

​

Introduction

Slowly Changing Dimensions (SCDs) are a common data modeling technique used to manage historical changes in dimension data over time. This enables more accurate time-based analysis and reporting, such as understanding how KPIs were affected under previous attribute values.

SCDs are categorized into different types based on how they handle changes to dimension data:

​

SCD Type 0 - Fixed Dimensions

• No changes allowed. The data remains as it was when first inserted.
• Useful when historical accuracy is critical and the value should never change.
• Example: A product’s original launch date.

​

SCD Type 1 - Overwrite

• Changes overwrite existing data. No history is preserved.
• Simple to implement but loses historical context.
• Example: If a customer changes their email, the old one is replaced.

​

SCD Type 2 - Historical Tracking

• Each change creates a new record, often with start/end timestamps or versioning.
• Preserves full history of changes.
• Example: Tracking changes to a customer’s loyalty tier over time.

​

SCD Type 3 – Previous Value

• Stores only the previous value alongside the current one.
• Limited history, useful when only one change needs to be tracked.
• Example: Keeping a “current region” and a “previous region” field for a customer.

​

Modeling SCDs in Honeydew

In Honeydew, the modeling of SCDs of Types 0, 1, and 3 is straightforward. Joins between entities are defined using the standard foreign key relationships.

For SCD Type 2, Honeydew supports modeling SCDs using a combination of foreign keys and date ranges. Joins between entities are defined using a custom SQL expression that includes the date range logic.

​

Example: Fact and a Slowly Changing Dimension (SCD2)

Given two tables:

• fact_sales: a fact table tracking order transactions that has a foreign key customer_id
• dim_customer: a dimension table tracking customer information over time (SCD Type 2). It has multiple entries per customer_id with validity ranges.

Would want to connect the sales data with the correct historical customer information as it was at the time of the order.

​

Sample data

fact_sales (Fact Table)

dim_customer (SCD2 Dimension Table)

​

Relations

To associate each order with the correct customer version at that point in time, use a custom SQL expression on valid_from and valid_to:

fact_sales.customer_id = dim_customer.customer_id
AND fact_sales.order_date >= dim_customer.valid_from
AND fact_sales.order_date < dim_customer.valid_to

And set a “many-to-one” relationship from fact_sales to dim_customer

​

Example query

Result of a query on both:

​

Advanced: Multiple SCD2 (Fact and Dimension) + Point-in-Time Reference point

Advanced use cases for slowly changing dimensions allow to inspect the state of the world at any point in time (including “now”), while every data table has slowly changing dimension fields.

Here, the previous example is extended to support of consistent point-in-time queries on historical data where:

• fact_sales: a fact table with changing business logic over time (e.g. updated amount, revised status). It has multiple versions per order_id, each valid over a time range.
• dim_customer: a dimension table with customer history over time (e.g. changed region), also with validity ranges.

A central dim_date or dim_point_in_time table is used to filter everything as of a specific point.

This structure is used in auditable data models, financial snapshots, and analytics platforms.

​

Sample data

fact_sales sample data:

dim_customer sample data:

dim_point_in_time: A joint reference point for all data

This is used to filter time centrally, so other joins respect that single reference point. This table can cover all possible dates.

​

Relations

Fact to customers

To associate each order with the corresponding version of the customer that was valid at the time the order version was valid, use the following relation:

1. Join on customer key and validity ranges
2. Direction: Many to one (from dim_customer to point in time)
3. Cross-filtering is as needed (one-to-many or bi-directional)

Relation:

fact_sales.customer_id = dim_customer.customer_id
AND fact_sales.valid_from >= dim_customer.valid_from
AND fact_sales.valid_from <  dim_customer.valid_to

The result of that relation is that customers are resolved to the right appropriate customer to the time of the order, while keeping multiple versions of the order.

Entities to point in time reference

1. fact_sales to dim_point_in_time:

2. Many to one (from fact_sales to point in time)

3. Cross-filtering is one-to-many (dim_point_in_time can filter the fact, but not vice versa)

Relation:

dim_point_in_time.snapshot_date >= fact_sales.valid_from
dim_point_in_time.snapshot_date <  fact_sales.valid_to

2. dim_customer to dim_point_in_time:

3. Many to one (from dim_customer to point in time)

4. Cross-filtering is one-to-many (dim_point_in_time can filter dim_customer, but not vice versa)

Relation:

dim_point_in_time.snapshot_date >= dim_customer.valid_from
dim_point_in_time.snapshot_date <  dim_customer.valid_to

​

Example query

Status of all orders given reference point of 2021-06-15

• Only one valid version per order_id and customer_id is active per point-in-time
• Any rows not yet valid are excluded (e.g. 5002 is not visible on 2021-06-15)

Status of all orders given reference point of 2022-05-01