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. Select a database and schema.

  4. On the Schema Details, 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 shares 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 and collaboration in 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

The following diagram illustrates suspend, resume, and manual refresh operations in the context of upstream and downstream relationships to other dynamic tables.

Relationship between dynamic tables. Used to help explain suspend, resume, and manual refresh.

The diagram depicts a simple declarative data pipeline built with dynamic tables. Similar to water flowing in a stream, data in a dynamic table pipeline flows from the root downstream toward the leaf dynamic table:

  • DT2 is described as downstream of DT1 because it depends on that dynamic table, and as upstream of DT3, which depends on it.

  • DT3 is downstream of both DT2 and DT1 because it depends on DT2 directly and on DT1 indirectly.

  • DT1 is directly or indirectly upstream of the other dynamic tables.

For additional details and examples of using downstream target lag, see Understanding target lag.

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, select 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.

Understanding the effects of changes to columns in base tables¶

The columns in the base tables, views, and underlying dynamic tables can change over time. Some changes can affect the dynamic table itself; others may have limited or no impact.

When the underlying objects associated with a dynamic table change, the following behaviors apply:

Change

Impact

  • New column added.

  • Existing unused column removed.

None. If a new column is added to the base table or an unused column is deleted, no action occurs and refreshes continue as before.

  • Underlying base table is recreated with identical column names and types.

  • Underlying base table column is recreated with the same name and type.

Full refresh/reinitialize: During the next refresh cycle, a full refresh is done to ensure that no incorrect or stale data is in the dynamic table.

  • An underlying column or other element used by a dynamic table changes in name or in some other way.

The state of the dynamic table changes to FAILING. The dynamic table must be recreated to respond to the change. For more information about dynamic table states, see Dynamic table states.

Understanding auto-suspend behavior¶

Note

A dynamic table is suspended if the system observes five continuous refresh errors. Dynamic tables suspended due to refresh errors are commonly referred to as auto-suspended because they are suspended by the system and not through user action.

Any dynamic tables that depend on the suspended dynamic table are also suspended. SCHEDULING_STATE describes the current state of a dynamic table.

  • To view the scheduling state of the dynamic table, call the DYNAMIC_TABLE_GRAPH_HISTORY table function, and examine the SCHEDULING_STATE column.

Language: English