Snowflake Snowpipe - Email Alert Mechanism

Suppose you are planning to use Snowpipe to load a data from a real-time source.
But for the support team the monitoring the pipe jobs,they need alert mechanism!
How can you implement alert mechanism for Snowpipe via email/mobile sms etc?

In this video I have discussed the solution of the above question :-)


Prerequisite:
----------------------
Configuring a Snowflake Storage Integration to Access Amazon S3
   • Configuring a Snowflake Storage Integ...  
Documentation:
https://docs.snowflake.com/en/user-gu...

Code:
----------
drop database if exists ramu;

create database ramu;

use ramu;


CREATE or replace NOTIFICATION INTEGRATION errornotificationsend
ENABLED = true
TYPE = QUEUE
NOTIFICATION_PROVIDER = AWS_SNS
DIRECTION = OUTBOUND
AWS_SNS_TOPIC_ARN = '{}'
AWS_SNS_ROLE_ARN = '{}';


DESC NOTIFICATION INTEGRATION errornotificationsend;

--Table Creation
create or replace table video (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));

--file-format creation
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;

--stage creation
create or replace stage ramu.PUBLIC.snow_simple url="s3://{}"
credentials=(aws_key_id='{}'
aws_secret_key='{}')
file_format = my_csv_format;

--check the data present in S3
list @ramu.PUBLIC.snow_simple;
--copy command
copy into ramu.PUBLIC.video from @ramu.PUBLIC.snow_simple FILE_FORMAT=(FORMAT_NAME=my_csv_format) pattern='.*[.]csv';


select * from ramu.PUBLIC.video;

---------------------------------------------------------------------------------------------------------------------------------------
--Table Creation
create or replace table video2 (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 or replace pipe spa
auto_ingest=true
ERROR_INTEGRATION = errornotificationsend
as copy into ramu.PUBLIC.video2 from @ramu.PUBLIC.snow_simple FILE_FORMAT=(FORMAT_NAME=my_csv_format) pattern='.*[.]csv';


show pipes;


select * from video2;

VVI:
--------
Make sure to clean up the environment when not in use (SNS, S3 , root user credentials , used roles , policies etc can be deleted when not in use to avoid chargers)

Check this playlist for more AWS Projects in Big Data domain:
   • Demystifying Data Engineering with Cl...