Snowflake Tasks are a great way to automatically execute SQL queries on a schedule. In this guide, we'll focus on setting up notifications which will be sent to an email address when a task run fails. As in Snowflakes Official Documentation, we'll use AWS SNS for this.
- Terraform Setup
- Create SNS Topic and IAM Policy
- Creating the Snowflake Notification Integration
- Granting Snowflake Access to the SNS Topic
- Enabling Error Notifications in Tasks
- Receive Email Notifications
Terraform Setup
First, we need to create the terraform folder structure:
mkdir terraform
touch terraform/variables.tf
touch terraform/providers.tf
touch terraform/outputs.tf
touch terraform/main.tf
variables.tf
variable "name" {
description = "the name of your stack, e.g. \"demo\""
default = "snowflake-error-notifications"
}
variable "environment" {
description = "the name of your environment, e.g. \"prod\""
default = "prod"
}
variable "region" {
description = "the AWS region in which resources are created, you must set the availability_zones variable as well if you define this value to something other than the default"
default = "eu-central-1"
}
providers.tf
provider "aws" {
region = var.region
}
outputs.tf
output "sns_topic_arn" {
value = "${aws_sns_topic.snowflake-error-notifications.arn}"
}
output "iam_role_arn" {
value = "${aws_iam_role.snowflake-error-notifications-role.arn}"
}
Create SNS Topic and IAM Policy
main.tf
resource "aws_sns_topic" "snowflake-error-notifications" {
name = "sns-${var.name}-${var.environment}"
tags = {
Name = "${var.name}"
Env = "${var.environment}"
}
}
resource "aws_iam_policy" "snowflake-error-notifications-policy" {
name = "policy-${var.name}-${var.environment}"
description = "Policy for Snowflake Tasks error notifications"
policy = jsonencode({
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"sns:Publish"
],
"Resource": "${aws_sns_topic.snowflake-error-notifications.arn}"
}
]
})
tags = {
Name = "${var.name}"
Env = "${var.environment}"
}
}
resource "aws_iam_role" "snowflake-error-notifications-role" {
name = "role-${var.name}-${var.environment}"
assume_role_policy = jsonencode({
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "sns.amazonaws.com"
},
"Action": "sts:AssumeRole"
}
]
})
tags = {
Name = "${var.name}"
Env = "${var.environment}"
}
}
resource "aws_iam_policy_attachment" "snowflake-error-notifications-policy-attachment" {
name = "policy-attachment-${var.name}-${var.environment}"
roles = ["${aws_iam_role.snowflake-error-notifications-role.name}"]
policy_arn = "${aws_iam_policy.snowflake-error-notifications-policy.arn}"
}
Now we can create the terraform configuration for our stack:
cd terraform
terraform init
terraform apply
Creating the Snowflake Notification Integration
In Snowflake, we'll create the notification integration. Please note that you need to be ACCOUNTADMIN for this to work.
Get SNS topic and IAM Role ARN from the Terraform output and add it to:
create notification integration task_error_notifications
enabled = true
type = queue
notification_provider = aws_sns
direction = outbound
aws_sns_topic_arn = 'arn:aws:sns:XXX:sns-snowflake-error-notifications-prod'
aws_sns_role_arn = 'arn:aws:iam::XXX:role/role-snowflake-error-notifications-prod';
Granting Snowflake Access to the SNS Topic
desc notification integration task_error_notifications;
You will need SF_AWS_IAM_USER_ARN and SF_AWS_EXTERNAL_ID from the query output.
Next, head back to our terraform code and add these values to the aws_iam_role resource:
resource "aws_iam_role" "snowflake-error-notifications-role" {
name = "role-${var.name}-${var.environment}"
assume_role_policy = jsonencode({
"Version": "2012-10-17",
"Statement": [
{
"Sid": "",
"Effect": "Allow",
"Principal": {
"AWS": "<sf_aws_iam_user_arn>"
},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": {
"sts:ExternalId": "<sf_aws_external_id>"
}
}
}
]
})
tags = {
Name = "${var.name}"
Env = "${var.environment}"
}
}
Now update the terrafom configuration:
terraform apply
Enabling Error Notifications in Tasks
The only thing left is to enable the integration we just created in our Snowflake Tasks.
For new tasks:
create task new_task
schedule = '5 MINUTE'
error_integration = task_error_notifications
as
insert into mytable(ts) values(current_timestamp);
For existing tasks:
alter task old_task suspend;
alter task old_task set error_integration = task_error_notifications;
alter task old_task resume;
In case a task fails, the payload will be sent to the SNS topic as a JSON string:
{\"version\":\"1.0\",\"messageId\":\"3ff1eff0-7ad7-493c-9552-c0307087e0c6\",\"messageType\":\"USER_TASK_FAILED\",\"timestamp\":\"2021-11-11T19:46:39.648Z\",\"accountName\":\"AWS_UTEN_DPO_ACC\",\"taskName\":\"AWS_UTEN_DPO_DB.AWS_UTEN_SC.UTEN_AWS_TK1\",\"taskId\":\"01a03962-2b57-889e-0000-000000000001\",\"rootTaskName\":\"AWS_UTEN_DPO_DB.AWS_UTEN_SC.UTEN_AWS_TK1\",\"rootTaskId\":\"01a03962-2b57-889e-0000-000000000001\",\"messages\":[{\"runId\":\"2021-11-11T19:46:23.826Z\",\"scheduledTime\":\"2021-11-11T19:46:23.826Z\",\"queryStartTime\":\"2021-11-11T19:46:24.879Z\",\"completedTime\":\"null\",\"queryId\":\"01a03962-0300-0002-0000-0000000034d8\",\"errorCode\":\"000630\",\"errorMessage\":\"Statement reached its statement or warehouse timeout of 10 second(s) and was canceled.\"}]}
Receive Email Notifications
If you'd like to receive an email every time a task fails, you can add the following to main.tf:
resource "aws_sns_topic_subscription" "snowflake-error-notifications-subscription" {
topic_arn = "${aws_sns_topic.snowflake-error-notifications.arn}"
protocol = "email"
endpoint = "youremail@example.com"
}
And apply the changes:
terraform apply
You will receive an Email from AWS where you have to confirm the subscription:
Top comments (0)