Three Ways to Achieve Slack Notifications with Snowflake

Published in

Snowflake Engineering

7 min readApr 26, 2024

Introduction

Have you ever wanted to receive notifications from Snowflake to the chat tool you use? Implementing a notification system is essential for monitoring in order to quickly notice errors that occur in Snowflake. This time, I would like to introduce several methods to achieve Slack notifications from Snowflake.

Method 1: Utilizing External Network Access for External Access Integration

EXTERNAL NETWORK ACCESS is known as a secure way to access external networks. It can be used in UDFs and stored procedures, making it easy to achieve external access integration. By using this method, we can send requests to Slack for notifications. Theoretically, as long as the API is open, notifications can be sent to any chat tool. The flow is as follows:

1. Obtaining a Slack Token

First, log in to https://api.slack.com/apps/ and click on “Create New App,” then enter the necessary information. In the section for your app’s scopes and settings, select From scratch.

You will be able to see the Basic Information of the app, so proceed to “Add features and functionality” > “Permissions”.

After scrolling a bit, you will see a section called “Scopes”. From there, select “Bot Token Scopes” and assign the necessary permissions. If you want to post as a user, choose “User Token Scopes”.

By the way, here are my settings. Please customize them to fit your company’s requirements.

After completion, we will proceed with the installation in the Workspace. Once the installation is finished, you will be able to copy the token.

2. Perform EXTERNAL ACCESS INTEGRATION

The process flow begins by defining a network rule to apply the policy to the host `slack.com`. Next, create a secret named `slack_token` to manage confidential information internally in Snowflake. Use the network role and registered secret to create an external access integration between Slack and Snowflake using the `EXTERNAL ACCESS INTEGRATION` command.

CREATE OR REPLACE NETWORK RULE slack_rule
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('slack.com');

CREATE OR REPLACE SECRET slack_token
  TYPE = GENERIC_STRING
  SECRET_STRING = '[slack-token]';

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION slack_apis_access
  ALLOWED_NETWORK_RULES = (slack_rule)
  ALLOWED_AUTHENTICATION_SECRETS = (slack_token)
  ENABLED = true;

3. Posting to Slack

To complete the process of posting to Slack, implement the function within a UDF or stored procedure.

Here is an example of implementation using a UDF. In this example, we are calling the Slack `chat.postMessage` API to send a message. Set the API key (token) in the header and send data to Slack via a POST request.

CREATE OR REPLACE FUNCTION post_to_slack(channel varchar ,message varchar)
RETURNS variant
LANGUAGE python
RUNTIME_VERSION = 3.8
HANDLER = 'main'
EXTERNAL_ACCESS_INTEGRATIONS = (slack_apis_access)
PACKAGES = ('requests')
SECRETS = ('cred' = slack_token)
AS
$$
import _snowflake
import requests
import json
def main(p_channel ,p_message):
    api_key = _snowflake.get_generic_secret_string('cred')
    url = 'https://slack.com/api/chat.postMessage'
    v_headers = {"Authorization": "Bearer "+ api_key}
    v_data_json = {
    'channel': p_channel
    ,'text': p_message
    }
    response = requests.post(url ,headers=v_headers ,json = v_data_json)
    return response.json()
$$;

You can try running it here.

SELECT
'[channel ID]' as channel
,'TEST' as message
,post_to_slack(channel ,message) as slack_notify

By the way, you can confirm the channel ID at the bottom of the popup that appears after clicking on the channel name to open the details.

By defining a function called `slack_notification` as shown below, you can achieve dynamic Slack notifications within a stored procedure. This code is used for error notifications.

def slack_notification(session, channel_id, message):
    notification_query = f"""
    SELECT
    '{channel_id}' as channel,
    '{message}' as message,
    post_to_slack(channel, message) as slack_notify;
    """

    session.sql(notification_query).collect()

def create_database(session, database_name):
    try:
        session.sql(f"CREATE DATABASE IF NOT EXISTS \"{database_name}\"").collect()
        return f"Database '{database_name}' is successfully created"
    except Exception as e:
        error_message = f"An error occurred while creating the database '{database_name}'. Error details: {e}"

        # Slack notification on error occurrence
        slack_notification(session, 'error-log-channel-id', error_message)
        return error_message

Method 2: Notify Slack channel email address using NOTIFICATION INTEGRATION

This is the simplest and lightest way to specify a Slack channel email address in NOTIFICATION INTEGRATION. It is recommended for those who do not want to think about various difficult things related to networks…

Snowflake provides a system function called `SYSTEM$SEND_EMAIL` for sending email notifications, so we will use this.

https://docs.snowflake.com/ja/sql-reference/stored-procedures/system_send_email

Example

CALL SYSTEM$SEND_EMAIL(
'my_email_list',
'email_address',
'subject',
'body')

The process involves registering the Slack channel email address in the NOTIFICATION INTEGRATION email list and sending it to the Slack channel email address using SYSTEM$SEND_EMAIL. The steps are as follows:

Get the email address of the Slack channel
When you press the Integration tab in the details of the channel, you will see “Send emails to this channel” at the bottom, and you can generate the channel’s email address (available only in paid plans).

2. Create an email list in NOTIFICATION INTEGRATION

CREATE NOTIFICATION INTEGRATION my_email_list
    TYPE=EMAIL
    ENABLED=TRUE
  ALLOWED_RECIPIENTS=('x-asdfghjklasdfghj@hogehoge.slack.com');

3. Call SYSTEM$SEND_EMAIL with the configured email address
Once called, you can send emails using SYSTEM$SEND_EMAIL.

CALL SYSTEM$SEND_EMAIL(
'my_email_list',
'x-asdfghjklasdfghj@hogehoge.slack.com',
'snowflake notification test',
'koreeda_test')

The following dynamic text generation is possible as shown below:

SET my_dynamic_body = (SELECT 'Today's date is ' || CURRENT_DATE() || '. The database system is running smoothly.');
SET additional_message = 'If any issues arise, please contact the system administrator immediately.';
SET full_message = $my_dynamic_body || '\n\n' || $additional_message;
CALL SYSTEM$SEND_EMAIL(
'my_email_list',
'x-asdfghjklasdfghj@hogehoge.slack.com',
'snowflake notification test',
$full_me

Of course, it can also be used within a stored procedure. By preparing a dedicated stored procedure for sending emails as shown below, the notification feature can be easily implemented by calling it from an existing stored procedure.

CREATE OR REPLACE PROCEDURE send_dynamic_email()
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
HANDLER = 'send_email_handler'
PACKAGES = ('snowflake-snowpark-python==0.7.0')
AS
$$
def send_email_handler(session):
 session.sql("CALL SYSTEM$SEND_EMAIL('my_email_list', 'x-asdfghjklasdfghj@hogehoge.slack.com', 'snowflake notification test', 'hogehoge')")
 return "Email sent successfully."
$$;
/* Usage Command
call send_dynamic_email();
/*

One important point to note is that only emails of users who have been verified can be specified in ALLOWED_RECIPIENTS. The method for email verification is by clicking on My profile.

At the top, there is something like “Resend email validation,” so I will resend it.

Method 3: Utilizing Cloud Messaging

Finally, this method involves utilizing cloud messaging such as AWS SNS topics. When you search for “snowflake task notification,” you will likely find the official documentation first, so many people may be using this method.

https://docs.snowflake.com/en/user-guide/tasks-errors

You can use cloud messaging services like the following. One thing to note is that it is not cross-cloud supported, so it depends on the cloud platform hosting your Snowflake account.

・ Amazon Simple Notification Service (SNS)
・ Microsoft Azure Event Grid
・ Google Pub/Sub

Create a topic to receive error notifications and configure the notification integration of that topic.

In this case, we will focus on AWS SNS for explanation. The flow involves creating an Amazon SNS topic, creating IAM policies and roles, and specifying topic_arn and iam_role_arn in NOTIFICATION INTEGRATION.

NOTIFICATION INTEGRATION command

CREATE NOTIFICATION INTEGRATION <integration_name>
  ENABLED = true
  TYPE = QUEUE
  NOTIFICATION_PROVIDER = AWS_SNS
  DIRECTION = OUTBOUND
  AWS_SNS_TOPIC_ARN = '<topic_arn>'
  AWS_SNS_ROLE_ARN = '<iam_role_arn>'

Pros and Cons of Each Method

EXTERNAL ACCESS INTEGRATION Usage

Advantages: Completes within Snowflake while ensuring security, allowing notifications via API with high customizability.
Disadvantages: Requires understanding and managing network rules and security policies for external access settings.

Slack Email-to-Channel Notification

Advantages: Quick and easy setup. Utilizes Slack’s existing email-to-channel feature for convenient notifications.
Disadvantages: Requires a paid Slack plan and has limited customizability for notifications (primarily in text email format).

Cloud Messaging Method

Advantages: Ensures high scalability and reliability by using a wide range of platform-independent cloud services (such as AWS SNS).
Disadvantages: Requires setup and management of cloud services, which may increase costs. It is necessary to choose a cloud provider that matches the operating environment of Snowflake.

If the internal network policy is not particularly strict, it is recommended to use the external access integration within Snowflake (Method 1), which can be self-contained and ensure security. On the other hand, if you want to implement a notification feature in the simplest way possible, utilizing Slack’s email-to-channel feature (Method 2) is recommended. Additionally, if cloud systems like AWS are already established and their maintenance is not an issue, using cloud messaging (Method 3) would be optimal. Personally, I recommend the 1. EXTERNAL ACCESS INTEGRATION usage method within Snowflake, which can be self-contained and allows for customization of notification styles, and we have also implemented it in our company.

In Conclusion

How was it? There are various methods available, so it might be confusing, but I hope you can implement a notification method that suits your company!