How to Implement Slowly Changing Dimension(SCD) Type 2 Using Insert and Update Commands in Snowflake скачать в хорошем качестве

How to Implement Slowly Changing Dimension(SCD) Type 2 Using Insert and Update Commands in Snowflake

In this video , I am going to show you how to implement Slowly Changing Dimension(SCD) Type 2 Using Insert and Update Commands in Snowflake. ⌚Timestamps: 00:00 Intro 00:16 SCD Type2 06:16 Demo on SCD Type2 - Day 1 file processing 17:13 Demo on SCD Type2 - Day 2 file processing 20:38 Demo on SCD Type2 - Day 3 file processing 23:05 Outro use role accountadmin; use database demo_db; use schema public; create or replace TRANSIENT TABLE STG_EMP ( HK_EMPID VARCHAR(16777216), CHANGE_HK VARCHAR(16777216), EMPID NUMBER(38,0), NAME VARCHAR(30), SALARY NUMBER(20,2), DESIGNATION VARCHAR(16777216), OFFICE VARCHAR(30), INSERT_TS TIMESTAMP_NTZ(9), FILE_NAME VARCHAR(200), FILE_ROW_NUMBER NUMBER(38,0) ); create or replace TABLE EMP ( hk_empid varchar, Change_HK varchar, empID NUMBER(38,0), NAME VARCHAR(30), SALARY NUMBER(20,2), DESIGNATION VARCHAR(16777216), OFFICE VARCHAR(30), active_indicator varchar(1), effective_start_date date, effective_end_date date, INSERT_TS TIMESTAMP_NTZ(9), UPDATE_TS TIMESTAMP_NTZ(9), FILE_NAME VARCHAR(200), FILE_ROW_NUMBER NUMBER(38,0), dss_version number ); use role accountadmin; use database demo_db; use schema public; remove @mycsvstage; --put file://c:\data\EMP_20210929.csv @mycsvstage; --put file://c:\data\EMP_20211001.csv @mycsvstage; put file://c:\data\EMP_20211002.csv @mycsvstage; truncate table STG_EMP; COPY INTO STG_EMP( empid ,name ,salary ,Designation ,office ,file_name ,file_row_number ,insert_ts ,hk_empid ,change_hk ) FROM ( SELECT t.$1,t.$2,t.$3,t.$4,t.$5 ,metadata$filename ,metadata$file_row_number ,CAST(current_timestamp as timestamp) ,CAST(MD5( NVL(CAST($1 as varchar),'null') ) as varchar ) as hk_empid ,CAST(MD5( NVL(CAST($2 as varchar),'null') || '||'|| NVL(CAST($3 as varchar),'null') || '||'|| NVL(CAST($4 as varchar),'null') || '||'|| NVL(CAST($5 as varchar),'null') ) as varchar ) as change_hk from @mycsvstage/ t ) pattern = '.*EMP.*[.]csv.gz' on_error = 'skip_file' ; insert into EMP ( hk_empid ,change_hk ,empid ,name ,salary ,Designation ,office ,active_indicator ,effective_start_date ,effective_end_date ,file_name ,file_row_number ,insert_ts ,update_ts ,dss_version ) SELECT s.hk_empid ,s.change_hk ,s.empid ,s.name ,s.salary ,s.Designation ,s.office ,'Y' ,current_date()-1 ,to_date('9999-12-31','YYYY-MM-DD') ,s.file_name ,s.file_row_number ,s.insert_ts ,update_ts ,NVL(current_rows.dss_version, 0) + 1 as dss_version from ( SELECT s.hk_empid ,s.change_hk ,s.empid ,s.name ,s.salary ,s.Designation ,s.office ,s.file_name ,s.file_row_number ,s.insert_ts ,CAST(current_timestamp as timestamp) update_ts ,row_number() over (partition by s.hk_empid order by s.file_row_number desc ) rn FROM stg_emp s qualify rn=1 ) s LEFT OUTER JOIN ( select a.hk_empid ,max(a.effective_start_date) as effective_start_date ,max(dss_version) dss_version from EMP a group by hk_empid ) as current_rows on s.hk_empid = current_rows.hk_empid where not exists( select 1 from EMP e where s.hk_empid = e.hk_empid and s.change_hk = e.change_hk and current_rows.effective_start_date = e.effective_start_date ); update emp e set e.effective_end_date = current_date(), e.active_indicator = 'N' from ( SELECT s.hk_empid ,s.change_hk ,s.empid ,s.name ,s.salary ,s.Designation ,s.office ,s.file_name ,s.file_row_number ,s.insert_ts ,s.update_ts ,dss_version ,row_number() over (partition by s.hk_empid order by s.dss_version desc ) rn FROM emp s where active_indicator ='Y' qualify rn !=1 ) S where e.hk_empid = s.hk_empid and e.change_hk = s.change_hk and e.dss_version = s.dss_version and e.effective_end_date = to_date('9999-12-31','YYYY-MM-DD') ; #snowflake#datacloud#database#datawarehouse#vcklytech