How to Track History in a Data Warehouse (Slowly Changing Dimensions)

A slowly changing dimension is a dimension table whose descriptive values change over time: a customer moves to a new state, a company renames itself after a merger, a product changes color. You track that history by picking one of two approaches. Type 1 overwrites the old value and keeps only the current one. Type 2 keeps every version as its own row, which gives you history but means the same ID now appears more than once. Type 2 works when you add a few metadata columns, an active from date, an active to date, an is active flag and a surrogate key, and join on them instead of the ID alone. Add a deleted at timestamp and an is deleted flag and you can track removals from the source too.

What a slowly changing dimension is

Picture a fact table in the middle of a star schema, say fct_order_transactions. A fact is something that happened, and generally it happens one time.

A purchase comes through with a customer ID on it. That ID joins to dim_customers to bring in the name, the location and everything else about that customer.

The dimension is where things change. The customer was in one state and moves to another. There's a merger and the company name is completely different. The join itself doesn't change, you still have the ID.

The question a join has to answer

The question is what you want to see when you bring the two together. Only the current value, or the value that was in place at the time of the transaction?

If a report summarizes sales by location and the location changed, which bucket does that sale fall into? That's what a slowly changing dimension handles: a dimension value changed over time and the warehouse needs to account for it.

The four things a batch can contain

Set aside the staging layer for a moment and think about a source table on one side and a dimension table on the other. Each time a batch of records arrives, every record falls into one of four cases:

• Unchanged. It already exists in the dimension and nothing is different.
• New. It isn't in the dimension yet and needs to be inserted.
• Updated. It exists already but a value has changed, like the customer who moved.
• Deleted. It's in the dimension but no longer exists in the source.

Everything below is about handling those four cases.

Don't brush past the simple example. Until you can explain it on a table with four rows, you can't expect to get it right on anything bigger.

A simple example

The source has three columns: id, color and created_date. Call it a product table.

The first time you load it, every record is new. The dimension looks exactly like the source, and the join to the fact table is one to one on product_id.

The second batch

Now the next batch arrives and record 3, which used to be green, is purple. It existed already and it has changed.

There are two main ways to handle that in the dimension, and those are the two types of slowly changing dimension you'll use most often.

Type 1: overwrite the value

In a type 1 slowly changing dimension you overwrite the value with whatever the current one is. Record 3 becomes purple and the green version is gone.

The first company I worked at took this approach everywhere: use the latest record, keep that in the warehouse, move on. It worked. There were no major issues and it got the job done.

What you give up is any ability to look back. If a report needs to show what things looked like when a transaction happened, or compare before and after, type 1 can't answer it.

Type 2: keep every version

In a type 2 slowly changing dimension you keep both records. The original row where 3 was green stays, and a second row for 3 is inserted with purple. Now you have history.

Where the duplicates come from

This is where the complexity starts. Go back to the join from the fact table. ID 3 now shows up twice in the dimension, so the join brings both rows in, your numbers double, and your stakeholders tell you everything is off.

This happens a lot when teams first move to type 2. You get more options, and you need a few more columns to use them safely.

Columns that make type 2 workable

The columns you add here are metadata: values generated inside the warehouse to track what's happening, not values that come from the application. I usually give them a dw_ prefix so it's obvious which columns are ours.

dw_active_from

When this version of the record became active. You could use a created date, but most of the time I use the batch time, the timestamp of the run that brought the record in.

dw_active_to

When this version expired. For the current record people either leave it null or populate it with an arbitrary far future date as a default. I usually do the far future date.

dw_is_active

A boolean flag that says whether this is the latest record. There will only ever be one active row per ID at a given time.

Applied to the example

Record 3 now has two rows in the dimension:

• The green version. Active from its original load, active to March 15 when the next batch found the purple record, and is active set to false.
• The purple version. Active from March 15, an open ended active to, and is active set to true.

Populating these takes some SQL to find the latest record and close out the previous one, but at the end of the day it's updates and inserts.

How the join changes

When you create a fact record and look up its dimension key, you don't join on ID alone, you join on ID where the row is active:

select ...
from fct_order_transactions f
join dim_products d
  on d.product_id = f.product_id
 and d.dw_is_active = true

That tells you what the latest record was at the time of the action.

And because the dates are stored, you can also query the other direction: what was the value at a certain point in time, or within a certain window, using the active from and active to columns.

Surrogate keys

There's still one gap. If the primary key is id, you now have two rows with the same one.

When you stamp the fact table with a dimension key, you want something unique you can look up directly without repeating all of those join conditions every time, including further down in the marts layer.

What a surrogate key is

That's a surrogate key: an internal key you create inside the warehouse. It has no real world meaning and isn't tied to any application. It exists for three things:

• Joins
• Data quality
• Giving every record a unique value

Generating one with a hash

The usual way to generate one is a hash. Most databases have md5 built in. Hash every column of the row together and you get a key.

Change a single value and the hash is different, so each version of record 3 gets its own surrogate key even though the ID is the same.

Once that's in place, you store the surrogate key on the fact table and every join back to the dimension lands on exactly one record.

I'd add surrogate keys to every dimension table whether it's type 1, type 2 or neither. If you do it once, do it everywhere for consistency.

Handling deletes

Updates are only part of it. Say the next batch of source data no longer contains ID 2. It's been deleted from the application.

With type 1 you'd probably delete it from the dimension too, and now there's no way to get back to it.

Two more columns

What I recommend is two more metadata columns:

• dw_deleted_at. A timestamp of when you detected the delete.
• dw_is_deleted. A boolean flag.

A query compares the dimension to the source, sees that ID 2 exists in the dimension but not in the source, stamps the timestamp and sets the flag to true.

What happens to the active flag

This is a matter of preference, and it should make sense to you and your team.

My approach is to keep the idea of active tied to the most recent record. Even though ID 2 is deleted, that row is still the most recent version of that data, so dw_is_active stays true. The deleted flag is what tells you it's gone.

The fact join then becomes: on ID, where is active is true and is deleted is false. Flip that to where is deleted is true and you see every deleted record instantly.

The simpler alternative

If you'd rather not deal with deleted flags, you can mark the record inactive and leave it there. You won't be able to tell a delete from an update, but it's simpler.

Either way, flags, surrogate keys and delete tracking give you control over the slowly changing parts of your data.

Related reading

• The 4 Types of Dimensions
• The 3 Types of Facts
• The "Key" To Building A Reliable Data Model
• dbt Snapshots, Workflow and More

Final takeaway

Nearly every data team I've worked with has hit the moment where a stakeholder asks what something looked like six months ago and the warehouse can't answer. This is the pattern I put in place so that question has an answer, and it's the same set of columns whether the warehouse is on Snowflake, BigQuery or Postgres.