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.
Creating dynamic tables, including privileges required to create a dynamic table.
Dropping dynamic tables.
Unsupported query constructs.
Supported non-deterministic functions.
Understanding creating dynamic tables and change tracking.
Create a dynamic table¶
For example, suppose that you want to create a dynamic table named
product that contains the
product_name columns from the table named
staging_table, and you decide:
You want the data in the
producttable to be at most 20 minutes behind the data in
You want to use the warehouse
mywhfor 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;
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.
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.
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:
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.
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.
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.
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.
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.