Monitor dynamic tables

The topic describes the ways you can view and understand the dynamic tables in your pipelines:

Section

Description

List dynamic tables or view information on specific columns

List the dynamic tables in a schema and view information about them.

View the graph of tables connected to your dynamic tables

See the graph of tables connected to your dynamic tables.

Monitor your dynamic tables using SQL table functions

Monitor your dynamic tables using SQL table functions.

Monitor the refresh status for your dynamic tables

View the refresh status for your dynamic tables.

List dynamic tables or view information on specific columns

To list the dynamic tables in a schema and view information about those dynamic tables, you can use either the following SQL commands or Snowsight, as long as you use a role that has the MONITOR privilege on the dynamic tables.

For more information, see Privileges to view a dynamic table’s metadata.

To list the dynamic tables in the current database (or in the account, if no database is currently in use), use the SHOW DYNAMIC TABLES command.

For example, to list the dynamic tables with names that start with product_ in the database mydb and schema myschema, execute the following SQL statement:

SHOW DYNAMIC TABLES LIKE 'product_%' IN SCHEMA mydb.myschema;
Copy
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | created_on               | name       | database_name | schema_name | cluster_by | rows | bytes  | owner    | target_lag | refresh_mode | refresh_mode_reason  | warehouse | comment | text                            | automatic_clustering | scheduling_state | last_suspended_on | is_clone  | is_replica  | is_iceberg | data_timestamp           | owner_role_type |
  |-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
  |2025-01-01 16:32:28 +0000 | product_dt | my_db         | my_schema   |            | 2    | 2048   | ORGADMIN | DOWNSTREAM | INCREMENTAL  | null                 | mywh      |         | create or replace dynamic table | OFF                  | ACTIVE           | null              | false     | false       | false      |2025-01-01 16:32:28 +0000 | ROLE            |
                                                                                                                                                                                         |  product dt ...                 |                                                                                                                                                 |                                                                                                                                                                                                                                                                                                                                                                                                                       |
  +-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

To output information about the columns in a dynamic table, use the DESCRIBE DYNAMIC TABLE command.

For example, to list the columns in my_dynamic_table, execute the following SQL statement:

DESC DYNAMIC TABLE my_dynamic_table;
Copy
+-------------------+--------------------------------------------------------------------------------------------------------------------------+
  | name   | type         | kind   | null? | default | primary key | unique key | check | expression | comment | policy name  | privacy domain |
  |-------------------+------------------------------------------------------------------------------------------------------------------------|
  | AMOUNT | NUMBER(38,0) | COLUMN | Y     | null    | N           | N          | null  | null       | null    | null         | null           |                                                                                                                                                  |                                                                                                                                                                                                                                                                                                                                                                                                                       |
  +-------------------+------------------------------------------------------------------------------------------------------------------------+

Dynamic tables are also included in the results of the TABLES view.

View the graph of tables connected to your dynamic tables

Viewing dependencies is particularly useful for troubleshooting dynamic table chains. In Snowsight, you can visualize which dynamic tables a given dynamic table depends on using the lineage graph. For example, you can identify the following:

  • Upstream dependencies where a dynamic table pulls data from.

  • Downstream dependencies that might be impacted by changes to a dynamic table.

An example graph.

Dependencies can impact refresh performance. For example, suppose your dynamic table’s upstream table has a large data load added just before its scheduled refresh. Your dynamic table will wait for it to finish the refresh, causing it to miss its target lag. In the lineage graph, you’d see the input table marked as “executing,” indicating the delay.

To view the graph of a particular dynamic table, do the following:

  1. Sign in to Snowsight.

  2. In the navigation menu, go to Monitoring » Dynamic Tables.

  3. Select your dynamic table. The Graph view is displayed by default. This displays the graph with the node for the dynamic table selected. The Details pane on the right displays information about its lag metrics and configuration.

  4. To display the details of a different table in the graph, select that table.

To update the graph, select the refresh button in the bar above the graph.

Refreshing the display of the graph.

If a refresh failed due to an UPSTREAM_FAILED error code, you can use the graph to visualize which upstream table caused the failure.

UPSTREAM_FAILED in the display of the graph.

To view the full details of a table in the graph, see List dynamic tables or view information on specific columns.

Monitor your dynamic tables using SQL table functions

Use the following INFORMATION_SCHEMA table functions to monitor your dynamic tables:

  • DYNAMIC_TABLES: Returns metadata about your dynamic tables, including aggregate lag metrics and the status of the most recent refreshes, within seven days of the current time.

  • DYNAMIC_TABLE_REFRESH_HISTORY: Returns information about each completed and running refresh of your dynamic tables, including refresh status and trigger, and the target lag.

    • DYNAMIC_TABLE_REFRESH_HISTORY view: This Account Usage view also displays information for dynamic table refresh history. It is useful for debugging issues that are for longer than the DYNAMIC_TABLE_REFRESH_HISTORY table function’s data retention time (seven days).

  • DYNAMIC_TABLE_GRAPH_HISTORY: Returns information that 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 returns the most up to date property.

Monitor the refresh status for your dynamic tables

This section explains how to view the refresh status of all or specific dynamic tables.

For troubleshooting refresh, see Troubleshooting skipped, slow, or failed dynamic table refreshes and Diagnosing common dynamic table refresh issues.

Monitor the refreshes for all your dynamic tables

You can use Snowsight or the DYNAMIC_TABLES table function to view the refresh status for all your dynamic tables.

Sign in to Snowsight. In the navigation menu, select Monitoring » Dynamic Tables.

You can view the state and last refresh status for all your dynamic tables on this page. You can also filter by database or schema to narrow the results.

Refresh history view in Snowsight.

Monitor all the refreshes for a specific dynamic table

You can use Snowsight or the DYNAMIC_TABLES_REFRESH_HISTORY table function to view the refresh history for a given dynamic table.

  1. Sign in to Snowsight.

  2. In the navigation menu, select Monitoring » Dynamic Tables.

  3. Select your dynamic table and then go to the Refresh History tab.

    This page displays your dynamic table’s refresh history, which includes information about each refresh’s status, duration, and actual lag time, and the number of rows changed with each refresh.

    It also displays your dynamic table’s lag metrics, which includes the percentage of the time within the target lag and the longest actual lag time during the given interval.

Refresh history view in Snowsight.