Setting Up Alerts Based on Data in Snowflake¶

Introduction¶

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:

• Condition: The credit consumption for a warehouse (the sum of the credits_used column in the WAREHOUSE_METERING_HISTORY view in the ACCOUNT_USAGE) schema exceeds a specified limit.

• Action: Email the administrator.

• Frequency / schedule: Check for this condition every 30 minutes.

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.

  Note

  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 my_warehouse.

To do this:

1. Have a user with the ACCOUNTADMIN role do the following:

   1. Create the custom role.

      For example:

      USE ROLE ACCOUNTADMIN;
      
      CREATE ROLE my_alert_role;
      

      Copy

   2. Grant the EXECUTE ALERT global privilege to that custom role.

      For example:

      GRANT EXECUTE ALERT ON ACCOUNT TO ROLE my_alert_role;
      

      Copy

   3. Grant the custom role to a user.

      For example:

      GRANT ROLE my_alert_role TO USER my_user;
      

      Copy

2. 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;
     

     Copy

   • The owner of the database must grant the USAGE privilege on the database:

     GRANT USAGE ON DATABASE my_database TO ROLE my_alert_role;
     

     Copy

   • The owner of the warehouse must grant the USAGE privilege on the warehouse:

     GRANT USAGE ON WAREHOUSE my_warehouse TO ROLE my_alert_role;
     

     Copy

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 gauge_value_exceeded_history.

You can create an alert that:

• Evaluates the condition that gauge_value exceeds 200.

• Inserts the timestamp into gauge_value_exceeded_history if this condition evaluates to true.

To create an alert named my_alert that does this:

1. 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.

2. Verify that you are using database and schema in which you plan to create the alert.

   If you are not using that database and schema, execute the USE DATABASE and USE SCHEMA commands to use that database and schema.

3. 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());
   

   Copy

   For the full description of the CREATE ALERT command, refer to CREATE ALERT.

   Note

   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.

4. Resume the alert by executing the ALTER ALERT … RESUME command. For example:

   ALTER ALERT my_alert RESUME;
   

   Copy

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 alert_role, execute:

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:

1. Call the GET_CONDITION_QUERY_UUID function to get the query ID for the SQL statement for the condition.

2. Pass the query ID to the RESULT_SCAN function to get the results of the execution of that SQL statement.

For example:

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;

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 my_alert to my_other_warehouse, execute:

  ALTER ALERT my_alert SET WAREHOUSE = my_other_warehouse;
  

  Copy

• To change the schedule for the alert named my_alert to be evaluated every 2 minutes, execute:

  ALTER ALERT my_alert SET SCHEDULE = '2 minutes';
  

  Copy

• To change the condition for the alert named my_alert so that you are alerted if any rows in the table named gauge have values greater than 300 in the gauge_value column, execute:

  ALTER ALERT my_alert MODIFY CONDITION EXISTS (SELECT gauge_value FROM gauge WHERE gauge_value>300);
  

  Copy

• To change the action for the alert named my_alert to CALL my_procedure(), execute:

  ALTER ALERT my_alert MODIFY ACTION CALL my_procedure();
  

  Copy

Dropping an Alert¶

To drop an alert, execute the DROP ALERT command. For example:

DROP ALERT my_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:

SHOW ALERTS;

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;

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;
    

    Copy

  • 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.)