Slowly changing dimensions and temporal history

A customer’s region changes from West to South. A report may need the region at purchase time, today’s region for every past purchase, or both. Slowly changing dimension (SCD) policies encode that choice. A name correction and a sales-territory change can follow different policies in the same dimension.

One customer change, two reporting questions

Before the change, the customer dimension has one row:

The business key C42 identifies the customer in this example. The surrogate customer key 101 identifies a warehouse row. Type 2 will allocate another surrogate when it creates a new version.

Two sales make the reporting consequence visible. Amounts are illustrative currency units.

The total is always 250. The design decides whether June’s 100 belongs to West at sale time or South under today’s customer classification.

Type 1: overwrite the attribute

After the July change, the existing dimension row contains:

The key remains 101. Both sales can reference that row, so both now join to South. Reporting by current region gives South = 250. The dimension no longer contains West for this attribute.

Type 1 fits a corrected value or a reporting policy that deliberately applies the current classification to earlier facts. Audit history in a source log does not automatically become historical reporting through this dimension.

A Type 2 change at an exact boundary

Type 2 closes the old version and inserts a new one. All rows below describe customer C42, Asha Rao:

The customer identity stays C42; the version key changes from 101 to 205. NULL means the current version has no known end yet.

Use half-open intervals: row 101 covers 2026-01-01 <= sale_ts < 2026-07-01. Row 205 starts exactly at 2026-07-01. A sale at that instant matches 205 only. Dates here represent midnight UTC; production timestamps need a consistent timezone and precision.

When facts are resolved against their sale time, they receive these keys:

June’s fact keeps 101. July’s fact gets 205. Normal dimension changes leave those historical foreign keys intact.

The total agrees; the grouping answers a different question.

Types 0 and 3: original or limited alternate values

Type 0 retains an original attribute. If the column is named original_region, the July move leaves this row unchanged:

Both sales are grouped under the customer’s original region, giving West = 250. This does not claim that the customer still lives in West; the column’s meaning is explicitly the original value.

Type 3 adds a bounded alternate or previous-value column. A one-previous-value design would look like this:

The second row illustrates a later state of the same record, not two simultaneously stored dimension rows. With only these two region columns, West is no longer retained after September. Type 3 supports selected comparisons; it cannot reconstruct an unrestricted sequence of past classifications.

Types 4 and 5: keep changing profiles in a mini-dimension

Consider the same customer changing from a Low / Occasional spending profile to High / Frequent on 1 July. These profile attributes are handled separately from region in this example.

Kimball’s Type 4 assigns a surrogate to each distinct profile combination:

These are reusable combinations: many customers can reference profile 20. A profile row is not a customer’s individual history record.

The base customer row can retain key 101. Each sale stores both the customer key and the profile key that applied at sale time:

Joining through the fact’s profile key gives Low / Occasional = 100 and High / Frequent = 150. If every profile transition must be recorded, including periods without sales, the design needs a separate relationship/change record as well.

Type 5 adds a current-profile pointer to the base dimension. After July, overwrite that pointer:

The fact’s profile key still answers “which profile applied at sale time?” The customer’s current_profile_key answers “which profile applies now?” Following the current pointer groups both sales under High / Frequent = 250. Give historical and current profile columns distinct names in consumer models.

Types 4 and 5 here follow Kimball’s terminology. Some material calls a separate history table “Type 4”; describing the stored keys and join paths avoids that ambiguity.

Type 6: historical rows also carry current attributes

Type 6 retains Type 2 versions and adds current-value columns that are overwritten across all versions of the customer. After the July move:

S01 still joins to row 101. That row now exposes West historically and South currently. S02 joins to row 205 and sees South in both columns.

A later move to East would create another version and update current_region to East on every version for C42. The extra reporting flexibility comes with those repeated updates.

Type 7: facts carry historical and durable lookup keys

Type 7 supports the same two reporting views through explicit join paths. Give C42 a warehouse durable customer key 9001, shared by every version. A durable key identifies the entity across versions; customer keys 101 and 205 identify individual versions.

The dimension retains the Type 2 rows shown earlier, with customer_dk = 9001 on both. Consumers select a join path:

The current-row filter is essential. Joining on durable key alone matches both versions and duplicates each fact. A current view should expose at most one row per durable customer key.

Type 6 stores current attributes on historical rows. Type 7 keeps the reporting choice in the join and consumer view.

Choose the policy by the question

Publish the closed and new versions together

Closing the old version and inserting the new version need one database transaction or a controlled publication mechanism. Publishing only the closure creates a gap. Publishing the new version first can create two matches.

SELECT f.sale_id, d.customer_key, d.region
FROM sales AS f
LEFT JOIN dim_customer AS d
  ON d.customer_id = f.customer_id
 AND d.valid_from <= f.sale_ts
 AND (f.sale_ts < d.valid_to OR d.valid_to IS NULL);

A fact should match exactly one eligible dimension version under this model. A left join preserves unresolved facts for investigation; an inner join can silently drop them. Check for duplicate surrogate keys, multiple current rows, overlapping intervals and gaps where continuous coverage is required.

Late facts, late dimensions and corrections

A July sale arriving in September still needs the July-valid region. Joining to today’s current row rewrites its meaning. When the relevant dimension is missing, an unknown or inferred member can retain the fact until the actual attributes arrive. Record which facts need reconciliation.

A correction backdated to March may split an existing interval and require rekeying or restating affected facts. Rebuilding only the current row leaves history wrong. Process all relevant versions in source order, and define whether a correction changes historical truth or only fixes a previously recorded mistake.

Valid time describes when the value applies in the business. System time records when the platform knew it. A bitemporal design retains both, allowing “what was true then?” and “what did our report believe at that time?” to differ.

dbt snapshots preserve detected row changes using the configured strategy. A periodic snapshot cannot recover every intermediate source change between observations. Complete change history may require a retained change-data-capture (CDC) feed.

Estimate history growth

For an illustrative fixed population of two million customers, assume 2% receive one new Type 2 version per month. That adds 40,000 rows monthly and 960,000 over 24 months: 2.96 million total rows before retention changes. At an assumed 300 bytes per stored row, the logical payload is about 888 MB; indexes, compression, replicas and variable-width values change physical storage.

Measure actual change frequency per tracked attribute. A volatile “last seen” field can create a new dimension version for almost every event; moving that observation to an event fact may better express the domain.