Setting Up Alerts Based on Data in Snowflake¶
This topic explains how to set up an alert that periodically performs an action under specific conditions, based on data within Snowflake.
In some cases, you might want to be notified or take action when data in Snowflake meets certain conditions. For example, you might want to receive a notification when:
The warehouse credit usage increases by a specified percentage of your current quota.
The resource consumption for your pipelines, tasks, materialized views, etc. increases beyond a specified amount.
Your data fails to comply with a particular business rule that you have set up.
To do this, you can set up a Snowflake alert. A Snowflake alert is a schema-level object that specifies:
A condition that triggers the alert (e.g. the presence of queries that take longer than a second to complete).
The action to perform when the condition is met (e.g. send an email notification, capture some data in a table, etc.).
When and how often the condition should be evaluated (e.g. every 24 hours, every Sunday at midnight, etc.).
For example, suppose that you want to send an email notification when the credit consumption exceeds a certain limit for a warehouse. Suppose that you want to check for this every 30 minutes. You can create an alert with the following properties:
Granting the Privileges to Create Alerts¶
In order to create an alert, you must use a role that has the following privileges:
The EXECUTE ALERT privilege on the account.
This privilege can only be granted by a user with the ACCOUNTADMIN role.
The USAGE and CREATE ALERT privileges on the schema in which you want to create the alert.
The USAGE privilege on the database containing the schema.
The USAGE privilege on the warehouse used to execute the alert.
To grant these privileges to a role, use the GRANT <privileges> command.
For example, suppose that you want to create a custom role named
my_alert_role that has the privileges to create an alert in
the schema named
my_schema. You want the alert to use the warehouse
To do this:
Have a user with the ACCOUNTADMIN role do the following:
USE ROLE ACCOUNTADMIN; CREATE ROLE my_alert_role;
Grant the EXECUTE ALERT global privilege to that custom role.
GRANT EXECUTE ALERT ON ACCOUNT TO ROLE my_alert_role;
Grant the custom role to a user.
GRANT ROLE my_alert_role TO USER my_user;
Have the owners of the database, schema, and warehouse grant the privileges needed for creating the alert to the custom role:
The owner of the schema must grant the CREATE ALERT and USAGE privileges on the schema:
GRANT CREATE ALERT ON SCHEMA my_schema TO ROLE my_alert_role; GRANT USAGE ON SCHEMA my_schema TO ROLE my_alert_role;
The owner of the database must grant the USAGE privilege on the database:
GRANT USAGE ON DATABASE my_database TO ROLE my_alert_role;
The owner of the warehouse must grant the USAGE privilege on the warehouse:
GRANT USAGE ON WAREHOUSE my_warehouse TO ROLE my_alert_role;
Creating an Alert¶
Suppose that whenever one or more rows in a table named
gauge has a value in the
gauge_value column that exceeds 200,
you want to insert the current timestamp into a table named
You can create an alert that:
Evaluates the condition that
Inserts the timestamp into
gauge_value_exceeded_historyif this condition evaluates to true.
To create an alert named
my_alert that does this:
Verify that you are using a role that has the privileges to create an alert.
If you are not using that role, execute the USE ROLE command to use that role.
Verify that you are using database and schema in which you plan to create the alert.
Execute the CREATE ALERT command to create the alert:
CREATE OR REPLACE ALERT my_alert WAREHOUSE = mywarehouse SCHEDULE = '1 minute' IF( EXISTS( SELECT gauge_value FROM gauge WHERE gauge_value>200)) THEN INSERT INTO gauge_value_exceeded_history VALUES (current_timestamp());
For the full description of the CREATE ALERT command, refer to CREATE ALERT.
When you create an alert, the alert is suspended by default. You must resume the newly created alert in order for the alert to execute.
Resume the alert by executing the ALTER ALERT … RESUME command. For example:
ALTER ALERT my_alert RESUME;
Specifying Timestamps Based on Alert Schedules¶
In some cases, you might need to define a condition or action based on the alert schedule.
For example, suppose that a table has a timestamp column that represents when a row was added, and you want to send an alert if any new rows were added between the last alert that was successfully evaluated and the current scheduled alert. In other words, you want to evaluate:
<now> - <last_execution_of_the_alert>
If you use CURRENT_TIMESTAMP and the scheduled time of the alert to calculate this range of time, the calculated range does not account for latency between the time that the alert is scheduled and the time when the alert condition is actually evaluated.
Instead, when you need the timestamps of the current schedule alert and the last alert that was successfully evaluated, use the following functions:
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.
These functions are defined in the SNOWFLAKE.ALERT schema. To call these functions, you need
to use a role that has been granted the SNOWFLAKE.ALERT_VIEWER database role. To
grant this role to another role, use the GRANT DATABASE ROLE command. For example, to grant this role
to the custom role
GRANT DATABASE ROLE SNOWFLAKE.ALERT_VIEWER TO ROLE alert_role;
The following example sends an email message if any new rows were added to
my_table between the time that the last
successfully evaluated alert was scheduled and the time when the current alert has been scheduled:
CREATE OR REPLACE ALERT alert_new_rows WAREHOUSE = my_warehouse SCHEDULE = '1 MINUTE' IF (EXISTS ( SELECT * FROM my_table WHERE row_timestamp BETWEEN SNOWFLAKE.ALERT.LAST_SUCCESSFUL_SCHEDULED_TIME() AND SNOWFLAKE.ALERT.SCHEDULED_TIME() )) THEN CALL SYSTEM$SEND_EMAIL(...);
Checking the Results of the SQL Statement for the Condition in the Alert Action¶
Within the action of an alert, if you need to check the results of the SQL statement for the condition:
Call the GET_CONDITION_QUERY_UUID function to get the query ID for the SQL statement for the condition.
Pass the query ID to the RESULT_SCAN function to get the results of the execution of that SQL statement.
CREATE ALERT my_alert WAREHOUSE = my_warehouse SCHEDULE = '1 MINUTE' IF (EXISTS ( SELECT * FROM my_source_table)) THEN BEGIN LET condition_result_set RESULTSET := (SELECT * FROM TABLE(RESULT_SCAN(SNOWFLAKE.ALERT.GET_CONDITION_QUERY_UUID()))); ... END;
Manually Executing Alerts¶
In some cases, you might need to execute an alert manually. For example:
If you are creating a new alert, you might want to verify that the alert works as you would expect.
You might want to execute the alert at a specific point in your data pipeline. For example, you might want to execute the alert at the end of a stored procedure call.
To execute an alert manually, run the EXECUTE ALERT command:
EXECUTE ALERT my_alert;
The EXECUTE ALERT command manually triggers a single run of an alert, independent of the schedule defined for the alert.
You can execute this command interactively. You can also execute this command from within a stored procedure or a Snowflake Scripting block.
For details on the privileges required to run this command and the effect of this command on suspended, running, and scheduled alerts, see EXECUTE ALERT.
Suspending and Resuming an Alert¶
If you need to prevent an alert from executing temporarily, you can suspend the alert by executing the ALTER ALERT … SUSPEND command. For example:
ALTER ALERT my_alert SUSPEND;
To resume a suspended alert, execute the ALTER ALERT … RESUME command. For example:
ALTER ALERT my_alert RESUME;
If you are not the owner of the alert, you must have the OPERATE privilege on the alert to suspend or resume the alert.
Modifying an Alert¶
To modify the properties of an alert, execute the ALTER ALERT command. For example:
To change the warehouse for the alert named
ALTER ALERT my_alert SET WAREHOUSE = my_other_warehouse;
To change the schedule for the alert named
my_alertto be evaluated every 2 minutes, execute:
ALTER ALERT my_alert SET SCHEDULE = '2 minutes';
To change the condition for the alert named
my_alertso that you are alerted if any rows in the table named
gaugehave values greater than
ALTER ALERT my_alert MODIFY CONDITION EXISTS (SELECT gauge_value FROM gauge WHERE gauge_value>300);
To change the action for the alert named
CALL my_procedure(), execute:
ALTER ALERT my_alert MODIFY ACTION CALL my_procedure();
You must be the owner of the alert to modify the properties of the alert.
Dropping an Alert¶
To drop an alert, execute the DROP ALERT command. For example:
DROP ALERT my_alert;
You must be the owner of the alert to drop the alert.
Viewing Details About an Alert¶
To list the alerts that have been created in an account, database, or schema, execute the SHOW ALERTS command. For example, to list the alerts that were created in the current schema, run the following command:
This command lists the alerts that you own and the alerts that you have the MONITOR or OPERATE privilege on.
To view the details about a specific alert, execute the DESCRIBE ALERT command. For example:
DESC ALERT my_alert;
If you are not the owner of the alert, you must have the MONITOR or OPERATE privilege on the alert to view the details of the alert.
Monitoring the Execution of Alerts¶
To monitor the execution of the alerts, you can:
Check the results of the action that was specified for the alert. For example, if the action inserted rows into a table, you can check the table for new rows.
View the history of alert executions by using one of the following:
The ALERT_HISTORY table function in the INFORMATION_SCHEMA schema.
For example, to view the executions of alerts over the past hour, execute the following statement:
SELECT * FROM TABLE(INFORMATION_SCHEMA.ALERT_HISTORY( SCHEDULED_TIME_RANGE_START =>dateadd('hour',-1,current_timestamp()))) ORDER BY SCHEDULED_TIME DESC;
The ALERT_HISTORY view in the ACCOUNT_USAGE schema in the shared SNOWFLAKE database.
In the query history, the name of the user who executed the query will be SYSTEM. (The alerts are run by the system service.)