CREATE PROCEDURE [Dim].[Customer_Merge]
@ExecutionLogId bigint
AS
BEGIN
DECLARE @dfValidFrom  datetime2(3) = '1900-01-01 00:00:00.000'
DECLARE @dfValidTo    datetime2(3) = '9999-12-31 00:00:00.000'
-- 1. Handle type 2 dimension update and insert rows in Dimension Customer.
-- Insert rows for updated and deleted data into Target table Dimension Customer.
INSERT INTO Dim.Customer(Customer_dkey, Customer_pkey, CustomerId_bkey, CustomerName, [Address], City, BusinessDate, RecordSource_audit, DateOfChange_audit, ValidFrom_meta, ValidTo_meta, IsCurrent_meta, IsDeleted_meta, IsInferred_meta, InsertExecutionLogId_audit)
SELECT                   Customer_dkey, Customer_key,  CustomerId_bkey, CustomerName, [Address], City, BusinessDate, RecordSource_audit, DateOfChange_audit, ValidTo_meta,   @dfValidTo,   1,              IsDeleted_meta, IsInferred_meta, @ExecutionLogId
FROM                                                                                                               -- updated row's ValidTo_meta output value becomes ValidFrom_meta value in new inserted row.
(
MERGE INTO Dim.Customer AS tgt -- Target table is Dim.Customer and Source table is Staging.Customer that will be Merge Into target with Insert or Update,
USING                          -- where updated rows (via Merge ActionOutput = 'UPDATE') will be followed by the INSERT INTO above.
(SELECT TOP(100) PERCENT Customer_dkey, CustomerId_bkey, CustomerName, [Address], City, BusinessDate, RecordSource_audit, DateOfChange_audit, IsCorrection_meta, IsDeleted_meta, IsInferred_meta
FROM Staging.Customer
ORDER BY CustomerId_bkey) AS src       -- Source scr is a staging table and Target tgt is a dimension table.
ON tgt.Customer_dkey = src.Customer_dkey -- no tgt.IsCurrent_meta = 1 here.
-- If new business key Then insert row with new data into Target table:
WHEN NOT MATCHED BY TARGET THEN
INSERT (Customer_dkey,     CustomerId_bkey,     Customer_pkey, CustomerName,     [Address],     City,     BusinessDate,     RecordSource_audit,     DateOfChange_audit,     ValidFrom_meta, ValidTo_meta, IsCurrent_meta, IsDeleted_meta,     IsInferred_meta,     InsertExecutionLogId_audit)
VALUES (src.Customer_dkey, src.CustomerId_bkey, 0,             src.CustomerName, src.[Address], src.City, src.BusinessDate, src.RecordSource_audit, src.DateOfChange_audit, @dfValidFrom,   @dfValidTo,   1,              src.IsDeleted_meta, src.IsInferred_meta, @ExecutionLogId)
-- If existing business key is current and not inferred with different values from Source or is marked Deleted at Source Then update row in Target table:
WHEN MATCHED AND tgt.IsCurrent_meta = 1 AND tgt.IsDeleted_meta = 0 AND tgt.ValidFrom_meta < src.DateOfChange_audit AND src.IsCorrection_meta = 0 AND
((src.IsDeleted_meta = 0 AND tgt.IsInferred_meta = 0 AND (tgt.CustomerName != src.CustomerName OR ISNULL(tgt.[Address],'¤') != ISNULL(src.[Address],'¤') OR tgt.City != src.City OR ISNULL(tgt.BusinessDate,'1900-01-01') != ISNULL(src.BusinessDate,'1900-01-01'))) OR -- RecordSource_audit is a correction.
(src.IsDeleted_meta = 1)) THEN
UPDATE SET tgt.IsCurrent_meta   = 0,
tgt.ValidTo_meta     = src.DateOfChange_audit, -- as a business date from operational system.
tgt.UpdateTime_audit = SysUTCdatetime(),
tgt.UpdateExecutionLogId_audit = @ExecutionLogId
-- The updated rows in Target table become output and inserted as new rows in Target via INSERT INTO above.
OUTPUT $Action AS ActionOutput, src.Customer_dkey, src.CustomerId_bkey, src.CustomerName, src.[Address], src.City, src.BusinessDate, src.RecordSource_audit, src.DateOfChange_audit, src.IsDeleted_meta, src.IsInferred_meta, inserted.ValidTo_meta, inserted.Customer_key
) AS MergeOutput                                         -- or DATEADD(day,1,inserted.ValidTo_meta) AS ValidTo_meta when new inserted row wants ValidFrom_meta to be at the next day.
WHERE MergeOutput.ActionOutput = 'UPDATE';               -- inserted.ValidTo_meta is the value from the updated row in target table, to become ValidFrom_meta value in new inserted row.
-- inserted.Customer_key is the value from the updated row in target table, to become Customer_pkey value in new inserted row.
-- 2. Handle overwrite of inferred rows or correction of data rows in Customer dimension.
MERGE INTO Dim.Customer AS tgt
USING Staging.Customer AS src
ON tgt.Customer_dkey = src.Customer_dkey AND tgt.IsCurrent_meta = 1 AND tgt.IsDeleted_meta = 0 AND (tgt.IsInferred_meta = 1 OR src.IsCorrection_meta = 1)
-- If existing business key is current and existing inferred row has different values from Source or a correction of data Then overwrite row in Target table:
WHEN MATCHED AND src.IsDeleted_meta = 0 AND (tgt.CustomerName != src.CustomerName OR ISNULL(tgt.[Address],'¤') != ISNULL(src.[Address],'¤') OR tgt.City != src.City OR ISNULL(tgt.BusinessDate,'1900-01-01') != ISNULL(src.BusinessDate,'1900-01-01') OR tgt.RecordSource_audit != src.RecordSource_audit) THEN
UPDATE SET tgt.IsInferred_meta      = 0,
tgt.CustomerName         = src.CustomerName,
tgt.[Address]            = src.[Address],
tgt.City                 = src.City,
tgt.BusinessDate         = src.BusinessDate,
tgt.RecordSource_audit   = src.RecordSource_audit,
tgt.DateOfChange_audit   = src.DateOfChange_audit,
tgt.CorrectionTime_audit = SysUTCdatetime(),
tgt.UpdateTime_audit     = SysUTCdatetime(),
tgt.UpdateExecutionLogId_audit = @ExecutionLogId;
END