With any data warehousing solution, the ability to be alerted on key events is critical.

In order to ensure business users are consuming valid, fresh data, operational guardrails should be in place related to job failures, business rule violations or data delivery delays. As data projects mature, the scope of monitoring needs increases. Becoming aware of any cost increases, performance degradations or security related events also becomes a must.

For each of these scenarios, it is critical that users can easily set up flexible alerts to get notified when specific events occur. To help with this, Snowflake natively offers two solutions on their platform: Email Notifications & Alerts. In this post we'll do a deep dive into each solution.

Snowflake Email Notifications

The first feature is email notifications. Snowflake allows you to send emails any verified email address using a stored procedure called SYSTEM$SEND_EMAIL. An email address is verified if it belongs to a Snowflake user and has been verified by the user through an email activation link (instructions here). If you have a shared mailbox or specific email address you want to use for notifications, this means that you first have to create a separate user in Snowflake, and then assign this email to this user.

In order to use the SYSTEM$SEND_EMAIL, users need to create a notification integration.

What is a Snowflake Notification Integration

You can imagine the notification integration as a "wrapper object" which encapsulates the information and security privileges needed for sending notifications from Snowflake. To create a notification integration, the user must have a role with the CREATE INTEGRATION privilege. By default, this privilege is only available to users with the ACCOUNTADMIN role. This allows for Snowflake adminstrators to maintain the principle of "segregation of duties". Admins can create the notification integration and maintain the recipient list, and grant usage to notification integration object to the developers who can use it, but not modify it.

How to create a notification integration

To create a notification integration, you can run the following command:

CREATE NOTIFICATION INTEGRATION my_email_int
    TYPE=EMAIL
    ENABLED=TRUE
  ALLOWED_RECIPIENTS=('joe.doe@my_domain.com','another.joe@my_domain.com');

The CREATE NOTIFICATION command has 3 parameters:

• TYPE: what kind of notification integration we want (EMAIL, QUEUE)
• ENABLED: - TRUE or FALSE
• ALLOWED_RECIPIENTS: up to 10 verified email addresses where we want to deliver the email message

Once we have the notification integration in place, users might want to grant the usage privilege to some other roles so they can use it for sending emails.

GRANT USAGE ON INTEGRATION my_email_int TO ROLE my_developer_role;

Once a notification integration object is created with at least one verified email address, users can now send an email.

Manually Triggering Emails from Snowflake

The SYSTEM$SEND_EMAIL procedure is invoked with CALL keyword, just like any other stored procedure. Here's an example command:

CALL SYSTEM$SEND_EMAIL(
'my_email_int',
'joe.doe@my_domain.com,
'Hello from Snowflake Alerting',
'My first email sent from Snowflake via SYSTEM$SEND_EMAIL stored procedure'

You can notice that we define the email address again in function call. If an email address that was not in the ALLOWED_RECIPIENTS property defined in the notification integration, no email would be sent.

Here's what the email looks like from the command above:

If the stored procedure executes successfully, it returns **TRUE**:

Automatically Triggering Emails from Snowflake

Most Snowflake customers will want to have automated alerting in place. How can users achieve this?

A common deployment pattern is to embed the SYSTEM$SEND_EMAIL stored procedure in another procedure. Typically, stored procedure fill first validate if some condition is met, and if it is, trigger the SYSTEM$SEND_EMAIL procedure to send the emails.

What kind of conditions might be checked?

• Monitoring Snowflake tasks by checking if the task has been suspended
• Monitoring Snowflake streams to get notified if a stream has become stale too soon
• Validating specific business roles (i.e. users must be a paying customer in order to access feature X)

Let’s use the Snowflake task monitoring as an example. You might have a task or tree of tasks which is supposed to be running on a given schedule. You want to know if a task has become suspended and therefore stopped running. This can happen whenever a task is modified, since users have to suspend the task prior to making the change, and resume it again.

To achieve the detection of suspended tasks, we'll create a stored procedure called task_state_monitoring which will be monitoring the state of the given task and if it finds it in suspended state, it will send you an email. This procedure lists all the tasks using the show tasks command, then loops through each task and checks if the task's current state variable is equal to 'suspended'. An email will be sent for each task that is suspended.

The code of such procedure can look like this:

create or replace procedure task_state_monitor(task_name string)
returns varchar not null
language SQL
AS
$$
DECLARE
    task_state string;
    c CURSOR FOR SELECT "state" from table(result_scan(last_query_id())) where "name" = ?;
BEGIN
    show tasks;
    open c USING (task_name);
    fetch c into task_state;
    IF(task_state = 'suspended') THEN 
        CALL SYSTEM$SEND_EMAIL(
            'my_email_int',
            '[email protected]',
            'Email alert: Task is suspended!',
            'Please check the task state.' 
        );
        RETURN 'Email has been sent.';
     ELSE
         RETURN 'Task state is ok.';
     END IF;
END;
$$
;

To deploy this stored procedure, users can create a Snowflake task which will call this task_state_monitoring procedure according any desired schedule (i.e. every 5 mins, 2 times per day, etc.).

Snowflake Alerts

Another powerful notification feature is Snowflake Alerts. This is schema level object which allows you to react on different situations in your data, or your Snowflake account in general.

Cost Alerting is common use case for Snowflake Alerts. For example, an alert can be created to detect if an account's credit usage spikes for a single virtual warehouse, or any other Snowflake service. Another use case could be validation of some business rules and getting a notification if the validation fails. Alerts are not limited to just triggering an email. For example, whenever the validation of a given rule does not pass, you add the record into a log table that contains the timestamp, the validation type and a failure description.

Snowflake Alert consists of three components:

• A condition that triggers the alert (e. g. query returns no records)
• An action that defines what should happen in case condition is met (e.g. send email, insert record into the table)
• A frequency setting that defines how often the condition evaluated (e.g. every 24 hours)

How to create Snowflake Alert?

For this exmaple, we'll create a Snowflake Alert to notify us via email if the daily Snowflake credit consumption exceeds 100 credits.

Here is the code for the alert which runs every hour:

CREATE OR REPLACE ALERT credits_consumption
  WAREHOUSE = COMPUTE_WH
  SCHEDULE = 'USING CRON 0 1 * * * UTC'
  IF( EXISTS (
    select
        1
    from
        snowflake.account_usage.metering_history 
    where
        start_time >= SNOWFLAKE.ALERT.LAST_SUCCESSFUL_SCHEDULED_TIME() 
        AND SNOWFLAKE.ALERT.SCHEDULED_TIME()
    group by service_type
    having sum(credits_used) > 100

))
THEN
    CALL SYSTEM$SEND_EMAIL(
            'my_email_int',
            '[email protected]',
            'Credit consumption warning',
            'Please be aware that daily credit consumption is out of your specified limit of 100 credits per day' 
        );

Let’s go through the code and explain it:

• We have to define a virtual warehouse, COMPUTE_WH, which will be used for running the Alert.
• We defined a schedule using the CRON syntax to have the alert run daily at 1AM UTC
• As a condition we have a SQL query which returns a single row containing the value "1" if the Snowflake credit consumption is over 100 in last 24 hours
• As an action we call the SYSTEM$SEND_EMAIL procedure.

Once the Alert is successfully created we have to resume it, otherwise it will not run:

alter alert credits_consumption resume;

Now the Alert is in operational mode.

Alerts Monitoring

Snowflake offers an ALERT_HISTORY view in the ACCOUNT_USAGE which can be used for monitoring of the alerts execution:

SELECT * 
FROM snowflake.account_usage.alert_history
ORDER BY SCHEDULED_TIME DESC
;

The view contains an overview about each Alert execution together with the query_ids associated with the query run for to validate the alert condition (CONDITION_QUERY_ID) and the query run for the associated action (ACTION_QUERY_ID, i.e. the query that triggers the SYSTEM$SEND_EMAIL procedure).

Please note that action and condition queries are not visible in the QUERY_HISTORY and if you want to get them you have to use the RESULT_SCAN function:

select * from table(result_scan('<action_query_id>'));

How are Snowflake Alerts Billed

There is no extra payment for sending emails or for the Snowflake Alerts themselves. Users only pay for the compute time required to run the validation and action queries on the specified virtual warehouse.

Advantages of Snowflake Alerts

Snowflake Alerts vastly simplify the process of validating business logic and triggering some action on top of that. To trigger email notifications using the SYSTEM$SEND_EMAIL procedure, we had to create a separate stored procedure which explicitly executed some SQL, processed the results and performed the validation checks before triggering the email. Then, we had to create a separate task that invoked the stored procedure on a schedule.

With Snowflake Alerts, all of these steps are abstracted into the creation of a single alert object.

How to send notifications to Slack from Snowflake?

Many users prefer to receive their notifications in a team Slack channel instead of via email. This helps enable quicker collaboration and resolution of the notification. Since Slack supports sending emails to a channel through a specific email address, we can setup our Snowflake notification integration using the email address dedicated to this channel.

Step 1: Create email address for Slack channel

First, we need to create a new integration in our Slack channel to receive an email address we can use. Under the channel settings, click "Integrations" then "Send emails to this channel":

Step 2: Receive Slack confirmation

After completing this step, Slack will automatically send you a message into your channel:

Step 3: Create Snowflake user with Slack email address

Since Snowflake only allows emails to be sent to verified Snowflake users, we have to create a new user in Snowflake and assign it this email address from Slack. Then we must verify the email address.

Step 4: Verify the Snowflake email in Slack

The verification email will be sent from Snowflake directly to the Slack channel, where you can click on the link and verify the email address for future usage in the SYSTEM$SEND_EMAIL() stored procedure. Here's what the Snowflake verification email will render in Slack as:

How to trigger other notification types from Snowflake

Previously, when creating the notification integration we set the TYPE parameter to have a value of EMAIL. But, this parameter also supports QUEUE as an option. When set to QUEUE, users can send notifications from Snowflake to other cloud messaing services like Amazon SNS, Google Pub/Sub, or Microsoft Azure Event grid. Pub/Sub, or Microsoft Azure Event Grid.

This functionality works well in conjunction with the ERROR_INTEGRATION parameter on Snowflake Tasks or Snowpipe. The ERROR_INTEGRATION parameter accepts a notification integration with a QUEUE type. To learn more about this functionality, check out the blog post on Error Notifications for Snowflake tasks.