Sending Alerts to MS Teams from Snowflake

Date
  • Jeff Skoldberg
    Jeff Skoldberg
    Sales Engineer at SELECT

Microsoft Teams Alerts In Snowflake

Snowflake has recently expanded alerting capability, allowing alerts to be sent via Email or Webhook. Webhook alerts are very powerful, because this enables the ability to send alerts to Slack or Microsoft Teams. This is useful for a variety of use cases including pipeline failure alerts and data driven alerts.

This post will provide a step-by-step guide to configure Snowflake alerts in Microsoft Teams.

Quickstart

Step 1: Create a Webhook in Teams

In Teams, create a new channel, or navigate to the channel where you want the alerts to land.

In the top right corner of the channel, click the 3 dots, then “Manage channel”

Sending Alerts to MS Teams from Snowflake

Go to the Settings tab, Connectors section, then click Edit.

Sending Alerts to MS Teams from Snowflake

You will be presented with a list of Connectors. Search for “Webhook”, then click “Add”.

Sending Alerts to MS Teams from Snowflake

On the next screen, you can upload an image for the webhook. I will use a nice Snowflake logo for the image. Be sure to name the webhook, then click “Create”.

Sending Alerts to MS Teams from Snowflake

After you click “Create”, it will give you the webhook URL. The URL contains your secret, so treat it carefully. For now, copy that URL and paste it to a notepad.

Sending Alerts to MS Teams from Snowflake

Test the Webhook

You can use this CURL command to test the webhook. If you’re on Windows, use Git Bash.

curl -X POST https://paste-your-url-with-secret-here \
-H "Content-Type: application/json" \
-d '{"text": "Hello, world"}'

Immediately, you should see the “Hello, world” message come into your Teams channel.

Congrats, your new Webhook is now working!

Sending Alerts to MS Teams from Snowflake

Step 2: Create the Snowflake Secret

The Webhook URL contains a secret. Replace the secret string below with your secret string, the run this command in Snowflake.

Reminder: Secrets are schema level objects. Be careful about your worksheet database and schema context, or use fully qualified names.

In my case, I created a secret called gmds_teams_secret in the public schema of the analytics database.

use schmea <database>.<schema>;
CREATE OR REPLACE SECRET gmds_teams_secret
TYPE = GENERIC_STRING
SECRET_STRING = 'this-is-the-secret';

To be clear, the secret is the last part of the URI show here: https://org-name.webhook.office.com/webhookb2/webhook-id/IncomingWebhook/this-is-the-secret

Step 3: Create a webhook notification integration

Now that we’ve successfully created the secret, let’s create the Notification Integration:

CREATE OR REPLACE NOTIFICATION INTEGRATION gmds_teams_webhook_integration
TYPE=WEBHOOK
ENABLED=TRUE
WEBHOOK_URL='https://org-name/webhook.office.com/webhookb2/webhook-id/IncomingWebhook/SNOWFLAKE_WEBHOOK_SECRET'
WEBHOOK_SECRET=analytics.public.gmds_teams_secret
WEBHOOK_BODY_TEMPLATE='{"text": "SNOWFLAKE_WEBHOOK_MESSAGE"}'
WEBHOOK_HEADERS=('Content-Type'='application/json');

Step 4: Send the notification

To send a notification, we use the built-in SYSTEM$SEND_SNOWFLAKE_NOTIFICATION stored procedure. We must pass the SANITIZE_WEBHOOK_CONTENT function to the procedure to remove placeholder (i.e. SNOWFLAKE_WEBHOOK_SECRET from the message.

Here is the code I ran in my account:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
SNOWFLAKE.NOTIFICATION.TEXT_PLAIN(
SNOWFLAKE.NOTIFICATION.SANITIZE_WEBHOOK_CONTENT('This is a test Teams Alert from my Snowflake Account')
),
SNOWFLAKE.NOTIFICATION.INTEGRATION('gmds_teams_webhook_integration')
);

The notification arrived in Microsoft Teams instantly!

Sending Alerts to MS Teams from Snowflake

Now that we have the basic gears in place, let’s move on to a real-world example.

Alerts Example: Send an alert when warehouse usage spikes

Craft a SQL query to identify warehouse usage spikes and wrap it in a serverless task

The following query compares recent usage (last completed hour), to the average hourly usage of each warehouse over the last month. In this case, we will flag warehouses with 50% increased usage.

We are using a serverless task because it will save us money! Just omit the warehouse name to make the task serverless.

For example purposes, I’m going to union a dummy record to ensure that every execution of the task produces a row and an alert.

CREATE OR REPLACE TASK monitor_warehouse_spikes
SCHEDULE = 'USING CRON 2 * * * * America/New_York'
SERVERLESS_TASK_MIN_STATEMENT_SIZE = 'XSMALL'
SERVERLESS_TASK_MAX_STATEMENT_SIZE = 'XSMALL'
as
insert into usage_spike_alerts (warehouse_name,last_hour_credits,avg_monthly_credits,credit_diff,percent_increase)
WITH last_hour_usage AS (
SELECT
warehouse_name,
sum(credits_used) AS last_hour_credits
FROM
snowflake.account_usage.warehouse_metering_history
WHERE
start_time >= DATEADD(hour, -2, CURRENT_TIMESTAMP)
AND end_time <= CURRENT_TIMESTAMP

SCHEDULE = 'USING CRON 2 * * * * America/New_York' means 2 minutes after every hour of every day.

Create a table to store the query results

CREATE or replace TABLE usage_spike_alerts (
alert_id INT AUTOINCREMENT PRIMARY KEY,
warehouse_name STRING NOT NULL,
last_hour_credits FLOAT NOT NULL,
avg_monthly_credits FLOAT NOT NULL,
credit_diff FLOAT NOT NULL,
percent_increase FLOAT NOT NULL,
inserted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
alert_sent boolean default false
);

Note this table has a few helper columns with default values not included in the query results:

  • alert_id: primary key
  • inserted_at: helps us know when the task inserted the record.
  • alert_sent: initially false, will be set to true after the alert is sent.

Test the task, review the results

execute task monitor_warehouse_spikes;
select * from usage_spike_alerts where not alert_sent;
Sending Alerts to MS Teams from Snowflake

Create a procedure to send the alert if a spike exists

We want to do the following:

  • Check the usage_spike_alerts table for unsent records: alert_sent==false
  • If we have unsent records, send them to Microsoft Teams.
  • Mark the record as sent.
  • Output the number of alerts sent in the SQL console.
CREATE OR REPLACE PROCEDURE send_usage_spike_alerts()
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'send_alerts'
AS $$
import snowflake.snowpark as snowpark
def send_alerts(session):
query = """
SELECT warehouse_name, last_hour_credits, avg_monthly_credits, credit_diff, percent_increase
FROM usage_spike_alerts
WHERE alert_sent = FALSE
"""

Manually test the procedure

We’ve already inserted a test row into the “spikes” table. Let’s run the sproc and ensure:

  • The records are updated to alert_sent==true
  • We receive the message in Microsoft Teams
execute task monitor_warehouse_spikes; -- if you haven't already...
select * from usage_spike_alerts where not alert_sent; -- review it, sent = false
CALL send_usage_spike_alerts(); -- send the alert
-- wait for the alert to come
select * from usage_spike_alerts where not alert_sent; -- 0 rows

You should have received an alert in Teams!

Sending Alerts to MS Teams from Snowflake

Formatting the alert can be a little tedious. I encourage you to play with the python code to send this alert in a prettier format and let us know how you did it!

Chain the task and the procedure together

We want the hourly schedule of the monitor_warehouse_spikes task to handle the end-to-end process; let’s make sure that after the monitor_warehouse_spikes task is run, the procedure that sends the alert is also run.

First, wrap the procedure in a serverless task and turn it on:

CREATE TASK send_usage_spike_alerts_task
AS
CALL send_usage_spike_alerts();
alter task send_usage_spike_alerts_task resume;

Next, chain the tasks together:

ALTER TASK send_usage_spike_alerts_task
ADD AFTER monitor_warehouse_spikes;

Test it:

execute task monitor_warehouse_spikes;

This task should now add the new records to the table and send the alert(s) to Teams!

When calling send_usage_spike_alerts() procedure directly, the message appears in Microsoft Teams right away. When chaining send_usage_spike_alerts_task to monitor_warehouse_spikes task, it takes up to 3 minutes for the alert to come.

Wrap Up

Sending Snowflake alerts to Microsoft Teams is incredibly useful. The use cases for this type of alerting are endless!

This article has equipped you with the knowledge of:

  • Creating a Webhook in Microsoft Teams.
  • Creating Secret in Snowflake.
  • Creating a Webhook Integration in Snowflake.
  • Creating a task to log warehouse usage spikes.
  • Creating a procedure to send alerts based on a condition. In this case the existence of rows in a query.
  • Wrapping a procedure in a task.
  • Chaining tasks together.

I’m looking forward to hearing about the use cases you come up with! 🥂

Jeff Skoldberg
Jeff Skoldberg
Sales Engineer at SELECT

Jeff Skoldberg is a Sales Engineer at SELECT, helping customers get maximum value out of the SELECT app to reduce their Snowflake spend. Prior to joining SELECT, Jeff was a Data and Analytics Consultant with 15+ years experience in automating insights and using data to control business processes. From a technology standpoint, he specializes in Snowflake + dbt + Tableau. From a business topic standpoint, he has experience in Public Utility, Clinical Trials, Publishing, CPG, and Manufacturing.

Get up and running with SELECT in 15 minutes.

Snowflake optimization & cost management platform

Gain visibility into Snowflake usage, optimize performance and automate savings with the click of a button.

SELECT web application screenshot