Snowflake Notification : Now comes with more improvement…!

Published in

Snowflake Builders Blog: Data Engineers, App Developers, AI/ML, & Data Science

5 min readMay 11, 2024

In this blog, we are going to discuss recent further enhancement in Snowflake Notification area. Snowflake recently has released new SYSTEM$SEND_SNOWFLAKE_NOTIFICATION stored procedure for sending notifications. In this blog we are going to concentrate what we can achieve from this new procedure and what’s the key difference as compare to old SYSTEM$SEND_EMAIL procedure.

If you have not read my “How to notify users in Snowflake ?” blog on SYSTEM$SEND_EMAIL than do read this for better understanding & comparison.

What are the new enhancement ?

With this new Snowflake notification procedure, you can achieve below:

With a single call to this stored procedure, you can:

Send a message to multiple types of destinations (email addresses and queues).
Send a message to multiple email addresses and queues.
Send a message in a specified format, according to the type of notification integration (plain text or HTML for email, JSON for queues).

This will also reduce the overhead of writing a wrapper script to mimic the other functionality which is now being supported out-of-the-box as a feature with this new procedure.

How new procedure is different from old one ?

SYSTEM$SEND_EMAIL was introduced to cope up the basic need to send email from Snowflake in early days, but later on it was realized that the use-case for sending notification is multifold and need some enhancement.

With old procedure, we can only send email notification in plaint text or HTML text, but with new procedure we can also send as application JSON type.

With old procedure we used to put all email address in “To” section of mail by default there were no option to put proper mailing structure. But with this new procedure you can structure your mail properly, and you can decide which email address should go to “To section” or “Cc section” or “Bcc section”.

Now, with this new procedure, it becomes so easy to send the notification to any queue provided by a cloud service (Amazon SNS, Google Cloud PubSub, or Azure Event Grid). This can be setup using Notification Integration.

With this new procedure, there are lots of helper function introduced which makes your life sorted. To construct the JSON object for the message, you can use the following helper functions:

For an HTML message, call TEXT_HTML.
For a plain text message, call TEXT_HTML.
For a JSON message, call APPLICATION_JSON.

Now with this procedure, you can send same notification to multiple targets using multiple integrations. You can use multiple integrations to send messages when:

You want to send a message in email and to a topic in the same function call.
You want to send a message to different email addresses specified by different email notification integrations.

Is there any change in process flow to use this new procedure?

No, there is no change to process flow slight different in syntax which is quite obvious.

Create Notification Integration for your desired configuration like email notification or cloud provider queue.
Call SYSTEM$SEND_SNOWFLAKE_NOTIFICATION stored procedure using above notification integrations.
Validate if you have received email notification or your cloud provider queue got populated with new notification.

How to call SYSTEM$SEND_SNOWFLAKE_NOTIFICATION procedure?

The following is an example to call this stored procedure for warehouse resize activity:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
   -- Message type and content.
  '{ "text/html": "<p>Warehouse WH_COMPUTE has been resized to XXLARGE size.</p>" }',
  -- Integration used to send the notification and values used for the subject and recipients.
  -- These values override the defaults specified in the integration.
  '{
    "NI_EMAIL_01": {
      "subject": "Snowflake Alert - Warehouse Resized",
      "toAddress": ["rajiv@gmail.com", "gupta@gmail.com"],
      "ccAddress": ["snowflakedatasuperhero@gmail.com"],
      "bccAddress": ["Alert@gmail.com"]
    }
  }'
);

Here you notification integration is the key to decide whether you want to send email notification or to cloud provider message queue.

How about some demo code snippet?

Below is the code to send an HTML message.

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  SNOWFLAKE.NOTIFICATION.TEXT_HTML('<h1>The code element</h1> <p>The HTML <code>button</code> tag defines a clickable button.</p>
<p>The CSS <code>background-color</code> property defines the background color of an element.</p>'),
  SNOWFLAKE.NOTIFICATION.INTEGRATION('<Notification_Integration_Name>')
);

2. Below is the code to send an JSON message.

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  SNOWFLAKE.NOTIFICATION.APPLICATION_JSON('{
        "author": "Rajiv Gupta",
        "title": "Snowflake Developers Guide",
        "genre": "Computer",
        "price": 44.95,
        "publish_date": "2023-05-11",
        "description": "An in-depth look at sending notification with Snowflake."
      }'),
  SNOWFLAKE.NOTIFICATION.INTEGRATION('<Notification_Integration_Name>')
);

3. Below are multiple ways you can send a plain notification.

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  SNOWFLAKE.NOTIFICATION.TEXT_HTML('This is plan text example mail'),
  SNOWFLAKE.NOTIFICATION.INTEGRATION('<Notification_Integration_Name>')
);

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  '{ "text/plain": "This is another way to send plan text notification which also includes ccAddress." }',
  '{
    "<Notification_Integration_Name>": {
      "subject": "Plain text notification",
      "toAddress": ["rajiv@gmail.com"],
      "ccAddress": ["gupta@gmail.com"]      
    }
  }'
);

Hope this blog helps you to get insight into the SYSTEM$SEND_SNOWFLAKE_NOTIFICATION feature. If you are interested in learning more details about Snowflake notification, you can refer to Snowflake documentation. Feel free to ask a question in the comment section if you have any doubts regarding this. Give a clap if you like the blog. Stay connected to see many more such cool stuff. Thanks for your support & love.

You Can Find Me:

Subscribe to my YouTube Channel: https://www.youtube.com/c/RajivGuptaEverydayLearning

Follow me on Medium: https://rajivgupta780184.medium.com/

Follow me on X (formerly known as Twitter): https://twitter.com/RAJIVGUPTA780

Connect with me in LinkedIn: https://www.linkedin.com/in/rajiv-gupta-618b0228/