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:

INTEGRATION_NAME

VARCHAR

Name of the integration used for this notification.

MESSAGE

VARCHAR

Message payload.

Note

The MESSAGE column is deprecated and will be removed in a future release.

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 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¶

The following examples demonstrate how to call this function:

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¶

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   |
+-------------------+-------------+-----------------------------------+-----------------------------------+-----------------------+
Language: English