How to setup Snowflake backup confirmation and alert notifications
Reading Time: 5 minutes
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:
Example of Backup Task
By default snowflake tasks are created in suspended mode. You have to start them using the following command:
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:
- Make sure to have AWS SNS Topic with confirmed subscription in place.
- 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.
Access Policy for SNS Topic
Step 2: Setting up AWS Lambda function:
Prerequisites:
- AWS IAM Role for Lambda function with LambdaBasicExecution Policy and SNS: Publish permission attached.
- Import Snowflake connector module by creating a custom Lambda Layer.
Steps:
- Create a Lambda function with Python 3.8 Runtime.
- Choose IAM Role created for lambda function.
- 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.
- Add Snowflake username, password, account and database by going to Configuration > Environment Variables.
- Add the following code in Lambda Function.
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
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.
Lambda Function Python Code
Step 3: Setting up AWS EventBridge rule:
- Go to the Amazon EventBridge console https://console.aws.amazon.com/events/.
- Go to the navigation pane and choose Rules.
- Click Create rule.
- 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. - For the Event bus, choose default.
- For Rule type, choose Schedule
- Click Next.
- 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.
- Enter Cron expression: For ex: 31 10 * * ? *
- Click Next.
- For Target types, choose AWS service.
- For Select a target, choose Lambda Function.
- Select Lambda Function as configured earlier.
- Click Next.
- (Optional) Enter one or more tags for the rule.
- Click Next.
- 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.
