Snowflake Alerts

Monitoring & governing through Snowflake “ALERTS”

Very often we come across scenarios where there is a need to perform some actions based on some conditions and this is where “Snowflake alerts” would come into picture.

What is a Snowflake alert ?

This is a schema level object that periodically performs actions based on conditions which we provide and that too in a scheduled manner.

1. Condition :: This has to be defined as something which is essentially going to trigger the alert.
2. Action: Once the condition is met we can define what actions do we want to take and this can be sending out email notifications, logging the out of some queries to the table, calling a stored procedure, etc..
3. Schedule: How frequently we want to use this. Basically it defines how often we want to run these alerts.

Some of the use cases which it can solve are given as below:

Use Case 1 :: Sending alert based on the long running query.

Use Case 2 :: Loading a custom metadata table based on what is the output of the primary query which would have the threshold.

Use Case 3 :: To run some logging programs based on what gets evaluated in the primary condition.

Use Case 4:: Defined the alert post successful execution of the pipeline.

Use Case 5:: It can help in monitoring resource utilization.

Use Case 6:: It does help in monitoring the Snowflake usage like Storage, Compute and all separately as well.

Use Case 7:: To get the real-time status of replication happening from one account to another.

How it is governed ?

Creating and managing alerts are completely governed by RBAC, hence apart from the system defined role a custom role can be created and necessary privileges to be granted to that role which eventually can be granted to the end user for managing the alerts. The steps required to create a custom role for alerts are given as below:

GRANT CREATE ALERT ON SCHEMA my_schema TO ROLE my_alert_role;
GRANT USAGE ON SCHEMA my_schema TO ROLE my_alert_role;

GRANT USAGE ON DATABASE my_database TO ROLE my_alert_role;
GRANT USAGE ON WAREHOUSE my_warehouse TO ROLE my_alert_role;

GRANT EXECUTE ALERT ON ACCOUNT TO ROLE my_alert_role;

GRANT ROLE my_alert_role TO USER my_user;

Let us see it in action now !!

Over here I would be demonstrating a use case in which there is a need to track the usage in the Snowflake account and have some alert mechanism put so that if there is “any query which takes long than some specific time” then an “email alert” to be sent out.

Step 1 : Let us create a notification integration object first

CREATE OR REPLACE NOTIFICATION INTEGRATION my_email_int
  TYPE=EMAIL
  ENABLED=TRUE
  ALLOWED_RECIPIENTS=('somen.XXXXX@YYYY.com');  ---> Please put the exact email id which has been validated by the Snowflake account.

Step 2 : The virtual warehouse & necessary roles for creating the alert.

Over here we can perform steps which has been explained above 
under the "how it is governed" section

Step 3 : Create an alert

CREATE OR REPLACE ALERT demo_alert_lrq        
  WAREHOUSE = compute_wh
  SCHEDULE = '30 MINUTE'
  IF (EXISTS (
      select query_id from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY where warehouse_name='compute_wh' and total_elapsed_time>300
  ))
  THEN CALL SYSTEM$SEND_EMAIL(
    'my_email_int',
    'somen.XXXXX@YYYY.com',
    'Email Alert: Warehouse Load',
    'Queries running longer than usual'    
);

This is the most important step, if we see above then our condition is “long running query identification”. The action is to send an email. The schedule is over here to run it at an interval for 30 minutes. Once these are configured below is what we get and we do some basic validations.

Step 4: Validations of alerts created

show alerts;
desc alert demo_alert_lrq;

The o/p from the console

Other considerations:

After the alert is created we can even manually execute the alert to see if things are working as expected or not. The command for doing it is given as below:

EXECUTE ALERT demo_alert_lrq;

If the alert is paused(in suspended state), the EXECUTE ALERT command will make it run the job but would not the status of the alert would not change i.e., the alert will stay paused OR in suspended state.

Manual execution of alert is something which has been introduced only recently within Snowflake feature release that gives the ability to do the testing of the created alerts.

Good to know details about “alerts”:

Details of the alerts

SUMMARY:

Snowflake Alerts go a long way in terms of giving insights around what is happening within the platform. When used along with notifications it has got potential to trigger alarms to the intended recipients based on certain conditions which we provide.

Please keep reading my blogs it is only going to encourage me in posting more such content. You can find me on LinkedIn by clicking here and on Medium here. Happy Learning :)

Awarded as “Data Superhero by Snowflake for year 2023”, click here for more details.

Disclaimer: The views expressed here are mine alone and do not necessarily reflect the view of my current, former, or future employers.