How to Implement SCD Type 2 in SQL Server

Slowly Changing Dimension Type 2 (SCD Type 2) is a common data warehousing technique used to preserve historical changes in dimensional data. This article explains how to implement SCD Type 2 in SQL Server without writing complex SQL scripts-using Advanced ETL Processor.

What Is SCD Type 2?

SCD Type 2 allows you to track changes in dimensional data over time by maintaining both current and historical versions of records. When a change occurs (e.g., updated email or phone), the system inserts a new row while marking the old one as expired.

The Problem With Manual SCD Type 2

While Microsoft’s own documentation shows how to build SCD Type 2 using Data Factory, the process is:

• Time-consuming: Multiple data flows, lookups, and branching logic are needed.
• Script-heavy: Requires deep knowledge of SQL or pipeline expressions.
• Cloud-dependent: Data Factory runs on Azure-self-hosting isn’t an option.
• Hard to Maintain: Logic is scattered across complex, visual flows.

For teams looking for a faster, local, and cost-effective solution, a different approach is needed.

The Solution: SCD Type 2 with Advanced ETL Processor

Advanced ETL Processor offers a visual, automated, and code-free method to implement SCD Type 2 in SQL Server. It uses hash-based comparison logic to detect changes across any number of fields and update the dimension table accordingly.

SQL Server Table Design

Create the main SCD dimension table and a staging table. The hash column is used to detect changes.

Leads_SCD (Main Dimension Table)

CREATE TABLE dbo.Leads_SCD ( SurrogateKey INT IDENTITY(1,
  1) PRIMARY KEY,
  [ACCOUNT ID] VARCHAR(20),
  [LEAD OWNER] VARCHAR(20),
  [FIRST NAME] VARCHAR(20),
  [LAST NAME] VARCHAR(20),
  [COMPANY] VARCHAR(40),
  [PHONE 1] VARCHAR(30),
  [PHONE 2] VARCHAR(30),
  [EMAIL 1] VARCHAR(50),
  [EMAIL 2] VARCHAR(50),
  [WEBSITE] VARCHAR(50),
  [SOURCE] VARCHAR(40),
  [DEAL STAGE] VARCHAR(20),
  [NOTES] VARCHAR(100),
  HashValue VARCHAR(64),
  ValidFrom DATETIME,
  ValidTo DATETIME,
  IsCurrent BIT
);

Leads_Staging (ETL Load Target)

CREATE TABLE dbo.Leads_Staging ( [ACCOUNT ID] VARCHAR(20),
  [LEAD OWNER] VARCHAR(20),
  [FIRST NAME] VARCHAR(20),
  [LAST NAME] VARCHAR(20),
  [COMPANY] VARCHAR(40),
  [PHONE 1] VARCHAR(30),
  [PHONE 2] VARCHAR(30),
  [EMAIL 1] VARCHAR(50),
  [EMAIL 2] VARCHAR(50),
  [WEBSITE] VARCHAR(50),
  [SOURCE] VARCHAR(40),
  [DEAL STAGE] VARCHAR(20),
  [NOTES] VARCHAR(100),
  HashValue VARCHAR(64)
);

Stored Procedure to Apply SCD Type 2

Use this stored procedure to detect and insert updated records while expiring old ones.

CREATE PROCEDURE dbo.Update_Leads_SCD
AS
BEGIN
SET NOCOUNT
ON;
DECLARE @currentTime DATETIME = GETDATE(); -- 1. Expire existing changed records
UPDATE scd
SET scd.ValidTo = @currentTime,
  scd.IsCurrent = 0
FROM dbo.Leads_SCD scd
INNER JOIN dbo.Leads_Staging stg
ON scd.[ACCOUNT ID] = stg.[ACCOUNT ID]
WHERE scd.IsCurrent = 1 AND scd.HashValue <> stg.HashValue; -- 2. Insert new rows for changed or new accounts
INSERT INTO dbo.Leads_SCD ( [ACCOUNT ID],
  [LEAD OWNER],
  [FIRST NAME],
  [LAST NAME],
  [COMPANY],
  [PHONE 1],
  [PHONE 2],
  [EMAIL 1],
  [EMAIL 2],
  [WEBSITE],
  [SOURCE],
  [DEAL STAGE],
  [NOTES],
  HashValue,
  ValidFrom,
  ValidTo,
  IsCurrent )
SELECT stg.[ACCOUNT ID],
  stg.[LEAD OWNER],
  stg.[FIRST NAME],
  stg.[LAST NAME],
  stg.[COMPANY],
  stg.[PHONE 1],
  stg.[PHONE 2],
  stg.[EMAIL 1],
  stg.[EMAIL 2],
  stg.[WEBSITE],
  stg.[SOURCE],
  stg.[DEAL STAGE],
  stg.[NOTES],
  stg.HashValue,
  @currentTime,
  NULL,
  1
FROM dbo.Leads_Staging stg
LEFT JOIN dbo.Leads_SCD scd
ON stg.[ACCOUNT ID] = scd.[ACCOUNT ID] AND scd.IsCurrent = 1
WHERE scd.[ACCOUNT ID] IS NULL OR scd.HashValue <> stg.HashValue;
END

Benefits Over Azure Data Factory

• No Azure Lock-In: Works with local or on-prem SQL Server-no cloud account needed.
• Faster Setup: No branching pipelines or script maintenance.
• Visual Interface: Design workflows using drag-and-drop ETL components.
• Unlimited Processing: No per-run or data volume restrictions.
• Fully Audit-Ready: Every change is tracked with validity dates and hash comparison.

Ready to Automate?

The free trial of Advanced ETL Processor Enterprise is fully functional - no limitations, no disabled features. You can build, run, and automate real transformations immediately after installation.

Get Started Today

If you're searching for an on-prem, no-code ETL tool for SCD Type 2, Advanced ETL Processor is your best alternative to Azure Data Factory.

Contact us today to request a personalized demo and start tracking changes with confidence.