Sending Email Notifications

Note

All Snowflake customers can send email messages using this feature. Email messages sent from the Notifications System Stored Procedure are processed through Snowflake’s Amazon Web Services (AWS) deployments, using AWS Simple Email Service (SES). The content of an email message sent using AWS may be retained by Snowflake for up to thirty days to manage the delivery of the message. After this period, the message content is deleted.

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.

If you want to restrict the list of email addresses that can receive notifications through this integration, set ALLOWED_RECIPIENTS to the list of those email addresses. If you do not set ALLOWED_RECIPIENTS, the integration can be used to send notifications to any user in the account, provided that the user has verified their email address.

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, execute the following statement:

CREATE NOTIFICATION INTEGRATION my_email_int
  TYPE=EMAIL
  ENABLED=TRUE;
Copy

To restrict the notification integration so that email messages can be sent only to first.last@example.com and first2.last2@example.com, set ALLOWED_RECIPIENTS to the list of those addresses:

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

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;
Copy

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 first.last@example.com and first2.last2@example.com, execute the following statement:

CALL SYSTEM$SEND_EMAIL(
    'my_email_int',
    'first.last@example.com, first2.last2@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'
);
Copy

Note

If you set the ALLOWED_RECIPIENTS property of the notification integration and any email address in the recipient list is not that list, no email notifications are sent.

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

Language: English