Setting up alerting for Snowflake data platform

Thank you for reading my earlier blogs, we are going to learn more about different ways, approaches we can use to integrate alerting for Snowflake data platform.

What are Alerts? What is Alerting?

Alerting or Alerts play a crucial role in any application, system implementation. Alerts are notifications, messages setup to notify existing issues as well as proactive notification upon reaching up given threshold values, limits of utilizations. We can refer these alerts as part of reactive and pro-active alerting mechanisms.

What is reactive alerting? How it is being used?

As name says, reactive alerting mechanism is used to send alerts, messages, emails as part of reaction to any event, issues, failures happened / happening in the system/application/data platform. E.g. notification of job failure, alerting failed queries etc. These alerts, notifications are set to inform application teams or support teams on failures so that they can take necessary actions to maintain application health in result meeting their business needs/requirements.

What is pro-active alerting? How it is being used?

As name indicates, this is proactive alerting which is setup based on the proactive monitoring, observing application health, system utilizations, capturing and comparing thresholds, limits. Alerts/notifications are send as soon as it reaches or crosses threshold setup. E.g. sending notification as soon as warehouse utilization reaches 90%, sending notification if any queries are being run for long time than given threshold etc. These alerts help application teams to configure warehouses of right size, setting up appropriate scaling policies, optimizing queries etc.

What are the different ways to setup alerting in Snowflake?

We can setup both reactive as well as proactive alerting in Snowflake. There are various ways to set this up however I would like to share some of them in this blog. We are going to discuss about below approaches in this blog –

· Sending email notification using Snowflake function

· Sending email notification using AWS SNS

We can also integrate these alerts to any other alerting integrations like OpsGenie, AWS Cloudwatch or any other services of cloud providers or open source.

How can we use Snowflake function to send email notification?

Snowflake offers various types of integrations using which we can create integrations with data platform. We are going to use notification integration and SYSTEM$SEND_EMAIL function to send notification. Lets learn this approach step by step –

1. Create notification integration –

Notification integration is nothing but an object in snowflake which is used to integrate with third party services. We can create notification integration using simple SQL statement –

create notification integration Sample_email_int
type=email
enabled=true
allowed_recipients=('firstusr.last@sample.com','secondusr.last@sample.com')
;

Allowed recipients are the users which have verified and authorized their email ids in their accounts.

2. Setting up right permissions –

We need to set permissions to use the notification integration created and grant usage permissions to be used along with function SYSTEM$SEND_EMAIL. We can grant usage permissions using SQL –

grant usage on integration Sample_email_int to role data_eng_role;

3. Send email notification –

We can use SQL statement to call the system function and use notification integration to send notification

call system$send_email(
'Sample_email_int',
'eng_team@example.com, ops_team@example.com',
'Batch Alert: Data load has finished.',
'Todays Batch has successfully finished.\nStart Time: 09:15:30\nEnd Time: 17:25:55\nTotal jobs Processed: 56'
);

This SQL statement will send an email to recipients mentioned in SQL statements. These users have to be allowed recipients, if the users are not verified then no mail will be sent. We are defining email subject as Batch alert and email body as text mentioned with batch details.

You can use this simple integration and send notifications using scheduled jobs or tasks in Snowflake. There is limit of setting up to 10 integrations per account.

How can we use AWS SNS to send notification from Snowflake?

Similar to notification integration created for email, we can use same to create for SNS. We can use similar SQL with SNS details to create an integration.

1. Create AWS SNS Topic — create a topic to be used to send messages and subscribe to email. Note the ARN created for SNS topic.

2. Create IAM policy — create IAM policy from AWS console and add below JSON with SNS topic ARN created above step

{
"Version": "2012–10–17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"sns:Publish"
],
"Resource": "<sns_topic_arn>"
}
]
}

3. Create AWS IAM Role — Create a role to grant permissions to receive , publish messages received from Snowflake

4. Note ARN of SNS topic & IAM role created in first 2 steps

5. Create integration — Use ARNs noted in #3 step and run below SQL to create integration

CREATE NOTIFICATION INTEGRATION aws_sns_int
ENABLED = true
TYPE = QUEUE
NOTIFICATION_PROVIDER = AWS_SNS
DIRECTION = OUTBOUND
AWS_SNS_TOPIC_ARN = 'arn:aws:sns:us-west-2:xxxx:snowflakemailnotification'
AWS_SNS_ROLE_ARN = 'arn:aws:iam::xxxx:role/snowflake_sns_role'
;

6. Describe integration and note the values of external ID and user ARN

DESC NOTIFICATION INTEGRATION aws_sns_int;

7. Go to AWS IAM role created and create policy and update the JSON to add the values of user ARN and external id to allow publish messages

{
"Version": "2012–10–17",
"Statement": [
{
"Sid": "",
"Effect": "Allow",
"Principal": {
"AWS": "<sf_aws_iam_user_arn>"
},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": {
"sts:ExternalId": "<sf_aws_external_id>"
}
}
}
]
}

8. Create a snowflake task to use the notification integration created and send message upon failure

create task mytask
schedule = '15 MINUTE'
error_integration = aws_sns_int
as
insert into audittable(ts) values(current_timestamp);

9. Enable the task created using sql command –

show tasks;
alter task mytask resume;

10. Create a subscription to SNS topic created in AWS. Give your email id to receive mail notifications

11. Task will run every 15 minutes and send notification upon failure

12. The mail sent will be as and when task failed to run the command

This is second approach to use AWS SNS to receive messages from Snowflake and send it to email users. Once the messages received in SNS , we can use different approaches to send it as notification/alerts or messages to end users.

Hope this blog helps you to learn more about alerting and notification integrations in Snowflake.

About Me :

I am DWBI and Cloud Architect! I am currently working as Senior Data Architect — GCP, Snowflake. I have been working with various Legacy data warehouses, Bigdata Implementations, Cloud platforms/Migrations. I am SnowPro Core certified Data Architect as well as Google certified Google Professional Cloud Architect. You can reach out to me LinkedIn you need any further help on certification, Data Solutions and Implementations!