Type 2 Slowly Changing Dimensions: Versions, Validity and Lookups

A customer moves from Oslo to Bergen on the eleventh of March. An order placed on the eighth of March must still report Oslo; an order placed on the twelfth must report Bergen. A report that filters on Bergen and includes February’s revenue has answered a question nobody asked, because it has projected the customer’s current region backwards over history.

Type 2 is the dimension pattern that preserves both readings. It stores a new row for the customer at each change rather than overwriting the old one, and the fact table’s foreign key decides which version each fact refers to. Everything difficult about the pattern follows from that one indirection.

The shape of a versioned row

A type 2 dimension row carries, beyond the attributes, four pieces of metadata:

• a surrogate key, unique per version, assigned by the warehouse rather than inherited from the source;
• a natural key, the source’s identifier, repeated across all versions of the same entity;
• a validity interval, usually valid_from and valid_to timestamps or dates;
• a current flag, either a boolean or a sentinel valid_to such as 9999-12-31.

CREATE TABLE dim_customer (
  customer_sk   BIGINT       NOT NULL PRIMARY KEY,  -- one per version
  customer_id   VARCHAR(32)  NOT NULL,              -- stable natural key
  full_name     VARCHAR(200) NOT NULL,
  region        VARCHAR(64)  NOT NULL,
  valid_from    TIMESTAMP    NOT NULL,
  valid_to      TIMESTAMP    NOT NULL,
  is_current    BOOLEAN      NOT NULL
);

The interval is half-open: valid_from <= t < valid_to. Half-open intervals make adjacent versions meet without overlapping, so exactly one row satisfies the predicate for any instant. The alternative, inclusive valid_to, requires the next version’s valid_from to be one unit later than the previous version’s valid_to, and that unit depends on the column’s resolution — a day, a second, a microsecond. Half-open intervals avoid the question entirely, and they make the “as of” predicate a single pair of comparisons.

The surrogate key is not optional. If the fact table joined on the natural key, the join would match every version of the customer and multiply the fact rows. The surrogate key is what makes a fact row’s reference unambiguous, and it is also what lets the dimension be rebuilt without changing fact keys — a property that matters during a remodelling.

A third column, a version number starting at 1, is sometimes added. It is not needed for correctness, but it makes the version sequence explicit for consumers who want “the second version” and it makes ordering independent of timestamp ties.

The lookup that has to happen at load time

The fact table does not know the surrogate key; the source system emits the natural key. The load therefore resolves each natural key to a surrogate key, and the resolution must use the event time of the fact, not the load time. That distinction is the core of the pattern.

The resolution is a range lookup against the version that was current at the event’s timestamp:

SELECT customer_sk
FROM dim_customer
WHERE customer_id = :natural_key
  AND valid_from <= :event_ts
  AND valid_to   >  :event_ts;

For a fact arriving promptly, this is an equi-join on the natural key plus a range filter, and with an index on (customer_id, valid_from) it is a small index range scan. For a batch of facts, the same lookup is expressed as a join with a non-equi predicate:

SELECT f.order_id, d.customer_sk, f.amount
FROM staging_orders f
JOIN dim_customer d
  ON d.customer_id = f.customer_id
 AND f.ordered_at >= d.valid_from
 AND f.ordered_at <  d.valid_to;

This join is a range join, and its cost is not the cost of an equi-join. A hash join cannot be used directly because the predicate is an inequality; engines either sort both sides and merge, or iterate the dimension versions per fact row. When the dimension has few versions per member, the range scan per row is cheap. When a member has hundreds of versions — a rapidly changing attribute such as a customer’s credit score — the per-row scan dominates and the dimension becomes the bottleneck. That is the case that pushes a design toward a mini-dimension holding the fast-changing attributes separately.

Three degenerate cases have to be handled explicitly. A fact whose timestamp precedes the entity’s first version has no matching row; it needs a default or inferred member, which is a separate problem treated in late-arriving dimensions and inferred members. A fact whose natural key is absent from the dimension entirely needs the same. And a fact whose natural key has a gap in its validity intervals — because a version was loaded with a start date later than the previous version’s end — matches nothing, and the gap is a load bug that must be detected rather than silently absorbed.

Detecting a change without detecting noise

A new version is emitted when an attribute that the model tracks changes. Deciding what counts as a change is not free.

The comparison must be over the tracked attribute set, not over the whole source row. A source table whose last_modified timestamp updates on every read will produce a new version every load if the loader compares every column. The comparison set is a modelling decision: it names the attributes whose history matters. A customer’s phone number may not, their region may, their credit band may.

Comparison must also be null-safe and type-safe. In SQL, NULL = NULL is unknown, so a naive WHERE NOT (old.col = new.col) misses changes from a value to null and from null to a value. The null-safe comparison is IS DISTINCT FROM in engines that implement it, or an explicit (a IS NULL) <> (b IS NULL) OR a <> b. String comparison must respect collation: a source that pads with trailing spaces and a warehouse that does not will see a change on every load, and a case-insensitive collation will see none.

Numeric comparison has the same problem at a smaller scale. A decimal stored as NUMERIC(18,4) in the dimension and computed in floating point upstream can differ in the fourth place on every load, producing a new version per day. The fix is to compare at the precision the dimension stores, which means rounding before comparison rather than after.

Finally, a change to an attribute that is not tracked must not produce a version, and a change to a tracked attribute that reverts to a previous value must produce one. A customer who moves Oslo → Bergen → Oslo has three versions, not one, because the second Oslo period is a distinct interval of history. Loaders that deduplicate against “any prior version” rather than “the current version” collapse the third version into the first and lose the interval.

Closing the old version and the ordering problem

The new version’s valid_from is the timestamp at which the change took effect, and the old version’s valid_to becomes that same value. The pair of writes must be atomic or the dimension temporarily holds either two current versions or none.

Two sources of that timestamp exist, and they are not interchangeable. The effective date is when the change happened in the world, taken from the source’s own timestamp. The load timestamp is when the warehouse learned about it. Using the load timestamp makes every backfill and every late-arriving change retroactively wrong: a change that occurred on the eleventh but arrived on the fifteenth would be recorded as effective on the fifteenth, and facts from the twelfth to the fourteenth would resolve to the old version.

Using the effective date is correct and creates the harder problem: a change may arrive out of order. If the change effective on the eleventh arrives after a change effective on the twentieth has already been loaded, the loader must insert a version in the middle and adjust its neighbours’ boundaries. That is an insert plus two updates, and it is the operation that distinguishes a dimension loader that handles history from one that only appends. The same difficulty, from the perspective of the transaction timeline rather than the effective timeline, is what bitemporal dimension design addresses by keeping both timestamps.

A third option exists and is sometimes forced by the source: an effective date with no time component, meaning the change is recorded as effective at day granularity. Then the interval boundaries are dates, and a fact timestamped mid-day resolves to the version whose day contains it. Two changes on the same day cannot be represented as two versions; they collapse to the last one, and the collapse is invisible unless the loader counts changes per day and reports the collision.

What the versioned dimension does to queries

Every consumer of the dimension now faces a choice that an overwriting dimension did not present. Filtering on a current attribute and then aggregating history answers “revenue attributed to the customer’s present region”. Joining facts to their version and grouping on that version’s attribute answers “revenue in the region the customer was in at the time”. Both are legitimate; they are different questions, and the report has to say which it means.

The current-version filter is the cheap one, because is_current = TRUE is a selective predicate on a small table. The as-of join is the one that costs, and it is the reason a versioned dimension is often accompanied by a pre-joined fact view that materializes the resolved key at load time. Materializing the key converts a range join at query time into an equi-join, at the price of fixing the version assignment at load time — which is correct only if the dimension’s history will not be rewritten afterwards. When late-arriving changes do rewrite it, the fact rows already loaded point at versions whose attributes have changed, and the history they report silently shifts. The decision between resolving at load time and resolving at query time is therefore a decision about how much retroactive correction the dimension is expected to receive, and it has to be made before the first fact is loaded rather than after the first complaint.