Prasanna Kumar
Prasanna Kumar

Reputation: 41

Snowflake SCD type 2 Merge statement

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?

Upvotes: 1

Views: 1131

Answers (0)

Related Questions