2
0
During this post we will discuss how to handle the TASK failure and send a notification to respective stakeholders. As we know we can implement TASK notification with the help of AWS:SNS notification service. But as per requirement we are not allowed to use any AWS service and if possible implement with out of box Snowflake functions. Consider the scenario in our database we have multiple TASKS exists and bind to various objects. Like some TASKS executing procedure while some others perform DML actions. It can be the possibility that any of the tasks fail because of any unseen issues. Currently the Operations team must check the TASK_HISTORY table after every hour manually and find out the erroneous TASKS. Now, the operations team wants to have track of these failure TAKS automatically. An email should be sent to the Team in case any TASK gets fail in last one hour.
We will be leveraging the SYSTEM$SEND_EMAIL() function a notification integration object, which is a Snowflake object that provides an interface between Snowflake and third-party services (e.g. cloud message queues, email, etc.
Also we will be devising a query which will scan the TASK_HISTORY table for every hour and fetch the failed tasks.
To implement the solution we have created the below stored procedure. Procedure first find out the Error tasks and fetch the TASK id and error message. Then we can call send_email function to send the details about task to the subscribers.
Say we have three TASK in system.
For the purpose of this demo , I have bind the wrong Table with TASK2 and TASK3 respectively.
So if we see TASKS history table we can see the entry for these failed tasks.
Now we create below procedure to send the details to the stake holders.
Tied the Procedure with the TASK and schedule the frequency according to your need.
CREATE OR REPLACE TASK TASK_NOTIFY
WAREHOUSE = COMPUTE_WH,
SCHEDULE = '1 MINUTE'
AS
call TASK_FAILURE_NOTIFY();
An Email sent to the mail id with the details:
