PoP Data from SnowAlert to Slack
Written by Roman Dobrik and PJ-Paul Julius
Intro
SnowAlert is open source software, released by Snowflake. It enables creating arbitrary rules-based notifications triggered by data in a Snowflake Data Warehouse. SnowAlert provides Snowflake users with a power tool for turning otherwise static data into realtime actions!
When an alert occurs, one could send an email, create a JIRA ticket, or send a Slack message (like we do in this example). But, it doesn’t end there. SnowAlert could call a Lambda function on AWS to automate an operational task. It could use a stored procedure to update your database. An alert could even call a REST API to turn a light on or off, as is popular in the Continuous Delivery world.
We describe our current use case below, which revolves around monitoring accounts for Customer Support purposes. SnowAlert itself grew out of Snowflake Security Team’s need to monitor breaches. SnowAlert does not limit the types of applications for which it can be used. Hopefully, this example will get your thinking juices flowing.
Why
With so many monitoring tools already on the market, why consider adding another into the mix. You don’t have to! It’s as easy as that.
However, we decided to use SnowAlert because it integrates easily with Snowflake data warehouses out of the box. Since we store our data in Snowflake (duh!), easy integration sounded like a win for us.
Our starting use case was simple. We wanted to get proactive about potential critical issues that Snowflake customers might encounter. For example, whenever a customer has queries running for longer than “usual”, it could indicate that something changed and disrupted the system.
Instead of waiting for customers to stumble onto this potential pitfall, we look at their query ids to uncover any culprits. Next, we get in touch with the customer to talk through how to fix the issue. This provides a better customer experience and helps reduce the number of reactive issues sent to our Support team. A win win!
What
To follow along, you should start by getting your Snowflake data into one place. If you don’t have a Snowflake account, you can signup for a trial at https://www.snowflake.com.
Next, you’ll need a SnowAlert instance. That’s simple because you could start by running a Docker instance on your own machine. Checkout the SnowAlert installation instructions and source code here.
The following diagram explains the basic building blocks of a SnowAlert application.
And this next diagram shows how SnowAlert interacts with a Snowflake data warehouse and your Slack application.
Finally you’ll need to create a Slack application. Set application permission scopes as seen below:
and capture your Slack token from that Slack application. Then paste that token into your SnowAlert properties file for the SLACK_API_TOKEN property:
SLACK_API_TOKEN=your Slack API token hereAt this point, you should have your Snowflake account with a data warehouse. You should also have an instance of SnowAlert running. And you should have access to your Slack application.
How
For our example, we will use one of our real world SnowAlert deployments. It analyzes Salesforce CRM data imported into a Snowflake warehouse. It combines that with data from other sources to retrieve all open support cases which have not been active (no response, no comments…) in the last 10 days.
Create a bunch of tables in Snowflake for use in this example as shown in the following SQL. Note: be sure to change the example email addresses like user.one@yourcompany.com to the same email address you use with Slack.
USE DATABASE snowalert;CREATE SCHEMA IF NOT EXISTS samples;USE SCHEMA samples;CREATE TABLE IF NOT EXISTS account (id VARCHAR,name VARCHAR,type VARCHAR);CREATE TABLE IF NOT EXISTS user (id VARCHAR,name VARCHAR,email VARCHAR);CREATE TABLE IF NOT EXISTS case (id VARCHAR,severity VARCHAR,case_number VARCHAR,priority VARCHAR,status VARCHAR,is_closed BOOLEAN,created_date TIMESTAMP_TZ,last_modified_date TIMESTAMP_TZ,subject VARCHAR,description VARCHAR,owner_id VARCHAR,account_id VARCHAR);INSERT INTO account (id,name,type) VALUES ('0013100001rtE29AAE','Snowflake Community Portal','Customer');INSERT INTO user (id,name,email) VALUES ('0050Z000006Ht19QAC','User One','user.one@yourcompany.com');INSERT INTO user (id,name,email) VALUES ('0050Z000006Ht19QAD','User Two','user.two@yourcompany.com');INSERT INTO user (id,name,email) VALUES ('0050Z000008ZCHSQA4','User Three','user.three@yourcompany.com');INSERT INTO case (id,severity,case_number,priority,status,is_closed,created_date,last_modified_date,subject,description,owner_id,account_id) VALUES ('5000Z000010veDYQAY','Severity-1: Halts and impairs all business operations','00026612','Critical','New Comment',false,to_timestamp_ntz(CURRENT_TIMESTAMP()),to_timestamp_ntz(CURRENT_TIMESTAMP()),'test for slack integration for sev1','test for slack integration for sev1','0050Z000006Ht19QAC','0013100001rtE29AAE');INSERT INTO case (id,severity,case_number,priority,status,is_closed,created_date,last_modified_date,subject,description,owner_id,account_id) VALUES ('5000Z000013SybMQAS','Severity-2: Severity-2: High impact, but business is operational','00026613','High','Waiting on Engineering',false,to_timestamp_ntz(CURRENT_TIMESTAMP()),to_timestamp_ntz(CURRENT_TIMESTAMP()),'test for slack integration for sev2','test for slack integration for sev2','0050Z000006Ht19QAD','0013100001rtE29AAE');INSERT INTO case (id,severity,case_number,priority,status,is_closed,created_date,last_modified_date,subject,description,owner_id,account_id) VALUES ('5000Z000019XeiNQAS','Severity-3: Medium to low impact to business/operations','00026614','Medium','Waiting on Customer',false,to_timestamp_ntz(CURRENT_TIMESTAMP()),to_timestamp_ntz(CURRENT_TIMESTAMP()),'test for slack integration for sev3','test for slack integration for sev3','0050Z000008ZCHSQA4','0013100001rtE29AAE');
Create a data view in your Snowflake instance named snowalert.data.customer_case. This view aggregates data from the CRM system and converts it to a row-based structure for later use.
Note: The CRM tables used in the example emulate the Salesforce schema created by our Fivetran import. So, it is very easy to use this demo application with your real Salesforce data.
Here we join data from the CRM case, case owner and the account tables.
CREATE OR REPLACE VIEW snowalert.data.customer_case AS
SELECT
customer_case.severity,
customer_case.id,
customer_case.case_number,
customer_case.priority,
customer_case.status,
customer_case.is_closed,
customer_case.created_date,
customer_case.last_modified_date,
customer_case.subject,
case_owner.email AS assignee_email,
case_owner.name AS assignee_name,
account.id AS account_id,
account.name AS account_name,
account.type AS account_type
FROM snowalert.samples.case AS customer_case
LEFT JOIN snowalert.samples.user AS case_owner ON customer_case.owner_id = case_owner.id
LEFT JOIN snowalert.samples.account AS account ON account_id=account.id
WHERE customer_case.is_closed=false ORDER BY customer_case.priority;Create a query view for the alert named snowalert.rules.customer_pending_cases_alert_query. This view will use the data view snowalert.data.customer_case that we created before. This view has 2 Slack handlers defined. One delivers messages to individual assignees and the other to a Slack channel. In the following SQL code block, you can see them highlighted in bold text as an array of objects:
CREATE OR REPLACE VIEW snowalert.rules.customer_pending_cases_alert_query COPY GRANTS
COMMENT='Alerts on pending customer cases
@tags snowflake,customer'
AS
SELECT 'SnowAlert' AS environment
, array_construct('customer_pending_cases') AS sources
, case_number AS object
, 'Alerts on pending customer cases' AS title
, CURRENT_TIMESTAMP() AS event_time
, CURRENT_TIMESTAMP() AS alert_time
, subject AS description
, 'snowalert' AS actor
, ARRAY_CONSTRUCT(
OBJECT_CONSTRUCT(
'type', 'slack',
'recipient_email', assignee_email,
'channel', 'snowalert-channel',
'template','rules.case_channel_template'
),
OBJECT_CONSTRUCT(
'type', 'slack',
'recipient_email', assignee_email,
'template', 'rules.case_assignee_template'
)
) AS handlers
, 'login' AS action
, 'SnowAlert' AS detector
, OBJECT_CONSTRUCT(*) AS event_data
, priority AS severity
, 'customer_pending_cases' AS query_name
, id AS query_id
FROM snowalert.data.customer_case
ORDER BY customer_case.severity
;
GRANT ALL privileges ON VIEW snowalert.rules.customer_pending_cases_alert_query to role app_snowalert_rl;In the example above, you could have created as many handlers as you like. They could be different types, or even the same. For example, if I wanted to send an alert to an email recipient in addition to the two Slack channels, we would add an email handler into the array.
Before going any further, be sure to create the Slack channel used in your handler property channel. In this example, the channel is named snowalert-channel. See the Slack documentation on creating channels, if you aren’t sure how.
Moving on… In order take advantage of Slack’s rich UI message options, we have to generate a JSON payload. For this purpose we use Snowflake’s Javascript UDF template API. The name of the function we will create matches the handler template attribute, like:rules.case_assignee_template.
For an example of Slack’s rich UI in action, see the screenshot below.
To generate and test Slack messages we use the Block Kit Builder tool from Slack. This interactive tool gives you a way to build a rich interactive UI.
The custom Javascript UDF snowalert.rules.case_assignee_template will generate the Slack JSON payload for messages to the individual support case assignee.
The Javascript UDF will receive the full data stack from SnowAlert including:
- EVENT_DATA
- handler properties
- assignee_email, if defined in handler properties
- Slack user profile, if SnowAlert’s Slack plugin can match the email
Using all of this data, the UDF can construct highly personalized Slack message JSON payloads. There are a ton of options in Slack’s Payload API.
The SQL code below creates a Javascript UDF which outputs Slack JSON payloads with the name matching our handler’s template property, named rules.case_assignee_template.
create or replace function snowalert.rules.case_assignee_template(v object)
returns object
language javascript
as '
function template(vars)
{
var alert = vars.alert;
var event_data = alert.EVENT_DATA;// initialize template envelope
var template = {"blocks":{}, "attachments":{}, "text":event_data.SUBJECT};
var severity = event_data.SEVERITY;
// if case priority is Critical, add warning sign emoji
if("Critical" === event_data.PRIORITY)
severity = severity + "⚠️";
// create Slack blocks json structure with data from event
template.blocks = [
{
"type": "section",
"text": {
"type": "mrkdwn",
"text": "*" + event_data.ACCOUNT_NAME + "*"
}
},
{
"type": "section",
"text": {
"type": "plain_text",
"emoji": true,
"text": event_data.SUBJECT
}
},
{
"type": "divider"
},
{
"type": "section",
"fields": [
{
"text": "Case Number",
"type": "plain_text"
},
{
"text": event_data.CASE_NUMBER,
"type": "mrkdwn"
},
{
"text": "Status",
"type": "plain_text"
},
{
"text": event_data.STATUS,
"type": "mrkdwn"
},
{
"text": "Severity",
"type": "plain_text"
},
{
"text": severity,
"type": "mrkdwn"
} ,
{
"text": "Last Modified Date",
"type": "plain_text"
},
{
"text": event_data.LAST_MODIFIED_DATE,
"type": "mrkdwn"
}
]
},
{
"type": "divider"
}
];
return template;
}
return template(V);
';
grant all privileges on function snowalert.rules.case_assignee_template(object) to role app_snowalert_rl;
As you can see at the bottom of the code snippet above, grants are important! Do not forget to grant permissions to the SnowAlert role so that it can call the template Javascript UDF.
You can use Snowflake Worksheet to test the UDF. We want to ensure that it generates a good JSON payload. Again, we use the Slack Kit Builder tool to verify if generated blocks will be rendered properly in Slack.
Note: Slack has certain limitations, for example number of fields in a section cannot exceed 10, so it’s a good idea to test it before releasing to avoid problems in production. Slack’s blocks documentation describes the restrictions.
The following SQL calls our UDF with some example data:
select snowalert_support.rules.case_assignee_template(parse_json('{"alert":{"EVENT_DATA": {"ACCOUNT_ID": "0013100001rtE29AAE","ACCOUNT_NAME": "Snowflake Community Portal","ACCOUNT_TYPE": "Customer","ASSIGNEE_EMAIL": "user.one@yourcompany.com","ASSIGNEE_NAME": "User One","CASE_NUMBER": "00026612","CREATED_DATE": "2019-07-02 10:49:51.100 -0700","ID": "5000Z000010veDYQAY","IS_CLOSED": false,"LAST_MODIFIED_DATE": "2019-07-02 10:49:51.100 -0700","PRIORITY": "Critical","SEVERITY": "Severity-1: Halts and impairs all business operations","STATUS": "New Comment","SUBJECT": "test for slack integration for sev1"}}}'))If you call the template UDF function, it should return a JSON structure as seen below. Just copy the blocks array (highlighted) and paste it into the Slack Block Kit Builder.
{
"attachments": {},
"blocks": [
{
"text": {
"text": "*Snowflake Community Portal*",
"type": "mrkdwn"
},
"type": "section"
},
{
"text": {
"emoji": true,
"text": "test for slack integration for sev1",
"type": "plain_text"
},
"type": "section"
},
{
"type": "divider"
},
{
"fields": [
{
"text": "Case Number",
"type": "plain_text"
},
{
"text": "00026612",
"type": "mrkdwn"
},
{
"text": "Status",
"type": "plain_text"
},
{
"text": "New Comment",
"type": "mrkdwn"
},
{
"text": "Severity",
"type": "plain_text"
},
{
"text": "Severity-1: Halts and impairs all business operations⚠️",
"type": "mrkdwn"
},
{
"text": "Last Modified Date",
"type": "plain_text"
},
{
"text": "2019-07-02 10:49:51.100 -0700",
"type": "mrkdwn"
}
],
"type": "section"
},
{
"type": "divider"
}
],
"text": "test for slack integration for sev1"
}Alternatively, the following Javascript UDF function demonstrates additional Slack rich UI capabilities.
create or replace function snowalert.rules.case_channel_template(v object)
returns object
language javascript
as '
function template(vars)
{
var user = vars.user;
var alert = vars.alert;
var event_data = alert.EVENT_DATA;
var template = {"blocks":{}, "attachments":{}, "text":event_data.SUBJECT};
var name = event_data.ASSIGNEE_NAME;
var assignee = name;var image = "https://ca.slack-edge.com/T02BSG1HZ-WEJC6104F-g1911419a252-72";if(typeof user != "undefined")
{
assignee = "<@" + user.id + ">";if(typeof user.profile.image_original != "undefined")
image = user.profile.image_original;
}template.blocks =
[
{
" type": "section",
"fields": [
{
"text": "Assignee",
"type": "plain_text"
},
{
"text": assignee,
"type": "mrkdwn"
}
],
"accessory": {
"type": "image",
"image_url": image,
"alt_text": name
}
}
];var color = "#ff0000";
if("Low" === event_data.PRIORITY)
color = "#ffff33";
if("Medium" === event_data.PRIORITY)
color = "#ffa500";template.attachments = [
{
"title": "Case Number " + event_data.CASE_NUMBER,
"author_name": event_data.ACCOUNT_NAME,
"text": event_data.SUBJECT,
"color": color,
"fields": [
{
"title": "Status",
"value": event_data.STATUS,
"short": true
},
{
"title": "Severity",
"value": event_data.SEVERITY,
"short": true
},
{
"title": "Last Modified Date",
"value": event_data.LAST_MODIFIED_DATE,
"short": true
}
]
}
]
return template;
}
return template(V);
';grant all privileges on function snowalert_support.rules.case_channel_template(object) to role app_snowalert_support_rl;
Our internal users love when we use the additional Slack features like user pictures, links and attachments. Here’s an example:
Conclusion
That’s how easy it is to tie your Snowflake data warehouse to a powerful notification mechanism like Slack. We really do use this internally. It works well for us. As we rolled this out to the team, they started getting excited about the potential. Before we knew it, we had more ideas to implement than time allows.
If you want to know more or questions, feel free to contact Roman Dobrik or PJ-Paul Julius. You can participate in the SnowAlert project on GitHub. And go to Snowflake.com for many options to read more, contact us, and expand your Snowflake usage.
