Snowflake Alert Long Running Queries

Question

1. How to alert long running queries, to multiple users in snowflake ? Right now the alert is sent only to the account admin role user.

   Is there any way to notify the long query alert to "the user running the query OR notify to multiple users belong to the particular warehouse/database" ?

2. Is there any way to leverage Snowflake Notification Integration for the above alerts?

Thanks In Advance

Sundar

Answer 1

The only notification available out-of-the-box in Snowflake is the Resource Monitor whereby AccountAdmin members only can subscribe for notifications.

https://docs.snowflake.com/en/user-guide/resource-monitors.html#resource-monitor-properties

Answer 2

It is possible to fulfill such requirement by using alerts and email notifications.

Setting Up Alerts Based on Data in 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.
• A data access request is received from an unauthorized user.
• 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.).

Sample:

CREATE OR REPLACE ALERT alert_long_queries
  WAREHOUSE = my_warehouse_name
  SCHEDULE = '5 MINUTE'
  IF (EXISTS (
      SELECT *
      FROM TABLE(SNOWFLAKE.INFORMATION_SCHEMA.QUERY_HISTORY())
      WHERE EXECUTION_STATUS ILIKE 'RUNNING'
        AND start_time < current_timestamp() - INTERVAL '5 MINUTES'
  ))
  THEN CALL SYSTEM$SEND_EMAIL(...);