Deepika

1. The two CTEs (lines 1-9)

sql

WITH SourceClean AS ( SELECT DISTINCT * FROM [Veritas_WH].[stg].[New_Analyst_Roster] ),

• WITH SourceClean AS (...) defines a temporary named result set that exists only for this statement.

• SELECT DISTINCT * takes the incoming roster from the staging table and removes rows that are identical in every column.

sql

TargetClean AS ( SELECT DISTINCT * FROM [Veritas_WH].[ref].[Analysts] WHERE is_active = 1 )

• This does the same for the target table, but keeps only current rows (is_active = 1).

• Expired history rows are left out because the comparison should only be against each analyst’s current version.

2. The MERGE header (lines 10-11)

sql

MERGE INTO [Veritas_WH].[ref].[Analysts] AS T USING ( ... ) AS S

• MERGE INTO ... AS T makes the real ref.Analysts table the target, aliased T. Updates and inserts happen here.

• USING (...) AS S supplies the rows to apply, aliased S. Everything inside the parentheses builds that set.

3. The USING subquery, Branch 1 (lines 12-18)

sql

SELECT S.Analyst_ID, S.Name, S.Title, S.ManagerID, S.Analyst_ID AS MergedKey FROM SourceClean AS S INNER JOIN TargetClean AS T ON T.AnalystID = S.Analyst_ID WHERE T.Title <> S.Title OR T.ManagerID <> S.ManagerID

• The SELECT returns the new values from the source, plus an extra column MergedKey set to the real Analyst_ID.

• INNER JOIN keeps only analysts that exist in both source and active target.

• WHERE keeps only those whose Title or ManagerID changed. Unchanged analysts drop out here.

• Because MergedKey holds the real ID, these rows will match the existing active row in the MERGE. They are the “expire the old row” rows.

• The T here is TargetClean, not the MERGE target. The alias is reused, but it is a separate scope inside the subquery.

4. UNION ALL (line 20)

• It stacks Branch 1 and Branch 2 into one result set.

• UNION ALL is used instead of UNION so no deduplication happens. The two branches intentionally produce different rows for the same analyst, one with a real key and one with NULL.

5. The USING subquery, Branch 2 (lines 22-28)

sql

SELECT S.Analyst_ID, S.Name, S.Title, S.ManagerID, NULL AS MergedKey FROM SourceClean AS S LEFT JOIN TargetClean AS T ON T.AnalystID = S.Analyst_ID WHERE T.Title <> S.Title OR T.ManagerID <> S.ManagerID OR T.AnalystID IS NULL

• The SELECT returns the same columns, but MergedKey is NULL.

• LEFT JOIN keeps every source row, even when there is no matching target row.

• The first two WHERE conditions catch changed analysts, the same as Branch 1.

• OR T.AnalystID IS NULL catches brand-new analysts. For them the left join gives NULL on the target side, so T.Title <> S.Title evaluates to unknown and would be filtered out without this condition.

• Since MergedKey is NULL, these rows can never match in the MERGE. That makes them the “insert a new row” rows.

• A changed analyst therefore appears twice in S: once from Branch 1 and once from Branch 2.

6. The ON clause (line 29)

sql

) AS S ON T.AnalystID = S.MergedKey AND T.is_active = 1

• The match rule is: the target row’s AnalystID equals the source’s MergedKey, and the target row is currently active.

• Branch 1 rows match. Branch 2 rows never do, because NULL = anything is not true.

• T.is_active = 1 stops the MERGE from touching expired history rows.

7. WHEN MATCHED (lines 31-34)

sql

WHEN MATCHED AND (T.Title <> S.Title OR T.ManagerID <> S.ManagerID) THEN UPDATE SET T.end_date = DATEADD(DAY, -1, CAST(GETDATE() AS DATE)), T.is_active = 0

• This runs for Branch 1 rows that found their active target row.

• The extra condition re-checks that something changed. It is already guaranteed by Branch 1’s WHERE, so it is redundant but harmless.

• GETDATE() returns the current datetime and CAST(... AS DATE) strips the time. DATEADD(DAY, -1, ...) subtracts one day, so end_date becomes yesterday.

• is_active = 0 marks the old version as expired.

• Only these two columns are updated. The old Title and Manager stay as they were, which is what preserves history.

8. WHEN NOT MATCHED (lines 35-38)

sql

WHEN NOT MATCHED THEN INSERT (AnalystID, Name, Title, ManagerID, start_date, end_date, is_active) VALUES (S.Analyst_ID, S.Name, S.Title, S.ManagerID, CAST(GETDATE() AS DATE), NULL, 1);

• This runs for source rows with no matching target row, which means the NULL-key Branch 2 rows. These are new analysts and the new versions of changed analysts.

• INSERT (...) lists the target columns. VALUES (...) fills them with the source values.

• start_date is today, end_date is NULL because the row is open-ended, and is_active is 1 for current.

• The trailing ; is mandatory because T-SQL requires MERGE statements to end with a semicolon.