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_intTYPE=EMAILENABLED=TRUEALLOWED_RECIPIENTS=('joe.doe@my_domain.com','another.joe@my_domain.com');
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
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
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 nulllanguage SQLAS$$DECLAREtask_state string;c CURSOR FOR SELECT "state" from table(result_scan(last_query_id())) where "name" = ?;BEGINshow tasks;open c USING (task_name);fetch c into task_state;IF(task_state = 'suspended') THENCALL SYSTEM$SEND_EMAIL('my_email_int','Email alert: Task is suspended!','Please check the task state.');RETURN 'Email has been sent.';ELSERETURN '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.).
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_consumptionWAREHOUSE = COMPUTE_WHSCHEDULE = 'USING CRON 0 1 * * * UTC'IF( EXISTS (select1fromsnowflake.account_usage.metering_historywherestart_time >= SNOWFLAKE.ALERT.LAST_SUCCESSFUL_SCHEDULED_TIME()AND SNOWFLAKE.ALERT.SCHEDULED_TIME()group by service_typehaving sum(credits_used) > 100))THENCALL SYSTEM$SEND_EMAIL('my_email_int','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.
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_historyORDER 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
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
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
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.