About monitoring dynamic tables¶

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:

1. 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.

2. To display the details of a different item in the graph, select that item.

3. 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.

   

4. 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

Use information schema functions to monitor 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:

• Using SQL: Run the SHOW DYNAMIC TABLES statement. In the output, the text column shows the user-specified refresh mode, the refresh_mode column shows the actual refresh mode, and the refresh_mode_reason shows why the actual refresh mode was chosen.

• Using 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.