监控动态表

This topic describes how to view and monitor the dynamic tables in your pipelines. For guidance on what to look for when diagnosing performance issues, see Monitor dynamic table performance.

SectionDescription
列出动态表或查看有关特定列的信息List the dynamic tables in a schema and view information about them.
查看连接到动态表的表图形See the graph of tables connected to your dynamic tables.
使用 SQL 表函数监控动态表Monitor your dynamic tables using SQL table functions.
监控动态表的刷新状态View the refresh status for your dynamic tables.

列出动态表或查看有关特定列的信息

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

查看连接到动态表的表图形

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:

  • 动态表从中提取数据的上游依赖项。
  • 可能受到动态表更改影响的下游依赖项。
An example graph.

依赖项可能会影响刷新性能。例如，假设动态表的上游表在计划刷新之前添加了大量数据负载。动态表将等待它完成刷新，从而导致其无法满足目标滞后要求。在沿袭图中，您会看到标记为“正在执行”的输入表，这表示存在延迟。

要查看特定动态表的图表，请执行以下操作：

  1. Sign in to Snowsight.
  2. In the navigation menu, select Transformation » 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. 要在图表中显示不同表的详细信息，请选择相应表。

要更新图表，请选择图表上方栏中的刷新按钮。

Refreshing the display of the graph.

如果由于 UPSTREAM_FAILED 错误代码导致刷新失败，则可以使用图表来可视化哪个上游表导致了失败。

UPSTREAM_FAILED in the display of the graph.

To view the full details of a table in the graph, see 列出动态表或查看有关特定列的信息.

使用 SQL 表函数监控动态表

使用以下 INFORMATION_SCHEMA 表函数监控动态表：

  • 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 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 Transformation » Dynamic tables.

您可以在此页面上查看所有动态表的状态和上次刷新状态。您还可以按数据库或架构进行筛选以缩小结果范围。

Refresh history view in Snowsight.

监控特定动态表的所有刷新

You can use Snowsight or the DYNAMIC_TABLE_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 Transformation » Dynamic tables.
  3. Select your dynamic table and then go to the Refresh History tab.

此页面显示动态表的刷新历史记录，其中包括有关每次刷新的状态、持续时间和实际滞后时间以及每次刷新时更改的行数的信息。

它还显示动态表的滞后指标，其中包括目标滞后内的时间百分比以及给定间隔内最长的实际滞后时间。

Refresh history view in Snowsight.