Streamlining Snowflake Task Monitoring with Automated Alerts and Email Notifications

Introduction:

As we are all aware, Snowflake is a widely adopted modern cloud-based data platform that provides data warehousing, analytics, and data sharing capabilities. It is designed to handle large-scale data workloads with high performance, scalability, and flexibility. As with any critical system, ensuring that your workflows run smoothly is key to maintaining data integrity and avoiding costly errors. One such way of ensuring this is through task failure notifications—a way to quickly identify and act on problems before they escalate.

In this blog, we will go through the various ways Snowflake can notify you about failures and a use case to notify task failures.

Why Error Notifications Are Important:

In data workflows, failing to catch an error early can lead to delayed reports, incorrect analysis, or even bigger system failures down the line. By setting up error notifications, you can:

• Stay ahead of failures: You’ll be alerted to issues in real-time.
• Maintain system uptime: Timely notifications mean problems can be addressed before they snowball into larger issues.
• Ensure data integrity: Alerts allow you to fix issues that might affect data quality or reliability.

Snowflake Native Error Logging and Monitoring: Snowflake provides built-in capabilities to monitor errors through QUERY_HISTORY, TASK_HISTORY, and INFORMATION_SCHEMA views. These can be queried to detect failed tasks and provide a log for troubleshooting.

Event Notifications via Cloud Providers: If you’re on AWS, Azure, or Google Cloud, you can configure Snowflake Event Notifications to send alerts when certain events occur, like a task failure. These notifications can be sent via SNS (AWS), Event Grid (Azure), or similar services.

NOTIFICATION INTEGRATION:

It is used to define the connection or integration for sending notifications via an external service like email. This requires ACCOUNTADMIN access.

1. TYPE = ‘EMAIL’:

Specifies that the integration is for email notifications.

2. ENABLED = TRUE:

This enables the integration so that it can be used for sending email notifications.

3. ALLOWED_RECIPIENTS:

This is a list of email addresses that are allowed to receive notifications from this integration. You can list multiple email addresses separated by commas.

Important: You must specify email addresses of users in the current account. These users must verify their email addresses.

The maximum number of email addresses that you can specify is 50.

4. COMMENT:

This is an optional description that helps to identify the purpose of the integration.

Usecase:
The SP_SEND_FAILURE_ALERT procedure is designed to send an email alert about task failures within a specified Snowflake database. It takes the database name (DB_NAME) as an input parameter to get the database specific failures (if all failures irrespective of database need to be notified then it need not be parameterized) and then runs a query to check for tasks in the TASK_HISTORY table of the Snowflake ACCOUNT_USAGE schema that have failed within the last 60 minutes for the given database. The query retrieves key information such as the task name, status, database, schema, and error message.

Once the query is executed, the results are processed, and an HTML email message is constructed. If task failures are found, the failure details, including task name, status, database, schema, and error message, are formatted into an HTML table. After constructing the email content, the procedure uses the SYSTEM$SEND_EMAIL function to send the email to a predefined recipient with the subject “Task and Pipeline Failure Alert: Snowflake Account” and the HTML body containing the failure information or the no-failure message. Finally, the procedure returns a success message, “Succeeded! Email sent,” indicating the alert was successfully dispatched.

Procedure Script:

Steps Involved:

1. Task Failure Query:

A SQL query is run against the SNOWFLAKE.ACCOUNT_USAGE.TASK_HISTORY table to find tasks that have a “FAILED” status in the last 60 minutes for the given database name.

2. Query Execution:

The procedure executes the above SQL query using the snowflake.createStatement() function, passing the DB_NAME as a bind parameter.

If there are any failed tasks within the last hour, their details (Task Name, Status, Database Name, Schema Name, and Error Message) are retrieved.

3. HTML Email Construction:

An HTML email message is dynamically constructed, displaying the task failure details in a table format.

The email contains the following:

3. Subject: “Task Failure Alert: Snowflake Account”
4. Body: Includes details of the failed tasks (Task Name, Status, Database, Schema, and Error Message) formatted into an HTML table.
5. Send Email:

The procedure then uses SYSTEM$SEND_EMAIL to send the email alert to a specified recipient.

The email content is passed as an HTML-formatted message.

In addition to using Snowflake Event Notifications for triggering real-time alerts, another effective way to monitor and notify about errors in Snowflake (or any cloud platform) is by logging failure details in a custom audit table and triggering failure notification emails when an error occurs.
You can also build a cross-platform error logging and alerting system that can be used to monitor and track failures in various cloud environments (such as AWS, Azure, and Google Cloud Platform) and send error notifications in a custom format. By leveraging a similar approach to the SP_SEND_FAILURE_ALERT procedure, which is specifically designed for Snowflake and integrates task failure data, the same concept can be applied across different cloud platforms to log errors from services like AWS Lambda, Google Cloud Functions, or Azure Data Factory (ADF) pipelines, and then notify users of any failures.

ALERTS:
Create an alert that runs every 60 mins to check for any task failures and call the above procedure accordingly. This can be created using ACCOUNTADMIN role.

The alert checks the condition (EXISTS clause) periodically based on the schedule you define. In your case, the condition is checked every 60 minutes.

If the condition evaluates to true (meaning there are task failures within the last 60 minutes for the specified database), the action (the CALL to the stored procedure) is triggered.

In this case, the action is calling the SP_SEND_FAILURE_ALERT stored procedure to send an email notification or perform other necessary actions.

This alert helps automate monitoring and notifications, ensuring that the team is automatically notified about task failures without needing to manually check the task history.

 Conclusion:

Snowflake task failure notifications are crucial for maintaining a smooth data pipeline and ensuring that issues are addressed promptly. The above-mentioned use case ensures that your team is alerted immediately when something goes wrong with the task, allowing for quicker resolutions and better system reliability. Please feel free to reach out to us for your Snowflake solution needs. Cittabase is a Premier  partner with Snowflake.