Create dynamic tables¶

Dynamic tables can be created and dropped much like traditional tables, although there are some differences and limitations. In addition, changing the underlying tables, views, and other dynamic tables which make up a dynamic table query can cause behavior changes or render an existing dynamic table inoperative. The following sections describe creating and dropping dynamic tables and some limitations and concerns.

Task	Description
Create a dynamic table	Creating dynamic tables, including privileges required to create a dynamic table.
Drop a dynamic table	Dropping dynamic tables.
Query constructs not currently supported in dynamic tables	Unsupported query constructs.
Non-deterministic functions supported in dynamic tables	Supported non-deterministic functions.
Dynamic tables and change tracking	Understanding creating dynamic tables and change tracking.

Create a dynamic table¶

To create a dynamic table, use the CREATE DYNAMIC TABLE command, specifying the query to use, the target lag of the data, and the warehouse to use to perform the refreshes.

For example, suppose that you want to create a dynamic table named product that contains the product_id and product_name columns from the table named staging_table, and you decide:

You want the data in the product table to be at most 20 minutes behind the data in staging_table.
You want to use the warehouse mywh for the compute resources needed for the refreshes (incremental and full).

To create this dynamic table, execute the following CREATE DYNAMIC TABLE SQL statement:

CREATE OR REPLACE DYNAMIC TABLE product
  TARGET_LAG = '20 minutes'
  WAREHOUSE = mywh
  AS
    SELECT product_id, product_name FROM staging_table;

Copy

As with a materialized view, the columns in a dynamic table are determined by the columns specified in the SELECT statement used to create the dynamic table. For columns that are expressions, you must specify aliases for the columns in the SELECT statement.

You must ensure that all objects used by the dynamic table query have change tracking enabled. See Dynamic tables and change tracking in this topic for additional details.

Note

If the query depends on another dynamic table, see How data is refreshed when dynamic tables depend on other dynamic tables for guidelines on choosing the target lag time.

Also note that the minimum TARGET_LAG for any dynamic table is one minute.

Privileges required to create dynamic tables¶

In order to create a dynamic table, you must have the following privileges:

USAGE on the database and schema in which you plan to create the table.
CREATE DYNAMIC TABLE on the schema in which you plan to create the table.
SELECT on the existing tables and views that you plan to query for the dynamic table.
USAGE on the warehouse that you plan to use to refresh the table.

In order to query a dynamic table or create a dynamic table that queries another dynamic table, you must have the following privileges:

SELECT on the dynamic table.

Drop a dynamic table¶

To drop a dynamic table, you can use either Snowsight or SQL commands.

Snowsight

In Snowsight, open the dynamic table details page (instructions).
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

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.

Dynamic table limitations and supported functions¶

Dynamic tables differ from traditional tables in that certain query constructs and functions are not allowed. Additionally, search optimization, clustering, and the query acceleration service (QAS) are not supported. The following sections describe these limitations.

Query constructs not currently supported in dynamic tables¶

The following constructs are not currently supported in the query for a dynamic table. If you specify these in the query for a dynamic table, an error occurs.

External functions
Non-deterministic functions (except those listed in Non-deterministic functions supported in dynamic tables)
Sources that include external tables, streams, and materialized views
Views on dynamic tables or other unsupported objects

Non-deterministic functions supported in dynamic tables¶

The following non-deterministic functions are supported in dynamic tables. Note that these functions are only supported for full refreshes.

VOLATILE user-defined functions
Sequence functions (e.g., SEQ1, SEQ2)
The following context functions:
The following date and time functions (with their respective aliases):

Dynamic tables and change tracking¶

Dynamic tables are updated as underlying database objects change. Change tracking must be enabled on all underlying objects used by a dynamic table.

Snowflake will attempt to enable change tracking on all underlying objects when a dynamic table is created. However, the user creating the dynamic table might not have the sufficient privileges to enable change tracking on all underlying objects.

To avoid errors when refreshing dynamic tables, use SHOW VIEWS, SHOW TABLES, and similar commands, and examine the CHANGE_TRACKING column to determine whether change tracking is enabled for a specific database object.

Use ALTER TABLE, ALTER VIEW, and related documentation to enable change tracking on a specific database object.