Enabling Snowpipe error notifications for Microsoft Azure Event Grid

This topic provides instructions for pushing Snowpipe error notifications to the Microsoft Azure Event Grid (Event Grid).

This feature can push error notifications for the following types of loads:

  • Auto-ingest Snowpipe.

  • Calls to the Snowpipe insertFiles REST API endpoint.

  • Loads from Apache Kafka using the Snowflake Connector for Kafka with the Snowpipe ingestion method only.

Cloud platform support

Currently, this feature is limited to Snowflake accounts hosted on Microsoft Azure. Snowpipe can load data from files in any supported cloud storage service; however, push notifications to Event Grid are only supported in Snowflake accounts hosted on Azure

Notes

  • Snowflake guarantees at-least-once message delivery of error notifications (i.e. multiple attempts are made to deliver messages to ensure at least one attempt succeeds, which can result in duplicate messages).

  • This feature is implemented using the notification integration object. A notification integration is a Snowflake object that provides an interface between Snowflake and third-party cloud message queuing services. A single notification integration can support multiple pipes.

Enabling error notifications

Step 1: 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 (for Snowpipe error notifications) or tasks (for task error notifications) in your Snowflake account.

For instructions on creating Event Grid topics, see the Event Grid documentation. Record the Event Grid topic endpoint, which you will need later in these instructions.

Optionally subscribe to the topic to inform Event Grid which events you want to track and where to send those events.

Step 2: Creating a Notification Integration in Snowflake

Retrieve the Tenant ID

Retrieve your Azure tenant ID, which you will need later in these instructions.

  1. Log into the Microsoft Azure portal.

  2. Navigate to Azure Active Directory » Properties. Record the Tenant ID value for reference later. The directory ID, or tenant ID, is needed to generate the consent URL that grants Snowflake access to the Event Grid topic.

Create the Notification Integration

Create an integration using the CREATE NOTIFICATION INTEGRATION command. An integration is a Snowflake object that references the Azure storage queue you created.

Note

Only account administrators (users with the ACCOUNTADMIN role) or a role with the global CREATE INTEGRATION privilege can execute this SQL command.

CREATE NOTIFICATION INTEGRATION <integration_name>
  ENABLED = true
  TYPE = QUEUE
  NOTIFICATION_PROVIDER = AZURE_EVENT_GRID
  DIRECTION = OUTBOUND
  AZURE_EVENT_GRID_TOPIC_ENDPOINT = '<event_grid_topic_endpoint>'
  AZURE_TENANT_ID = '<azure_tenant_id>'
Copy

For example:

CREATE NOTIFICATION INTEGRATION myint
  ENABLED = true
  TYPE = QUEUE
  NOTIFICATION_PROVIDER = AZURE_EVENT_GRID
  DIRECTION = OUTBOUND
  AZURE_EVENT_GRID_TOPIC_ENDPOINT = 'https://myaccount.region-1.eventgrid.azure.net/api/events'
  AZURE_TENANT_ID = 'mytenantid';
Copy

Where:

  • event_grid_topic_endpoint is the Event Grid topic endpoint you recorded in section Step 1.

  • azure_tenant_id is your Azure directory ID, or tenant ID, which you recorded earlier in this section.

Grant Snowflake Access to the Topic

  1. Execute the DESCRIBE INTEGRATION command to retrieve the consent URL:

    DESC NOTIFICATION INTEGRATION <integration_name>;
    
    Copy

    Where:

    Note the values in the following columns:

    AZURE_CONSENT_URL:

    URL to the Microsoft permissions request page.

    AZURE_MULTI_TENANT_APP_NAME:

    Name of the Snowflake client application created for your account. In a later step in this section, you will need to grant this application the permissions necessary to obtain an access token on your allowed topic.

  2. In a web browser, navigate to the URL in the AZURE_CONSENT_URL column. The page displays a Microsoft permissions request page.

  3. Click the Accept button. This action allows the Azure service principal created for your Snowflake account to be granted an access token on specified resources inside your tenant. Obtaining an access token succeeds only if you grant the service principal the appropriate permissions on the container (see the next step).

    The Microsoft permissions request page redirects to the Snowflake corporate site (snowflake.com).

  4. Log into the Microsoft Azure portal.

  5. Navigate to Azure Active Directory » Enterprise applications. Verify the Snowflake application identifier you recorded in Step 2 in this section is listed.

    Important

    If you delete the Snowflake application in Azure Active Directory at a later time, the notification integration stops working.

  6. Navigate to Event Grid Topics » topic_name, where topic_name is the name of the topic you created to receive event notifications.

  7. Click Access Control (IAM) » Add role assignment.

  8. Search for the Snowflake service principal. This is the identity in the AZURE_MULTI_TENANT_APP_NAME property in the DESC NOTIFICATION INTEGRATION output (in Step 1). Search for the string before the underscore in the AZURE_MULTI_TENANT_APP_NAME property.

    Important

    • It can take an hour or longer for Azure to create the Snowflake service principal requested through the Microsoft request page in this section. If the service principal is not available immediately, we recommend waiting an hour or two and then searching again.

    • If you delete the service principal, the notification integration stops working.

  9. Grant the Snowflake application the EventGrid Data Sender permission.

Step 3: Enabling error notifications in pipes

A single notification integration can be shared by multiple pipes. The body of error messages identifies the pipe, external stage and path, and file where the error originated, among other details.

To enable error notifications for a pipe, specify an ERROR_INTEGRATION parameter value.

Note

Creating or modifying a pipe that references a notification integration requires a role that has the USAGE privilege on the notification integration. In addition, the role must have either the CREATE PIPE privilege on the schema or the OWNERSHIP privilege on the pipe, respectively.

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

New pipe

Create a new pipe using CREATE PIPE:

CREATE PIPE <name>
  AUTO_INGEST = TRUE
  [ INTEGRATION = '<string>' ]
  ERROR_INTEGRATION = <integration_name>
  AS <copy_statement>
Copy

Where:

ERROR_INTEGRATION = <integration_name>

Name of the notification integration you created in Step 4: Creating the Notification Integration (in this topic).

For example:

CREATE PIPE mypipe
  AUTO_INGEST = TRUE
  INTEGRATION = 'my_storage_int'
  ERROR_INTEGRATION = my_notification_int
  AS
  COPY INTO mydb.public.mytable
  FROM @mydb.public.mystage;
Copy

Existing pipe

Modify an existing pipe using ALTER PIPE:

ALTER PIPE <name> SET ERROR_INTEGRATION = <integration_name>;
Copy

Where <integration_name> is the name of the notification integration you created in Step 4: Creating the Notification Integration (in this topic).

For example:

ALTER PIPE mypipe SET ERROR_INTEGRATION = my_notification_int;
Copy

Error notification message payload

The body of error messages identifies the pipe and the errors encountered during a load.

The following is a sample message payload describing a Snowpipe error. The payload can include one or more error messages.

{\"version\":\"1.0\",\"messageId\":\"a62e34bc-6141-4e95-92d8-f04fe43b43f5\",\"messageType\":\"INGEST_FAILED_FILE\",\"timestamp\":\"2021-10-22T19:15:29.471Z\",\"accountName\":\"MYACCOUNT\",\"pipeName\":\"MYDB.MYSCHEMA.MYPIPE\",\"tableName\":\"MYDB.MYSCHEMA.MYTABLE\",\"stageLocation\":\"azure://myaccount.blob.core.windows.net/mycontainer/mypath\",\"messages\":[{\"fileName\":\"/file1.csv_0_0_0.csv.gz\",\"firstError\":\"Numeric value 'abc' is not recognized\"}]}
Copy

Note that you must parse the string into a JSON object to process values in the payload.