Simple SCD Type 2 Pipeline Implementation With Google Cloud Workflow and Bigquery скачать в хорошем качестве

Simple SCD Type 2 Pipeline Implementation With Google Cloud Workflow and Bigquery

In this video I am going to show you, how you can implement Slowly Changing Dimension 2, SCD type2, in Bigquery and also show you how you can use google cloud workflow to orchestrate your jobs. SQL's: View: ------------ CREATE VIEW `raw_dataset.v_employee_raw` as SELECT id , name , address , phone , dw_date , dw_ingest_timestamp , MD5(CONCAT(name,address)) m_hash FROM `raw_dataset.employee_raw` where dw_date=(select max(dw_date) from `raw_dataset.employee_raw` ) QUALIFY row_number() over(partition by id order by dw_ingest_timestamp desc)=1 ------------------------- SQL's: --------------------------- 1: BEGIN INSERT INTO `output_dataset.employee_changes` select *,current_date as start_date,Null as end_data,CAST("Y" AS STRING) as is_active from `source_raw_dataset.v_employee_raw` a where a.id not in (select id from `output_dataset.employee_changes`); END 2: BEGIN update `output_dataset.employee_changes` c set c.end_date=current_date() , c.is_active=CAST("F" AS STRING) where c.id in (select a.id from `source_raw_dataset.v_employee_raw` a join `output_dataset.employee_changes` b on a.id=b.id where a.m_hash != b.m_hash and b.is_active=CAST("Y" AS STRING) ) and c.is_active=CAST("Y" AS STRING); END 3: BEGIN insert into `output_dataset.employee_changes` select a.id,a.name,a.address,a.phone,a.dw_date,a.dw_ingest_timestamp,a.m_hash,current_date as start_date,Null as end_data,CAST("Y" AS STRING) as is_active from `source_raw_dataset.v_employee_raw` a join `output_dataset.employee_changes` b on a.id=b.id and a.m_hash != b.m_hash; END ---------------------------- Difference Between Airflow, Google Cloud Worklfow and Event Scheduler Post:   / why-you-should-use-google-cloud-workflows