Creating a SQL Stored Procedure to Load a SCD2

This is a blog that I am writing for future me and hopefully it’ll help a few of you save some time too! It’s not often that I get to build out a data warehouse from scratch, but when I do, I want to make sure I do it well with best practices in place. Because this is not something I do a lot of, I frequently forget lessons I’ve learned and have to go back and drop tables to recreate them in the best way before it’s too late. One table type that is vital to do right the first time is a Slowly Changing Dimension Type 2 (SCD2 for short).

Edits have been made (thank you Johnny Winter for the feedback!) to address key limitations in the Fabric data warehouse versus any other SQL database (including Fabric SQL database). You’ll notice two sections of code, one will be specifically for Fabric data warehouse and one will be specifically for other SQL databases.

Key differences between Fabric data warehouses and SQL databases are caused by the underlying engines. You may be wondering why would I use one over the other? There are a million scenarios that belong in their own blog post, but for my client we used a warehouse because we had data landing in a lakehouse that we want to leverage and the Fabric SQL database cannot grab data from the lakehouse as part of a SP without duplicating data while a warehouse can call lakehouse data using a three part naming convention (lakehouse.schema.table).

Here’s what changes for the Fabric Warehouse scripts in particular:

• No IDENITITY_INSERT. The surrogate id is a plain BIGINT generated by MAX(id) + ROW_NUMBER(), which also lets us keep the literal -1 unknown row.
• No CREATE INDEX of any kind. The filtered unique index and the temp-table clustered index are gone. A PRIMARY KEY NONCLUSTERED NOT ENFORCED on id is added purely as an optimizer hint.
• No computed columns. hash_key and hash_diff are real columns, populated explicitly on every insert.
• The filtered unique index used to fail the load if the source produced two current versions of one identity. NOT ENFORCED constraints do not enforce anything, so that safety net is gone. A staging dedup step now guarantees one current row per identity.

A SCD2 is a dimension, or lookup, table that soft deletes old records instead of overwriting, modifying, or fully removing records that no longer exist. They can be useful for a number of circumstances, but the primary reason is for time traveling. For example, let’s say that my sales data contains a customer key, but I want to know where a customer lived when they bought the item, not where they live now. In that case, I would need a record in my customer table that contains both their current and past addresses. The example below is for GL accounts, but it can be used with anything.

Skip to the end of this blog for the full script including the table creation.

In the example below, I am tracking any changes to the descriptions of a given account. For example, if GL code 40 changes from INVENTORY to GYM INVENTORY on 6/10/2026 12:00 PM, then any records before that minute in time will return INVENTORY as the GL code description while everything on or after will return GYM INVENTORY.

There are a couple of columns you need to do a proper SCD2 – an effective start date, an effective end date, and an is current flag. You can use different names than the ones I’ve chosen, but the pattern should remain. The start and end dates don’t need a time component if you only load daily. I prefer to use hash keys rather than comparing column by column, because predicates like ISNULL(a, ”) = ISNULL(b, ”) are non-SARGable and get slow, since it takes longer to find matches and identify new, updated, or deleted rows. A hash gives you an easy way to identify a row by its contents even when it has no natural unique id. Without one, you end up writing long predicates checking whether every column matches between source and target. With a hash it is a simple = or <>. One caveat for Fabric Warehouses – SARGability is a b-tree seek concept (feel free to dig into this on your own, such a cool internals thing to learn about!), and Warehouses in Fabric have no indexes to seek. But comparing a single hash is still cheaper than comparing many wide columns.

The downside to using a hash key is that if a column is added, you’ll need to manually rerun all the hashes to include the new column so you don’t end up with a huge change on the day the new column was added. Small price to pay for the daily speed in my opinion, but it could be a valid concern if columns change frequently enough.

Creating the SCD2 Table

Why the || between each field? The || is a character combo that doesn’t appear in the wild very often and it’s critical to have a clear separation between fields so the hash is truly unique. It’s a small thing that makes a huge difference to make sure you don’t end up modifying records unintentionally.

I’ve also created all the columns to be in alphabetical order for future user sanity with the standard fields at the end (effective start/end and the hashes). There’s also an index on this table for the current records since I’m anticipating most queries will only care about the current account descriptions.

Why two hash columns? The first hash gives us what I think of as Type 1 fields, that is fields that if they change actually denote a new record entirely, not a modification on existing record. The hash diff is built off Type 2 fields, fields that when they change I want the old version soft deleted and the new one set to current.

ONLY DO THIS ONCE. If you drop the table after facts have started flowing in, be aware that it will be very hard to rebuild since you will have deleted the ids that the fact table is trying to join to. If you need to make adjustments to this dimension, put it in a temp table and temporarily suspend the identity insert so you can preserve the original ids of the table.

You can use IDENTITY in Microsoft Fabric warehouses now, but it does contain significant restrictions as it is in preview. If you want to use this preview feature, please refer to this Microsoft Learn doc for the latest information on the limitations: IDENTITY Columns in Fabric Data Warehouse – Microsoft Fabric | Microsoft Learn.

One caveat to the data types in the create table, you may notice I’m using DATETIME2(6) instead of just DATETIME. The reason is that I’ve been working in Microsoft Fabric data warehouses for a while and DATETIME is not supported (Data Types in Fabric Data Warehouse – Microsoft Fabric | Microsoft Learn). If you’re using an on-prem SQL Server, feel free to use your preferred datetime data type.

Create Default “NULL” Row

This is optional, but highly encouraged. Having a NULL row allows your fact table to never have a NULL value in the surrogate key for dim account. Pretty handy for reporting purposes to have a default “Unknown Account” show up instead of an empty record or, heaven forbid, the record get removed automatically when you group on a field from the account dimension. This only needs to be done once and is not included in the stored proc because it never needs to be updated.

Create Loading Stored Procedure

This is where the real work happens. Keep in mind, this stored procedure will need to be called at some point by an orchestrator. Orchestrators can be a data pipeline, a SQL trigger, a SQL agent job, a notebook, etc. Anything that can call EXEC dim.usp_LoadAccount will work. Be sure that this comes BEFORE you create your fact tables. Fact tables will need to know what id to write into the surrogate key for your dimension.

This stored procedure does a few things – creates the source table, expires (aka soft deletes) records changed in the source, insert new and updated records, and expire the records that don’t exist at all in source anymore.

General notes about this stored procedure:

• The source temporary table does contain an index on it in the example below since that source temp table is called 3 times throughout the procedure.
• Everything is wrapped in transactions and try catches so nothing fails silently, and clear failure messages will bubble up to our orchestrator.

Full SQL Script (Including Create Table)

Additional Information

• Slowly changing dimension type 2 – Microsoft Fabric | Microsoft Learn
• Index Architecture and Design Guide – SQL Server | Microsoft Learn
• Migrate to IDENTITY Columns in Fabric Data Warehouse – Microsoft Fabric | Microsoft Learn