7 0
Read Time:2 Minute, 40 Second

During the last post we discussed how to handle the TASK failure and send an alert to respective stakeholders. We leveraged SYSTEM$SEND_EMAIL() function with TASK to notify the users. But in real time it is very much possibility that all  process are not tied to the TASKS. And still there should be an automated way to notify the users in case of any process failure occurs. Also not limited to failure but sometimes we need to keep an eye over the usage of Credits of WH or Cloud Service layer. However, Recently Snowflake has announced “Snowflake ALERTS” to proactively manage your data and workload issues in highly efficient manner. With introduction of ALERTS ,customers can create alerts notification based on the business need and simplify their operations work.

Moreover,we will discuss two use cases with ALERTS:

Case1: Firstly, Say we have few TASKS are running in your account and there is high chance that any of the task gets failed because of any valid reason. To observe the failed task team has to scan the TASK_HISTORY table for every hour and fetch the failed tasks and then use the stored procedure to send the mails.

With the introduction of ALERTS we can easily achieve with out writing any Stored procedure and TASK to bind this SP.

CREATE OR REPLACE ALERT TASK_FAILURE
WAREHOUSE = COMPUTE_WH
SCHEDULE = '1 MINUTE'
IF ( EXISTS
(
select NAME,ERROR_CODE,ERROR_MESSAGE
from table(information_schema.task_history(scheduled_time_range_start=>dateadd
('hour',-1,current_timestamp())))
WHERE (QUERY_ID IS NOT NULL AND STATE != 'SUCCEEDED') AND
CAST(SCHEDULED_TIME AS DATE) = CURRENT_DATE()
ORDER BY SCHEDULED_TIME DESC
)
)

THEN call system$send_email (
'TASK_FAILURE_ALERT',
'sachin.mittal04@gmail.com',
'Task Failure Notification',
'Issue with task in past 1 HOUR'
);

I have forcefully failed the task and ALERT will send the mail to my mail box.

Case2:

Case2 :In extension to the Case1 ,Can we call any Stored procedure with the ALERTS. The answer is YES. Based on the condition defined in ALERTS we can call the Custom procedure as well. As we know WH consumes the credits and which is acceptable because of our data ingestion or extraction queries. But business is curious to know if there is any CREDITS consume by Cloud service layer. And if any credits consumed then they want to track of consumption on daily basis along with time and credits.

Here again ALERTS played a major role and solved the problem in a  simpler way.

First create the Stored procedure:

This process fetches the credit consumed on daily basis.

Now Create the Alert and call the procedure:

CREATE OR REPLACE ALERT CLOUD_SERVICES
WAREHOUSE = COMPUTE_WH
SCHEDULE = '1 MINUTE'
IF ( EXISTS
(
WITH QRY AS (select
WAREHOUSE_NAME
,SUM(CREDITS_USED_CLOUD_SERVICES) as CREDITS_USED_CLOUD_SERVICES
,SUM(CREDITS_USED_CLOUD_SERVICES)/SUM(CREDITS_USED) PERCENT_CLOUD_SERVICES
from "SNOWFLAKE"."ACCOUNT_USAGE"."WAREHOUSE_METERING_HISTORY"
where TO_DATE(START_TIME) >= DATEADD(month,-1,CURRENT_TIMESTAMP()) GROUP BY 1
)
SELECT WAREHOUSE_NAME,CREDITS_USED_CLOUD_SERVICES FROM QRY WHERE
WAREHOUSE_NAME = 'CLOUD_SERVICES_ONLY' AND PERCENT_CLOUD_SERVICES > 0
)
)

THEN call test_sp();

Below mail from ALERT.

Average Rating

5 Star
100%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

One thought on “Snowflake ALERTS

Leave a Reply

Your email address will not be published. Required fields are marked *