Notification Msg
2 0
Read Time:2 Minute, 22 Second

During this post we will see how we can configure the ALERT or EMAIL mechanism from Snowpipe. Say you have configured the Snowpipe in your account. In case of any file processing via Snowpipe, if error comes we don’t have any alert mechanism in place. Moreover, We need to monitor the status of pipe manually to confirm the status of load. To ease our monitoring process we will configure the Email notification which would trigger the mail in case of any failure.

Steps to be included:

  1. SNS Service: Firstly, Configure Simple Notification Service (Amazon SNS) is a managed service that provides message delivery from publishers to subscribers. Publishers communicate asynchronously with subscribers by sending messages to a  Clients can subscribe to the SNS topic and receive published messages using a supported endpoint type, such as Amazon Kinesis Data Firehose, Amazon SQS
SNS
  • Select Everyone for this use case in Access Policy-optional section.
  • Create Topic and noted down the generated AR.

2. Secondly, Create subscription to whom mail will be sent.

Subscription

3. Thirdly, and most importantly, Go to AWS Management Console and create Policy : snowflake_sns_error_notification

Policy

4. Moreover, Create Role and Associate Policy.

Snowpipe Role
  • Associate your previous created Policy
  • Name the Role : snowpipe_SNS_Error_Role

Notification Integration:

5. Creating the Notification Integration.

  • In addition, Create a notification integration using CREATE NOTIFICATION INTEGRATION. An integration is a Snowflake object that references the SNS topic you created

CREATE NOTIFICATION INTEGRATION Snowpipe_Error_Notify
ENABLED = true
TYPE = QUEUE
NOTIFICATION_PROVIDER = AWS_SNS
DIRECTION = OUTBOUND
AWS_SNS_TOPIC_ARN = 'arn:aws:sns:us-east-1:913267004595:snowpipe_alert'
AWS_SNS_ROLE_ARN = 'arn:aws:iam::913267004595:role/snowpipe_SNS_Error_Role'

  • DESC notification integration Snowpipe_Error_Notify;
  • Note down: SF_AWS_IAM_USER_ARN, SF_AWS_EXTERNAL_ID
Notification Info

6.After that, Modify the Trust Relationship in the IAM Role with above noted ARN and External ID.

Trust Policy

7. Above all, Create PIPE and Enabling Error Notifications.

CREATE or replace PIPE demo_db.public.error_notify auto_ingest=true
ERROR_INTEGRATION = Snowpipe_Error_Notify
AS copy into CUST_INFO from @demo_db.public.ext_csv_stage/customers

8.Finally, Configure the PIPE ARN with S3 Bucket and upload the file to bucket. This will trigger the PIPE and in case of any error     an email will be triggered to our mail ID.

Notification Msg
Snowpipe Error

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 *