SQL Server › Merge Statement (Upsert)

How would you use MERGE to implement a slowly changing dimension pattern in a data warehouse, tracking historical changes to a record over time?

Advanced

Answer

You would use MERGE to compare incoming dimension data against the current table, closing out the previous version of a changed record by setting an end date and marking it inactive when a match is found with different values, while inserting a brand new row representing the current version of that record, preserving a full history of changes over time.

MERGE INTO CustomerDimension AS target
USING StagingCustomers AS source
ON target.CustomerId = source.CustomerId AND target.IsCurrent = 1
WHEN MATCHED AND target.Address <> source.Address THEN
  UPDATE SET target.IsCurrent = 0, target.EndDate = GETDATE()
WHEN NOT MATCHED THEN
  INSERT (CustomerId, Address, IsCurrent, StartDate) VALUES (source.CustomerId, source.Address, 1, GETDATE());

Real-world example A data warehouse tracks a complete history of customer address changes over time using a slowly changing dimension pattern built with MERGE, allowing analysts to see exactly what a customer's address was at any point in the past.

Common follow-ups: What is a Type 2 slowly changing dimension, and how does this pattern implement it?;How do you handle inserting the new current version after closing out the old one in the same MERGE statement?

Data Types & Schema Design;Normalization