Snowflake SCD type 2 Merge statement

Question

I have a source and target table as below:

Source table: Source Table

Target table: ![Target Table

Desired Output: ID value1 value2 end_date active_flag 1 x x 9/3/2023 N 1 x y 9/4/2023 N 2 x x null Y 1 x z null Y 3 x x null Y 4 x x null Y

Scenario:

1. When ID's in both tables matches, value1 & value2 matches do nothing
2. When ID's in both tables matches, either one of value1 or value2 not matches then update the existing target record - set end_date as todays date and set active_flag to N and inset the new record
3. When ID's does not match then insert the new record with end_date as null and active_flag as Y

I tried to write Merge statement but getting error that MATCHED clause in MERGE statement must be followed by UPDATE or DELETE clause.

merge into target_table as tgt 
using source_table as src
on src.id = tgt.id
when not matched then 
insert (id, value1, value2, end_date, active_flag) values (src.id, src.value1, src.value2, null, 'Y')
when matched and (src.value1 != tgt.value1 or src.value2 != src.value2) then
update set id = src.id, value1 = src.value1, value2 = src.value2, end_date = get_date(), active_flag = 'N'
when matched and (src.value1 != tgt.value1 or src.value2 != src.value2) then
insert (id, value1, value2, end_date, active_flag) values (src.id, src.value1, src.value2, null, 'Y')

How can I achieve the desired output using MERGE statement or custom query?

• marcothesane: For real Slowly Changing Dimensions, you need a bit more. You need a validity start date and a validity end date; you need a primary key that is usually an IDENTITY column, in addition to your id, which is a business identifier. And you need a staging table, same structure as the SCD target. Two statements: fill the staging table with the processed input table's data, then apply the merge from staging to target.
• NickW: There is a detailed article on how to implement SCD2 in Snowflake here: community.snowflake.com/s/article/…