Sending Alerts from Snowflake using External Functions

There are likely scenarios where you will want to be notified of events that take place within your Snowflake environment — these could be alerts or informing a user group that a fresh load of data is now available, the most likely delivery medium will be email, but there may also other channels like Slack, Teams, SQS, etc.

There are some really exciting capabilities coming from the awesome product and engineering teams at Snowflake that will handle this natively, but in the near term with exciting features like SnowPark, Java UDFs and other cool stuff they’ve got their hands full — so in the meantime we can turn to External Functions to provide the extensibility to send these notifications.

In real world terms, a simple example of needing an alert or notification could be a task failure. A more complex scenario could be tied to monitoring some of the various account usage views to get alerted if certain activity takes place; consecutive failed logins, usage of the ACCOUNTADMIN role.

As mentioned above, we can use the External Functions feature to use an AWS Lambda to invoke the alert distribution. External Functions are well documented with some great examples on how to do this (along with Cloud Formation templates) and this article will not cover this part of the setup.

Python Code

Within the Lambda, we’ll leverage the boto3 module to lean on the various AWS Services (SES, SQS) and then create separate functions for the external channels like Slack (which will use the requests module).

Notes:

1. Because this uses the requests library, you’ll either need to use something like the serverless framework or create a deployment package which contains this library or use Lambda Layers. Instructions on this approach can be found here.
2. There is scope for expanding the delivery channel (e.g. Teams, Zoom IM, JIRA). You would just need to use different parameter values and create the appropriate functions in the Lambda script, most of these platforms use webhooks or have an API of sorts.
3. This code can probably be made more extensible by storing AWS Region, Sender Email etc. in a config file or AWS SSM Parameter Store.

Snowflake DDL

Once the Lambda Function is implemented, and the other necessary components are configured (API Gateway, IAM etc) we can create our API Integration in Snowflake — please refer to the Snowflake documentation on doing this.

USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE API INTEGRATION INT_ALERT_API
  API_PROVIDER = AWS_API_GATEWAY
  API_AWS_ROLE_ARN = 'arn:aws:iam::000000000000:role/snowflake-api-role'
  ENABLED = TRUE
  API_ALLOWED_PREFIXES = ('https://zzzzzzzz.execute-api.us-west-2.amazonaws.com');

Once this is created, you can create your external function:

CREATE OR REPLACE EXTERNAL FUNCTION SEND_ALERT(SUBJECT STRING, BODYCONTENT STRING, DELIVERYCHANNEL STRING, ADDRESS STRING)
    RETURNS VARIANT
    API_INTEGRATION = INT_ALERT_API  
    AS 'https://zzzzzzzz.execute-api.us-west-2.amazonaws.com/cloudwatch/';

Best practice would be to not use ACCOUNTADMIN to create the External Function, instead grant usage of the API Integration to a lower role in the RBAC structure and use this to create the External Function.

Examples

Sending an alert is as simple as the following:

-- Email Alert
SELECT SENDALERT(
    'Alert Test', 
    'This is an really really big emergency', 
    'SES', 
    'someone_important@yourcompany.com',
    ''
);
-- Slack Alert
SELECT SENDALERT(
    'Alert Test', 
    'This is an really really big emergency', 
    'Slack', 
    'someone_important@yourcompany.com',
    'https://a_dashboard_somewhere.com'
);
-- SQS Alert
SELECT SENDALERT(
    'Alert Test', 
    'This is an really really big emergency', 
    'SQS', 
    'someone_important@yourcompany.com',
    ''
);

Example of an email from Snowflake

Example of a Slack Alert from Snowflake

Building this into a Task to capture errors, can be handled with a Try / Catch block: