Slowly Changing Dimensions in Plain T-SQL

The source system changes a customer address, and the warehouse must decide what history means. Slowly changing dimensions give that decision a repeatable shape. Type 1 replaces an attribute, while Type 2 keeps an earlier version for facts that happened before the change.

Choose Rules for Slowly Changing Dimensions by Attribute

A Type 1 change overwrites the current dimension value. It fits corrections or attributes where historical reporting should use the latest value. A Type 2 change closes the current row and inserts a new version with a new surrogate key. It fits attributes whose past value matters for analysis.

I ask the business what last year’s report should show after a change today. That question settles more than a technical definition. A region assignment and a spelling correction can belong to different change types in the same dimension.

Keep a Stable Source Key

Each incoming row needs a natural key and source identity for matching. The warehouse assigns a surrogate key to each stored dimension version. A Type 2 member has multiple surrogate keys over time but one source key. EffectiveFrom and EffectiveTo boundaries should be half-open so one instant matches only one version.

I normalize source keys and check duplicates before touching the dimension. If a stage has two conflicting rows for one key, a MERGE or UPDATE can fail or choose an arbitrary winner. Reject ambiguity loudly and keep the input for review.

Write Type 1 as an Update

For a Type 1 attribute such as a corrected display name, update the active dimension row when the value differs. Compare NULLs carefully. Keep a modification timestamp and run identifier if the warehouse needs audit detail. The example assumes stage rows are unique by SourceCustomerID and the dimension has an IsCurrent flag.

Run this inside the load’s controlled transaction after validation. It does not change historical versions; that policy should be explicit.

UPDATE d
SET d.CustomerName = s.CustomerName
FROM dbo.DimCustomer AS d
JOIN dbo.CustomerStage AS s
  ON s.SourceCustomerID = d.SourceCustomerID
WHERE d.IsCurrent = 1
  AND ISNULL(d.CustomerName, N'')
      <> ISNULL(s.CustomerName, N'');

Write Type 2 in Two Steps

For a history-bearing attribute, identify rows whose value changed. Close the current version at the new effective time, then insert a fresh version starting at the same time. The two statements belong in one transaction. Check that the source change timestamp is valid and later than the current version’s start.

I prefer explicit UPDATE and INSERT steps when they are easier to inspect than a complex MERGE. The example shows the close step; the insert follows from the same validated change set.

UPDATE d
SET d.EffectiveTo = s.ChangeAt,
    d.IsCurrent = 0
FROM dbo.DimCustomer AS d
JOIN dbo.CustomerStage AS s
  ON s.SourceCustomerID = d.SourceCustomerID
WHERE d.IsCurrent = 1
  AND d.RegionCode <> s.RegionCode
  AND s.ChangeAt > d.EffectiveFrom;

Insert the New Version

After closing changed rows, insert one new dimension row per validated source key. Use the same ChangeAt timestamp as the closed row’s EffectiveTo. A high sentinel end date represents the open version in this design. Choose data types and boundaries that the application can compare safely.

The stage must contain only changed keys for this INSERT, or the WHERE clause must identify them reliably. I materialize the change set in a staging table so the update cannot erase evidence needed by the insert.

INSERT INTO dbo.DimCustomer
    (SourceCustomerID, CustomerName, RegionCode,
     EffectiveFrom, EffectiveTo, IsCurrent)
SELECT s.SourceCustomerID, s.CustomerName,
       s.RegionCode, s.ChangeAt,
       '9999-12-31', 1
FROM dbo.CustomerChanges AS s;

Test the Timeline of Slowly Changing Dimensions

A good Type 2 load leaves no overlapping ranges for one natural key and exactly one current row. Test rows at the instant before a change and at the change time. Half-open intervals make the old version stop where the new one begins. Facts should resolve to the version valid at their event time.

I include an unchanged customer, a changed customer, a new customer, and a corrected source key in test data. One happy path is not enough. This query catches keys with more than one current row.

SELECT SourceCustomerID,
       COUNT_BIG(*) AS current_rows
FROM dbo.DimCustomer
WHERE IsCurrent = 1
GROUP BY SourceCustomerID
HAVING COUNT_BIG(*) <> 1;

Treat MERGE With Care

MERGE can express matching, insert, and update rules in one statement, but concurrency and duplicate source rows need attention. A Type 2 change needs both closing the old row and adding a new one, which can make a single MERGE hard to read. Separate statements are easier to test and restart in many warehouses.

If the team uses MERGE, verify unique stage keys, target indexes, and locking behavior under concurrent loads. I keep the output actions for audit and compare the result with the two-statement version. Shorter SQL is not automatically simpler operations.

Handle Late-Arriving Changes

A source change can arrive after facts have already been loaded. Decide whether to restate affected fact surrogate keys, add a correction, or report with the information known at load time. The choice belongs to the business definition of history. A timestamp alone does not make the answer automatic.

I log late changes separately. They can create overlapping versions if inserted without adjusting neighboring intervals. Test a change that arrives out of order, not just a sequence arriving neatly by date.

Keep Loads for Slowly Changing Dimensions Auditable

Record run ID, source batch, rows classified as new, Type 1, Type 2, unchanged, and rejected. Advance the source watermark only after the dimension transaction succeeds. Run overlap and current-row checks before facts are loaded against new keys. A failed check should stop the pipeline.

Slowly changing dimensions are straightforward once each attribute has a written rule. The SQL then enforces that rule and proves the timeline. A dimension should tell a stable story about the past, even when the source keeps editing the present.

Late changes in slowly changing dimensions need a business rule, not just another UPDATE. If a correction applies to an earlier effective date, decide whether to split an existing Type 2 interval, update an old version, or restate affected facts. Check that intervals for one source key never overlap and that the current row is unique.

I test a customer that changes twice, one that never changes, and one that arrives after related facts. Those cases expose gaps that a simple first load misses. Keep the source change identifier and run ID on dimension versions where audit needs justify them. A history table is valuable only when the team can explain why each version exists and which facts should point to it.

Can your timeline show exactly one dimension version for every fact date, including a late correction?

Related reading on this blog: What is Slowly Changing Dimension: Quiz: Puzzle: 31 of 31 and Incremental Loads: Moving Only What Changed.

A Type 2 row is not a duplicate customer, it is a recorded version of that customer.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.