Configuring Snowflake Email Alerts for Failed Tasks

Question

Pulumi · Accepted Answer

To configure Snowflake email alerts for failed tasks using Pulumi, you would typically do the following:

1. Create a Notification Integration that specifies the email channel for sending notifications.
2. Create a Snowflake Task that performs a specific operation and specifies the Notification Integration to use in case of failure.

Below is a detailed Pulumi program in TypeScript that sets up a Snowflake email alert for failed tasks. The program will include comments to guide you through what each section and line of code does.

We'll be using two resources from the Pulumi Snowflake provider:

- `snowflake.NotificationIntegration` to create the notification integration for email alerts.
- `snowflake.Task` to define the task along with the error notification setup pointing to the previously created Notification Integration.

Let's start writing the Pulumi program.

```typescript
import * as pulumi from "@pulumi/pulumi";
import * as snowflake from "@pulumi/snowflake";

// Create a Snowflake Notification Integration for email alerts.
const emailIntegration = new snowflake.NotificationIntegration("emailAlerts", {
    // The name of the notification integration.
    name: "email_alerts_integration",
    // Type of integration, in this case, we are using an external cloud service for notifications.
    type: "EMAIL",
    // Set the notification service provider, Snowflake currently supports AWS SNS for email notifications.
    notificationProvider: "AWS_SNS",
    // The ARN of the AWS SNS topic that will be used to send the email notifications.
    // Assumed to be created outside of Pulumi and available for use.
    awsSnsTopicArn: "arn:aws:sns:us-west-2:123456789012:my_sns_topic",
    // This would enable the notification integration.
    enabled: true,
});

// Create a Snowflake Task that has a dependency on the Notification Integration for failed executions.
const sampleTask = new snowflake.Task("sampleTask", {
    // The name of the task in Snowflake.
    name: "my_sample_task",
    // The database and schema in which this task should be created.
    database: "MY_DATABASE",
    schema: "MY_SCHEMA",
    // The SQL statement the task will execute.
    sqlStatement: "SELECT COUNT(*) FROM my_table",
    // The warehouse that provides resources for the task.
    warehouse: "MY_WAREHOUSE",
    // Set the error integration to the email integration created above.
    // This is what links the task to the notification integration for alerts.
    errorIntegration: emailIntegration.name,
    // The schedule in cron format; here it's set to run every day at midnight.
    schedule: "USING CRON 0 0 * * * UTC",
    // You can set this to whether the task is enabled or not.
    enabled: true,
});

// Export the Notification Integration and Task names
export const emailIntegrationName = emailIntegration.name;
export const taskName = sampleTask.name;
```

In this program, we're first creating a `NotificationIntegration` resource. We've named it `emailAlerts`, and we specify the email channel and relevant details for AWS SNS, which Snowflake will use to send email alerts.

After creating the notification integration, we define a `Task` that will execute a simple SQL statement on the specified database and schema. Note the `errorIntegration` property of the task, where we pass the name of the notification integration we created. This setup ensures that when the task fails, Snowflake will trigger an email alert via the AWS SNS topic.

Lastly, we export the names of the created resources as stack outputs, so you can easily retrieve them after deployment.

Remember to replace placeholder values with actual ARNs and names relevant to your Snowflake and AWS setup.

To run this Pulumi program:

1. Ensure you have Pulumi CLI installed and configured with the necessary cloud provider credentials.
2. Save the code in a `index.ts` file within a Pulumi project directory.
3. Run `pulumi up` to deploy the resources to your Snowflake account.

This program should set up a Snowflake task with email alerts configured for failures using your AWS SNS topic. When the task fails, it should trigger an email notification through AWS SNS, and you will receive an email alert.