Snowflake Ninja

Snowflake ETL Example With Pipe, Stream & Task Objects Part-07

Watch E2E Snowflake ETL Demo

You can watch the complete hands on video tutorial

Data Validation Post Data Loading

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
select count(*) from ch19.landing_zone.landing_order; --10003 (10001) new records + one update
select count(*) from ch19.landing_zone.landing_item; --2793 (2791)new records + one update
select count(*) from ch19.landing_zone.landing_customer; -- 8889 (8887) new records + one update


select count(*) from ch19.landing_zone.landing_order_stm; 
select count(*) from ch19.landing_zone.landing_item_stm; 
select count(*) from ch19.landing_zone.landing_customer_stm; 


select count(*) from ch19.curated_zone.curated_order; --10001(10002) one updated and one inserted
select count(*) from ch19.curated_zone.curated_item; --2791 (2792) one updated and one inserted
select count(*) from ch19.curated_zone.curated_customer; -- 8887 (8888) one updated and one inserted

select count(*) from ch19.consumption_zone.order_fact; --5740 (5741)
select count(*) from ch19.consumption_zone.item_dim; --2791 (2791)
select count(*) from ch19.consumption_zone.customer_dim; -- 8887 (8888)

use schema ch19.consumption_zone;
select *  from table(information_schema.task_history()) 
            where name in ('ORDER_FACT_TSK')
            order by scheduled_time;

-- lets validate the change using time travel feature
select * from ch19.consumption_zone.customer_dim where customer_id = 'AAAAAAAAPOJJJDAA'
union all
select * from ch19.consumption_zone.customer_dim at(offset => -60*10) where customer_id = 'AAAAAAAAPOJJJDAA';


select * from ch19.consumption_zone.item_dim where item_id = 'AAAAAAAACDLBXPPP'
union all
select * from ch19.consumption_zone.item_dim at(offset => -60*10) where item_id = 'AAAAAAAACDLBXPPP';

SQL Scripts - Part 01 to Part 07

  1. Part-01 covers curated Zone SQL Script & Data Loading
  2. Part-02 covers curated Zone SQL Script & Data Loading
  3. Part-03 covers consumption Zone SQL Script & Data Loading
  4. Part-04 covers stage and pipe creation script for landing layer
  5. Part-05 stream and task creation for curated layers
  6. Part-06 stream and task cration for consumption layer
  7. Part-07 Lets validate the merge statements (Insert/Update)

Watch E2E Snowflake ETL Demo

You can watch the complete hands on video tutorial