SCD2-Check-snowflake.sql

--Full run
create table Full_Run as
select 
distinct
md5(CUSTOMERID||CUSTOMERNAME||CUSTOMEREMAIL) as CustKey,
CUSTOMERID,CUSTOMERNAME,CUSTOMEREMAIL,current_date as start_date, null as end_date,
true as flag
from dbt_db1.dbt_schema1.orders; --raw table

create table ch_capture_1 as 
(select 
a.CustKey,
a.CUSTOMERID,
a.CUSTOMERNAME,
a.CUSTOMEREMAIL,
a.start_date, 
a.end_date,
a.flag
from --one new record added  one updated -- this will capture the records
(select distinct md5(CUSTOMERID||CUSTOMERNAME||CUSTOMEREMAIL) as CustKey,CUSTOMERID,CUSTOMERNAME,
                    CUSTOMEREMAIL,current_date as start_date, null as end_date, true as flag
                    from dbt_db1.dbt_schema1.orders) a
left join (select * from Full_Run where flag = 'TRUE') b
on a.CUSTOMERID = b.CUSTOMERID
where a.CUSTOMERNAME <> b.CUSTOMERNAME or a.CUSTOMEREMAIL <> b.CUSTOMEREMAIL 
or a.CUSTOMERID not in (select CUSTOMERID from Full_Run));

--since this is incremental  new row will be inserted and old will be updated.
select * from ch_capture_1
union all
select 
CustKey,CUSTOMERID,CUSTOMERNAME,CUSTOMEREMAIL,start_date,current_date as end_date, false as flag
from Full_Run where CUSTOMERID in (select distinct CUSTOMERID from ch_capture)