Using SYSTEM$SEND_SNOWFLAKE_NOTIFICATION to send 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.

If you need to send notifications to an email address, webhook, or a queue provided by a cloud service (Amazon SNS, Google Cloud PubSub, or Azure Event Grid), use the SYSTEM$SEND_SNOWFLAKE_NOTIFICATION stored procedure.

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

  • Send a message to multiple types of destinations (email addresses, webhooks, and queues).

  • Send a message to multiple email addresses, webhooks, 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).

For example, 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.

Send a notification

Before you send a notification, you must have a notification integration that you will use to send the notification. If you are sending an email notification, you must also validate the email addresses of the recipients. For details, see Notifications in Snowflake.

To send a notification to email addresses or queues, call the SYSTEM$SEND_SNOWFLAKE_NOTIFICATION stored procedure, specifying the messages and the notification integrations to use.

The following is an example of a call to this stored procedure:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
   -- Message type and content.
  '{ "text/html": "<p>This is a message.</p>" }',
  -- Integration used to send the notification and values used for the subject and recipients.
  -- These values override the defaults specified in the integration.
  '{
    "my_email_int": {
      "subject": "Status update",
      "toAddress": ["person_a@example.com", "person_b@example.com"],
      "ccAddress": ["person_c@example.com"],
      "bccAddress": ["person_d@example.com"]
    }
  }'
);
Copy

As shown in the example above, you pass in JSON-formatted strings as arguments to specify the message to send and the notification integration to use.

For the syntax for these strings, see SYSTEM$SEND_SNOWFLAKE_NOTIFICATION.

To construct these JSON-formatted strings, you can call helper functions like TEXT_HTML to specify the message and EMAIL_INTEGRATION_CONFIG to specify the notification integration, subject line, and email addresses. For example:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  SNOWFLAKE.NOTIFICATION.TEXT_HTML('<p>a message</p>'),
  SNOWFLAKE.NOTIFICATION.EMAIL_INTEGRATION_CONFIG(
    'my_email_int',
    'Status update',
    ARRAY_CONSTRUCT('person_a@example.com', 'person_b@example.com'),
    ARRAY_CONSTRUCT('person_c@example.com'),
    ARRAY_CONSTRUCT('person_d@example.com')
  )
);
Copy

For the list of helper functions that you can use, see SYSTEM$SEND_SNOWFLAKE_NOTIFICATION.

Override the default values in the email notification integration

To use a different set of recipients or a different subject line from the default specified in the email notification integration, set the following properties of the integration configuration object that you pass to SYSTEM$SEND_SNOWFLAKE_NOTIFICATION:

  • subject (this cannot exceed 256 characters in length)

  • toAddress

  • ccAddress

  • bccAddress

For example, to use the email notification integration my_email_int and override the subject line, “To:” line, “Cc:” line, and “Bcc:” line:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  '{ "text/html": "<p>This is a message.</p>" }',
  '{
    "my_email_int": {
      "subject": "Status update",
      "toAddress": ["person_a@example.com", "person_b@example.com"],
      "ccAddress": ["person_c@example.com"],
      "bccAddress": ["person_d@example.com"]
    }
  }'
);
Copy

To construct the JSON-formatted string for the integration configuration, you can call the EMAIL_INTEGRATION_CONFIG helper function.

For example, to send the email message to oncall-a@snowflake.com and oncall-b@snowflake.com with the subject line “Service down”, execute the following statement:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  SNOWFLAKE.NOTIFICATION.TEXT_PLAIN('Your message'),
  SNOWFLAKE.NOTIFICATION.EMAIL_INTEGRATION_CONFIG(
    'my_email_int,
    'Service down',
    ARRAY_CONSTRUCT('oncall-a@example.com', 'oncall-b@example.com')
  )
);
Copy

To include the “Cc:” and “Bcc:” lines in the email message, pass in additional arguments with arrays of email addresses for those lines:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  SNOWFLAKE.NOTIFICATION.TEXT_PLAIN('Your message'),
  SNOWFLAKE.NOTIFICATION.EMAIL_INTEGRATION_CONFIG(
    'my_email_int,
    'Service down',
    ARRAY_CONSTRUCT('oncall-a@example.com', 'oncall-b@example.com'),
    ARRAY_CONSTRUCT('cc-a@example.com', 'cc-b@example.com'),
    ARRAY_CONSTRUCT('bcc-a@example.com', 'bcc-b@example.com')
  )
);
Copy

If you only want to set the “Cc:” or “Bcc:” line (not both), pass in an empty array or NULL for the corresponding arguments. If you are constructing the JSON object without using the helper function, omit the ccAddress or bccAddress property from the JSON object.

Send HTML, plain text, and JSON messages

To send a message in HTML, plain text, or JSON, pass in a JSON object that contains the message type as the name of the property and the message as the value of the property:

'{ "<message_type>": "<message>" }'
Copy

"message_type" can be one of the following values:

  • "text/html"

  • "text/plain"

  • "application/json"

For example:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  '{ "text/html": "<p>This is a message.</p>" }',
  '{ "my_email_int": {} }'
);
Copy

To construct the JSON object for the message, you can use the following helper functions:

The following example sends an HTML message, using the my_email_int email notification integration:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  SNOWFLAKE.NOTIFICATION.TEXT_HTML('<p>a message</p>'),
  SNOWFLAKE.NOTIFICATION.INTEGRATION('my_email_int')
);
Copy

The following example sends a plain text message, using the same integration:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  SNOWFLAKE.NOTIFICATION.TEXT_PLAIN('A message'),
  SNOWFLAKE.NOTIFICATION.INTEGRATION('my_email_int')
);
Copy

The following example sends a JSON message to the queue specified by the my_queue_int notification integration. For instructions on creating a notification integration for a queue, see the following topics:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  SNOWFLAKE.NOTIFICATION.APPLICATION_JSON('{ "name": "value" }'),
  SNOWFLAKE.NOTIFICATION.INTEGRATION('my_sns_int')
);
Copy

Send a notification 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.

To use multiple integrations, call the ARRAY_CONSTRUCT function to construct an array of integration configurations, and pass the array as the second argument of the SYSTEM$SEND_SNOWFLAKE_NOTIFICATION stored procedure.

For example, to send a plain text message to a queue and email addresses configured in different notification integrations:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  '{"text/plain":"A message"}',
  ARRAY_CONSTRUCT(
    '{"my_sns_int":{}}',
    '{"my_email_int":{}}',
 )
);
Copy

Note

The array cannot contain more than one object for the same notification integration.

If you prefer to use the helper functions to construct the integration configurations, you can pass the values returned by the helper functions to the ARRAY_CONSTRUCT function. For example:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  SNOWFLAKE.NOTIFICATION.TEXT_PLAIN('A message'),
  ARRAY_CONSTRUCT(
    SNOWFLAKE.NOTIFICATION.INTEGRATION('my_sns_int'),
    SNOWFLAKE.NOTIFICATION.INTEGRATION('my_email_int')
  )
);
Copy

The following example sends messages in different formats to a queue and email addresses:

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