About managing dynamic tables

Managing dynamic tables typically involves the following tasks:

Task

Description

List dynamic tables and view information about a dynamic table

Listing and viewing dynamic tables.

Drop a dynamic table

Dropping dynamic tables.

Alter the warehouse or target lag for a dynamic table

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:

  1. Sign in to Snowsight.

  2. Select Data » Databases.

  3. In the database object explorer next to the navigation pane, select a database schema.

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

  5. 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:

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 mydb and schema myschema, execute the following SQL statement:

    SHOW DYNAMIC TABLES LIKE 'product_%' IN SCHEMA mydb.myschema;
    
    Copy

    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 product:

    DESC DYNAMIC TABLE product;
    
    Copy

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.

Snowsight:
  1. In Snowsight, open the dynamic table details page (instructions).

  2. On the More menu in the upper-right corner of the page, select Drop.

SQL:
  • Use the DROP DYNAMIC TABLE command.

    For example, to drop the dynamic table named product:

DROP DYNAMIC TABLE product;
Copy

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.

How data is shared with dynamic tables

Providers can share selected dynamic tables with other Snowflake accounts using Direct Sharing or Listings, which allow data to be shared between accounts without physically moving it. The process involves creating a share of a database within the provider’s account, granting database access within the provider’s account, granting access privileges, and adding other objects to the share. To learn more, see Introduction to Secure Data Sharing and Overview of data sharing at Snowflake.

A data sharing provider can choose to grant the SELECT privilege on a single dynamic table or grant the SELECT privilege on all dynamic tables in a database, as shown in the following examples.

GRANT SELECT ON ALL DYNAMIC TABLES IN SCHEMA mydb.public TO SHARE share1;

GRANT SELECT ON DYNAMIC TABLE mydb.public TO SHARE share1;
Copy

For more details, see GRANT <privilege> … TO SHARE.

Create a dynamic table to ingest shared data

To create a dynamic table to ingest shared data, do the following:

  1. Ensure that you have the right privileges, and create a database from a share and grant privileges on it.

    CREATE DATABASE shared_db FROM SHARE provider_account.share1;
    
    Copy
  2. Grant privileges to the shared database.

  3. Create a shared dynamic table.

    CREATE OR REPLACE DYNAMIC TABLE <dt_name>
      TARGET_LAG = '1 day'
      WAREHOUSE = <warehouse_name>
      AS
        SELECT * FROM shared_db.public.mydb;
    
    Copy

    Note

    Change tracking must be enabled on all underlying objects used by a dynamic table. To use a dynamic table to ingest shared data, the data sharing provider needs to enable change_tracking on the shared object. See Enable change tracking.

When you use a dynamic table to ingest shared data, the query can’t select from a shared dynamic table or a shared secure view that references an upstream dynamic table.

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 product to my_other_wh, run:

    ALTER DYNAMIC TABLE product SET
      WAREHOUSE = my_other_wh;
    
    Copy

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.

Snowsight:
  1. On the dynamic table details page, click the Refresh History tab.

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

SQL:
  • 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.

Snowsight:
  • 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.

SQL:
  • Use the SHOW DYNAMIC TABLES command, and check the value of the REFRESH_MODE column in the output.

    If the REFRESH_MODE = FULL, REFRESH_MODE_REASON includes additional details on why a full refresh was executed.