Snowpipe setup for AWS S3

Here is the step by step guide for beginners to establish snowpipe connectivity in Snowflake with AWS S3 bucket. If you are looking for navigation using AWS UI to configure the set up, then you are at a right place here.

1) Set up snowflake connectivity with AWS S3

https://snowflakewiki.medium.com/connecting-snowflake-to-aws-ef7b6de1d6aa

2) Create a PIPE in Snowflake

Create pipe in snowflake here with name mypipe and make a note of notification_channel value populated using DESC PIPE.

create pipe mypipe 
auto_ingest=true 
as
copy into mycsvtable
from @my_s3_stage
file_format = (type = csv field_delimiter = '|' skip_header = 1);

DESC PIPE mypipe;

3) Set up the notification in AWS

Login to AWS and go to Amazon S3 bucket.

In the properties tab go to Event notification section and create event notification here with name snowpipe_ingest.

Select Event type for all objects created and at destination SQS queue, fill in the value of notification_channel noted in step2) and save changes.

4) Verify the Pipe and check if data is loading through it

4a) Check the status of the pipe if its in RUNNING status

select system$pipe_status('mypipe');

4b) Upload new file in S3 blog storage and validate if they are being processed by the pipe

select * from table(information_schema.copy_history (table_name=>'mycsvtable', 
start_time=> dateadd(hours, -1, current_timestamp())));

References: https://docs.snowflake.com/en/user-guide/data-load-s3-config-storage-integration.html

Follow and Clap if you like the content and feel free to ask if you have any questions in the comments. I will be more than happy to assist and guide you.