About managing dynamic tables¶

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:

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

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 About Secure Data Sharing and 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;

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.

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: