How to setup Snowflake backup confirmation and alert notifications

Data backups are key to ensuring business continuity. However, it is important to track if the backups are successful with a notification system without incurring any significant additional costs.

Due to the lack of sufficient Snowflake documentation on backup alerting, this blog discusses how to set up alerts to monitor backup task in Snowflake. We will cover setting up AWS Services such as: SNS, Lambda and EventBridge to monitor backup task in Snowflake. Here are the steps for implementing monitoring and alerting for a Snowflake backup task.

• Step 1: Setting Up AWS SNS Topic
• Step 2: Setting up AWS Lambda Function
• Step 3: Setting up AWS EventBridge Rule

Prerequisites

Consider an existing scheduled backup task in Snowflake, scheduled with cron expression. You can have a backup task that calls a stored procedure, a set of sql statements. Then this stored procedure backs up tables of snowflakes in S3 path through S3 stage. Let’s consider we have a backup task called backup_task that is scheduled to run at 4:00 p.m daily. This backup_task then calls a procedure called snowflake_database that backs up tables called sample1_table and sample2_table in S3 path through a S3 stage called my_s3_stage.

Below is an example of how a backup task can look like:

CREATE OR REPLACE PROCEDURE snowflake_database(CREATE_DATE varchar) 
returns string not null 
language javascript 
as 
$$ 
var sql_commands=['copy into @my_s3_stage/' + CREATE_DATE + '/sample1_table_backup/backup from sample1_table ', 'copy into @my_s3_stage/' + CREATE_DATE + '/sample2_table_backup/backup from sample2_table '] 
for (let i = 0; i < sql_commands.length; i++) { 
var stmt = snowflake.createStatement( {sqlText: sql_commands[i]+' header=true file_format=(format_name=csv_file_format);',binds: [CREATE_DATE]} ) 
var result = stmt.execute(); 
} 
return 'Successfully Executed'; 
$$ 
  
create or replace task backup_task
schedule = 'USING CRON 0 14 * * * Asia/Calcutta' 
as 
call snowflake_database(TO_VARCHAR(current_timestamp,'yyyy/mm/dd'));

Example of Backup Task

By default snowflake tasks are created in suspended mode. You have to start them using the following command:

Alter  task <task_name> resume;

Backup confirmation/failed alerts are crucial to be aware that our data is being backed up on a timely basis. Let’s assume that backup task in snowflake is scheduled at 4:00 PM daily, we will schedule AWS EventBridge to run daily at 4:01 PM using a cron job and trigger a Lambda Function that access the snowflake database and checks if that backup task has executed successfully or not. If the backup task has executed successfully we will receive a backup confirmation notification, if not then a backup failed notification will be received so that an action can be taken as early as possible.

Fig 1. Flow of Backup Success or Failed Alert Mechanism

Step 1: Setting up AWS SNS topic:

1. Make sure to have AWS SNS Topic with confirmed subscription in place.
2. Now edit the Access Policy of SNS Topic with following permissions:

NOTE: Replace with the Region that this notification is for. Replace with your account ID. Replace with the SNS topic name.

{
   "Version": "2012-10-17",
   "Id": "example-ID",
   "Statement": [
     {
       "Sid": "SNS topic policy",
       "Effect": "Allow",
       "Principal": {
         "Service": "lambda.amazonaws.com"
       },
       "Action": "SNS:Publish",
       "Resource": "arn:aws:sns:<region-name>:<account-id>:<sns-topic-name>",
       "Condition": {
         "StringEquals": {
           "aws:SourceAccount": "<account-id>"
         }
       }
     }
   ]
 }

Access Policy for SNS Topic

Step 2: Setting up AWS Lambda function:

Prerequisites:

1. AWS IAM Role for Lambda function with LambdaBasicExecution Policy and SNS: Publish permission attached.
2. Import Snowflake connector module by creating a custom Lambda Layer.

Steps:

1. Create a Lambda function with Python 3.8 Runtime.
2. Choose IAM Role created for lambda function.
3. Add permission for Lambda function to be invoked from AWS EventBridge Rule. Go to configuration tab in Lambda function, select permissions tab, scroll down to Resource-based policy, click add permissions. Now add permissions like in the image below.

Note: Replace source account with your AWS Account ID. In Source ARN add your EventBridge Rule ARN.

Fig 2. Resource Based Policy for Lambda Function

1. Add Snowflake username, password, account and database by going to Configuration > Environment Variables.
2. Add the following code in Lambda Function.

The following Lambda function accesses the snowflake database with the credentials provided. In statement_3, we are checking if the backup_task has failed with an error with task_history. This table function can be used to query the history of task usage within a specified date range. The function returns the history of task usage for your entire Snowflake account or a specified task. Learn more.

Then we are fetching the output from statement_3 and counting if row fetched is 1. As we are only fetching only 1 row from query in statement_3 result_limit => 1 that means history of last executed backup_task is fetched making sure it has executed with error, ERROR_ONLY => true.

Then, rowCount is storing the number of rows fetched and if rowCount > 0, Backup failed SNS notification is sent through the SNS Topic ARN specified. Moreover, we are also storing the output of statement_3 as stored in the rows variable and storing the error_message column in the res variable to include that in the email alert body.

If rowCount equals to 0 then backup confirmation SNS notification is sent through the SNS Topic ARN specified.

import snowflake.connector as sf
import os
import boto3
import json
import os
import logging
def run_query(conn, query):
   cursor = conn.cursor();
   cursor.execute(query);
   cursor.close();
def lambda_handler(event, context):
   user=os.environ['username']
   password=os.environ['password']
   account=os.environ['account']
   database=os.environ['database']
   warehouse="COMPUTE_WH"
   schema="PUBLIC"
   role="SYSADMIN"
   conn=sf.connect(user=user,password=password,account=account)
   statement_1 = ("use warehouse compute_wh; ")
   statement_2 = ("use schema database.schema; ")
   statement_3= ( "select * from table(information_schema.task_history(scheduled_time_range_start=>dateadd          ('hour',-1,current_timestamp()), result_limit => 1, ERROR_ONLY => true, task_name=>'backup_task')); ")
  
   run_query(conn,statement_1) #executing statement_1
   run_query(conn,statement_2) #executing statement_2
   cursor = conn.cursor();
   cursor.execute(statement_3) #executing statement_3
   rows = cursor.fetchall()  #fetching all rows from output of statement_3
   rowCount = len(rows) #counting number of rows fetched
   res = [lis[8] for lis in rows] #storing error_message fetched from row i.e list of tuple and storing in     variable res to print in email body
if rowCount > 0:    # if number of rows fetched is greater than 0, send backup failed SNS notification
     # Set up logging
     logger = logging.getLogger()
     logger.setLevel(logging.INFO)
     # Set up Boto 3 client for SNS
     client = boto3.client('sns')
     # Variables for the SNS:
     snsTopicARN = "arn:aws:sns:<region-name>:<account-id>:<topic-name>"
     message = \
       "Backup of Snowflake on S3 path has failed due to the following error:  " +str(res)
     print(message)
     response = client.publish(
       TargetArn=snsTopicARN,
       Message=json.dumps({'default': json.dumps(message)}),
       Subject='Snowflake Backup Failed!!',
       MessageStructure='json')
      
   if rowCount == 0:   # if number of rows fetched is equals to 0, send backup success SNS notification
     # Set up logging
     logger = logging.getLogger()
     logger.setLevel(logging.INFO)
     # Set up Boto 3 client for SNS
     client = boto3.client('sns')
     # Variables for the SNS:
     snsTopicARN = "arn:aws:sns:<region-name>:<account-id>:<topic-name>"
     message = \
       "Backup of Snowflake on S3 path has succeeded!! "
     print(message)
     response = client.publish(
       TargetArn=snsTopicARN,
       Message=json.dumps({'default': json.dumps(message)}),
       Subject='Snowflake Backup Succeeded!!',
       MessageStructure='json')

Lambda Function Python Code

Step 3: Setting up AWS EventBridge rule:

1. Go to the Amazon EventBridge console at https://console.aws.amazon.com/events/.
2. Go to the navigation pane and choose Rules.
3. Click Create rule.
4. Enter a name and description for the rule.
   A rule can’t have the same name as another rule in the same Region and on the same event bus.
5. For the Event bus, choose default.
6. For Rule type, choose Schedule
7. Click Next.
8. For Schedule Pattern, choose A fine-grained schedule that runs at a specific time, such as 8:00 a.m. PST on the first Monday of every month.
9. Enter Cron expression: For ex: 31 10 * * ? *
10. Click Next.
11. For Target types, choose AWS service.
12. For Select a target, choose Lambda Function.
13. Select Lambda Function as configured earlier.
14. Click Next.
15. (Optional) Enter one or more tags for the rule.
16. Click Next.
17. Review the details of the rule and click Create rule.

Following is the example of how Backup Confirmation/Failed alert is received

Fig 3. Example of Backup Confirmation Notification

Fig 4. Example of Backup Failed Alert

About the author

Prajna Bahuguna is a DevOps Engineer at Sigmoid. She is a DevOps enthusiast who is always curious to learn about innovative solutions using Cloud, Python and Devops practices. In her leisure time she enjoys doing artwork and mobile photography.