3 0
Read Time:2 Minute, 9 Second

During this post we will configure the Snowpipe on bucket, which in turn will load data into multiple tables. Consider the scenario business has allowed to create a consolidated bucket. Under this bucket we would be having separate folders based on the business domain. Say Sales team contributes to sales folder, Marketing team feeds to marketing and Finance will ingest file to finance folder.

We have three different tables in Snowflake mapped to these business domains folders. As per ask if any team ingest file to respective folder, load should trigger and ingest data into the respective table.  Business wants Snowpipe Configuration on the bucket itself. Notifications on this bucket should identify the POST/PUT event to the respective folder and trigger the load.

In other words there should be  a single SQS Notification but trigger the Snowpipe accordingly based on the event happens to the folder.

We have businessportfolio bucket and under this there are 3 different folders.

Bucket

Prerequisites:

Firstly, Storage Integration: s3_int

File format: csv_format

Stage: ext_csv_stage

Tables: CUST_SALES,CUST_MARKETING,CUST_FINANCE

Moreover, Now we have created the PIPES to load data into respective tables:

First Pipe:

create or replace pipe demo_db.public.sales auto_ingest=true as
copy into cust_sales
from @demo_db.public.ext_csv_stage/sales
on_error = CONTINUE;

Second Pipe:

create or replace pipe demo_db.public.finance auto_ingest=true as

copy into cust_finance

from @demo_db.public.ext_csv_stage/finance

on_error = CONTINUE;

Finally, Third pipe:

create or replace pipe demo_db.public.marketing auto_ingest=true as

copy into cust_marketing

from @demo_db.public.ext_csv_stage/marketing

on_error = CONTINUE;

show pipes;

Once you perform SHOW PIPES,

We see the value in notification_channel is same for all three pipes. However, This is beauty of SQS notification, these are tied to the bucket irrespective to the underlying folders.

Notification_Channel

After that, configure the S3 bucket with notification_channel value.

snowpipe notification
Sales folder

Similarly uploaded file to the finance and marketing folder.

finance and marketing

Finally, Snowpipe triggered and tables got loaded successfully

tables loaded

In conclusion, verify the Snowpipe history.

Snowpipe history

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

Leave a Reply

Your email address will not be published. Required fields are marked *