Snowflake Alerts: Schedule Conditional SQL Statements

Snowflake alerts provide a proactive way to monitor and respond to critical events within your Snowflake environment. By setting up automated alerts, users can track various metrics, such as query execution times, failed login attempts, data load failures, or warehouse credit usage, without manual intervention.

In this article, let us discuss what are Snowflake Alerts, their use case and how can they be configured.

2. What are Snowflake Alerts?

A Snowflake Alert allows scheduled execution of conditional SQL statements, enabling users to monitor specific events or thresholds within their Snowflake environment. Alerts run at predefined “frequency”, checking for a “condition” and triggering an “action” when that condition is met.

The following is the syntax to create Snowflake Alerts.

CREATE OR REPLACE ALERT <alert_name>
WAREHOUSE = <warehouse_name> 
SCHEDULE = '<schedule_details>'
IF( EXISTS(
    <condition>
  ))
  THEN
    <action>

2.1. <alert_name>

The name of the Snowflake Alert

2.2. WAREHOUSE = <warehouse_name>

An alert requires a warehouse for execution. Either of the following compute models can be chosen for individual tasks:

User-managed (i.e. Virtual warehouse)
Snowflake-managed (i.e. Serverless compute model)

If you choose to use the serverless compute model, the WAREHOUSE parameter is not required.

2.3. SCHEDULE = ‘<schedule_details>’

Snowflake Alerts are not event based, instead they run on a schedule. The Snowflake engine has a CRON and NONCRON variant scheduling mechanisms.

NONCRON: <num> MINUTE

Specifies the interval (in minutes) of wait time between alert evaluations. Only positive integers are accepted.

CRON: USING CRON <cron_expr> <time_zone>

To schedule a snowflake task in CRON notation, you must use the keyword USING CRON followed by 5 asterisks and the time zone.

The cron expression consists of the following fields:

# __________ minute (0-59)
# | ________ hour (0-23)
# | | ______ day of month (1-31, or L)
# | | | ____ month (1-12, JAN-DEC)
# | | | | _ day of week (0-6, SUN-SAT, or L)
# | | | | |
# | | | | |
  * * * * *

2.4. <condition>

The SQL statement that represents the condition for the alert. The supported commands are:

SELECT
SHOW <objects>
CALL

2.5. <action>

The SQL statement to be executed if the condition returns one or more rows.

3. How to Create a Snowflake Alert?

The following code creates a Snowflake Alert that checks for any long-running queries and sends out an email notification if such queries exist.

CREATE OR REPLACE ALERT my_alert
  WAREHOUSE = compute_wh
  SCHEDULE = '5 minutes'
  IF (EXISTS(
    SELECT query_id
    FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
    WHERE DATEDIFF(MINUTE, start_time, end_time) > 5
    AND start_time > DATEADD(HOUR, -1, CURRENT_TIMESTAMP())
  ))
  THEN
    CALL SYSTEM$SEND_EMAIL(
    'my_email_int',
    'admin@thinketl.com',
    'Snowflake Alert: Long Running Queries',
    'Long Running queries detected.\nFrom Snowflake')
    ;

In the above Snowflake Alert:

WAREHOUSE: The alert runs using the compute_wh warehouse for execution. If we are creating a serverless alert, WAREHOUSE parameter should be omitted.
SCHEDULE: The alert is set to run every 5 minutes.
CONDITION: Looks for queries that executed for more than five minutes within the last hour.
ACTION: Sends an email to admin@thinketl.com using SYSTEM$SEND_EMAIL procedure if any long-running queries are detected.

4. What are Snowflake Functions that provide Information of Alert Schedules?

In the above scenario, if the alert is inactive and then resumed after a few hours, it will still only check for long-running queries within the last one hour. However, ideally, we would want the alert to monitor long-running queries since the last time it was successfully executed.

To address this, Snowflake provides functions (defined in the SNOWFLAKE.ALERT schema) that allow you to fetch the current schedule details of the alert, as well as information about its last successful execution.

SCHEDULED_TIME returns the timestamp of the current scheduled alert.
LAST_SUCCESSFUL_SCHEDULED_TIME returns the timestamp of the last successfully scheduled alert.

These functions help ensure that any queries missed during the alert’s inactive period are still accounted for when the alert is resumed.

The following code creates a Snowflake Alert that checks for any long-running queries between the time since its last ran successfully and current scheduled time.

CREATE OR REPLACE ALERT my_alert
  WAREHOUSE = compute_wh
  SCHEDULE = '5 minutes'
  IF (EXISTS(
    SELECT query_id
    FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
    WHERE DATEDIFF(MINUTE, start_time, end_time) > 5
    AND start_time BETWEEN SNOWFLAKE.ALERT.LAST_SUCCESSFUL_SCHEDULED_TIME()
    AND SNOWFLAKE.ALERT.SCHEDULED_TIME()
  ))
  THEN
    CALL SYSTEM$SEND_EMAIL(
    'my_email_int',
    'admin@thinketl.com',
    'Snowflake Alert: Long Running Queries',
    'Long Running queries detected.\nFrom Snowflake')
    ;

5. How to Resume and Suspend a Snowflake Alert?

When a Snowflake Alert is created, it is in suspended state by default. The alert must be resumed manually for it to start executing. The following command resumes a Snowflake Alert.

ALTER ALERT my_alert RESUME;

A Snowflake Alert can be suspended from executing temporarily if required. The following command suspends a Snowflake Alert.

ALTER ALERT my_alert SUSPEND;

6. How to Manually Execute a Snowflake Alert?

The EXECUTE ALERT command manually runs an alert, ignoring its regular schedule.

The following command manually executes a Snowflake Alert.

EXECUTE ALERT my_alert;

7. How to Modify a Snowflake Alert?

Snowflake Alerts can be modified by executing the ALTER ALERT command.

The following commands demonstrates modifying various aspects of an Alert.

-- Modifying Warehouse
ALTER ALERT my_alert SET WAREHOUSE = <new_warehouse>;

-- Modifying Frequency
ALTER ALERT my_alert SET SCHEDULE = '10 minutes';

-- Modifying Condition
ALTER ALERT my_alert MODIFY CONDITION EXISTS (<new condition>);

-- Modifying Action
ALTER ALERT my_alert MODIFY ACTION <new action>;

8. How to Extract Information of Existing Snowflake Alerts?

8.1. SHOW ALERTS

Lists the alerts for which the user have access privileges. It returns information of creation date, database and schema names, schedule, and its current state.

The below command extracts the alerts created in the database and schema of the current session.

SHOW ALERTS;

8.2. DESCRIBE ALERT

Describes the properties of a Snowflake Alert, including the creation date, name, data type, and SQL expression.

The below command extracts information of a Snowflake Alert.

DESCRIBE ALERT my_alert;
DESC ALERT my_alert;

9. How to Monitor Snowflake Alerts?

The ALERT_HISTORY table function in the INFORMATION_SCHEMA schema provides information about alert executions within a specified date range.

The following query will return the most recent 10 entries for the alert my_alert within the last 24 hours.

SELECT *
FROM
  TABLE(INFORMATION_SCHEMA.ALERT_HISTORY(
    SCHEDULED_TIME_RANGE_START => DATEADD(HOUR, -24, CURRENT_TIMESTAMP()),  
    SCHEDULED_TIME_RANGE_END => CURRENT_TIMESTAMP(), 
    RESULT_LIMIT => 10, 
    ALERT_NAME => 'my_alert'
  )
)
ORDER BY SCHEDULED_TIME DESC;

10. Snowflake Alerts vs Snowflake Tasks

While there are certain similarities between Tasks and Alerts in the way they can be scheduled and executed, Tasks are primarily used to automate the execution of SQL based operations like copying and loading data, whereas Alerts trigger are designed for condition-based monitoring which triggers an action when specific conditions are met.

Although conditional statements can be coded into a Stored Procedure and triggered through a Task similar to an Alert, Alerts offer built-in functions to track their scheduled execution times. This feature is particularly useful for recovering after periods of inactivity, as Alerts can automatically assess when they were last successfully run, ensuring no critical conditions are missed during downtime.

Subscribe to our Newsletter !!

Join our WhatsApp Channel

Connect with us on LinkedIn

Related Articles: