Slowly changing dimensions: Type 2

The demo runs June’s depot report twice: before Priya’s transfer and after it. Before pressing play, predict the second result. All five deliveries happened in June, but the dimension will describe July. Watch Priya’s row change and compare the rerun with your answer.

The first run returns Northside 3 and Riverside 2. The update then replaces the only depot value Priya has. The same join reads her three June deliveries through that new row, so the rerun returns Riverside 5. Northside disappears from a month it actually served.

No error fires. The update did exactly what dispatch needed, and the report query is unchanged and valid. Its answer changes because a question about June is joined to whatever the dimension says today. With no row carrying Priya’s earlier depot, the warehouse can no longer reconstruct the original result.

Running the report against the updated tables reproduces the rewrite:

select d.home_depot as depot, count(*)::int as deliveries
from fact_deliveries f
join dim_courier d on f.courier_id = d.courier_id
group by d.home_depot
order by d.home_depot;

The single result, Riverside with 5, contains a correct total that can survive a quick review. What it loses is the location of the work, the only fact this depot report exists to explain.

The update destroyed a fact the business still needs. home_depot was never just one value: Priya worked from Northside through June and Riverside from July onward. Each value carried a period that one overwritable cell could not hold, so recording the new truth erased the old one.

That overwrite is correct in the operational dispatch system, which needs exactly the current roster. Normalization says to store a current fact once. The warehouse has a different job because its questions reach into the past, so current-state design alone cannot answer them faithfully.

Ralph Kimball’s dimensional method calls these attributes slowly changing dimensions, which gives this guide its SCD abbreviation. The method numbers the standard treatments. Type 1 is the overwrite you watched and is legitimate when no one will need the old value. Type 2 preserves the old value, and its rule is short:

When a tracked value changes, close the old row and add a new one.

Adding a row keeps both truths and binds each to the period it was valid. The dimension can now answer questions about then and now. The price is several rows per courier, which breaks the one-row-per-thing assumption. The method below makes those versions safe to use.

Priya now has two versions under one natural key. July 1 is the seam where one window closes and the next opens. Trace each event date against the version rows before revealing the matching rule. Select the row that is valid for a June delivery and for a July 2 delivery, using effective_from and effective_to rather than is_current.

select v.home_depot as depot, count(*)::int as deliveries
from fact_deliveries f
join dim_courier_versions v on f.courier_key = v.courier_key
group by v.home_depot
order by v.home_depot;

The fact-loading rule resolves the effective interval, then stamps the matching courier_key onto each delivery. A version matches an event when effective_from <= event_time AND (effective_to > event_time OR effective_to IS NULL). The later analytic query joins fact_deliveries to the stored surrogate key. It does not recalculate the interval. June’s three Northside deliveries retain courier_key 1, so they always resolve to the Northside version. The report remains Northside 3 and Riverside 2 after later transfers. The trace is complete when it selects the version whose effective interval contains the event time. Filtering is_current returns today’s roster from the same dimension.

Type 2 preserves history and charges for it. Every change adds a row, every consumer must understand that one courier can have several versions, and the load must detect changes and close windows correctly. Even count(*) needs care because it counts versions rather than couriers.

Ask whether anyone will need an attribute’s value as of an event date. A depot report asks exactly that of home_depot, so it earns Type 2. A courier’s phone number rarely does, so Type 1 is usually enough. Make the choice per attribute because one dimension can carry both treatments.

When the business needs every entity’s state at every period end, the question changes shape and calls for snapshot fact types. Type 2 remains the fit for history recorded when occasional changes occur rather than on a schedule.

• Counting versions as couriers. Once one courier can occupy several rows, count(*) answers how many versions exist. Count distinct courier_id, or filter is_current when the question is the present roster.
• Windows that overlap or gap. Use contiguous half-open windows where each effective_to equals the next effective_from. An overlap returns two versions for one date, while a gap returns none. Close the old row and open the new one in the same load step.
• A stale current flag. is_current repeats what an open window already says and can drift after a failed load. Derive or verify it from validity columns instead of trusting the flag alone.
• Versioning a value that never sits still. Type 2 assumes occasional change. A daily-changing value creates excessive versions, while a value that changes with every delivery is a measure and belongs on the fact table.