Sending Email Notifications¶
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.
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:
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.
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;
To restrict the notification integration so that email messages can be sent only to
email@example.com, set ALLOWED_RECIPIENTS to the list of those addresses:
CREATE NOTIFICATION INTEGRATION my_email_int TYPE=EMAIL ENABLED=TRUE ALLOWED_RECIPIENTS=('firstname.lastname@example.org','email@example.com');
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
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
firstname.lastname@example.org, execute the following statement:
CALL SYSTEM$SEND_EMAIL( 'my_email_int', 'email@example.com, firstname.lastname@example.org', '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' );
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