The SCD Type 2 Load Is One Statement, Not a Workflow
A MERGE With OUTPUT Expires And Re-Inserts Changed Rows In One Pass
August 5, 2026
A hand-built Type 2 dimension load is rarely one thing. It is a change-detection query, an UPDATE to expire the old row, an INSERT to add the new version, a second UPDATE for the Type 1 columns, and a transaction wrapped around the lot in the hope that nothing runs twice. Each step is easy. Keeping the steps consistent across every dimension, on every platform, for years, is the part that hurts.
BimlFlex generates that whole dance as one set-based pass, and the heart of it is a single statement: a MERGE with an OUTPUT clause, wrapped in an outer INSERT. We have covered automated approaches to slowly changing dimensions at the level of concepts and trade-offs. This post goes one level down and reads the statement your project actually generates.
Where Hand-Written SCD2 Breaks
The failure modes are familiar to anyone who has debugged a dimension at month-end. Two current rows for the same customer. An end date that overlaps the next version's start date. A Type 1 correction that quietly spawned a new version. A nullable column that registered as a change on every single run.
Almost all of them share one root cause: the expire step and the insert step live in separate statements, and separate statements drift. Someone tunes the UPDATE and forgets the INSERT. A retry after a half-failed batch inserts the new version twice. A deployment ships one statement and not the other. The logic was correct on the whiteboard; the implementation is four artifacts that have to stay synchronized by discipline alone.
Then there is change detection itself. Comparing 40 columns means 40 null-safe comparisons, and every hand-rolled ISNULL wrapper is a chance to get trailing spaces, collation, or type coercion subtly wrong.
Two Hashes Decide What Changed
In the BimlFlex model, change handling is declared per column, not per load. Biml, the language underneath, types every table column with a change disposition: the business key, Type 2 (historical), Type 1 (update in place), plus dispositions for audit columns, surrogate keys, and columns excluded from change analysis entirely.
From those markings, BimlFlex derives two row hashes. You can see both in the metadata configurations, named RowHashType1 and RowHashType2 by default, and you can rename them or change their data types there. RowHashType1 hashes the Type 1 columns; RowHashType2 hashes the Type 2 columns.
That split is the entire decision mechanism. A Type 2 change means the current row's RowHashType2 no longer matches the incoming row, so history must fork: expire the old version, insert a new one. A Type 1 change means only RowHashType1 moved, so the values are overwritten in place with no new version. Null handling, trimming, and type normalization happen once, inside the generated hash derivation, instead of in every load you own.
The Generated Statement
Here is the shape of the incremental load BimlFlex generates for a Type 2 dimension, trimmed to a small example model:
INSERT INTO [dim].[Customer]
( [CustomerCode], [CustomerName], [CustomerSegment]
, [RowHashType1], [RowHashType2]
, [RowStartDate], [RowEndDate], [RowIsCurrent] )
SELECT
[CustomerCode], [CustomerName], [CustomerSegment]
, [RowHashType1], [RowHashType2]
, [RowStartDate], [RowEndDate], [RowIsCurrent]
FROM (
MERGE [dim].[Customer] AS TGT
USING [stg].[Customer] AS SRC
ON TGT.[CustomerCode] = SRC.[CustomerCode]
AND TGT.[RowIsCurrent] = 1
WHEN MATCHED
AND COALESCE(TGT.[RowHashType2], 0x0000...) <> SRC.[RowHashType2]
AND SRC.[RowHashType2] <> 0x0000...
THEN UPDATE SET
[RowIsCurrent] = 0
, [RowHashType1] = SRC.[RowHashType1]
, [RowEndDate] = DATEADD(MS, -1, SRC.[RowStartDate])
WHEN NOT MATCHED BY TARGET
THEN INSERT ( /* full column list */ )
VALUES ( /* SRC values */ )
OUTPUT $action AS [Action], SRC.*
) AS MergeOutput
WHERE MergeOutput.[Action] = 'UPDATE'
AND [CustomerCode] IS NOT NULL;
The table and column names come from the example model; yours come from your metadata. The 0x0000... literal is elided here. In your generated code it is a zero-hash sentinel sized to your configured hash algorithm.
Read it from the inside out. The MERGE joins staged rows to the dimension's current rows only, because RowIsCurrent = 1 sits in the join condition itself. A business key the dimension has never seen falls into the NOT MATCHED branch and inserts directly. A key whose RowHashType2 differs from the stored hash hits the MATCHED branch and gets expired in place: current flag zeroed, end date stamped just before the incoming row's start date, Type 1 hash refreshed on the way out.
Then the trick. OUTPUT $action, SRC.* streams every source row the MERGE touched up to the wrapper, and the outer INSERT keeps only the rows whose action was UPDATE, re-inserting them as fresh current versions. Expire and re-insert are the same statement. They commit together or not at all, and no one can edit one without the other because neither exists as a separate artifact.
The COALESCE guard matters more than it looks. A stored hash that is NULL, or still holds the zero sentinel, never registers as a Type 2 change, which protects rows loaded before hashing was in place. A short generated UPDATE ahead of the MERGE backfills those sentinel hashes from the incoming rows so they settle instead of churning.
Two variations round it out. When a dimension has both Type 1 and Type 2 columns, one more set-based UPDATE follows the statement above, joined on the business key alone, so a Type 1 correction rewrites every version of the member, expired rows included. That is what Type 1 means: no history, even inside a history-keeping table. And a dimension with only Type 1 columns skips the whole apparatus and gets a plain upsert MERGE. The generated orchestration also gates all of this behind an initial-load check, so a first load takes the bulk path instead of merging against an empty table.
One Millisecond or One Day
Look again at the end-dating line:
[RowEndDate] = DATEADD(MS, -1, SRC.[RowStartDate])
Whether that says MS or DD is not a template constant. BimlFlex reads the data type you configured for the RowStartDate configuration: a DateTime timeline gets DATEADD(MS, -1, ...), a date-grained timeline gets DATEADD(DD, -1, ...).
This is the kind of detail that separates a generated pattern from a copied one. If your timeline has date grain and the load subtracts a millisecond, every as-at query at midnight returns two rows. If your timeline has datetime grain and the load subtracts a day, you get either gaps or overlaps between versions. The correct decrement depends on the grain, the grain lives in your metadata, and the generator picks accordingly. Nobody on the team ever chooses MS or DD by hand, which means nobody chooses wrong.
The Same Decision in SSIS
Not every dimension load is a SQL statement. In package-based SSIS loads, the same dual-hash decision runs inside the data flow of the generated package: a surrogate key Lookup against the dimension, filtered to RowIsCurrent = 1, returns the surrogate key plus both stored hashes, and a conditional split routes each incoming row three ways. No surrogate key found means insert. A differing Type 2 hash routes to the expire-and-version path. A differing Type 1 hash routes to update in place.
Same model, same two hashes, same column markings, expressed in the engine's native idiom. We walked through the broader dimensional build in dimensions and facts with BimlFlex, and the same principle carries the rest of the mart: what changes between platforms is the artifact, not the logic.
When the Pattern Is Not Yours
Some dimension has a rule the standard pattern cannot express. For that case the generated load carries a documented extension point, OverrideMerge, scoped to a single object: supply your own load statement for dim.Customer and BimlFlex uses it there while every other dimension keeps the generated pattern. An escape hatch per object beats a fork of the framework.
MERGE has a mixed reputation among SQL Server practitioners, mostly earned by hand-written MERGEs with subtle match conditions maintained across dozens of slightly different copies. Generation changes that math. The statement ships in one tested shape, derived from column markings, and it is regenerated rather than edited.
That derivation is the real point. Mark a column Type 2 instead of Type 1 in the metadata and everything downstream follows: the hash definitions, the match conditions, the end-dating grain, the SSIS split. The same modeling decisions drive the automated parts of a data mart end to end, whether the mart sits over a relational stage or you are delivering data from a Data Vault. The Type 2 load stops being a workflow you maintain and becomes a statement you read.
