Snowflake Task Error Notifications with Terraform/ AWS SNS
Tags: terraform, aws, tutorial, snowflake
Published: 2022-08-17 12:56:41

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,](https://docs.snowflake.com/en/user-guide/tasks-errors.html) we'll use AWS SNS for this.

- [Terraform Setup](#terraform-setup)
- [Create SNS Topic and IAM Policy](#create-sns-topic-and-iam-policy)
- [Creating the Snowflake Notification Integration](#creating-the-snowflake-notification-integration)
- [Granting Snowflake Access to the SNS Topic](#granting-snowflake-access-to-the-sns-topic)
- [Enabling Error Notifications in Tasks](#enabling-error-notifications-in-tasks)
- [Receive Email Notifications](#receive-email-notifications)

## Terraform Setup
First, we need to create the terraform folder structure:
```bash
mkdir terraform
touch terraform/variables.tf
touch terraform/providers.tf
touch terraform/outputs.tf
touch terraform/main.tf
```

### `variables.tf`
```terraform
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`
```terraform
provider "aws" {
  region = var.region
}
```

### `outputs.tf`
```terraform
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`
```terraform
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:
```bash
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:

```sql
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
```sql
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:

```terraform
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:
```bash
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:
```sql
create task new_task
  schedule = '5 MINUTE'
  error_integration = task_error_notifications
  as
  insert into mytable(ts) values(current_timestamp);
```

For existing tasks:
```sql
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:
```bash
{\"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`:

```terraform
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:
```bash
terraform apply
```

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5yxw9pa7uiokuplcttut.png)