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.
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.
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.
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:
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.
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**
:
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?
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.).
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:
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:
COMPUTE_WH
, which will be used for running the Alert.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_history ORDER BY SCHEDULED_TIME DESC ;
The view contains an overview about each Alert execution together with the query_id
s 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>'));
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.
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.
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.
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":
After completing this step, Slack will automatically send you a message into your channel:
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.
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:
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.
Tomas is a longstanding Snowflake Data SuperHero and general Snowflake subject matter expert. His extensive experience in the data world spans over a decade, during which he has served as a Snowflake data engineer, architect, and admin on various projects across diverse industries and technologies. Tomas is a core community member, actively sharing his expertise and inspiring others. He's also an O'Reilly instructor, leading live online training sessions.
Get up and running with SELECT in 15 minutes.
Gain visibility into Snowflake usage, optimize performance and automate savings with the click of a button.