How to implement SCD Type 1 in a Data Warehouse

In an SCD Type 1 dimension of a Data Warehouse, when an attribute changes the old value is overwritten by the new one and no history is kept. It is the ideal technique for fixing data errors or keeping reference attributes always current, such as a customer's city or segment — cases where the previous value is no longer relevant. Next, you will implement SCD Type 1 step by step, with a single SQL MERGE that updates what changed and inserts what is new, without duplicating rows.

Prerequisites

• SQL Server 2008 or later, or an Azure SQL database.
• Permissions to create tables and insert or update data.
• Basic SQL knowledge and familiarity with the dimension table concept.

Step 1: Create the dimension table

The dimension has a surrogate key (a technical key generated automatically by the IDENTITY column) and the business key, which identifies each customer in the source. The remaining attributes are the fields we want to keep up to date, and the UpdatedAt column records when the row was last changed.

CREATE TABLE dbo.DimCustomer
(
    CustomerSK  INT IDENTITY(1,1) PRIMARY KEY,  -- surrogate key
    CustomerID  INT           NOT NULL,         -- business key
    Name        NVARCHAR(100) NOT NULL,
    City        NVARCHAR(80)  NULL,
    Segment     NVARCHAR(40)  NULL,
    UpdatedAt   DATETIME2     NOT NULL
);

Step 2: Load the source into staging

Put the latest snapshot of customers into a staging table. This is the table the MERGE will compare against the dimension. In a real load it would be filled from the source system; here we insert two rows by hand for the example, where Ana changed city and Bruno does not yet exist in the dimension.

CREATE TABLE dbo.StgCustomer
(
    CustomerID INT           NOT NULL,
    Name       NVARCHAR(100) NOT NULL,
    City       NVARCHAR(80)  NULL,
    Segment    NVARCHAR(40)  NULL
);

INSERT INTO dbo.StgCustomer (CustomerID, Name, City, Segment) VALUES
(1, N'Ana Silva',   N'Porto', N'Retail'),      -- City changed to Porto
(2, N'Bruno Costa', N'Braga', N'Enterprise');  -- new customer

Step 3: Apply SCD Type 1 with MERGE

The MERGE statement handles everything at once. The WHEN MATCHED block deals with customers that already exist: it only overwrites attributes when there is an actual difference, avoiding unnecessary writes. The WHEN NOT MATCHED BY TARGET block inserts new customers.

MERGE dbo.DimCustomer AS target
USING dbo.StgCustomer AS source
   ON target.CustomerID = source.CustomerID
WHEN MATCHED AND (
        target.Name    <> source.Name
     OR target.City    <> source.City
     OR target.Segment <> source.Segment
   ) THEN
   UPDATE SET
        target.Name      = source.Name,
        target.City      = source.City,
        target.Segment   = source.Segment,
        target.UpdatedAt = SYSUTCDATETIME()
WHEN NOT MATCHED BY TARGET THEN
   INSERT (CustomerID, Name, City, Segment, UpdatedAt)
   VALUES (source.CustomerID, source.Name, source.City, source.Segment, SYSUTCDATETIME());

Always end the MERGE statement with a semicolon: in SQL Server it is mandatory and prevents a syntax error that is hard to track down.

Step 4: NULL-safe change detection

The <> operator returns unknown when one side is NULL, so a change to or from NULL may go undetected. For a robust comparison, replace the WHEN MATCHED condition with an EXCEPT, which treats two NULL values as equal:

WHEN MATCHED AND EXISTS (
        SELECT source.Name, source.City, source.Segment
        EXCEPT
        SELECT target.Name, target.City, target.Segment
   ) THEN
   UPDATE SET
        target.Name      = source.Name,
        target.City      = source.City,
        target.Segment   = source.Segment,
        target.UpdatedAt = SYSUTCDATETIME()

Verify the result

Run the MERGE and then query the dimension. Ana's city should now read Porto (the old value is gone) and Bruno should appear as a new row, with no duplicates.

SELECT CustomerSK, CustomerID, Name, City, Segment, UpdatedAt
FROM dbo.DimCustomer
ORDER BY CustomerID;

Run the same MERGE a second time without changing the source: no row is updated. That idempotency confirms SCD Type 1 is working as expected.

Conclusion

With a single MERGE, the dimension always reflects the latest state of the source — the goal of SCD Type 1. The next step is to decide, attribute by attribute, what should keep history: for those cases you would choose SCD Type 2, often in the same table. Looking at your dimension, which attributes make sense to overwrite and which would you rather preserve over time?