About managing dynamic tables¶
Managing dynamic tables typically involves the following tasks:
Listing and viewing dynamic tables.
Dropping dynamic tables.
Altering dynamic tables.
List dynamic tables and view information about a dynamic table¶
To list the dynamic tables in a database and view information about those dynamic tables, you can use either Snowsight or SQL commands:
Use Snowsight to list dynamic tables and view details¶
To list the dynamic tables in a schema and view information about a dynamic table:
Sign in to Snowsight.
Select Data » Databases.
In the database object explorer next to the navigation pane, select a database schema.
In the schema details page, select the Dynamic Tables tab.
Note that you can also view the list of dynamic tables in the database object explorer by expanding Dynamic Tables.
To view information about a specific dynamic table, select the dynamic table in the database object explorer or in the list of dynamic tables in the Dynamic Tables tab.
The tabs on this page provide some of the same information as the table details page, including:
The definition of the dynamic table and the privileges granted for working with the dynamic table
Information about the columns in the dynamic table
A preview of up to 100 rows of the data in the dynamic table
In addition, the dynamic table details page includes tabs that you can use to view information about the dynamic table:
The Graph tab displays the directed acyclic graph (DAG) that includes this dynamic table. See Use Snowsight to examine the graph of dynamic tables.
The Refresh History tab displays the history of refreshes. See Use Snowsight to monitor refreshes.
Use SQL commands to list dynamic tables and view details¶
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
myschema, execute the following SQL statement:
SHOW DYNAMIC TABLES LIKE 'product_%' IN SCHEMA mydb.myschema;
For the full syntax of the command, see SHOW DYNAMIC TABLES.
To output information about the columns in a dynamic table, use the DESCRIBE DYNAMIC TABLE command.
For example, to list the columns in the dynamic table named
DESC DYNAMIC TABLE product;
Dynamic tables are also included in the results of the TABLES View.
Drop a dynamic table¶
To drop a dynamic table, you can use either Snowsight or SQL commands.
Data sharing with dynamic tables¶
Dynamic tables are shareable objects. To share a dynamic table, data sharing providers grant privileges on a dynamic table to a share, which in turn can be used by data sharing consumers.
Alter the warehouse or target lag for a dynamic table¶
To change the warehouse or target lag for a dynamic table, use the ALTER DYNAMIC TABLE command.
For example, to change the warehouse for the dynamic table
ALTER DYNAMIC TABLE product SET WAREHOUSE = my_other_wh;
For the full syntax of the ALTER DYNAMIC TABLE command, see ALTER DYNAMIC TABLE.
Determine the optimal target lag for a dynamic table¶
To determine the optimal target lag time for a dynamic table, you can use either Snowsight or SQL commands.
On the dynamic table details page, click the Refresh History tab.
Check the maximum actual lag time, which is displayed at the top of the tab. This metric is based on the actual lag time for each refresh.
Use the DYNAMIC_TABLE_REFRESH_HISTORY table function in INFORMATION_SCHEMA. This function returns information about each refresh of a dynamic table, including the time taken for the refresh and which refreshes were skipped.
The lag time that you choose can affect the refresh schedule determined by the automated refresh process. The automated refresh process chooses a schedule that best meets the lag times of the dynamic tables.
Determine whether an incremental or full refresh is used¶
To determine whether an incremental or full refresh is used to update a dynamic table, you can use either Snowsight or SQL commands.
Go to the dynamic table details page.
The basic details at the top of the page indicate whether incremental or full refreshes are used for that table.
Use the SHOW DYNAMIC TABLES command, and check the value of the
REFRESH_MODEcolumn in the output.
If the REFRESH_MODE = FULL, REFRESH_MODE_REASON includes additional details on why a full refresh was executed.