How to Create Alerts in Snowflake

The Snowflake Data Cloud is a leading cloud data platform that provides various features and services for data storage, processing, and analysis. A new feature that Snowflake offers is the ability to create alerts based on data in Snowflake. Alerts are schema-level objects that allow you to specify a condition, an action, and a schedule for periodically evaluating the condition and performing the action when the condition is met.

For example, you can use alerts to send notifications, capture data, or execute SQL commands when certain events or thresholds are reached in your data.

In this blog, we will go over how to create alerts in Snowflake, explaining the necessary privileges, how to configure the alert parameters, and how to monitor alert history. We will also provide some use cases and best practices for alerts. By the end of this post, you will be able to set up your own alerts and get notified or take action automatically based on data in Snowflake.

What Are Alerts in Snowflake?

Snowflake alerts are vital for proactive monitoring and action in response to specific data-related conditions. These alerts are crucial when you need to stay informed about events such as exceeding your warehouse credit usage or resource consumption thresholds or when data violates business rules you’ve established.

By configuring a Snowflake alert, you create a schema-level object that defines the triggering condition (e.g., queries with execution times over a specified duration), the action to take when the condition is met (like sending email notifications or storing data in a table), and the frequency of condition evaluation (e.g., daily or weekly).

This feature enables efficient management, resource optimization, and adherence to data integrity, enhancing the overall reliability and security of your Snowflake data operations.

Snowflake Alerts have recently been made available for the general public to use, but as an elite partner, phData has had early access to the feature. With these insights, we can shed light on how we created alerts in Snowflake for our clients and what use cases they were helpful with.

How to Create Alerts in Snowflake

Setting up Privileges

As with most features in Snowflake, privileges are required to be set up to create and use alerts.

EXECUTE ALERT on the account
- Can only be assigned by an ACCOUNTADMIN role

				
					USE ROLE ACCOUNTADMIN;
CREATE ROLE ALERT_ROLE;
GRANT EXECUTE ALERT ON ACCOUNT TO ROLE ALERT_ROLE;

USAGE and CREATE ALERT privileges on the schema in which you want to create the alert

				
					GRANT CREATE ALERT ON SCHEMA MY_SCHEMA TO ROLE ALERT_ROLE;
GRANT USAGE ON SCHEMA MY_SCHEMA TO ROLE ALERT_ROLE;

USAGE privilege on the database containing the schema

				
					GRANT USAGE ON DATABASE MY_DATABASE TO ROLE ALERT_ROLE;

USAGE privilege on the warehouse used to execute the alert

				
					GRANT USAGE ON WAREHOUSE MY_WAREHOUSE TO ROLE ALERT_ROLE;

Creating Email Notification Integration

Alerts aren’t required to be able to send email notifications, but if the use case requires it, you’ll need an email notification integration set up.

A few notes about email notification integrations:

Only an ACCOUNTADMIN can create them
Emails listed on the notification must be active users on the Snowflake account, and the users must have verified their email addresses.
Up to 10 email addresses can be listed on the notification.

The following code creates an email notification integration:

				
					CREATE NOTIFICATION INTEGRATION MY_EMAIL_INTEGRATION
TYPE = EMAIL
ENABLED = TRUE

Privileges will then have to be granted to the ALERT_ROLE we created earlier:

				
					GRANT USAGE ON INTEGRATION MY_EMAIL_INTEGRATION TO ROLE ALERT_ROLE;

Creating an Alert

Alerts require:

A specified warehouse to run on
A specified schedule
- It can be in the form of minutes or using CRON to schedule specific running dates and times.
A condition in the form of an SQL query
An action to take when the condition is met

Here is a simple example of an alert that will send an email whenever data in a table does not comply with the business rule:

				
					CREATE OR REPLACE ALERT SOFTWARE_UTILIZATION_UNDER_50_ALERT
WAREHOUSE = MY_WAREHOUSE
SCHEDULE = '1 MINUTE'
IF( EXISTS(
SELECT PERCENTAGE_VALUE FROM SOFTWARE_UTILIZATION WHERE PERCENTAGE_VALUE < 50))
THEN
CALL SYSTEM$SEND_EMAIL (
'MY_EMAIL_INTEGRATION',
'jane.doe@example.com',
'Email Alert: Utilization Under 50 Percent',
'Please note that the software utilization has fallen below 50%.'
);

Suspending and Resuming Alerts

Once an alert is created, it is in a suspended state by default. You must resume the alert before it begins its scheduled runs:

				
					ALTER ALERT SOFTWARE_UTILIZATION_UNDER_50_ALERT RESUME;

If an alert is no longer needed to be run, it can similarly be suspended:

				
					ALTER ALERT SOFTWARE_UTILIZATION_UNDER_50_ALERT SUSPEND;

Altering and Dropping Alerts

All the required parameters for alerts can be altered after the fact. Here are examples of altering each parameter:

				
					ALTER ALERT SOFTWARE_UTILIZATION_UNDER_50_ALERT 
SET WAREHOUSE = MY_OTHER_WAREHOUSE;

ALTER ALERT SOFTWARE_UTILIZATION_UNDER_50_ALERT 
SET SCHEDULE = '5 MINUTES';

ALTER ALERT SOFTWARE_UTILIZATION_UNDER_50_ALERT 
MODIFY CONDITION EXISTS (SELECT PERCENTAGE_VALUE FROM SOFTWARE_UTILIZATION WHERE PERCENTAGE_VALUE < 60);

ALTER ALERT SOFTWARE_UTILIZATION_UNDER_50_ALERT 
MODIFY ACTION CALL MY_PROCEDURE();

Dropping an alert is done with the DROP ALERT command:

				
					DROP ALERT SOFTWARE_UTILIZATION_UNDER_50_ALERT;

Best Practices and Tips for Alerts in Snowflake

Listing and Viewing Alert Details

Like most other objects in Snowflake, alerts can a user has MONITOR and USAGE privileges can be listed with a simple query:

				
					SHOW ALERTS;

The DDL for an alert can also be obtained with a DESC query:

				
					DESC ALERT SOFTWARE_UTILIZATION_UNDER_50_ALERT;

Monitoring Alerts

Snowflake’s information schema has a table function that can be utilized to monitor when alerts are executed. For example, here is a query to view the alerts executed within the last hour:

				
					SELECT *
FROM
TABLE(INFORMATION_SCHEMA.ALERT_HISTORY(SCHEDULED_TIME_RANGE_START => dateadd('hour',-1,current_timestamp())))
ORDER BY SCHEDULED_TIME DESC;

Define Alerts Based on the Alert Schedule

In some cases, you might need to define a condition or action based on the alert schedule. For example, you can evaluate the condition if a new row was added between alert runs. This will prevent the alert from re-performing the action every scheduled run.

Taking the example from earlier, we can modify the alert so it only sends an email if a new row has been added since the last time the alert was run:

				
					CREATE OR REPLACE ALERT SOFTWARE_UTILIZATION_UNDER_50_ALERT
WAREHOUSE = MY_WAREHOUSE
SCHEDULE = '1 MINUTE'
IF( EXISTS(
SELECT PERCENTAGE_VALUE 
FROM SOFTWARE_UTILIZATION 
WHERE PERCENTAGE_VALUE < 50
AND row_timestamp 
BETWEEN SNOWFLAKE.ALERT.LAST_SUCCESSFUL_SCHEDULED_TIME()
AND SNOWFLAKE.ALERT.SCHEDULED_TIME()
))
THEN
CALL SYSTEM$SEND_EMAIL (
'MY_EMAIL_INTEGRATION',
'jane.doe@example.com',
'Email Alert: Utilization Under 50 Percent',
'Please note that the software utilization has fallen below 50%.'
);

Using Alerts to Monitor Your Snowflake Account

Alerts can have other use cases other than checking data for failure to comply with business rules, as in the examples above. Here at phData, the use case we’ve most employed alerts on is monitoring what’s happening in the client’s Snowflake account.

Here are a couple of examples of what we’ve implemented for customers:

Detecting Changes in Warehouse Size

This alert will run every 2 hours to determine if a warehouse has been changed in size by someone other than the WAREHOUSE_ADMIN and send an email notification. As you know, the larger the warehouse, the more credits it uses.

This is designed to let the administrator know if something changed so they can change it back before it racks up a large bill:

				
					CREATE OR REPLACE ALERT WAREHOUSE_DDL_CHANGE
WAREHOUSE = PROD_LOAD_WH
SCHEDULE = '120 minute'
IF (EXISTS(
select *
from snowflake.account_usage.warehouse_events_history
where event_name not in ('RESUME_WAREHOUSE', 'RESUME_CLUSTER', 'SPINDOWN_CLUSTER', 'SUSPEND_WAREHOUSE', 'SPINUP_CLUSTER', 'SUSPEND_CLUSTER')
AND USER <> 'WAREHOUSE_ADMIN'
AND TIMESTAMP 
BETWEEN SNOWFLAKE.ALERT.LAST_SUCCESSFUL_SCHEDULED_TIME() AND SNOWFLAKE.ALERT.SCHEDULED_TIME()
))
THEN CALL SYSTEM$SEND_EMAIL(
'email_alert',
'jane.doe@example.com; john.doe@example.com',
'EMAIL ALERT: Snowflake Warehouse DDL Change',
'At least one warehouse''s ddl has been changed in Snowflake.
Run the following query to view the warehouses that changed:
select *
from snowflake.account_usage.warehouse_events_history
where event_name not in (''RESUME_WAREHOUSE'', ''RESUME_CLUSTER'', ''SPINDOWN_CLUSTER'', ''SUSPEND_WAREHOUSE'', ''SPINUP_CLUSTER'', ''SUSPEND_CLUSTER'')
and USER_NAME <> ''WAREHOUSE_ADMIN''
and TIMESTAMP >= current_date-1;'
);

Long Running Queries

Another useful alert is to let administrators know when queries are running incredibly long.

For instance, this example sends a notification whenever a query reaches over 30 minutes:

				
					CREATE OR REPLACE ALERT LONG_RUNNING_QUERIES_ALERT
WAREHOUSE = COMPUTE_WH
SCHEDULE = '15 MINUTE'
IF (EXISTS
(
SELECT *
FROM TABLE(SNOWFLAKE.INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE EXECUTION_STATUS ILIKE 'RUNNING'
AND START_TIME <= CURRENT_TIMESTAMP() - INTERVAL '30 MINUTES'
))
THEN CALL SYSTEM$SEND_EMAIL (
'email_notification_integration',
'jane.doe@example.com',
'Alert: LONG RUNNING QUERIES',
'There are queries running for more than 30 minutes in your DWH. Please check.'
);

Snowflake / Cloud Provider Outages

Unfortunately, Snowflake native alerts can not be configured to alert users when Snowflake or their cloud provider has an outage. Thankfully, phData created a platform monitoring tool called CDOpsMon, which can do that and much more. CDOpsMon can easily collect, evaluate, and alert on the state of your Snowflake data platform to ensure that everything continues to run as it should.

In addition to the advantage over native alerts of being able to send emails about outages, CDOpsMon also has the ability to send emails to distribution groups not in your Snowflake account, making alerting the correct people even more accessible.

CDOpsMon functions:

To get started, please contact us and let us know about your interest in platform monitoring. We can then give you a demo, learn more about your monitoring needs, and help you to deploy or customize a solution for your organization.

Closing

In this blog, we’ve explored how to create alerts in Snowflake, a leading cloud data platform. With this knowledge, you’re now equipped to proactively react to data anomalies and automate responses, ensuring your data ecosystem remains healthy.

Whether you’re a seasoned data professional or a business leader, Snowflake’s alerting capabilities empower you to optimize your data processes and harness the full potential of your data. By embracing this tool, you’re well-prepared to navigate the dynamic data landscape confidently and precisely, keeping your data operations in perfect harmony.

If you’re looking to leverage the full power of the Snowflake Data Cloud, let phData be your guide. As Snowflake’s 2023 Partner of the Year, phData has unmatched experience with Snowflake migrations, platform management, automation needs, and machine learning foundations. Reach out today for advice, guidance, and best practices!

FAQs

What’s the difference between a task and an alert?

A task is a SQL statement that runs on a schedule or when triggered by other tasks. SQL commands allow users to create, modify, suspend, resume, and drop tasks. Tasks can be used to automate data processing workflows, such as ETL jobs, data ingestion, and data transformation. On the other hand, an alert is a task that sends notifications to a cloud messaging service when data meets certain criteria.

How does CRON work for scheduling alerts?

CRON is a UNIX format for specifying time. Using CRON in Snowflake, you can specify minutes, hours, days of the week, months, and time zones.

For example:

Every minute UTC time:
USING CRON * * * * * UTC
9 AM to 5 PM on Sunday in Los Angeles Timezone:
USING CRON 0 9-17 * * SUN America/Los_Angeles
Last day of the month in June at 230AM Chicago time:
USING CRON 30 2 L 6 * America/Chicago