| Step | What It Does | |------|--------------| | | Adds brand‑new customers that never existed in the dimension. | | ChangedRows CTE | Finds keys where any attribute differs between source and the current dimension row. | | UPDATE (2a) | Closes the existing “current” row by setting EndDate and IsCurrent = 0 . | | INSERT (2b) | Inserts the fresh version with StartDate = GETDATE() and IsCurrent = 1 . |
-- Optional: enforce uniqueness of the active business key CREATE UNIQUE INDEX UX_DimCustomer_Active ON dbo.DimCustomer (CustomerID) WHERE IsCurrent = 1; ssis-586 english