When dealing with Slowly Changing Dimension Type 2 (SCD2) in a data warehouse, a common practice is to use a staging pattern to handle the loading and processing of data before it is inserted into the final dimension table. This staging pattern helps manage historical changes and ensures that only the necessary updates are applied to the dimension table.

Here’s a simplified example of a staging pattern in SQL for handling SCD2:

Step 1 – DimCustomer table, that will maintain the history of customer changes:

SQL
CREATE TABLE DimCustomer(
    Customer_SNK INT IDENTITY PRIMARY KEY,        -- SuperNaturalKey
    Customer_BK INT,                              -- BusinessKey
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    EffectiveDate DATE,
    EndDate DATE DEFAULT '9999-12-31',
    IsCurrent CHAR(1)
);

Step 2 – FactSalesTransaction table, with records of sales transactions:

SQL
CREATE TABLE FactSalesTransaction(
    TransactionID INT PRIMARY KEY,
    Customer_SNK INT,
    SalesDate DATE,
    SalesAmount MONEY
);

Step 3 – Staging_Customer table to fetch updates from the source system:

SQL
CREATE TABLE Staging_Customer(
    CustomerID INT,
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    LoadDate DATE,
);

Step 4 – Data load into the staging table from source system.

SQL
-- Truncate the staging table
TRUNCATE TABLE Staging_Customer;

-- Insert data into the staging table
INSERT INTO Staging_Customer (
    CustomerID,
    FirstName,
    LastName,
    LoadDate
)
SELECT 
    CustomerID,
    FirstName,
    LastName,
    GETDATE()     -- get the current date and time
FROM source_table;

Step 5 – Update EndDate of existing records in dimension table where changes are found:

SQL
UPDATE d
SET EndDate = s.LoadDate, IsCurrent = 'N'
FROM DimCustomer d 
JOIN Staging_Customer s 
ON d.Customer_BK = s.CustomerID 
WHERE d.IsCurrent = 'Y' 
AND (d.FirstName <> s.FirstName 
   OR d.LastName <> s.LastName);

Step 6 – Insert new records into dimension table for new data:

SQL
INSERT INTO  DimCustomer (
                          Customer_BK, 
                          FirstName, 
                          LastName, 
                          EffectiveDate, 
                          IsCurrent
                          )
                         
SELECT 
                          CustomerID, 
                          FirstName, 
                          LastName, 
                          LoadDate, 
                          'Y'
                          
FROM         Staging_Customer s

WHERE NOT EXISTS (
     SELECT 1
     FROM DimCustomer d 
     WHERE d.Customer_BK = s.CustomerID AND d.IsCurrent = 'Y'
     );
     
"""
The WHERE clause with NOT EXISTS is saying: 

If matching records are found, 
the NOT EXISTS condition becomes FALSE, 
and the respective record from the staging table 
is not inserted into the main table.
"""
Test Data

Step 1 – DimCustomer with initial data:

SQL
--Populating DimCustomer
INSERT INTO DimCustomer (Customer_BK, FirstName, LastName, EffectiveDate, IsCurrent)
VALUES 
(101, 'Bob', 'Weber', '2023-01-01', 'Y'),
(102, 'Alice', 'Miller', '2023-01-01', 'Y');

Step 2 – FactSalesTransaction with initial data (associating transactions with the customers Bob Weber and Alice Maier):

SQL
--Populating FactSalesTransaction
INSERT INTO FactSalesTransaction (TransactionID, Customer_SNK, SalesDate, SalesAmount)
VALUES 
(1, 1, '2023-01-02', 100.00),  -- Bob Weber made a $100 transaction
(2, 2, '2023-01-02', 200.00);  -- Alice Maier made a $200 transaction

Step 3 – New data arrives into the Staging_Customer table. Let’s assume that Alice changes her last name to Smith:

SQL
--Populating Staging_Customer 
INSERT INTO Staging_Customer (CustomerID, FirstName, LastName, LoadDate)
VALUES 
(101, 'Bob', 'Weber', '2023-01-02'),  --Existing record, no change 
(102, 'Alice', 'Smith', '2023-01-02');  --Existing record, name change

Step 4 – Run the SCD Type 2 updates to DimCustomer:

First, customers that have changes will be marked as non-current:

SQL
UPDATE d
SET EndDate = s.LoadDate - 1, IsCurrent = 'N'
FROM DimCustomer d 
JOIN Staging_Customer s 
ON d.Customer_BK = s.CustomerID 
WHERE d.IsCurrent = 'Y' 
AND (d.FirstName <> s.FirstName OR d.LastName <> s.LastName);

Then, insert new records for the changed customers:

SQL
INSERT INTO DimCustomer (Customer_BK, FirstName, LastName, EffectiveDate, IsCurrent)
SELECT CustomerID, FirstName, LastName, LoadDate, 'Y'
FROM Staging_Customer s
WHERE NOT EXISTS (
    SELECT 1
    FROM DimCustomer d 
    WHERE d.Customer_BK = s.CustomerID AND d.IsCurrent = 'Y');

Now, if you query the DimCustomer table, you’ll see that Alice Miller record has been marked as non-current and a new record has been added for Alice Smith.

For example, if Alice Smith makes a new transaction, you’d insert into FactSalesTransaction with the new Customer_SNK for Alice Smith (which would be 3 if you’re following this example):

SQL
INSERT INTO FactSalesTransaction (TransactionID, Customer_SNK, SalesDate, SalesAmount)
VALUES 
(3, 3, '2023-01-03', 300.00);
Walkthrough of how SCD 2 Type works

Initial Setup

DimCustomer Initial

Customer_SNK (PK)Customer_BKFirstNameLastNameEffectiveDateEndDateIsCurrent
1101BobWeber2023-01-01NULL‚Y‘
2102AliceMiller2023-01-01NULL‚Y‘

Each row of the table represents a customer in your system. The ‚PK‘ in ‚Customer_SNK (PK)‘ designates it as the primary key of the table. ‚NULL‘ represents no set date, implying that the customer is currently active. ‚Y‘ and ‚N‘ in ‚IsCurrent‘ column represent whether the record is current or not.

FactSalesTransaction Initial

TransactionID (PK)Customer_SNKCustomer_BKSalesDateSalesAmount
111012023-01-02100.00
221022023-01-02200.00

In this table, each row corresponds to a sale transaction. The ‚PK‘ in ‚TransactionID (PK)‘ designates it as the primary key of the table. SalesDate represents the date of the transaction and SalesAmount represents the amount of the transaction.

Staging_Customer Initial

CustomerID (PK)FirstNameLastNameLoadDate
101BobWeber2023-01-02
102AliceMiller2023-01-02

Each row of the table represents a customer in the staging area. The ‚PK‘ in ‚CustomerID (PK)‘ designates it as the primary key of the table. ‚LoadDate‘ is the date when the data was loaded into the staging area.

Event: Alice Miller Changes Her Last Name to Smith


Staging_Customer Update

CustomerID (PK)FirstNameLastNameLoadDate
101BobWeber2023-01-03
102AliceSmith2023-01-03

This table shows changes in customer data. It appears that the LastName of customer with CustomerID 102 has been updated from ‚Miller‘ to ‚Smith‘. The LoadDate has also been updated to ‚2023-01-03‘.

DimCustomer Update

We mark the old Alice Miller record as inactive and insert a new record for Alice Smith.

Customer_SNK (PK)Customer_BKFirstNameLastNameEffectiveDateEndDateIsCurrent
1101BobWeber2023-01-01NULL‚Y‘
2102AliceMiller2023-01-012023-01-02‚N‘
3102AliceSmith2023-01-03NULL‚Y‘

This table indicates that Alice Miller (Customer_BK 102) is no longer current (‚N‘) as of ‚2023-01-03‘. A new record has been created for Jane Smith (still Customer_BK 102) with a current status (‚Y‘) starting from ‚2023-01-03‘.

When New Transactions for Alice arrive, we associate the new transaction with the new Alice Smith record.

FactSalesTransaction after a New Transaction for Alice

TransactionIDCustomer_SNKCustomer_BKSalesDateSalesAmount
111012023-01-02100.00
221022023-01-02200.00
331022023-01-03150.00

In this table, each row corresponds to a sales transaction. The new entry (last row) indicates that customer with Customer_SNK 3 (Alice Smith with Customer_BK 102) made a purchase on 2023-01-03 with SalesAmount of 150.00.