Snowflake Ninja

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

Watch E2E Snowflake ETL Demo

You can watch the complete hands on video tutorial

Step - Create Object and list them

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
    -- order stage
    create stage delta_orders_s3
        url = 's3://toppertips/delta/orders' 
        comment = 'feed delta order files';
    -- item stage
    create stage delta_items_s3
        url = 's3://toppertips/delta/items' 
        comment = 'feed delta item files';

    -- customer stage
    create stage delta_customer_s3
        url = 's3://toppertips/delta/customers' 
        comment = 'feed delta customer files';
        
    show stages;
    

Create Pipe Objects for each of the table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
    create or replace pipe order_pipe
    auto_ingest = true
    as 
        copy into landing_order from @delta_orders_s3
        file_format = (type=csv COMPRESSION=none)
        pattern='.*order.*[.]csv'
        ON_ERROR = 'CONTINUE';

     create or replace pipe item_pipe
        auto_ingest = true
     as 
        copy into landing_item from @delta_items_s3
        file_format = (type=csv COMPRESSION=none)
        pattern='.*item.*[.]csv'
        ON_ERROR = 'CONTINUE';

    create or replace pipe customer_pipe
        auto_ingest = true
    as 
        copy into landing_customer from @delta_customer_s3
        file_format = (type=csv COMPRESSION=none)
        pattern='.*customer.*[.]csv'
        ON_ERROR = 'CONTINUE';

Step - Review Pipe Status

1
2
3
4
5
6
    show pipes;
    
    select system$pipe_status('order_pipe');
    select system$pipe_status('item_pipe');
    select system$pipe_status('customer_pipe');
    

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