1. Introduction
To effectively manage data projects, it’s crucial to receive timely notifications about events that could lead to performance degradation, increased costs, or security breaches. Snowflake offers built-in capabilities for sending email alerts using SYSTEM$SEND_EMAIL and SYSTEM$SEND_SNOWFLAKE_NOTIFICATION stored procedures, providing a seamless, integrated solution for monitoring and responding to potential issues.
In this article, let us explore how to send email notifications in Snowflake and its prerequisites.
2. Prerequisites for Sending Email Notifications in Snowflake
The following are the prerequisites for sending email notifications in Snowflake.
- Verify the Email Addresses of the Recipients
- Create a Notification Integration
2.1. Verify the Email Addresses of the Recipients
Email notifications can only be sent to verified Snowflake users within the same account. Users who are not part of the Snowflake account or do not have an email address configured and verified cannot receive email notifications from Snowflake.
2.2. Create a Notification Integration
The Notification Integration is a Snowflake object that acts as an interface between Snowflake and third-party services for sending notifications. When using Amazon Web Services (AWS), these notifications are routed through AWS Simple Email Service (SES), ensuring reliable and scalable email delivery directly from your Snowflake environment.
Syntax:
CREATE NOTIFICATION INTEGRATION <name>
TYPE = EMAIL
ENABLED = { TRUE | FALSE }
[ ALLOWED_RECIPIENTS = ( '<email_address>' [ , ... '<email_address>' ] ) ]The following SQL statement creates an Email Notification Integration my_email_int in Snowflake, restricting the list of email addresses that can receive notifications.
CREATE OR REPLACE NOTIFICATION INTEGRATION my_email_int
TYPE = EMAIL
ENABLED = TRUE
ALLOWED_RECIPIENTS = ('admin@thinketl.com','user@thinketl.com');If the ALLOWED_RECIPIENTS parameter is not set, email notifications can be sent to any verified email address in the current account.
3. Sending Email Notifications using SYSTEM$SEND_EMAIL
The SYSTEM$SEND_EMAIL stored procedure in Snowflake can be invoked to send email notifications to specified recipients.
Syntax:
SYSTEM$SEND_EMAIL(
'<integration_name>',
'<email_address_1> [ , ... <email_address_N> ]',
'<email_subject>',
'<email_content>',
[ '<mime_type>' ] )| Argument | Description |
|---|---|
| <integration_name> | Name of the Notification Integration used to send emails. |
| <email_address_1> | List of email addresses that should receive notifications. |
| <email_subject> | Subject of the email notification. |
| <email_content> | Content of the email notification. |
| <mime_type> | The MIME type of the email’s content. Optional. Supported values are: text/plain – Specify this when email_content is plain text.text/html – Specify this when email_content is HTML. |
3.1. Sending Email in Plain Text Format
The following example sends email notifications to two recipients in plain text format using SYSTEM$SEND_EMAIL stored procedure.
CALL SYSTEM$SEND_EMAIL(
'my_email_int', -- Notification Integration
'admin@thinketl.com, user@thinketl.com', -- Recipients List
'Snowflake Email Alert', -- Email Subject
'This is a test Message.\nFrom Snowflake' -- Email Content
);The stored procedure returns TRUE if executed successfully as shown below.
The following image shows the email received from Snowflake in plain text format.
3.2. Sending Email in HTML Format
The following example sends email notification to two recipients in HTML format using SYSTEM$SEND_EMAIL stored procedure.
CALL SYSTEM$SEND_EMAIL(
'my_email_int', -- Notification Integration
'admin@thinketl.com, user@thinketl.com', -- Recipients List
'Snowflake Email Alert', -- Email Subject
'<p>This is a test Message.<br><i>From Snowflake</i></p>', -- Email Content
'text/html' -- MIME_type
);The stored procedure returns TRUE if executed successfully as shown below.
The following image shows the email received from Snowflake in HTML format.
4. Sending Email Notifications using SYSTEM$SEND_SNOWFLAKE_NOTIFICATION
The SYSTEM$SEND_SNOWFLAKE_NOTIFICATION stored procedure in Snowflake can be invoked to send email notifications to specified recipients. Additionally, the stored procedure can send a notification message to a webhook, or queue provided by a Cloud service (Amazon SNS, Google Cloud PubSub, or Azure Event Grid).
The arguments can be passed to the SYSTEM$SEND_SNOWFLAKE_NOTIFICATION stored procedure to send notifications to email addresses in two different ways.
- Building JSON-Formatted Strings as Arguments
- Calling Helper Functions to Construct JSON-Formatted Strings
4.1. Building JSON-Formatted Strings as Arguments
JSON-formatted strings can be passed as arguments to specify the message and the notification integration when sending a notification to email addresses by invoking the SYSTEM$SEND_SNOWFLAKE_NOTIFICATION stored procedure.
Syntax:
CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
'{ "<content_type>": "<email_content>" }',
'{
"<integration_name>": {
"subject": "<email_subject>",
"toAddress": ["<email_address_1>",...,"<email_address_N>"],
"ccAddress": ["<email_address_1>",...,"<email_address_N>"],
"bccAddress": ["<email_address_1>",...,"<email_address_N>"]
}
}'
);| Argument | Description |
|---|---|
| <integration_name> | Name of the Notification Integration used to send emails. |
| <email_address> | List of email addresses that should receive notifications. |
| <email_subject> | Subject of the email notification. |
| <email_content> | Content of the email notification. |
| <content_type> | The MIME type of the email’s content. Supported values are: text/plain – Specify this when email_content is plain text.text/html – Specify this when email_content is HTML. |
The following example sends email notification to two recipients in HTML format using SYSTEM$SEND_SNOWFLAKE_NOTIFICATION stored procedure.
CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
'{ "text/html": "<p>This is a test Message.<br><i>From Snowflake</i></p>" }',
'{
"my_email_int": {
"subject": "Snowflake Email Alert",
"toAddress": ["user@thinketl.com"],
"ccAddress": ["admin@thinketl.com"]
}
}'
);The stored procedure returns Enqueued notifications if executed successfully as shown below.
4.2. Calling Helper Functions to Construct JSON-Formatted Strings
JSON-formatted strings can also be constructed by calling the helper functions like TEXT_HTML, TEXT_PLAIN to specify the message and EMAIL_INTEGRATION_CONFIG to specify the email notification integration, subject line, and email addresses.
Syntax:
CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
SNOWFLAKE.NOTIFICATION.<content_type>('<email_content>'),
SNOWFLAKE.NOTIFICATION.EMAIL_INTEGRATION_CONFIG(
'<integration_name>',
'<email_subject>',
ARRAY_CONSTRUCT('<email_address_1>',...,'<email_address_N>'), -- toAddress
ARRAY_CONSTRUCT('<email_address_1>',...,'<email_address_N>'), -- ccAddress
ARRAY_CONSTRUCT('<email_address_1>',...,'<email_address_N>') -- bccAddress
)
);| Argument | Description |
|---|---|
| <integration_name> | Name of the Notification Integration used to send emails. |
| <email_address> | List of email addresses that should receive notifications. |
| <email_subject> | Subject of the email notification. |
| <email_content> | Content of the email notification. |
| <content_type> | The helper function to specify the message. TEXT_PLAIN – Specify this when email_content is plain text.TEXT_HTML – Specify this when email_content is HTML. |
The following example sends email notification to two recipients in plain text format using SYSTEM$SEND_SNOWFLAKE_NOTIFICATION stored procedure.
CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
SNOWFLAKE.NOTIFICATION.TEXT_PLAIN('This is a test Message.\nFrom Snowflake'),
SNOWFLAKE.NOTIFICATION.EMAIL_INTEGRATION_CONFIG(
'my_email_int',
'Snowflake Email Alert',
ARRAY_CONSTRUCT('admin@thinketl.com', 'user@thinketl.com')
)
);
5. Sending Email Notifications using Multiple Integrations
SYSTEM$SEND_SNOWFLAKE_NOTIFICATION supports sending email notifications to multiple groups using multiple notification integrations in a single call.
Call the ARRAY_CONSTRUCT function to construct an array of integration configurations as shown below.
CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
'{ "text/html": "<p>This is a test Message.</p>" }',
ARRAY_CONSTRUCT(
'{
"my_email_int": {
"subject": "Snowflake Email Alert",
"toAddress": ["admin@thinletl.com"]
}
}',
'{
"my_email_int_2": {
"subject": "Snowflake Email Alert 2",
"toAddress": ["user@thinketl.com"]
}
}'
)
);CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
SNOWFLAKE.NOTIFICATION.TEXT_PLAIN('This is a test Message.'),
ARRAY_CONSTRUCT(
SNOWFLAKE.NOTIFICATION.EMAIL_INTEGRATION_CONFIG(
'my_email_int',
'Snowflake Email Alert',
ARRAY_CONSTRUCT('venkateshkolahalam@gmail.com')
),
SNOWFLAKE.NOTIFICATION.EMAIL_INTEGRATION_CONFIG(
'my_email_int_2',
'Snowflake Email Alert 2',
ARRAY_CONSTRUCT('venkateshkolahalam@gmail.com')
)
)
);6. Closing Thoughts
In summary, Snowflake offers native capabilities for sending email notifications directly within its environment using the built-in stored procedures SYSTEM$SEND_EMAIL and SYSTEM$SEND_SNOWFLAKE_NOTIFICATION.
- Emails can only be sent to users within the account who have verified email addresses.
- A Notification Integration must be configured, serving as a bridge between Snowflake and third-party services to facilitate notifications.
- The SYSTEM$SEND_EMAIL procedure is simple and efficient, ideal for creating basic notification alerts with straightforward email requirements.
- On the other hand, SYSTEM$SEND_SNOWFLAKE_NOTIFICATION provides enhanced functionality, allowing you to include CC and BCC recipients, and send emails to multiple groups using multiple notification integrations in a single call.
Subscribe to our Newsletter !!
Related Articles:
- Overview of Snowflake Time Travel
- Snowflake Zero Copy Cloning
- Snowflake Information Schema
- Snowflake Resource Monitors
- Snowflake Virtual Warehouses
- Snowflake SnowSQL: Command Line Tool to access Snowflake
- HOW TO: Remove Duplicates in Snowflake?
- HOW TO: Get DDL of database objects in Snowflake?
- HOW TO: Find and Kill long running queries in Snowflake?
- Snowflake Tasks: Execute SQL Statements on Schedule
- Change Data Capture using Snowflake Streams
- Change Data Capture using Snowflake Dynamic Tables
- GROUP BY ALL Clause in Snowflake
- QUALIFY Clause in Snowflake: Filter Window Functions
- GREATEST and LEAST Functions in Snowflake
- DATEDIFF Function in Snowflake
- Common Table Expressions (CTEs) in Snowflake
