The MERGE Behind SCDs
Type 1 And Type 2 Dimension Logic, Generated, Not Hand-Written
July 16, 2026
Most teams know the SCD theory cold. Type 1 overwrites the old value. Type 2 keeps the old row and adds a new one, with a date range and a current flag so you can ask what a customer's territory was last March. The theory is the easy part.
The hard part is the SQL that enforces it, run after run, without drift. A Type 2 dimension is only correct if every history row has the right RowEndDate, exactly one row per key is flagged current, and no gaps or overlaps creep in across loads. Hand-write that logic once and it works. Hand-maintain it across forty dimensions, six platforms, and a schema that changes every quarter, and it becomes the part of the warehouse nobody wants to touch.
BimlFlex doesn't ask you to maintain it. You set a column's change type, and the SCD logic is generated SQL you receive and own. Below is what actually comes out. We covered the high-level options in automated approaches to slowly changing dimensions; this is the code-deep companion that shows the generated MERGE itself.
Type 1: overwrite in place
A Type 1 column has no history. When the source value changes, the dimension row changes with it, and the old value is gone. The generated statement is a single MERGE that joins staged rows to the target on the business key, compares for an actual change, and updates only the rows that moved:
MERGE INTO dbo.DimCustomer TGT
USING
(
SELECT INS.CustomerCode
,INS.CustomerName
,INS.City
,INS.RowHash
FROM stg.DimCustomer_STAGE AS SRC
) INS
ON TGT.CustomerCode = INS.CustomerCode
WHEN MATCHED AND TGT.RowHash <> INS.RowHash THEN
UPDATE
SET TGT.CustomerName = INS.CustomerName
,TGT.City = INS.City
,TGT.RowHash = INS.RowHash
WHEN NOT MATCHED THEN
INSERT (CustomerCode, CustomerName, City, RowHash)
VALUES (INS.CustomerCode, INS.CustomerName, INS.City, INS.RowHash);
The detail that matters is the WHEN MATCHED AND TGT.RowHash <> INS.RowHash clause. The merge doesn't blindly rewrite every matched row. It compares a hash of the tracked columns and updates only rows where the hash actually differs, so an unchanged dimension load touches nothing. That keeps the update set small and your change-data lineage honest: a row updated by this statement genuinely changed.
The same model that produces this can also split the operation. On platforms where an in-place MERGE is awkward or expensive, BimlFlex emits the equivalent as a delta select into a temporary set followed by a keyed UPDATE and a WHERE ... IS NULL insert. The shape changes with the target; the contract, overwrite the changed rows and insert the new ones, does not.
Type 2: keep the history
Type 2 is where the generated code earns its keep. Now a changed value means a new row, the prior row gets closed off with an end date, and only the latest row stays flagged as current. The mechanism comes in two parts: insert the new version, then re-window the timeline for any key that now has more than one open row.
The insert is the familiar pattern, with a twist. New keys and changed rows land as fresh versions; the change comparison decides what counts as a new version rather than a no-op. The interesting half is how the end dates get computed. BimlFlex doesn't track "the previous row" with a correlated subquery or a self-join hunting for the next start date. It uses a window function:
SELECT IntegrationKey
,RowStartDate
,COALESCE(
LEAD(RowStartDate) OVER (
PARTITION BY IntegrationKey
ORDER BY RowStartDate
),
CONVERT(DATETIME2(7), '9999-12-31')
) AS RowEndDate
FROM dbo.DimCustomer
WHERE IntegrationKey IN
(
SELECT IntegrationKey
FROM dbo.DimCustomer
WHERE RowEndDate = CONVERT(DATETIME2(7), '9999-12-31')
GROUP BY IntegrationKey
HAVING COUNT(*) > 1
)
AND RowEndDate = CONVERT(DATETIME2(7), '9999-12-31');
Read the middle of it. For each business key, LEAD(RowStartDate) OVER (PARTITION BY IntegrationKey ORDER BY RowStartDate) looks one row ahead in that key's history and pulls the next version's start date. That value becomes the current row's end date: a version is valid from its own start until the next version begins. The COALESCE catches the last row in each key's timeline, where there is no next start, and sets it to the open-ended high date you saw in the docs (9999-12-31). One window function reconstructs the entire date range across every version of every key, in one pass, without a self-join.
The WHERE clause is the optimization. It only re-windows keys that have more than one row still sitting at the open-ended end date, which is exactly the set of keys that got a new version on this load. Keys that didn't change are left alone.
Closing the prior row
Computing the end dates is half the job. The other half is writing them back and flipping the current flag, and that is one composite-key MERGE:
MERGE INTO dbo.DimCustomer TGT
USING
(
SELECT IntegrationKey
,RowStartDate
,COALESCE(
LEAD(RowStartDate) OVER (
PARTITION BY IntegrationKey
ORDER BY RowStartDate
),
CONVERT(DATETIME2(7), '9999-12-31')
) AS RowEndDate
FROM dbo.DimCustomer
WHERE IntegrationKey IN
(
SELECT IntegrationKey
FROM dbo.DimCustomer
WHERE RowEndDate = CONVERT(DATETIME2(7), '9999-12-31')
GROUP BY IntegrationKey
HAVING COUNT(*) > 1
)
AND RowEndDate = CONVERT(DATETIME2(7), '9999-12-31')
) INS
ON TGT.IntegrationKey = INS.IntegrationKey
AND TGT.RowStartDate = INS.RowStartDate
AND INS.RowEndDate <> CONVERT(DATETIME2(7), '9999-12-31')
WHEN MATCHED THEN
UPDATE
SET RowEndDate = INS.RowEndDate
,RowIsCurrent = 0;
Look at the join. It isn't on the business key alone. It matches on IntegrationKey AND RowStartDate, the composite that uniquely identifies one version of one key, plus a guard that the computed end date isn't the open-ended high date. That guard is what protects the still-current row. Only rows that now have a real successor get matched, get their RowEndDate written, and get RowIsCurrent set to 0. The newest version, the one whose LEAD returned the high date, never matches, so it keeps the open end date and stays current.
That is the whole Type 2 contract enforced in SQL: every superseded version closed to the instant its successor began, exactly one current row per key, no overlaps. The RowEndDate and RowIsCurrent columns themselves are configurable in the product, named and typed however your standards require, and the generated code follows whatever you set.
Why generated beats maintained
You could write all of this by hand. The first time, it's an afternoon. The problem is what happens after.
A hand-maintained SCD is a standing liability. Add a tracked column and you edit the insert list, the compare, and the update set, in every dimension that shares the pattern. Retarget the warehouse from SQL Server to Fabric or Databricks and the LEAD windowing, the temp-table dance, and the high-date literal all have to be re-expressed in that platform's dialect. Each edit is a chance to flip a join condition or drop a column from the compare, and an SCD bug is the quiet kind: the load succeeds, the row counts look fine, and the history is silently wrong until someone audits a date range months later.
Generated SQL moves that risk out of your hands. The change type is metadata on the column. The windowing, the composite join, the current-flag flip, and the dialect are the generator's job, so the same model produces correct Type 2 logic whether you target SQL Server, Fabric, or Databricks, and a new tracked column is a regeneration rather than forty hand-edits. It's the same principle behind designing faster with BimlStudio templates, applied to the one piece of warehouse code that punishes manual maintenance the most.
The dimensional layer isn't the only place this shows up. The same generated delivery logic carries history out of a Data Vault into consumable marts, and it's part of automating the boring parts of a data mart so the schema, the loads, and the SCD behavior stay in step. Set the change type. Read the SQL it writes if you want to. Then stop maintaining it by hand.
