Recently, Snowflake released external access integrations into general availability. This allows Snowflake admins to create secure tunnels for Snowflake to communicate with the outside world, and can therefore communicate with external APIs to complete all manner of tasks. For example, external access could be used for any of the following, among many others:

• Send alerts to Teams webhooks so that the alerts can be presented in a Teams channel (this post).
• Send alerts to Slack webhooks so that the alerts can be presented in a Slack channel.
• Communicate with a Matillion ETL API to trigger jobs.
• Communicate with the Matillion DPC API to trigger pipelines.
• Communicate with a Tableau Server/Cloud API to trigger datasource extract refreshes.
• Communicate with any other external API for any other reason, the opportunities are limitless!

This specific article focuses on how to communicate with a Teams webhook to send alert messages to a Teams channel, however, this series will include the other examples above as well. With each new article, the entry above will be converted to a link.

How To Enable a Webhook to a Channel in Teams

Before we configure the external access to allow Snowflake to communicate with a Teams webhook, we need to create the webhook itself. To achieve this, we need to take the following steps:

1. Create a team in Teams, if you haven’t already.
   1. Navigate to the “Teams” pane in Teams and select the plus icon:
   2. Enter a name and description for the team. Then select “Create.” In our example, the team is called “Sandbox:”
2. Add a channel to the team.
   1. Right click on your team and select “Add channel:”
   2. Enter a name and description for the channel. For our example, the channel name is “Snowflake Alerts:”
3. Enable an incoming webhook for the channel.
   1. Right click on the channel and select “Manage channel:”
   2. Select “Edit” in the “Connectors” area:
   3. Find “Incoming Webhook” and select “Add.” You may need to use the search bar on the left of the page:
   4. Select “Add” again on the application:
   5. Give your webhook a name, upload an image if you wish and select “Create.” The image here is the profile picture that will be used by the webhook when posting messages to the channel:
   6. In the final screen, copy the webhook URL. This takes the following form, and comprises of the host for the account, an identifier for the channel, and some authentication materials:

      https://?????.webhook.office.com/webhookb2/???????@???????/IncomingWebhook/???????/???????

      

How Does External Access in Snowflake Work?

Before we dive into the specific example, let’s cover the architecture involved for external access in Snowflake. There are four main components:

1. A network rule that controls where traffic is allowed to travel to.
2. An external access integration that allows Snowflake objects to leverage the network rule.
3. An optional security component to faciliate authentication. This usually leverages a Snowflake Secret object that can either securely store information such as a username and password, or make use of a related Security Integration object that facilitates OAuth authentication with an API endpoint.
4. A Snowflake object such as a stored procedure or a user defined function (UDF) that leverages the external access integration.

This is demonstrated in the following diagram:

How to Configure an Outbound Network Rule

In short, network rules are database objects that store information on a type of network traffic for Snowflake. This traffic can be one of two categories:

• Ingress – Traffic from outside of Snowflake that is coming in to Snowflake.
• Egress – Traffic that is leaving Snowflake and travelling outside.

This article specifically focuses on the egress category, and how we can leverage this to enable Snowflake to communicate with endpoints outside of Snowflake’s network. Note that in Snowflake’s terminology, the category (ingress/egress) is referred to as the “mode,” and this terminology will be used for the rest of this article.

Example Network Rule for External Access

Here is a simple example for creating an outbound/egress network rule:

create network rule "MY_OUTBOUND_NETWORK_RULE"
type = HOST_PORT -- Always use this value for external access integrations
mode = EGRESS -- Always use this value for external access integrations
value_list = ('?????') -- Populate with host(s) of the target endpoint(s), such as matillion.customer.com, host.online.tableau.com, customer.webhook.office.com, eu1.api.matillion.com, etc
comment = 'Allows traffic to be sent to the host(s) in the value_list'
;

It is important to note that Snowflake stores network rules as database objects, meaning they sit inside schemas in databases. This is different to network policies, which are account-level objects. This is easy to get confused, which is why it has been mentioned here.

Specific Network Rule Example

The following network rule could be leveraged to enable outbound/egress traffic with a set of Teams webhooks within a target Teams account:

create network rule "EXTERNAL_ACCESS"."NETWORK_RULES"."EGRESS__TEAMS_WEBHOOKS"
type = HOST_PORT
mode = EGRESS
value_list = ('?????.webhook.office.com') -- Populate with webhook host of the target Teams account, such as customer.webhook.office.com
comment = 'Allows traffic to be sent to webhooks in the target Teams environment'
;

How To Set up an External Access Integration

In short, external access integrations are account-level objects that act as the bridge between egress network rules and any other Snowflake objects (stored procedures/UDFs).

Here is a simple example for creating an external access integration that leverages an outbound/egress network rule:

create external access integration "MY_EXTERNAL_ACCESS_INTEGRATION"
allowed_network_rules = ("MY_DATABASE"."MY_SCHEMA"."MY_OUTBOUND_NETWORK_RULE")
enabled = FALSE
comment = 'External access integration to support traffic sent to the <endpoint(s) enabled by the network rule(s)>'
;

Depending on your required target, you may have a need for additional security components to authenticate your requests with the destination. For example, the destination may expect authentication via a username/password combination that has been converted into a bearer token, or it may leverage OAuth and expect a combination of client ID and secret ID. Of course, these are just two examples and there are other possibilities for more complex use cases.

My personal recommendation is to create your external access integration before creating any additional security components, then apply them using alter commands.

In the case of Teams webhooks, no security components are required since the authentication is includeded as part of the webhook URL itself. It is therefore safe to create the integration and enable it in the same statement.

create external access integration "EAI__TEAMS_WEBHOOKS"
allowed_network_rules = ("EXTERNAL_ACCESS"."NETWORK_RULES"."EGRESS__TEAMS_WEBHOOKS")
enabled = TRUE
comment = 'External access integration to support traffic sent to webhooks in the target Teams environment'
;

How To Send Snowflake Alerts to a Teams Channel

Now that we have discussed and configured the underlying architecture behind our process, we can move on to our true goal, which is to create a stored procedure in Snowflake that can automatically send alerts to a Teams channel.

Creating a Mapping Table for Teams Channels and Webhooks

Whilst this step is not necessary and you could develop the process without it, leveraging a mapping table can make the process much simpler. The idea here is to create a table that maps channel names to webhook URLs. That way, if you have multiple channels and webhooks that you might use for different scenarios, you can have a single stored procedure that leverages the target channel name as an input. The benefit of the mapping table specifically is that the stored procedure can then convert the channel name into a webhook URL, both avoiding the webhook URL being exposed to end users and making it easier for them to send messages to their desired channel.

Here is some example code for creating such a mapping table:

create or replace table "EXTERNAL_ACCESS"."MAPPING_TABLES"."API__TEAMS__ENDPOINTS" (
"NAME" string comment 'Name of the Teams channel, for reference'
, "URL" string comment 'Endpoint for the webhook where messages should be delivered'
)
as
select *
from values
('Sandbox - Snowflake Alerts', 'https://?????.webhook.office.com/webhookb2/???????@???????/IncomingWebhook/???????/???????')
;

Understanding Adaptive Cards

Teams webhooks (and the wider Teams API) use Adaptive Cards as the main mechanism for sending messages. To make things easier, the stored procedure provided below already converts the inputs into an Adaptive Card body and sends it to the webhook.

If you wish to learn more about these and create your own input formats, take a look at the documentation, including the schema explorer and the samples.

Creating a Stored Procedure To Send a Message to a Teams Channel

Finally, we come to the main meat of the article. First, I will provide the full code for the stored procedure. I will then explain some key areas.

This article does not explain Python stored procedures in general. If you wish to know more about these, the following article is recommended: A Definitive Guide to Python Stored Procedures in the Snowflake UI. This is part of our Snowflake with Python series.

The full procedure definition is as follows:

-- Create stored procedure
create or replace procedure "EXTERNAL_ACCESS"."PROCEDURES"."API__TEAMS__SEND_MESSAGE"(
      PIPELINE varchar
    , DESCRIPTION varchar
    , ENVIRONMENT varchar default 'Snowflake - Main - Production'
    , ENDPOINT_NAME varchar default 'Sandbox - Snowflake Alerts'
  )
  copy grants
  returns string
  language python
  runtime_version = 3.10
  handler = 'main'
  external_access_integrations = ("EAI__TEAMS_WEBHOOKS")
  packages = ('snowflake-snowpark-python','requests')
as
$$

## Snowpark imports
from snowflake.snowpark import Session as snowparkSession
from snowflake.snowpark.functions import col, lit

## Other imports
import requests
import json

## Define main function
def main(
      snowpark_session: snowparkSession
    , pipeline: str
    , description: str
    , environment: str
    , endpoint_name: str
  ):

  ### Retrieve the endpoint url
  endpoint_url = retrieve_endpoint_url(
      snowpark_session = snowpark_session
    , endpoint_name = endpoint_name
  )

  if endpoint_url is None:
    return "Endpoint name not approved"

  ### Retrieve the adaptive card body
  adaptive_card_body = create_adaptive_card_body(
      pipeline = pipeline
    , description = description
    , environment = environment
  )

  ### Send the message to the endpoint
  response_message = send_message_to_endpoint(
      adaptive_card_body = adaptive_card_body
    , endpoint_url = endpoint_url
  )

  return response_message
  

## Define function to retrieve the endpoint URL
## from the ENDPOINTS table
def retrieve_endpoint_url(
      snowpark_session: snowparkSession
    , endpoint_name: str
  ):

  ### Query the ENDPOINTS table, filtered by NAME
  sf_df_endpoint_url = snowpark_session.table('"EXTERNAL_ACCESS"."MAPPING_TABLES"."API__TEAMS__ENDPOINTS"') \
    .filter(col('"NAME"') == lit(endpoint_name)) \
    .select(col('"URL"')) \
    .collect()

  ### If a value is found, return it.
  ### Otherwise, return None
  if sf_df_endpoint_url is not None \
    and len(sf_df_endpoint_url) > 0 \
      :
    endpoint_url = sf_df_endpoint_url[0]["URL"]
  else:
    endpoint_url = None

  return endpoint_url

## Define function to create the adaptive card body
## for the message send to the endpoint
def create_adaptive_card_body(
      pipeline: str
    , description: str
    , environment: str
  ):

  adaptive_card_body = [
            {
              "items": [
                {
                  "size": "large",
                  "text": f"Alert - {environment}",
                  "type": "TextBlock",
                  "weight": "bolder"
                },
                {
                  "text": pipeline,
                  "type": "TextBlock",
                  "weight": "bolder",
                  "wrap": True
                },
                {
                  "text": description,
                  "type": "TextBlock",
                  "wrap": True
                }
              ],
              "type": "Container"
            }
          ]
  
  return adaptive_card_body
  
## Define function to send the message to the endpoint
def send_message_to_endpoint(
      adaptive_card_body: list
    , endpoint_url: str
  ):
  
  json_body = {
    "attachments": [
      {
        "content": {
          "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
          "body": adaptive_card_body,
          "type": "AdaptiveCard",
          "msteams": {
            "width": "Full"
          },
          "version": "1.2"
        },
        "contentType": "application/vnd.microsoft.card.adaptive",
        "contentUrl": None
      }
    ],
    "type": "message"
  }

  ### Execute API call
  response = requests.request("POST", endpoint_url, json=json_body)

  try:
    if response.status_code == 200 :
      return "Complete"
    else :
      return f"Incomplete. Response code: {str(response.status_code)}"
  except Exception as e:
    return f"Error encountered:\n\n{str(e)}"

$$
;

-- Test query
call "EXTERNAL_ACCESS"."PROCEDURES"."API__TEAMS__SEND_MESSAGE"(
    PIPELINE => 'TEST PIPELINE'
  , DESCRIPTION => 'A test description'
)
;

• Line 13 enables the stored procedure to leverage the external access integration that facilitates access to the set of Teams webhooks.
• Lines 62-82 define a function that leverages Snowpark for Python to query the mapping table and retrieve the webhook URL, using the input endpoint name. This is leveraged on lines 36-39.
• Lines 86-117 define a function that leverages the inputs to create an adaptive card body. This is leveraged on lines 45-49.
• Lines 120-153 define a function that sends the adaptive card body to the webhook URL. This includes:8
  • Lines 125-142 place the adaptive card body into a wider json body to be sent to the webhook URL.
  • Line 145 sends the POST request itself to the webhook URL.
  • Lines 147-153 return the response from the request, with some minor error handling.
• Lines 159-162 can be used to test the process.

Testing the Stored Procedure

A successful test of the stored procedure should yield a simple “Complete” response, which has been configured within the stored procedure:

Once executed, you should see the message appear in your Teams channel.

Leveraging the Stored Procedure Inside a Snowflake Alert

The final step on our journey is to leverage this new stored procedure inside a Snowflake alert. Here is a simple template:

create or replace alert "MY_EXAMPLE_ALERT"
warehouse = MY_WAREHOUSE
schedule = 'USING CRON 0 * * * * UTC' -- Hourly
if (
exists (
select 1
from "MY_SOURCE_TABLE"
where <condition>
)
)
then
call "EXTERNAL_ACCESS"."PROCEDURES"."API__TEAMS__SEND_MESSAGE"(
PIPELINE => 'My Pipeline'
, DESCRIPTION => '<condition description> has been encountered in MY_SOURCE_TABLE'
)
;

You may want to take this further by creating another stored procedure in the middle. The alert can execute a wider stored procedure that performs multiple activities, one of which could be to call the stored procedure that sends a message to the Teams channel.

For full details on how alerts work, check out this article: Understanding Snowflake Alerts and Notifications

Wrap Up

Hopefully the information in this article provides you with all of the tools you need to establish your own connections that enable Snowflake to send alerts and other messages to Teams. This stored procedure can now be made available to other users using Snowflake’s Role-Based Access Control (RBAC) and could be useful in multiple scenarios, including:

• Sending alerts when dynamic table refreshes have failed.
• Sending alerts when tasks have failed.
• Sending alerts when other alerts have failed.
• Sending messages when specific pipelines are starting/finishing.
• Sending messages to confirm when bespoke activity has taken place.