About monitoring dynamic tables¶

Both Snowsight and SQL can be used to view dynamic table information. The following sections describe different methods for monitoring and viewing dynamic table information.

Task	Description
Use Snowsight to monitor refreshes	Monitor dynamic table refreshes.
Use Snowsight to examine the graph of dynamic tables	Examine dynamic table graphs.
Execute SQL statements to monitor dynamic tables	Examining dynamic tables by using SQL.
View dynamic table refresh mode	View dynamic table refresh mode

Use Snowsight to monitor refreshes¶

To determine whether refreshes have errors, on the dynamic table details page, select the Refresh History tab.

The information displayed at the top of the tab includes:
- The date and time when the dynamic table was most recently up to date
- The target lag time for the dynamic table
- The longest actual lag time for the dynamic table during the given interval
The tab also displays a table containing the history of refreshes. For each refresh in the history, the table displays the following information:
- The transactional timestamp when the refresh was evaluated
  
  (This might be slightly before the actual time that the refresh started.)
- The amount of time that the refresh took to complete
- The target lag and the maximum lag reached just before the refresh commits
- The status of the refresh

Use Snowsight to examine the graph of dynamic tables¶

In Snowsight, you can view the directed acyclic graph (DAG). You can use this graph to determine which tables a given dynamic table depends on. For example, you can:

Determine where a dynamic table retrieves data from.
Identify the upstream dependencies of a dynamic table.
Identify the downstream dependencies that might be affected by changes to a dynamic table.

To examine the graph that includes a dynamic table:

On the dynamic table details page, select the Graph tab.

This displays the graph with the node for the dynamic table selected. The Details pane on the right displays information about the selected table or dynamic table.
To display the details of a different item in the graph, select that item.
To view the full details of a table in the graph, hover over the node for the table, and select Go to Dynamic Table Page or Go to Table Page in the upper-right corner of the node.
To update the displayed graph and information with the latest changes, select the refresh button in the bar above the graph.

The following image displays the Graph tab and Details pane.

In the Details pane:

The information displayed for a regular base table includes:

The role that owns the table and the date when the table was created

The number of rows in the table

The size of the table

The information displayed for a dynamic table includes:

The role that owns the dynamic table and the date when the dynamic table was created

The refresh mode

The warehouse for the dynamic table

The target lag

The average actual lag

The maximum actual lag

Setting up alerts on new data to monitor dynamic table 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 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:

Set the severity level of the events to capture
Set up an alert on new data
Information logged for dynamic table events

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;
```
Copy
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;
```
Copy
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;
```
Copy

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;

Copy

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
Key-value pairs in the resource_attributes column
Key-value pairs in the record column

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`	TIMESTAMP_NTZ	The UTC timestamp when an event was created.
`observed_timestamp`	TIMESTAMP_NTZ	A UTC time used for logs. Currently, this is the same value that is in the `timestamp` column.
`resource_attributes`	OBJECT	Attributes that identify the dynamic table that was refreshed.
`record_type`	STRING	The event type, which is `EVENT` for dynamic table refreshes.
`record`	OBJECT	Details about the status of the dynamic table refresh.
`value`	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
`snow.database.id`	INTEGER	The internal/system-generated identifier of the database containing the dynamic table.	`12345`
`snow.database.name`	VARCHAR	The name of the database containing the dynamic table.	`MY_DATABASE`
`snow.executable.id`	INTEGER	The internal/system-generated identifier of the dynamic table that was refreshed.	`12345`
`snow.executable.name`	VARCHAR	The name of the dynamic table that was refreshed.	`MY_DYNAMIC_TABLE`
`snow.executable.type`	VARCHAR	The type of the object. The value is `DYNAMIC_TABLE` for dynamic table events.	`DYNAMIC_TABLE`
`snow.owner.id`	INTEGER	The internal/system-generated identifier of the role with the OWNERSHIP privilege on the dynamic table.	`12345`
`snow.owner.name`	VARCHAR	The name of the role with the OWNERSHIP privilege on the dynamic table.	`MY_ROLE`
`snow.owner.type`	VARCHAR	The type of role that owns the object, for example `ROLE`. . If a Snowflake Native App owns the object, the value is `APPLICATION`. . Snowflake returns NULL if you delete the object because a deleted object does not have an owner role.	`ROLE`
`snow.query.id`	VARCHAR	ID of the query that refreshed the dynamic table.	`01ba7614-0107-e56c-0000-a995024f304a`
`snow.schema.id`	INTEGER	The internal/system-generated identifier of the schema containing the dynamic table.	`12345`
`snow.schema.name`	VARCHAR	The name of the schema containing the dynamic table.	`MY_SCHEMA`
`snow.warehouse.id`	INTEGER	The internal/system-generated identifier of the warehouse used to refresh the dynamic table.	`12345`
`snow.warehouse.name`	VARCHAR	The name of the warehouse used to refresh the dynamic table.	`MY_WAREHOUSE`

Key-value pairs in the `record` column¶

The record column contains an OBJECT value with the following key-value pairs:

Key	Type	Description	Example
`name`	VARCHAR	The name of the event. The value is `refresh_status` for dynamic table refreshes.	`refresh_status`
`severity_text`	VARCHAR	The severity level of the event, which is one of the following values: `INFO`: The refresh succeeded. `ERROR`: The refresh failed. `WARN`: The refresh of an upstream dynamic table failed.	`INFO`

Key

Type

Description

Example

name

VARCHAR

The name of the event. The value is refresh_status for dynamic table refreshes.

refresh_status

severity_text

VARCHAR

The severity level of the event, which is one of the following values:

INFO: The refresh succeeded.
ERROR: The refresh failed.
WARN: The refresh of an upstream dynamic table failed.

INFO

Key-value pairs in the `value` column¶

The value column contains an VARIANT value with the following key-value pairs:

Key	Type	Description	Example
`state`	VARCHAR	The state of the refresh, which can be one of the following values: `SUCCEEDED`: The refresh succeeded. `ERROR`: The refresh failed. `UPSTREAM_FAILURE`: The refresh failed due to a failure to refresh a dynamic table that this dynamic table depends on.	`SUCCEEDED`
`message`	VARCHAR	If the value in `state` is `ERROR`, this column includes the error message.	`SQL compilation error:\nFailure during expansion of view 'MY_DYNAMIC_TABLE': SQL compilation error:\nObject 'MY_DB.MY_SCHEMA.MY_BASE_TABLE' does not exist or not authorized.`

Key

Type

Description

Example

state

VARCHAR

The state of the refresh, which can be one of the following values:

SUCCEEDED: The refresh succeeded.
ERROR: The refresh failed.
UPSTREAM_FAILURE: The refresh failed due to a failure to refresh a dynamic table that this dynamic table depends on.

SUCCEEDED

message

VARCHAR

If the value in state is ERROR, this column includes the error message.

SQL compilation error:\nFailure during expansion of view 'MY_DYNAMIC_TABLE': SQL compilation error:\nObject 'MY_DB.MY_SCHEMA.MY_BASE_TABLE' does not exist or not authorized.

Execute SQL statements to monitor dynamic tables¶

To monitor dynamic table refreshes, you can query the events logged in the active event table and call the table functions for dynamic tables. The next sections explain how to use SQL statements to monitor dynamic tables:

Query the event table to monitor refreshes
Call the table functions for dynamic tables

Query the event table 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 query the event table for these events.

For information on 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.

For example, to get the timestamp, dynamic table name, query ID, and error message for errors with dynamic tables in the database my_db:

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;

Copy

+-------------------------+------------------+--------------------------------------+---------------------------------------------------------------------------------+
| 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;

Copy

+-------------------------+-----------------+-------------------------+-------+----------+-------------------------------------------------+-------+------------------+-------------+-----------------------------+-------------------+-------------------------------+-----------+
| 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"               |       |                  |             |                             |                   |                               |           |
|                         |                 |                         |       |          | }                                               |       |                  |             |                             |                   |                               |           |
+-------------------------+-----------------+-------------------------+-------+----------+-------------------------------------------------+-------+------------------+-------------+-----------------------------+-------------------+-------------------------------+-----------+

Call the table functions for dynamic tables¶

To determine whether problems occur with the refreshes, use the following INFORMATION_SCHEMA table functions:
- DYNAMIC_TABLES provides information about each completed and running refresh of your dynamic tables.
- DYNAMIC_TABLE_REFRESH_HISTORY provides the history of refreshes for one or more dynamic tables in the account.
- DYNAMIC_TABLE_GRAPH_HISTORY provides the history of each dynamic table, its properties, and its dependencies on other tables and dynamic tables.
  
  You can use this table function to get a snapshot of the dependency tree of dynamic tables at a given point in time.
  
  The output also reflects the changes made to the properties of a dynamic table over time. Each row represents a dynamic table and a specific set of properties. If you change a property of a dynamic table (for example, the target lag), the function produces a new row of output for that updated set of properties.
To identify the refreshes that had errors, call the DYNAMIC_TABLE_REFRESH_HISTORY function, and pass in the argument ERROR_ONLY => TRUE.

For example, if you want to check for refresh errors in the dynamic tables in the mydb database and myschema schema, execute the following statement:
```
SELECT name, state, state_code, state_message, query_id, data_timestamp, refresh_start_time, refresh_end_time
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(NAME_PREFIX => 'MYDB.MYSCHEMA.', ERROR_ONLY => TRUE))
ORDER BY name, data_timestamp;
```
Copy

View dynamic table refresh mode¶

Dynamic tables have two refresh modes: incremental and full. When refresh mode is AUTO, the system attempts to apply an incremental refresh by default. However, when incremental refresh isn’t supported or expected to perform well, the dynamic table automatically selects full refresh instead. For more information, see Limitations on incremental refresh.

Using a role with the necessary privileges, you can verify the refresh mode using one of the following methods:

SQL:

Execute the SHOW DYNAMIC TABLES command.

In the output:

The text column shows the user-specified refresh mode.
The refresh_mode column shows the actual refresh mode.
The refresh_mode_reason shows why the actual refresh mode was chosen.

Snowsight:

In the navigation menu, select Monitoring » Dynamic Tables, and then select your dynamic table.

You can view the refresh mode for the dynamic table in the Table Details tab. The refresh mode reason is visible when hovering over the mode.

About monitoring dynamic tables¶

Use Snowsight to monitor refreshes¶

Use Snowsight to examine the graph of dynamic tables¶

Setting up alerts on new data to monitor dynamic table refreshes¶

Set the severity level of the events to capture¶

Set up an alert on new data¶

Information logged for dynamic table events¶

Event table column values¶

Key-value pairs in the resource_attributes column¶

Key-value pairs in the record column¶

Key-value pairs in the value column¶

Execute SQL statements to monitor dynamic tables¶

Query the event table to monitor refreshes¶

Call the table functions for dynamic tables¶

View dynamic table refresh mode¶

Key-value pairs in the `resource_attributes` column¶

Key-value pairs in the `record` column¶

Key-value pairs in the `value` column¶