DEV Community šŸ‘©ā€šŸ’»šŸ‘Øā€šŸ’»

DEV Community šŸ‘©ā€šŸ’»šŸ‘Øā€šŸ’» is a community of 963,274 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
Cover image for Snowflake Task Error Notifications with Terraform/ AWS SNS
Falk
Falk

Posted on

Snowflake Task Error Notifications with Terraform/ AWS SNS

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

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
Enter fullscreen mode Exit fullscreen mode

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"
}
Enter fullscreen mode Exit fullscreen mode

providers.tf

provider "aws" {
  region = var.region
}
Enter fullscreen mode Exit fullscreen mode

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}"
}
Enter fullscreen mode Exit fullscreen mode

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}"
}
Enter fullscreen mode Exit fullscreen mode

Now we can create the terraform configuration for our stack:

cd terraform
terraform init
terraform apply
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

Granting Snowflake Access to the SNS Topic

desc notification integration task_error_notifications;
Enter fullscreen mode Exit fullscreen mode

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}"
  }
}
Enter fullscreen mode Exit fullscreen mode

Now update the terrafom configuration:

terraform apply
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

For existing tasks:

alter task old_task suspend;
alter task old_task set error_integration = task_error_notifications;
alter task old_task resume;
Enter fullscreen mode Exit fullscreen mode

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.\"}]}
Enter fullscreen mode Exit fullscreen mode

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"
}
Enter fullscreen mode Exit fullscreen mode

And apply the changes:

terraform apply
Enter fullscreen mode Exit fullscreen mode

You will receive an Email from AWS where you have to confirm the subscription:

Image description

Top comments (0)

šŸŒš Browsing with dark mode makes you a better developer.

It's a scientific fact.