Snowflake Task Error Notification (Email)

Snowflake Tasks allow to orchestrate data pipelines in Snowflake. It is extremely cost effective while using STREAM_HAS_DATA as task trigger condition. In order to keep an eye on ETL processes, it is important to manage push notifications when data processing fails.

Architecture Diagram

There are 2 ways to manage Error notifications for Snowflake Tasks:

✅ Use Task Error Notification and queue-based Azure Function to send email.
✅ Handle error using SQL scripting within a Task.

Currently, task error notification is supported only in AWS. That is why, I will describe the second option in this article.

Handle error using SQL scripting

Exception block can be used in Snowflake SQL scripting in order to catch errors in data processing. Then system$send_email stored procedure can be used to send emails.

CREATE TASK IF NOT EXISTS TASK_NAME
	WAREHOUSE = WAREHOUSE_NAME
	SCHEDULE= '10 MINUTE'
	WHEN SYSTEM$STREAM_HAS_DATA('STREAM_NAME')
    AS
    EXECUTE IMMEDIATE
    $$
    BEGIN
        MERGE INTO TARGET_TABLE_NAME d
        USING (
        SELECT s.*, sysdate() as insert_datetime, to_timestamp('1900-01-01') as update_datetime 
        FROM SOURCE_TABLE_VIEW s) s
        ON d.hash_pk=s.hash_pk
        WHEN MATCHED THEN 
            UPDATE SET d.value=s.value, d.update_datetime = sysdate()
        WHEN NOT MATCHED THEN 
            INSERT (d.hash_pk, d.value, d.insert_datetime, d.update_datetime)
            VALUES (s.hash_pk, s.value, s.insert_datetime, s.update_datetime);
    EXCEPTION
      WHEN OTHER THEN
        LET LINE := sqlcode || ': ' || sqlerrm;
        call system$send_email(
            'AZURE_NOTIFICATION_EMAIL',
            'XXX@gmail.com, YYY@gmail.com',
            'Email Alert: Task TASK_NAME Failed',
            :line
            );
        RAISE; 
    END;
    $$;

In order to use system$send_email stored procedure, you have to create email integration first.

CREATE NOTIFICATION INTEGRATION IF NOT EXISTS AZURE_NOTIFICATION_EMAIL
    TYPE=EMAIL
    ENABLED=TRUE
    ALLOWED_RECIPIENTS=('XXX@gmail.com', 'YYY@gmail.com');