Categories:: Information Schema , Table functions

NOTIFICATION_HISTORY¶

This table function can be used to query the history of notifications sent through Snowflake. These notifications include:

The rows returned represent:

Requests that are being processed.
Failed attempts at sending notifications.
Notifications that were sent successfully.

The STATUS column indicates what each row represents. See Examples of output from the function.

Syntax¶

NOTIFICATION_HISTORY(
  [ START_TIME => <constant_expr> ]
  [, END_TIME => <constant_expr> ]
  [, INTEGRATION_NAME => '<string>' ]
  [, RESULT_LIMIT => <integer> ] )

Copy

Arguments¶

All the arguments are optional.

START_TIME=> constant_expr , . END_TIME=> constant_expr

Time range (in TIMESTAMP_LTZ format) when the notification is sent out.

If START_TIME is not specified, the range starts 24 hours prior to the END_TIME.
If END_TIME is not specified, the default is CURRENT_TIMESTAMP.

The maximum time range is 14 days.

INTEGRATION_NAME => 'string'

The fully qualified name of the integration that is tied with the notification. If you omit this argument, the function returns all notifications.

Default: An empty string.

RESULT_LIMIT => integer

A number specifying the maximum number of rows returned by the function.

Range: 1 to 10000

Default: 100

Output¶

The function returns the following columns:

Column Name	Data Type	Description
CREATED	TIMESTAMP_LTZ	Timestamp when the notification was created.
PROCESSED	TIMESTAMP_LTZ	Timestamp of the last attempt to send the notification.
MESSAGE_SOURCE	VARCHAR	Type of object or feature that generated the notification. Valid values include: `BUDGET` (for notifications from budgets) `TASK` (for notifications from tasks) `SNOWPIPE` (for notifications from Snowpipe) `STORED_PROCEDURE` (for email notifications sent by calling the SYSTEM$SEND_EMAIL or SYSTEM$SEND_SNOWFLAKE_NOTIFICATION stored procedure)
INTEGRATION_NAME	VARCHAR	Name of the integration used for this notification.
STATUS	VARCHAR	Status of the notification. Valid values are: `QUEUED`: The request to send the notification is being processed. `SUCCESS`: The notification was sent successfully. `RETRIABLE_FAILURE`: The attempt to send the notification failed, and the system will attempt to send the notification again. `FAILURE`: Multiple attempts to send the notification failed, and there will be no more attempts to send the notification.
ERROR_MESSAGE	VARCHAR	If the notification failed, provides details about why the notification failed. Note For webhook notifications, this column contains the body of the HTTP response, which might contain sensitive data. Before using this data, make sure to sanitize it.
ID	VARCHAR	Unique ID of a request to send a notification. If Snowflake fails to send a notification and attempts to send the notification again, the function returns a row for each attempt. Each row for an attempt has the same value in the ID column but a different value in the ATTEMPT column.
ATTEMPT	INTEGER	Number of the attempt made to send the notification.
MESSAGE_SOURCE_INFO	OBJECT	Object containing information about the source of the notification. The fields in this object depend on the type of the source. For notifications for budgets, the object contains the following fields: `budget_id`: Identifier for the budget. `budget_name`: The name of the budget. For error notifications for tasks, the object contains the following fields: `name`: The name of the task `graph_run_group_id`: Identifier for the graph run. `attempt_number`: Integer representing the number of the attempt to run this task. For error notifications for Snowpipe, the object contains the `pipe_name` field, which specifies the name of the pipe. For notifications sent by calling the SYSTEM$SEND_SNOWFLAKE_NOTIFICATION or SYSTEM$SEND_EMAIL stored procedure, the object contains the `query_id` field, which specifies the ID of the statement that called the stored procedure.

Usage notes¶

Returns results only for the ACCOUNTADMIN role, the integration owner (i.e. the role with the OWNERSHIP privilege on the integration) or a role with the USAGE privilege on the integration.
When calling an Information Schema table function, the session must have an INFORMATION_SCHEMA schema in use or the function name must be fully-qualified. For more details, see Snowflake Information Schema.

Examples¶

The following sections contain examples of calling the function and examples of output from the function:

Examples of calling the function
Examples of output from the function

Examples of calling the function¶

The following examples demonstrate how to call this function:

Retrieving the most recent notifications
Retrieving notifications by time and integration name

Retrieving the most recent notifications¶

Retrieve the most recent notifications that were created in the past 24 hours.

SELECT * FROM TABLE(INFORMATION_SCHEMA.NOTIFICATION_HISTORY());

Copy

Retrieving notifications by time and integration name¶

Retrieve the most recent notifications that were created in the past hour and sent using the integration named my_integration.

SELECT * FROM TABLE(INFORMATION_SCHEMA.NOTIFICATION_HISTORY(
  START_TIME=>DATEADD('hour',-1,CURRENT_TIMESTAMP()),
  END_TIME=>CURRENT_TIMESTAMP(),
  RESULT_LIMIT=>100,
  INTEGRATION_NAME=>'my_integration'));

Copy

Examples of output from the function¶

The following examples explain the output returned by this function for notification requests in different states:

Example of the output when two attempts fail and a third attempt is in progress
Example of the output when two attempts fail and a third attempt succeeds

Example of the output when two attempts fail and a third attempt is in progress¶

This example selects a subset of the columns in the output:

SELECT id, attempt, created, processed, status
  FROM TABLE(INFORMATION_SCHEMA.NOTIFICATION_HISTORY());

Copy

The output includes the rows that represent the attempts to send one notification. In the output:

The ID column identifies the notification that is being sent.
The first two attempts to send the notification have failed, but the system can attempt to send the notification again (as indicated by the value RETRIABLE_FAILURE in the STATUS column).
A third attempt is being processed, as indicated by the value QUEUED in the STATUS column.

+-------------------+-------------+-----------------------------------+-----------------------------------+-----------------------+
|   ID              |   ATTEMPT   |   CREATED                         |   PROCESSED                       |   STATUS              |
+-------------------+-------------+-----------------------------------+-----------------------------------+-----------------------+
|   10ae695e-93c3   |   3         |   2023-12-05 15:10:15.194 -0800   |   NULL                            |   QUEUED              |
|   10ae695e-93c3   |   2         |   2023-12-05 15:10:15.194 -0800   |   2023-12-05 15:11:21.443 -0800   |   RETRIABLE_FAILURE   |
|   10ae695e-93c3   |   1         |   2023-12-05 15:10:15.194 -0800   |   2023-12-05 15:10:21.443 -0800   |   RETRIABLE_FAILURE   |
+-------------------+-------------+-----------------------------------+-----------------------------------+-----------------------+

Example of the output when two attempts fail and a third attempt succeeds¶

This example selects a subset of the columns in the output:

SELECT id, attempt, created, processed, status
  FROM TABLE(INFORMATION_SCHEMA.NOTIFICATION_HISTORY());

Copy

The output includes the rows that represent the attempts to send one notification. In the output:

The ID column identifies the notification that is being sent.
The first two attempts to send the notification have failed, but the system can attempt to send the notification again (as indicated by the value RETRIABLE_FAILURE in the STATUS column).
A third attempt succeeded, as indicated by the value SUCCESS in the STATUS column.

+-------------------+-------------+-----------------------------------+-----------------------------------+-----------------------+
|   ID              |   ATTEMPT   |   CREATED                         |   PROCESSED                       |   STATUS              |
+-------------------+-------------+-----------------------------------+-----------------------------------+-----------------------+
|   10ae695e-93c3   |   3         |   2023-12-05 15:10:15.194 -0800   |   2023-12-05 15:12:21.443 -0800   |   SUCCESS             |
|   10ae695e-93c3   |   2         |   2023-12-05 15:10:15.194 -0800   |   2023-12-05 15:11:21.443 -0800   |   RETRIABLE_FAILURE   |
|   10ae695e-93c3   |   1         |   2023-12-05 15:10:15.194 -0800   |   2023-12-05 15:10:21.443 -0800   |   RETRIABLE_FAILURE   |
+-------------------+-------------+-----------------------------------+-----------------------------------+-----------------------+