Authors: Ankita Deep & Ritika Sharma
Introduction
We always wish that there was an automated way to receive a notification when a data issue occurs, right? Now we can do that within Snowflake using Snowflake Alerts and Notification Integration. Alerts help us in staying on top of issues so that we could catch the whole issue before they affect the whole system.
Within Snowflake, we may have both reactive and proactive alerting, for example, alert about failed tasks, notify warehouse overflow, or send a warning when a certain threshold is met. To get started, we’ll first understand Alerts syntax and in what ways we can leverage it.
Prerequisites
The notification integration object must be created before starting to play with alert objects.
Notification integration objects must be created with a verified email address by the AccountAdmin role.
Grant usage on notification integration object to a developer role.
A single Snowflake account can define a maximum of 10 email integrations, and more than one can be enabled simultaneously.
Syntax for Notification Integration Object:
USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE NOTIFICATION INTEGRATION IF NOT EXISTS <Name>
TYPE= EMAIL
ENABLED= TRUE
ALLOWED_RECIPIENTS= ('abc@gmail.com')
COMMENT = 'EMAIL INTEGRATION FOR ALERT MONITORING';
GRANT USAGE ON INTEGRATION <Name> TO ROLE <Role>;
Note: Users must have verified and authorized their email addresses in their accounts to be considered as allowed recipients.
Understanding Snowflake Alerts
Let’s take a look quick look at the syntax of Alert:
CREATE [ OR REPLACE ] ALERT [ IF NOT EXISTS ] <alert name>
WAREHOUSE = <warehouse name>
SCHEDULE = '{ <num> MINUTE | USING CRON <expr> <time zone> }'
-- Condition
IF( EXISTS(
<condition statement>
))
-- Action
THEN
<action statement>
;
Snowflake alert is a schema-level object which specifies certain conditions and actions.
A condition that triggers the alert (e.g. when a task is failed).
The action to perform when the condition is met (e.g. send an email notification).
When and how often the condition should be evaluated (e.g. every 2 hours, every Monday).
Consider the use-case, where we want to create an alert to trigger email notifications if any task has failed in the past one hour. It should run every hour during the week but not on weekends.
We’ll utilize the flow shown below while setting up the whole alert process.
Let's start with the task failed alert and work our way through all the components of an alert.
Condition
Let’s start by determining the condition required to trigger the alert:
SELECT 1
FROM SNOWFLAKE.ACCOUNT_USAGE.TASK_HISTORY
WHERE STATE = 'FAILED' AND
SCHEDULED_TIME >= DATEADD(MINUTE, -60, SYSDATE());
This condition will be true if any rows are returned from the task history view.
Action
Now, we want to send an email using the snowflake-provided stored procedure. Since SYSTEM$SEND_EMAIL() currently supports only string messages as a parameter. We will bootstrap the SYSTEM$SEND_EMAIL() inside a stored procedure to format our output.
Here’s a quick look at SYSTEM$SEND_EMAIL() syntax:
CALL SYSTEM$SEND_EMAIL(
'<integration_name>',
'<email_address_1> [ , ... <email_address_N> ]',
'<email_subject>',
'<email_content>'
);
We will aggregate the output by task names and all the error messages below the task names. Also, to make the email readable, we’ll add a timestamp as well. Our stored procedure will look like this:
SELECT CONCAT(NAME, ’\n’, LISTAGG(error_message, '\n')) INTO :message FROM SNOWFLAKE.ACCOUNT_USAGE.TASK_HISTORY WHERE STATE = 'FAILED' AND SCHEDULED_TIME >= DATEADD(MINUTE, -60, SYSDATE())
GROUP BY NAME;
SELECT CONCAT(CURRENT_TIMESTAMP(), '\nTASK FAILED ALERT:\n', :message,'\n') into :email;
CALL SYSTEM$SEND_EMAIL('alert_email_integration', 'abc@gmail.com', 'Email Alert: Snowflake Account', :email);
To create the alert, provide the below grants to the alert role.
GRANT EXECUTE ALERT ON ACCOUNT TO ROLE <alert-role-name>;
GRANT CREATE ALERT ON SCHEMA <schema-name> TO ROLE <alert-role-name>;
Also, the usage privilege on schema, database, and warehouse on which alert would be created.
Let's put everything together now that we have reached the end using the alert syntax that we learned above.
USE ROLE ALERT_ROLE;
--creating stored procedure to format email output
CREATE OR REPLACE PROCEDURE SEND_ACCOUNT_ALERT()
RETURNS VARCHAR(16777216)
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
message VARCHAR := '';
email VARCHAR;
BEGIN
SELECT CONCAT(NAME, ’\n’, LISTAGG(error_message, '\n')) INTO :message
FROM SNOWFLAKE.ACCOUNT_USAGE.TASK_HISTORY WHERE STATE = 'FAILED'
AND SCHEDULED_TIME >= DATEADD(MINUTE, -60, SYSDATE())
GROUP BY NAME;
SELECT CONCAT(CURRENT_TIMESTAMP(), '\nTASK FAILED ALERT:\n', :message,'\n') into :email;
CALL SYSTEM$SEND_EMAIL('alert_email_integration', 'abc@gmail.com', 'Email Alert: Snowflake Account', :email);
return 'MAIL_ALERT_TRIGGERED';
END;
$$;
Creating an alert to check if any task is failed in past hour and sending mail notification
CREATE OR REPLACE ALERT TASK_FAILED_ALERT
WAREHOUSE = MONITOR_WH
SCHEDULE = 'USING CRON 0 */1 * * Mon-Fri America/New_York'
IF(EXISTS(
SELECT 1
FROM SNOWFLAKE.ACCOUNT_USAGE.TASK_HISTORY
WHERE STATE = 'FAILED' AND
SCHEDULED_TIME >= DATEADD(MINUTE, -60, SYSDATE());
))
THEN
CALL SEND_ACCOUNT_ALERT();
– resuming alert
ALTER ALERT TASK_FAILED_ALERT RESUME;
Output
The email received looks something like this;
Conclusion
Setting up alerts will guarantee continuous monitoring of your data objects and ensure that you are notified of issues in advance of their possible impact on your data or downstream operations. This improves the agility of the businesses and boosts confidence in the overall data integration operations. Additionally, we can set-up alerts-on-alerts or alerts for logging mechanisms. Feel free to experiment with other use-cases as well.