[2024] Send Email OR Send Snowflake Notification? Let’s Decode

If you are using Snowflake for more than 2 years, you must be aware of snowflake native capabilities to setup Alerts and Notifications using SYSTEM$SEND_EMAIL(). However there’s another system stored procedure which can be used to trigger notifications too!

SYSTEM$SEND_SNOWFLAKE_NOTIFICATION() allows you to send a notification message to an email address or a queue provided by a Cloud service.

Let’s decode the difference between these two system stored procedures in details.

Executive Summary

• What SEND_EMAIL() does?
• What SEND_SNOWFLAKE_NOTIFICATION() does?
• Syntax Differences
• Helper Functions for SEND_SNOWFLAKE_NOTIFICATION()
• Deciding What to Use?

What SEND_EMAIL() Does?

The SYSTEM$SEND_EMAIL() stored procedure to send email notifications.

It uses the notification integration object, which is a Snowflake object that provides an interface between Snowflake and third-party services.

The notifications are processed through Snowflake’s Amazon Web Services (AWS) deployments, using AWS Simple Email Service (SES).

If you are on the Amazon Web Services (AWS) cloud platform, then the email notification message is sent from no-reply@snowflake.net. If you are on the Google Cloud Platform (GCP) or Microsoft Azure (Azure) cloud platform, the email notification message is sent from do-not-reply@snowflake.net.

What SEND_SNOWFLAKE_NOTIFICATION() Does?

The SEND_SNOWFLAKE_NOTIFICATIONS() stored Procedure allows:

• You can send messages to multiple destinations such as emails or queues
• You can send messages to multiple email addresses or queues
• You can send messages with different formats according to notification integration
• You can use multiple notification integrations in single call to send out notifications
• You can specify configs in JSON format

What’s the benefit?

This means, with a single call, you can send messages in plain text, HTML, and JSON formats to multiple email addresses and multiple SNS, PubSub, and Event Grid topics.

You can use multiple notification integrations to send the notification to different queues.

You can also create multiple email notification integrations that have different sets of email addresses and subject lines, making it easier to configure email messages for different recipients.

Syntax Differences

SYSTEM$SEND_EMAIL()

The SYSTEM$SEND_EMAIL() syntax takes up the following parameters:

• Integration Name
• Comma Separated Email addresses
• Email Subject
• Email Content
• [Optional] MIME Type

-> If the ALLOWED_RECIPIENTS property of the notification integration is set and any of the email addresses is not in that list, no email notifications are sent.

> For MIME, the following types are supported:

• text/plain — Specify this when email_content is plain text. This is the default value.
• text/html — Specify this when email_content is HTML.

CALL SYSTEM$SEND_EMAIL(
    '<integration_name>',
    '<email_address_1> [ , ... <email_address_N> ]',
    '<email_subject>',
    '<email_content>',
    [ '<mime_type>' ]
);

SYSTEM$SEND_SNOWFLAKE_NOTIFICATION()

The SYSTEM$SEND_SNOWFLAKE_NOTIFICATION() syntax takes up the following parameters:

• Message as JSON formatted string specifying type and content
• Integration as JSON-formatted string that specifies the notification integration or the email configuration to use to send the notification.

> Messages type can be:
“text/plain” for plain text messages.
“text/html” for HTML messages.
“application/json” for JSON messages.

> Integration Options is a comma-delimited list of properties (in JSON format) that specify values that override the defaults in the integration. These can be:

'{ "my_email_int": { 
                   "subject" : "Different subject" 
                    }, 
                    { "toAddress": ["person_a@example.com"] 
                    },
                    { "ccAddress" : ["person_to_cc1@example.com", "person_to_cc2@example.com"] 
                    },
                    { "bccAddress" : ["person_to_bcc1@example.com", "person_to_bcc2@example.com"]
                    }'

You can also:

• Define Messages as ARRAY of JSON-formatted strings, each of which specify a message type and content. Specify this argument if you want to send a message in multiple formats.
• Define Integrations as ARRAY of JSON-formatted strings, each of which specifies a notification integration and configuration to use. Specify this argument if you want to use multiple notification integrations or email configurations to send a message.

SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  <message>,
  <integration_configuration> );

SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  ( <message>, [ <message>, ... ] ),
  <integration_configuration> );

SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  <message>,
  ( <integration_configuration> [ , <integration_configuration> , ... ] ) );

SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  ( <message> [ , <message> , ... ] ),
  ( <integration_configuration> [ , <integration_configuration> , ... ] ) );

Helper Functions for SEND_SNOWFLAKE_NOTIFICATION()

SYSTEM$SEND_SNOWFLAKE_NOTIFICATION() comes with various helper functions for building the JSON formatted string. These are:

EMAIL_INTEGRATION_CONFIG

• If you are sending an email notification and want to override the default values specified in the email notification integration, call the EMAIL_INTEGRATION_CONFIG function.
• Returns a JSON object that specifies the email notification integration, recipients, and subject line to use for an email notification.

SNOWFLAKE.NOTIFICATION.EMAIL_INTEGRATION_CONFIG(
 '<email_integration_name>',
 '<subject>',
 <array_of_email_addresses_for_to_line>,
 <array_of_email_addresses_for_cc_line>,
 <array_of_email_addresses_for_bcc_line>
);

INTEGRATION

• If you are sending the notification to a queue, or if you are sending an email notification and want to use the default values specified in the email notification integration, call the INTEGRATION function.
• Returns a JSON object that specifies the notification integration to use to send a message.

SNOWFLAKE.NOTIFICATION.INTEGRATION( '<integration_name>' );

APPLICATION_JSON

• To send a JSON message to a queue, call the APPLICATION_JSON function.
• Returns a JSON object that specifies the JSON message to use for a notification.

SELECT SNOWFLAKE.NOTIFICATION.APPLICATION_JSON('{"data": "hello world"}');

TEXT_HTML

• To send an HTML email message, call the TEXT_HTML function.
• Returns a JSON object that specifies the HTML message to use for a notification.

SNOWFLAKE.NOTIFICATION.TEXT_HTML( '<message>' );

TEXT_PLAIN

• To send a plain text email message, call the TEXT_PLAIN function.
• Returns a JSON object that specifies the plain text message to use for a notification.

SNOWFLAKE.NOTIFICATION.TEXT_PLAIN( '<message>' );

Example:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  ARRAY_CONSTRUCT(
    SNOWFLAKE.NOTIFICATION.TEXT_PLAIN('A message'),
    SNOWFLAKE.NOTIFICATION.TEXT_HTML('<p>A message</p>'),
    SNOWFLAKE.NOTIFICATION.APPLICATION_JSON('{ "name": "value" }')
  ),
  ARRAY_CONSTRUCT(
    SNOWFLAKE.NOTIFICATION.INTEGRATION('my_sns_int'),
    SNOWFLAKE.NOTIFICATION.INTEGRATION('my_email_int')
  )
);

Deciding What to Use?

While SEND_EMAIL() sproc is very easy to use, SEND_SNOWFLAKE_NOTIFICATION() provides vast variety of options to send notifications as email or sns.

If you are building a basic notification alert to generate a query response’s email report, you can go with the SEND_EMAIL(). On the other side if you are building a notification alert where you will be sending different notifications to various user groups or LOBs, the SEND_SNOWFLAKE_NOTIFICATION() provides great flexibility.

About Me:

Hi there! I am Divyansh Saxena

I am an experienced Cloud Data Engineer with a proven track record of success in Snowflake Data Cloud technology. Highly skilled in designing, implementing, and maintaining data pipelines, ETL workflows, and data warehousing solutions. Possessing advanced knowledge of Snowflake’s features and functionality, I am a Snowflake Data Superhero & Snowflake Snowpro Core SME. With a major career in Snowflake Data Cloud, I have a deep understanding of cloud-native data architecture and can leverage it to deliver high-performing, scalable, and secure data solutions.

Follow me on Medium for regular updates on Snowflake Best Practices and other trending topics:

Divyansh Saxena - Medium

Also, I am open to connecting all data enthusiasts across the globe on LinkedIn:

https://www.linkedin.com/in/divyanshsaxena/