Event table monitoring and alerts for dynamic tables¶
This topic discusses how to query an event table that provides information about your refresh status and how to set up alerts on new data in an event table.
Query an event table to monitor refreshes¶
When a dynamic table is refreshed, you can configure Snowflake to record an event that provides information about the status of the refresh operation. The event is recorded in the active event table associated with the dynamic table.
For example, suppose that you have associated an event table with a database. When a dynamic table in that database is refreshed, Snowflake records an event to that event table.
You can query the events logged in this active event table to monitor your dynamic table refreshes.
For example, to get the timestamp, dynamic table name, query ID, and error message for errors with dynamic tables in the database my_db,
do the following:
SELECT
timestamp,
resource_attributes:"snow.executable.name"::VARCHAR AS dt_name,
resource_attributes:"snow.query.id"::VARCHAR AS query_id,
value:message::VARCHAR AS error
FROM my_event_table
WHERE
resource_attributes:"snow.executable.type" = 'DYNAMIC_TABLE' AND
resource_attributes:"snow.database.name" = 'MY_DB' AND
value:state = 'FAILED'
ORDER BY timestamp DESC;
+-------------------------+------------------+--------------------------------------+---------------------------------------------------------------------------------+
| TIMESTAMP | DT_NAME | QUERY_ID | ERROR |
|-------------------------+------------------+--------------------------------------+---------------------------------------------------------------------------------|
| 2025-02-17 21:40:45.444 | MY_DYNAMIC_TABLE | 01ba7614-0107-e56c-0000-a995024f304a | SQL compilation error: |
| | | | Failure during expansion of view 'MY_DYNAMIC_TABLE': SQL compilation error: |
| | | | Object 'MY_DB.MY_SCHEMA.MY_BASE_TABLE' does not exist or not authorized. |
+-------------------------+------------------+--------------------------------------+---------------------------------------------------------------------------------+
The following example retrieves all columns for upstream errors with dynamic tables in the schema my_schema:
SELECT *
FROM my_event_table
WHERE
resource_attributes:"snow.executable.type" = 'DYNAMIC_TABLE' AND
resource_attributes:"snow.schema.name" = 'MY_SCHEMA' AND
value:state = 'UPSTREAM_FAILURE'
ORDER BY timestamp DESC;
+-------------------------+-----------------+-------------------------+-------+----------+-------------------------------------------------+-------+------------------+-------------+-----------------------------+-------------------+-------------------------------+-----------+
| TIMESTAMP | START_TIMESTAMP | OBSERVED_TIMESTAMP | TRACE | RESOURCE | RESOURCE_ATTRIBUTES | SCOPE | SCOPE_ATTRIBUTES | RECORD_TYPE | RECORD | RECORD_ATTRIBUTES | VALUE | EXEMPLARS |
|-------------------------+-----------------+-------------------------+-------+----------+-------------------------------------------------+-------+------------------+-------------+-----------------------------+-------------------+-------------------------------+-----------|
| 2025-02-17 21:40:45.486 | NULL | 2025-02-17 21:40:45.486 | NULL | NULL | { | NULL | NULL | EVENT | { | NULL | { | NULL |
| | | | | | "snow.database.id": 49, | | | | "name": "refresh.status", | | "state": "UPSTREAM_FAILURE" | |
| | | | | | "snow.database.name": "MY_DB", | | | | "severity_text": "WARN" | | } | |
| | | | | | "snow.executable.id": 487426, | | | | } | | | |
| | | | | | "snow.executable.name": "MY_DYNAMIC_TABLE_2", | | | | | | | |
| | | | | | "snow.executable.type": "DYNAMIC_TABLE", | | | | | | | |
| | | | | | "snow.owner.id": 2601, | | | | | | | |
| | | | | | "snow.owner.name": "DATA_ADMIN", | | | | | | | |
| | | | | | "snow.owner.type": "ROLE", | | | | | | | |
| | | | | | "snow.schema.id": 411, | | | | | | | |
| | | | | | "snow.schema.name": "MY_SCHEMA" | | | | | | | |
| | | | | | } | | | | | | | |
+-------------------------+-----------------+-------------------------+-------+----------+-------------------------------------------------+-------+------------------+-------------+-----------------------------+-------------------+-------------------------------+-----------+
For information about the role that you need to use to query the event table and the conditions that you can use to filter the results, see Set up an alert on new data.
Set up alerts on new data to monitor refreshes¶
As mentioned earlier, when a dynamic table is refreshed, an event is logged in the event table to indicate whether the refresh succeeded or failed. You can set up an alert on new data to monitor the event table. You can configure the alert to send a notification when a refresh fails.
The next sections explain how to set up the event logging to capture the events, how to set up the alert, and how to interpret the events recorded in the event table:
Note
Logging events for dynamic tables incurs costs. See Costs of telemetry data collection.
Set the severity level of the events to capture¶
Note
If you do not set the severity level, no events will be captured.
To set up dynamic table events to be recorded to the event table, set the severity level of events that you want captured in the event table. Events are captured at the following levels:
ERROR: Refresh failure events.WARN: Failures to refresh upstream dynamic tables and refresh failure events.INFO: Successful refresh events, failures to refresh upstream dynamic tables, and refresh failure events.
To set the level, set the LOG_LEVEL parameter for the account or object. You can set the level for:
All objects in the account.
All objects in a database or schema.
A specific dynamic table.
For example:
To capture ERROR-level events and messages for all objects in the account, execute ALTER ACCOUNT SET LOG_LEVEL:
ALTER ACCOUNT SET LOG_LEVEL = ERROR;
Note that this level affects all types of objects in the account, including UDFs, stored procedures, dynamic tables, and tasks.
To capture INFO-level events and messages for all objects in the database
my_db, execute ALTER DATABASE … SET LOG_LEVEL:ALTER DATABASE my_db SET LOG_LEVEL = INFO;
Similar to the case of setting the level on the account, setting the level on the database affects all types of objects in the database, including UDFs, stored procedures, dynamic tables, and tasks.
To capture WARN-level events for the dynamic table
my_dynamic_table, execute ALTER DYNAMIC TABLE … SET LOG_LEVEL:ALTER DYNAMIC TABLE my_dynamic_table SET LOG_LEVEL = WARN;
Set up an alert on new data¶
After you set the severity level for logging events, you can set up an alert on new data to monitor the event table for new events that indicate a failure in a dynamic table refresh. An alert on new data is triggered when new rows in the event table are inserted and meet the condition specified in the alert.
Note
To create the alert on new data, you must use a role that has been granted the required privileges to query the event table.
If the alert condition queries the default event table (SNOWFLAKE.TELEMETRY.EVENTS) or the predefined view (SNOWFLAKE.TELEMETRY.EVENTS_VIEW view), see Roles for access to the default event table and EVENTS_VIEW.
To manage access to the EVENTS_VIEW view, see Manage access to the EVENTS_VIEW view.
If the alert condition queries a custom event table, see Access control privileges for event tables.
To manage access to a custom event table, see Managing access to event table data.
In the alert condition, to query for dynamic table events, select rows where
resource_attributes:"snow.executable.type" = 'DYNAMIC_TABLE'. To narrow down the list of events, you can filter on the
following columns:
To restrict the results to dynamic tables in a specific database, use
resource_attributes:"snow.database.name".To return events where the refresh failed due to an error with the dynamic table, use
value:state = 'FAILED'.To return events where the refresh failed due to an error with an upstream dynamic table, use
value:state = 'UPSTREAM_FAILURE'.
For information on the values logged for a dynamic table event, see Information logged for dynamic table events.
For example, the following statement creates an alert on new data that performs an action when refreshes fail for dynamic tables
in the database my_db. The example assumes that:
Your active event table is the default event table (SNOWFLAKE.TELEMETRY.EVENTS).
You have set up a webhook notification integration for that Slack channel.
CREATE ALERT my_alert_on_dt_refreshes
IF( EXISTS(
SELECT * FROM SNOWFLAKE.TELEMETRY.EVENT_TABLE
WHERE resource_attributes:"snow.executable.type" = 'dynamic_table'
AND resource_attributes:"snow.database.name" = 'my_db'
AND record_attributes:"event.name" = 'refresh.status'
AND record:"severity_text" = 'ERROR'
AND value:"state" = 'FAILED'))
THEN
BEGIN
LET result_str VARCHAR;
(SELECT ARRAY_TO_STRING(ARRAY_ARG(name)::ARRAY, ',') INTO :result_str
FROM (
SELECT resource_attributes:"snow.executable.name"::VARCHAR name
FROM TABLE(RESULT_SCAN(SNOWFLAKE.ALERT.GET_CONDITION_QUERY_UUID()))
LIMIT 10
)
);
CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
SNOWFLAKE.NOTIFICATION.TEXT_PLAIN(:result_str),
'{"my_slack_integration": {}}'
);
END;
Information logged for dynamic table events¶
When a dynamic table refreshes, an event is logged to the event table. The following sections describe the event table row that represents the event:
Event table column values¶
When a dynamic table refreshes, a row with the following values is inserted into the event table.
Note
If a column is not listed below, the column value is NULL for the event.
Column |
Data type |
Description |
|---|---|---|
|
TIMESTAMP_NTZ |
The UTC timestamp when an event was created. |
|
TIMESTAMP_NTZ |
A UTC time used for logs. Currently, this is the same value that is in the |
|
OBJECT |
Attributes that identify the dynamic table that was refreshed. |
|
STRING |
The event type, which is |
|
OBJECT |
Details about the status of the dynamic table refresh. |
|
VARIANT |
The status of the dynamic table refresh and, if the refresh failed, the error message for the failure. |
Key-value pairs in the resource_attributes column¶
The resource_attributes column contains an OBJECT value with the following key-value pairs:
Attribute name |
Attribute type |
Description |
Example |
|---|---|---|---|
|
INTEGER |
The internal/system-generated identifier of the database containing the dynamic table. |
|
|
VARCHAR |
The name of the database containing the dynamic table. |
|
|
INTEGER |
The internal/system-generated identifier of the dynamic table that was refreshed. |
|
|
VARCHAR |
The name of the dynamic table that was refreshed. |
|
|
VARCHAR |
The type of the object. The value is |
|
|
INTEGER |
The internal/system-generated identifier of the role with the OWNERSHIP privilege on the dynamic table. |
|
|
VARCHAR |
The name of the role with the OWNERSHIP privilege on the dynamic table. |
|
|
VARCHAR |
The type of role that owns the object, for example |
|
|
VARCHAR |
ID of the query that refreshed the dynamic table. |
|
|
INTEGER |
The internal/system-generated identifier of the schema containing the dynamic table. |
|
|
VARCHAR |
The name of the schema containing the dynamic table. |
|
|
INTEGER |
The internal/system-generated identifier of the warehouse used to refresh the dynamic table. |
|
|
VARCHAR |
The name of the warehouse used to refresh the dynamic table. |
|
Key-value pairs in the record column¶
The record column contains an OBJECT value with the following key-value pairs:
Key |
Type |
Description |
Example |
|---|---|---|---|
|
VARCHAR |
The name of the event. The value is |
|
|
VARCHAR |
The severity level of the event, which is one of the following values:
|
|
Key-value pairs in the value column¶
The value column contains an VARIANT value with the following key-value pairs:
Key |
Type |
Description |
Example |
|---|---|---|---|
|
VARCHAR |
The state of the refresh, which can be one of the following values:
|
|
|
VARCHAR |
If the value in |
|