Customizing Snowpipe Error Notifications in Azure

 Hey Folks, Thanks for visiting my blog. In this blog, I am going to talk about how you can configure automated notifications in Snowpipe and generate emails out of it in Azure.

Snowpipe can push error notifications to a cloud messaging service when it encounters errors while loading data. The notifications describe any errors encountered in each file, enabling further analysis of the data in the files.

Azure Services Used:

1) Azure Event Grid - To store failure message (aka JSON payload)

2) Azure Logic Apps - To send failure notification email

Architecture Diagram:

Steps to setup Error Notification for Snowpipe:

Step 1: Creating a Logic Apps:

Logic Apps will read JSON message from Event Grid custom topic and parse the JSON message to send email.

You can either create Logic Apps in Consumption or Standard model.

In below example, standard model has been used.

Download JSON file below to create a Logic Apps Workflow whose trigger is an HTTP End Point, which gets connected to Event Grid as Webhook.

snowpipe-error-notifications.json

Step 2: Creating a Custom Event Grid Topic

An Event Grid topic provides an endpoint where the source sends event notifications. Create a dedicated topic to receive error notifications published by Snowflake. You can use a single topic to receive error notifications for all pipes in your Snowflake account.

a) Create a Custom Event Grid Topic.

b) Under Subscription, select webhook and give url for HTTP End Point. Note: If connection is successful between Event Grid and Logic Apps, an email message will be sent.

Step 3: Creating a Notification Integration in Snowflake

Execute below SQL's to create notification integration in Snowflake :

use role accountadmin;

create notification integration <error_notification_name>

enabled = true

type = queue

notification_provider = azure_event_grid

direction = outbound

azure_event_grid_topic_endpoint = '<event_grid_url>'

azure_tenant_id = '<tenant_id>';

desc notification integration snowpipe_error_notification;

In the above step, create a service principal using consent url retrieved using above command and give SP eventgrid-data-sender Role.

show integrations;

grant usage on integration <error_notification_name> to role <Role Name>;

Step 4: Testing the integration

Test the integration by snowpipe using below command :

create or replace pipe <pipe_name>

AUTO_INGEST=TRUE

INTEGRATION=AZURE_STORAGE_ACCOUNT_NOTIFICATION_INT

error_integration = SNOWPIPE_ERROR_NOTIFICATION

as

copy into "<DB_Name>"."<Schema_Name>"."<Table_Name>"

from @<stage_name>/<folder_path>

ON_ERROR=CONTINUE

file_format = <File_Format_Name>;