TSQL - How to Load Slowly Changing Dimension Type 2 ( SCD2) By Using TSQL Merge Statement


Scenario : 

Let's say we have dbo.Customer Table in OLTP Database or in Staging Database from which we have to load our Dim.Customer Slowly changing type 2 dimension by using TSQL Merge Statement.


Solution:


Step 1: 
Create Source Table dbo.Customer and insert couple of records.
USE TEST1
GO
CREATE TABLE dbo.Customer
  (
     CustomerID   INT IDENTITY(1, 1),
     CustomerCode UNIQUEIDENTIFIER,
     FirstName    VARCHAR(50),
     LastName     VARCHAR(50),
     [Address]    VARCHAR(200),
     Phone        VARCHAR(10)
  )

INSERT INTO dbo.Customer 
VALUES ( NEWID(),'Aamir','Shahzad',' 2119 XYZ NEW YORK USA','5054141969')
INSERT INTO dbo.Customer 
VALUES ( NEWID(),'Najaf1','Ali',' 32211 XYZ NEW Jersey USA','5054141969')


Step 2:
Create Dimension Table. We used CustomerCode as business key from source.

CREATE TABLE Dim.Customer
  (
     DimCustomerID INT IDENTITY(1, 1),
     CustomerCode  UNIQUEIDENTIFIER,
     FirstName     VARCHAR(50),
     LastName      VARCHAR(50),
     [Address]     VARCHAR(200),
     Phone         VARCHAR(10),
     StartDT       DATETIME,
     EndDT         DATETIME,
     IsLatest      BIT
  )



Step 3: 
Create Merge Statement, the statement can be used in SQL Server agent job or It can be used in SSIS Package ( Execute SQL Task). If you wish , you can create stored procedure for this statement and use in SQL Server Agent or SSIS Package to populate Dim.Customer Table.


-- Insert Records from Inner Merge as they they are update and 
-- need to insert as new record
INSERT INTO TestDB.Dim.Customer (CustomerCode, FirstName, LastName,
[Address], Phone, IsLatest,StartDT)
SELECT CustomerCode, FirstName, LastName,[Address], Phone,1,GETDATE() AS StartDT FROM(
-- Start of Merge Statement
MERGE INTO TestDB.Dim.Customer AS DST
USING Test1.dbo.Customer AS SRC
ON (SRC.CustomerCode = DST.CustomerCode
-- Business Key for Matching
-- WHEN Business Key does not match with Destination Records, Then insert
WHEN NOT MATCHED THEN
INSERT (CustomerCode, FirstName, LastName, [Address], Phone, IsLatest,StartDT)
VALUES (SRC.CustomerCode, SRC.FirstName, SRC.LastName,
        SRC.[Address],SRC.Phone, 1,GETDATE())
-- Business Key Matched but One or More than One filed has changed in Source)
WHEN MATCHED AND IsLatest = 1 AND ( 
-- Comparing if any of the field is different from 
--Source in Destination WHEN Business Key matches.
 ISNULL(DST.FirstName,'') != ISNULL(SRC.FirstName,'')
 OR ISNULL(DST.LastName,'') != ISNULL(SRC.LastName,'')
 OR ISNULL(DST.Address,'') !=ISNULL(SRC.Address,'')
 OR ISNULL(DST.Phone,'') != ISNULL(SRC.Phone,'')
 )
--UPDATE the record. Expire the record by setting IsLatest=0 and EndDate=getdate()
THEN UPDATE
SET DST.IsLatest = 0,
    DST.EndDT = GETDATE()
-- Use the Source records which needs to be inserted in Dim because of Update.
 OUTPUT SRC.CustomerCode, SRC.FirstName, SRC.LastName, SRC.[Address],SRC.phone,
        $Action AS MergeAction) AS MRG
         WHERE MRG.MergeAction = 'UPDATE';

Note: 
I have used StartDT and EndDT as datetime, most of the time the dimensions are loaded once a day and these columns can be changed to date datatype.