Establishing customer trust and satisfaction is crucial for any business, and observability plays a major role in achieving this goal. In fact, it is one of the top trends in the data landscape. To enable more observability and measurability of data workloads, Snowflake has been continuously adding native features. The latest addition is the Snowflake Alerts and Notifications feature, which is currently in open preview. This feature provides valuable observability into your data and workloads.

Before delving into the use cases for these features, let’s first take a moment to understand what they are and how they work.

What Are Snowflake Alerts?

A Snowflake alert is a schema level object that specifies the following:

1. A condition that triggers the alert (e.g., task failure, resource usage exceeding threshold etc.)
2. The action to perform when the condition is met (e.g., send an email notification or capture the data in a table for further analysis or both)
3. When and how often the condition should be evaluated (e.g., every 30 minutes, every day at 7 p.m., etc.)

The action triggered by the alert can be one of the following.

1. Send notification using SYSTEM$SEND_EMAIL procedure.
2. Run SQL queries.
3. Call a custom stored procedure that can be used to perform further actions.

Alerts need a virtual warehouse to execute. The schedule for the alert can be specified either by a cron expression or by an interval in minutes.

Create alert syntax:

CREATE [ OR REPLACE ] ALERT [ IF NOT EXISTS ] <name>
  WAREHOUSE = <warehouse_name>
  SCHEDULE = '{ <num> MINUTE | USING CRON <expr> <time_zone> }'
  IF( EXISTS(
    <condition>
  ))
  THEN
    <action>

The maximum supported value for num in minutes is 11520 (eight days).

While creating an alert in Snowflake, consider the following factors:

1. Use a custom role to create and execute alerts.
2. Execute alert privileges can be granted only by a user with Accountadmin role.
3. The custom role should have the usage privilege and create alert privileges on the schema where you want to create the alert. Usage privilege should be granted on the database and warehouse, as well.
4. When the alert is created initially, it will be suspended by default. Resume the alert by running the following command:

ALTER ALERT <alertname> RESUME;

In addition to setting up alerts, we can send email notifications to users using the Stored Procedure SYSTEM$SEND_EMAIL.

Sending Email Notifications

This feature is currently in open preview.

Snowflake can send email notifications now using the built in stored procedure SYSTEM$SEND_EMAIL. Snowflake manages the delivery of emails, and this feature uses the notification integration object. Let us see how it works.

To send an email notification:

1. Make sure that the intended recipients of the notification have verified their email addresses (you can do it through your profile in Snowsight UI if you have not done so).
2. Create a notification integration with type=Email. This step will fail if you include any email which is not verified.
3. Grant the privilege to the role that needs to use the notification integration.

Note that you can define a maximum of 10 email notification integrations for a given account.

Creating a Notification Integration

Only the ACCOUNTADMIN role has this privilege to create integration by default. The privilege can be granted to additional roles as needed. Below is a sample script that creates an email notification integration for sending email messages with two verified email ids within the system.

CREATE NOTIFICATION INTEGRATION EMAIL_NOTIFICATION_INTEGRATION
    TYPE=EMAIL
    ENABLED=TRUE
    ALLOWED_RECIPIENTS=('myid@domain.com','admin@domain.com’);

You can grant the privilege to use the notification integration to required roles at this point.

Send an Email Notification

Syntax:

CALL SYSTEM$SEND_EMAIL(
    '<integration_name>',
    '<email_address_1> [ , ... <email_address_N> ]',
    '<email_subject>',
    '<email_content>'
);

We can send the notification to any of the emails included in the email notification integration.

Example:

CALL SYSTEM$SEND_EMAIL (
'EMAIL_NOTIFICATION_INTEGRATION',
'myid@domain.com',
'Alert: Task failed',
'Task tsk_ingest_raw failed'
);

Use Cases

Snowflake alerts and notifications offer valuable capabilities for various use cases related to observability. Alerts can be configured to promptly respond to any issues or anomalies. Alerts and notifications can be utilized independently or in combination to enhance monitoring of your data and other workloads. Let’s explore some specific use cases in greater detail.

Use Case 1: Check and Notify When the Queries Are Running Longer Than Expected

Imagine that the majority of queries in your Data Warehouse typically complete within 10 minutes. However, occasionally, certain queries may take longer to finish, indicating a need for optimization or a larger warehouse. To stay informed and take appropriate action, it would be beneficial to receive notifications when queries exceed a 30-minute threshold and are still running. One approach is to set up an alert that examines the query history every 15 minutes, identifying such instances and promptly notifying the user.

CREATE OR REPLACE ALERT LONG_RUNNING_QUERIES_ALERT
WAREHOUSE = COMPUTE_WH
SCHEDULE = 'USING CRON */15 * * * * UTC'
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',
'myid@domain.com',
'Alert: LONG RUNNING QUERIES',
'There are queries running for more than 30 minutes in your DWH. Please check.'
);

The email address receiving the notifications should already be added to the notification integration. In the provided DDL, the alert is scheduled to execute every 15 minutes using a CRON schedule. It is important to note that there might be queries that took longer than 30 minutes but completed between the alert intervals. However, by implementing a condition that detects long-running queries, you can easily identify and prioritize queries that require immediate attention.

An email notification from Snowflake for the above alert will look like this:

Use Case 2:  Use a Custom Stored Procedure as the Action of the Alert to Capture or Summarize the Data That Triggered the Alert

Let’s explore the possibility of capturing the data that triggered the alert and storing it in a table, in addition to sending notifications. This can be valuable for conducting more detailed analysis on the data when necessary. We’ll delve into a specific use case to understand how we can achieve this.

Imagine you are a product company aiming to optimize your complaint resolution process. One of your requirements is to capture all complaints raised by premium members and notify a dedicated team periodically. To achieve this, you can set up an alert that runs on a 30-minute schedule with the following configuration:

• Condition: When a new complaint is raised by a PREMIUM member
• Action: Call a Stored Procedure that inserts the complaint data into a designated table and sends a notification to the team

To capture new complaints each time the alert runs, you can utilize the LAST_SUCCESSFUL_SCHEDULED_TIME and SCHEDULED_TIME functions within the alert. This ensures that only the latest complaints are captured and processed.

• SCHEDULED_TIME returns the timestamp representing when the current alert was scheduled.
• LAST_SUCCESSFUL_SCHEDULED_TIME returns the timestamp representing when the last successfully evaluated alert was scheduled.

Note that these two functions can be used only within the alert.

CREATE OR REPLACE ALERT CAPTURE_AND_ALERT_NEW_PREMIUM_MEM_COMPLAINT
WAREHOUSE = COMPUTE_WH
SCHEDULE = '30 MINUTES'
IF (EXISTS (
  SELECT *
  	FROM CUSTOMER_COMPLAINTS
  WHERE COMPLAINT_LOG_TS BETWEEN SNOWFLAKE.ALERT.LAST_SUCCESSFUL_SCHEDULED_TIME() 
  		AND SNOWFLAKE.ALERT.SCHEDULED_TIME()
    AND MEMBERSHIP_TYPE ILIKE 'PREMIUM'
  ))
THEN
CALL SP_PREMIUM_MEM_COMPLAINT_CAPTURE_AND_ALERT(
  SNOWFLAKE.ALERT.LAST_SUCCESSFUL_SCHEDULED_TIME(),SNOWFLAKE.ALERT.SCHEDULED_TIME()
   );

In the stored procedure, the data is captured in a table, and the notification is also sent.  You can just capture the data without sending any notifications, as well.

CREATE OR REPLACE PROCEDURE TEST_COMPLAINTS.CUSTOMER_SERVICE.SP_PREMIUM_MEM_COMPLAINT_CAPTURE_AND_ALERT("START_TS" VARCHAR , "END_TS" VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT 
EXECUTE AS CALLER
AS
$$
var sql_ins = `INSERT INTO PREMIUM_MEMBER_COMPLAINTS
  (
  COMPLAINT_ID
 ,COMPLAINT_TYPE
 ,COMPLAINT_LOG_TS
  )
  SELECT COMPLAINT_ID,COMPLAINT_TYPE,COMPLAINT_LOG_TS
  FROM CUSTOMER_COMPLAINTS
  WHERE COMPLAINT_LOG_TS BETWEEN :1 AND :2 AND MEMBERSHIP_TYPE ILIKE 'PREMIUM'`;
  
var stmt1 = snowflake.createStatement({ sqlText: sql_ins, binds: [START_TS, END_TS] });
stmt1.execute();

email_query = `call system$send_email (
'email_notification_integration',
'admin@domain.com',
'Email Alert for PREMIUM membership: New complaints logged',
'There are new complaints from members with PREMIUM membership. Please check')`;

var stmt2 = snowflake.createStatement({ sqlText: email_query });
stmt2.execute();
return "Data capture and Notification evaluation successfully completed!"
$$
;

Please note that the necessary privileges should be granted to the caller role. We have the option to enhance the custom stored procedure to include a summary of the alert condition, which can be sent along with the notification. This additional step allows you to provide more context and relevant information to the recipients of the notification.

Use case 3: Monitor and Generate an Alert if the Data fails to Refresh as per the Expected Schedule

If you have a frequently updated table that serves as the basis for near real-time reporting, it’s crucial to ensure that the data is refreshed at regular intervals. To address this requirement, Snowflake alerts can be utilized effectively. Let’s explore how we can implement an alert that checks the freshness of the data.

In this scenario, we can set up an alert to monitor the last refresh time of the table. If the refresh hasn’t occurred within the last 30 minutes, it indicates a potential issue that requires attention. The alert will be triggered based on this condition, allowing you to take appropriate action. Following alert is scheduled to run every 30 minutes and is triggered if the table was not altered between LAST_SUCCESSFUL_SCHEDULED_TIME and SCHEDULED_TIME.

CREATE OR REPLACE ALERT NOTIFY_TABLE_NOT_REFRESHED
WAREHOUSE = COMPUTE_WH
SCHEDULE = '30 MINUTES'
IF(EXISTS(
SELECT * FROM MONITORING.INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'REPORTING'
  AND TABLE_NAME ='LOGS'
  AND LAST_ALTERED NOT BETWEEN SNOWFLAKE.ALERT.LAST_SUCCESSFUL_SCHEDULED_TIME()
  AND SNOWFLAKE.ALERT.SCHEDULED_TIME()
  ))
THEN CALL SYSTEM$SEND_EMAIL(
'email_notification_integration',
'myid@domain.com',
'Alert: Log Table not refreshed in last 30 minutes',
'The log table has not been refreshed for the last 30 minutes.'
);

If you prefer to have the flexibility to suspend or resume the alert, or if you want to continuously check the freshness of the table independent of the alert schedule functions, you can utilize the following query within the condition:

SELECT
    DATEDIFF(MINUTE, LAST_ALTERED, CURRENT_TIMESTAMP())
  AS MINUTES_SINCE_LAST_UPDATE
    FROM MONITORING.INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'REPORTING'
    AND TABLE_NAME ='LOGS' 
        AND MINUTES_SINCE_LAST_UPDATE > 30

What Are Some Other Use Cases for Alerts and Notifications in Snowflake?

1. Notify when data does not comply with a business rule.
2. Notify when a task failure occurs.
3. By utilizing Snowflake tags and alerts, you can effectively monitor resource usage by grouping resources and receiving notifications when they exceed predefined thresholds set for each group. This allows for better control and management of resource allocation within your Snowflake environment.
4. For a newly deployed production ingestion pipeline, it is beneficial to configure alerts that notify you when queuing or remote spilling occurs in a virtual warehouse. These alerts serve as indicators to assess whether scaling out the clusters or scaling up the warehouse size is necessary. By proactively monitoring and responding to these alerts, you can optimize the performance and efficiency of your ingestion pipeline to meet the demands of your workload.
5. Set up an alert to monitor the cloud services usage of the warehouse and identify if it exceeds a specific percentage relative to the compute credits. This proactive approach allows you to evaluate whether adjustments are required to effectively manage your costs. By closely monitoring the usage patterns and aligning them with your budget and resource allocation, you can optimize the utilization of cloud services and ensure cost-efficiency in your operations.

How Do We Monitor Execution of Snowflake Alerts?

Alerts can be used to monitor your workloads and data. How will you monitor and troubleshoot alert status? To effectively monitor and troubleshoot alert status, consider the following options:

1. 1. Check the history of alert executions by executing ALERT_HISTORY table function in the INFORMATION_SCHEMA. For example, below query will give the history of alert executions and the scheduled alerts over the past hour.

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

The history returns one row for each alert execution. Each record will show the execution details including the state of the alert which can be one of the following.

1. 1. 1. SCHEDULED
      2. EXECUTING
      3. FAILED
      4. CANCELLED
      5. CONDITION_FALSE
      6. CONDITION_FAILED
      7. ACTION_FAILED
      8. TRIGGERED
   2. If you do not see your alert in the ALERT_HISTORY, check whether the alert is resumed after the creation. The state of the alert(suspended/started) and other details can be checked by running either of the following commands.

      SHOW ALERTS; DESC ALERT <alert_name>;

   3. CONDITION_QUERY_ID and ACTION_QUERY_ID of the alert execution are returned in the alert history.  You can check the results of the query in the condition that triggered the alert by passing the CONDITION_QUERY_ID in the RESULT_SCAN function.Example:

      SELECT * FROM TABLE(RESULT_SCAN(<CONDITION_QUERY_ID_from_alert_history>));

Limitations and Considerations

1. Data security and privacy.The Send_Email stored procedure feature in Snowflake is currently hosted on AWS in some selected regions (refer Sending Email Notifications | Snowflake Documentation), which is outside of your Snowflake instance and may right now require going outside of your region. To avoid the risk of exposing sensitive information and PII (Personal Identification Information) data, it’s recommended to limit the email content to only notify the user of the condition or the summary that triggered the alert. Any relevant information about the condition can be captured within the system for further analysis instead of being included in the email body. Follow best practices and adhere to relevant data protection regulations.

2. Since the email provider for the Send_Email stored procedure is external to your organization, it’s important to note that using distribution email addresses from your organization to send notifications is not possible.
3. When scheduling an alert, it’s important to consider the virtual warehouse that will be used. For frequent alerts, it is advisable to select a virtual warehouse that is already active rather than starting a new warehouse solely for the purpose of the alerts. This approach optimizes resource utilization and reduces the need to activate additional warehouses.
4. Be mindful of the potential for alert noise. Irrelevant or excessive alerts can overwhelm the monitoring system. Regularly review and refine the alerting rules to align with the changing system dynamics.

Summary

Snowflake alerts offer valuable functionality for monitoring and troubleshooting workloads and data. When an alert is triggered, users have the flexibility to perform various actions, such as sending notifications or executing SQL statements to initiate downstream processes. By leveraging Snowflake’s native notification system in conjunction with alerts, you can ensure timely and effective communication, enabling you to quickly respond to critical events. Since we have the ability to call a custom stored procedure as an action of the alert trigger, it opens up opportunities to perform additional non-standard actions. For example, you can trigger an external function from the procedure, enabling you to send an email with your own email provider or add the alert information to an external queue that in turn passes the alert information to a Slack/Teams channel. Experience the power of Snowflake alerts and take control of your data operations today.