Sending Email Notifications

Snowflake logo in black (no text) Preview Feature — Open

Available to all accounts.

Note

The Notification System Stored Procedure feature is only hosted on Amazon Web Services (AWS). If you choose to use this feature, the hosting provider for this feature will be AWS and the available location(s) of the hosting will be as set forth in the table below. The content of a message sent using AWS SES may be retained by Snowflake for up to thirty days to manage the delivery of the message and is thereafter deleted.

Supported AWS Regions

us-west-2

us-east-1

eu-west-1

This topic explains how to use the built-in SYSTEM$SEND_EMAIL() stored procedure to send email notifications.

Introduction

This feature uses the 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.). A single account can define a maximum of ten email integrations and enable one or more simultaneously.

To send an email notification:

  1. Make sure that the intended recipients of email notifications have verified their email addresses.

  2. Create a notification integration.

  3. Grant the privilege to use the notification integration.

  4. Call the SYSTEM$SEND_EMAIL() stored procedure to send an email notification.

Note

You can use the NOTIFICATION_HISTORY table function to query the history of notifications sent through Snowpipe. For more information, refer to NOTIFICATION_HISTORY.

Verifying the Email Addresses of the Notification Recipients

Email notifications can only be sent to Snowflake users within the same account, and those users must verify their email addresses through one of the following interfaces:

Creating a Notification Integration

To create an email notification integration, use the CREATE NOTIFICATION INTEGRATION command with TYPE=EMAIL. Set ALLOWED_RECIPIENTS to the list of email addresses that should receive notifications.

Note

For each email address in ALLOWED_RECIPIENTS, make sure that the Snowflake user with that email address has verified their email address. If you specify an email address that hasn’t been verified, the CREATE NOTIFICATION INTEGRATION command fails with an error.

For example, to create an email notification integration named my_email_int for sending email messages to first.last@example.com and first2.last2@example.com, execute the following statement:

CREATE NOTIFICATION INTEGRATION my_email_int
    TYPE=EMAIL
    ENABLED=TRUE
    ALLOWED_RECIPIENTS=('first.last@example.com','first2.last2@example.com');

Note

You can define a maximum of ten email notification integrations for a given account.

For details about the syntax of this command, see CREATE NOTIFICATION INTEGRATION.

Granting the Privilege to Use the Notification Integration

When calling SYSTEM$SEND_EMAIL(), you pass in an email notification integration that is used to send the email. You must use a role that is granted the USAGE privilege on this email notification integration.

To grant this privilege to the role for the owner or caller, execute the GRANT <privileges> command.

For example, to grant the USAGE privilege on the notification integration my_email_int to the role my_sp_owner_role, execute the following command:

GRANT USAGE ON INTEGRATION my_email_int TO ROLE my_sp_owner_role;

Sending an Email Notification

After creating the email notification integration, you can call the SYSTEM$SEND_EMAIL stored procedure to send an email notification.

For example, to use the notification integration my_email_int to send an email message with the subject line “Email Alert: Task A has finished.” to person1@example.com and person2@example.com, execute the following statement:

CALL SYSTEM$SEND_EMAIL(
    'my_email_int',
    'person1@example.com, person2@example.com',
    'Email Alert: Task A has finished.',
    'Task A has successfully finished.\nStart Time: 10:10:32\nEnd Time: 12:15:45\nTotal Records Processed: 115678'
);

Note

If any email address in the recipient list is not included in the ALLOWED_RECIPIENTS property for the notification integration, no emails are sent.

The email notification message is sent from no-reply@snowflake.net.

Language: English