Troubleshooting Snowpipe in-depth explanation

[@KnowledgeAmplifier1]

Code:
---------
--drop database if exists
drop database if exists s3_to_snowflake;
--Database Creation
create database if not exists s3_to_snowflake;
--Use the database
use s3_to_snowflake;
--Table Creation
create or replace table s3_to_snowflake.PUBLIC.Iris_dataset (Id number(10,0),sepal_length number(10,4),sepal_width number(10,4),petal_length number(10,4) ,
petal_width number(10,4),class varchar(200));
--create the file format
create or replace file format my_csv_format
type = csv field_delimiter = ',' skip_header = 1
field_optionally_enclosed_by = '"'
null_if = ('NULL', 'null')
empty_field_as_null = true;
--create the external stage
create or replace stage s3_to_snowflake.PUBLIC.Snow_stage url="s3://{}"
credentials=(aws_key_id='{}'
aws_secret_key='{}')
file_format = my_csv_format;
list @Snow_stage;
--Create the Pipe
create or replace pipe s3_to_snowflake.PUBLIC.spa
auto_ingest=true as copy into s3_to_snowflake.PUBLIC.Iris_dataset from
@s3_to_snowflake.PUBLIC.Snow_stage FILE_FORMAT=(FORMAT_NAME=my_csv_format);
--Create event notification
show pipes;
--Check any data present or not
select * from s3_to_snowflake.PUBLIC.Iris_dataset;
-- note lastReceivedMessageTimestamp
select SYSTEM$PIPE_STATUS('s3_to_snowflake.PUBLIC.spa');
--Upload Setosa.csv & check lastReceivedMessageTimestamp changed or not
select * from s3_to_snowflake.PUBLIC.Iris_dataset;
-- Change the file format
create or replace file format my_csv_format
type = csv field_delimiter = '|' skip_header = 1
field_optionally_enclosed_by = '"'
null_if = ('NULL', 'null')
empty_field_as_null = true;
-- note lastReceivedMessageTimestamp
select SYSTEM$PIPE_STATUS('s3_to_snowflake.PUBLIC.spa');
----Upload virginica.csv & check lastReceivedMessageTimestamp changed or not
-- Query s3_to_snowflake.PUBLIC.Iris_dataset and check Virginica data came or not
select * from s3_to_snowflake.PUBLIC.Iris_dataset;
--Debugging
select * from table(information_schema.copy_history(table_name=>'s3_to_snowflake.PUBLIC.Iris_dataset', start_time=> dateadd(hours, -1, current_timestamp())));
-- Correct the file format
create or replace file format my_csv_format
type = csv field_delimiter = ',' skip_header = 1
field_optionally_enclosed_by = '"'
null_if = ('NULL', 'null')
empty_field_as_null = true;
-- note lastReceivedMessageTimestamp
select SYSTEM$PIPE_STATUS('s3_to_snowflake.PUBLIC.spa');
----Upload Versicolor_error.csv & check lastReceivedMessageTimestamp changed or not
-- Query s3_to_snowflake.PUBLIC.Iris_dataset and check Versicolor_error data came or not
select * from s3_to_snowflake.PUBLIC.Iris_dataset;
--Debugging
select * from table(information_schema.copy_history(table_name=>'s3_to_snowflake.PUBLIC.Iris_dataset', start_time=> dateadd(hours, -1, current_timestamp())));

[@JansiArul-bj8vp]

Hello, the data not loaded for me and error message shown like 'Access denied. Please check your credentials. Could you please help me on this.

[@KnowledgeAmplifier1]

Hello Jansi Arul, the error means that the command(LS, Copy, etc) which is being run against the storage area does not have access to the stage area or the specific file and is unable to retrieve the details at the time.
This error can be caused due to a few different reasons , you can refer this link which has explained the probable reasons and the solutions in detail -- community.snowflake.com/s/article/Troubleshooting-Error-Failed-to-access-remote-file-access-denied-Please-check-your-credentials

[@FTI2023]

It literally helps a lot, thanks

[@KnowledgeAmplifier1]

Glad to hear that!

[@fahadmahmood7150]

Can you share how to do the error logging when snow pipes fails??

[@KnowledgeAmplifier1]

There are 2 steps in monitoring, you set up everything , went to chill and after 2 days production system Snowpipe suddenly broke down😅then--
Step 1: alert should be sent to all developer then oy they will come to check health , else noone will even know issue happened in Snowpipe, so for Snowpipe alarm functionality check this video , in-depth explanation
th-cam.com/video/7X52Jv2uflo/w-d-xo.html
Now next step , once alert triggered, developer will come to check what happened
Then to troubleshoot that check this video
th-cam.com/video/6IVtCaW9P8w/w-d-xo.html
I tried to generate real time error in the video to show how to troubleshoot , hope this will be helpful 😊✌🏻

[@simhapurichinnodu5656]

Here first error file still not loaded right then how we can process it after new file arrival?

[@yamunau.yamuna5189]

Very nice explanation thank you bro, and also make one video if we load same file name in S3 bucket it will load to snowflake table,if filename same in that records different it will load through snowpipe like this scenario take bro make one video in interviews they are asking these type of questions so that it will be useful

[@kishorj007]

Thank you for creating these detailed videos.
can you please create 1 video on end to end ETL process including all technologies and tools that can be used to build automated snowflake pipeline without manual intervention.

[@Buzzingfact]

Thanks for creating these videos

[@abdullahsiddique7787]

if say 4 records fail in snowpipe and u make option in copy command that continue the load even if it has error records, so will both the error records get captured in copy history or none of them or only one of them?

[@KnowledgeAmplifier1]

All records will be captured