A Guide to Understanding Alerts and Notifications in Snowflake

Overview

Snowflake is a cloud-based data platform that helps organizations store, manage, and analyze data more efficiently. It is a cloud-based data warehouse that can serve as a centralized repository for our data. Snowflake uses a multi-cluster, shared data architecture that allows users to access the same data simultaneously without conflicts.

In a nutshell, Snowflake simplifies and modernizes data management and analysis by leveraging cloud technology. It can be a valuable platform for businesses looking to harness the power of their data for insights and decision-making.

Introduction

The most efficient and best possible mechanism to monitor data workloads or manage costs associated with data in Snowflake is by using ‘alerts and notifications’ features. The alerts and notifications are the new offerings from Snowflake that can be used for a better monitoring experience, and the best thing is we can automate them based on our requirements. The main benefit is that instead of relying on external systems to send notifications, we can leverage these two built-in Snowflake features for data loads. Currently, these two functionalities are in open preview, which will be activated on all Snowflake accounts and available to use by default. Let’s see how we can use both these functionalities and incorporate them into our workloads.

Imagine running an E-Commerce company and facing a common challenge while handling and analyzing massive amounts of customer data. With Snowflake:

• Scalability – You can scale data warehouse resources up or down based on customer shopping peak hours.
• Data Warehousing – Allows you to consolidate all customer data in one place.
• Data sharing – Helps you to collaborate with partners or marketing agencies.
• Performance – Allows your analytics team to run multiple queries simultaneously with efficient optimizations and no downtime.
• Security – Helps you to ensure customer data privacy and follow compliance regulations.

Snowflake Email Notifications:

Snowflake offers a built-in stored procedure to send email notifications called:

• SYSTEM$SEND_EMAIL()

When we use this built-in feature, email notifications will be sent to the specified recipients from ‘no-reply@snowflake.net’ and return a boolean value, either true or false, based on the execution status of the procedure.

Syntax:

Parameters:

<integration_name> = name of the email notification integration we want to use.

<email_address> = recipient list that should receive email notification. These should be specified in a comma-separated string.

<subject_of_the_email> = subject line of the email and cannot be an empty string.

<body_of_the_email> = email content and cannot be an empty string and supports only plain text.

Example 1

Sending an email notification from Snowflake involves three simple steps:

1. Creating a notification integration, and while creating, we should specify the email address that belongs to any user in our Snowflake account. Also, the email addresses specified in the ALLOWED_RECIPIENTS should verify their email address. Otherwise, the CREATE NOTIFICATION INTEGRATION command throws an error.

2. Granting USAGE privilege to a role to use the notification integration.

3. Sending an email notification. We need to ensure that email addresses in the recipient list are included in the ALLOWED_RECIPIENTS property of the notification integration. Otherwise, no emails will be sent.

The triggered email notification from Snowflake will look like this:

Note:

The Notification System Stored Procedure feature is only hosted on Amazon Web Services (AWS). So, our snowflake should reside in AWS to use this feature with our workloads.

Snowflake Alerts

‘Alerts’ in Snowflake are actions we need to perform when data that resides in Snowflake meets certain criteria (or specific conditions). We can schedule these snowflake alerts to perform periodically based on the requirement.

So, a snowflake alert is mainly comprised of three parts:

1. A condition (or a group of conditions) that triggers the alert.
2. An action that needs to be performed when the alert is triggered.
3. How often the condition should be evaluated is also called frequency.

Some of the most common scenarios where can use alerts are:

1. When an unauthorized user tries to access a table or view.
2. When resource utilization by a task or materialized view exceeds the specified limit.
3. When a user consumes warehouse credits more than the specified percentage of the assigned quota., etc.

Syntax:

Parameters:

<alert_name> =  must be a valid identifier and should be unique to the schema in which the alert is created.

<warehouse_name> = to specify the warehouse that provides computing resources to execute the alert.

<num> = specifies how often the alert condition should be evaluated in minutes and should be a positive integer.

<condition> = can be a SELECT or SHOW <objects> or CALL statement. If the specified SQL statement returns one or more rows, then the action for the alert will be performed.

<action> = can be an SQL statement that should be executed if the condition returns at least one row. For example, we can call Snowflake built-in email notification stored procedure.

Example 2

Let’s see how we can create an alert that monitors warehouse credits every day at 7 AM that we spent in the last 24 hours and send an alert if a warehouse usage exceeds 10 credits.

1. First, we must create a role with EXECUTE ALERT global privilege. This can be the ACCOUNTADMIN role.

2. Creating an alert

3. By default, the alert will be in a suspended state. We have to enable it manually.

Note:

When we create a new alert, it will be suspended by default. We have to enable it manually for the alert to execute by using the below command:

• ALTER ALERT <alert_name> RESUME;

Conclusion

These two new offerings by Snowflake have advantages and disadvantages, but by incorporating these two features into our workloads, we can have a better monitoring experience. We can manage our alerts by using the ‘SHOW ALERTS’ command. Also, we can monitor our alerts, i.e., alerts on alerts, by leveraging the ‘ALERT_HISTORY’ table because each time an alert is executed, a row is inserted into ‘ALERT_HISTORY’ table which explains the alert status.

Drop a query if you have any questions regarding Snowflake and we will get back to you quickly.

About CloudThat

CloudThat is an official AWS (Amazon Web Services) Advanced Consulting Partner and Training partner, AWS Migration Partner, AWS Data and Analytics Partner, AWS DevOps Competency Partner, Amazon QuickSight Service Delivery Partner, AWS EKS Service Delivery Partner, and Microsoft Gold Partner, helping people develop knowledge of the cloud and help their businesses aim for higher goals using best-in-industry cloud computing practices and expertise. We are on a mission to build a robust cloud computing ecosystem by disseminating knowledge on technological intricacies within the cloud space. Our blogs, webinars, case studies, and white papers enable all the stakeholders in the cloud computing sphere.

To get started, go through our Consultancy page and Managed Services Package, CloudThat’s offerings.