CREATE DYNAMIC TABLE¶
Creates a dynamic table, based on a specified query.
Syntax¶
CREATE [ OR REPLACE ] [ TRANSIENT ] DYNAMIC TABLE [ IF NOT EXISTS ] <name> (
-- Column definition
<col_name> <col_type>
[ [ WITH ] MASKING POLICY <policy_name> [ USING ( <col_name> , <cond_col1> , ... ) ] ]
[ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
[ COMMENT '<string_literal>' ]
-- Additional column definitions
[ , <col_name> <col_type> [ ... ] ]
)
TARGET_LAG = { '<num> { seconds | minutes | hours | days }' | DOWNSTREAM }
WAREHOUSE = <warehouse_name>
[ REFRESH_MODE = { AUTO | FULL | INCREMENTAL } ]
[ INITIALIZE = { ON_CREATE | ON_SCHEDULE } ]
[ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
[ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
[ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
[ COMMENT = '<string_literal>' ]
[ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
[ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
AS <query>
Variant syntax¶
CREATE DYNAMIC TABLE … CLONE¶
Creates a new dynamic table with the same column definitions and containing all the existing data from the source dynamic table, without actually copying the data. This variant can also be used to clone a dynamic table at a specific point in the past. See Cloning considerations.
CREATE [ OR REPLACE ] [ TRANSIENT ] DYNAMIC TABLE <name>
CLONE <source_dynamic_table>
[ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ]
[
COPY GRANTS
TARGET_LAG = { '<num> { seconds | minutes | hours | days }' | DOWNSTREAM }
WAREHOUSE = <warehouse_name>
]
If the source dynamic table has clustering keys, then the cloned dynamic table has clustering keys. By default, Automatic Clustering is suspended for the new table, even if Automatic Clustering was not suspended for the source table.
For more details about cloning, see CREATE <object> … CLONE.
CREATE DYNAMIC ICEBERG TABLE¶
Creates a new dynamic Iceberg table. For information about Iceberg tables, see Iceberg tables and CREATE ICEBERG TABLE (Snowflake as the Iceberg catalog).
CREATE [ OR REPLACE ] DYNAMIC ICEBERG TABLE <name> (
-- Column definition
<col_name> <col_type>
[ [ WITH ] MASKING POLICY <policy_name> [ USING ( <col_name> , <cond_col1> , ... ) ] ]
[ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
[ COMMENT '<string_literal>' ]
-- Additional column definitions
[ , <col_name> <col_type> [ ... ] ]
)
TARGET_LAG = { '<num> { seconds | minutes | hours | days }' | DOWNSTREAM }
WAREHOUSE = <warehouse_name>
[ EXTERNAL_VOLUME = '<external_volume_name>' ]
[ CATALOG = 'SNOWFLAKE' ]
BASE_LOCATION = '<directory_for_table_files>'
[ REFRESH_MODE = { AUTO | FULL | INCREMENTAL } ]
[ INITIALIZE = { ON_CREATE | ON_SCHEDULE } ]
[ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
[ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
[ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
[ COMMENT = '<string_literal>' ]
[ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
[ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
AS <query>
For more information about usage and limitations, see Create dynamic Iceberg tables.
Required parameters¶
name
Specifies the identifier (i.e. name) for the dynamic table; must be unique for the schema in which the dynamic table is created.
In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g.
"My object"
). Identifiers enclosed in double quotes are also case-sensitive.For more details, see Identifier requirements.
TARGET_LAG = { num { seconds | minutes | hours | days } | DOWNSTREAM }
Specifies the lag for the dynamic table:
'num seconds | minutes | hours | days'
Specifies the maximum amount of time that the dynamic table’s content should lag behind updates to the base tables.
For example:
If the data in the dynamic table should lag by no more than 5 minutes, specify
5 minutes
.If the data in the dynamic table should lag by no more than 5 hours, specify
5 hours
.
The minimum value is 1 minute. If the dynamic table A depends on another dynamic table B, the minimum lag for A must be greater than or equal to the lag for B.
DOWNSTREAM
Specifies that the dynamic table should be refreshed only when dynamic tables that depend on it are refreshed.
WAREHOUSE = warehouse_name
Specifies the name of the warehouse that provides the compute resources for refreshing the dynamic table.
You must use a role that has the USAGE privilege on this warehouse in order to create the dynamic table. For limitations and more information, see Privileges to create a dynamic table.
AS query
Specifies the query whose results the dynamic table should contain.
Optional parameters¶
TRANSIENT
Specifies that the table is transient.
Like permanent dynamic tables, transient dynamic tables exist until they’re explicitly dropped, and are available to any user with the appropriate privileges. Transient dynamic tables don’t retain data in fail-safe storage, which helps reduce storage costs, especially for tables that refresh frequently. Due to this reduced level of durability, transient dynamic tables are best used for transitory data that doesn’t need the same level of data protection and recovery provided by permanent tables.
Default: No value. If a dynamic table is not declared as
TRANSIENT
, it is permanent.REFRESH_MODE = { AUTO | FULL | INCREMENTAL }
Specifies the refresh mode for the dynamic table. This property cannot be altered after you create the dynamic table. To modify the property, replicate the dynamic table with a CREATE OR REPLACE DYNAMIC TABLE command.
AUTO
Enforces an incremental refresh of the dynamic table by default. If the CREATE DYNAMIC TABLE statement does not support the incremental refresh mode, the dynamic table is automatically created with the full refresh mode.
To determine the best mode for your use case, experiment with refresh modes and automatic recommendations. For consistent behavior across Snowflake releases, explicitly set the refresh mode on all dynamic tables.
To verify the refresh mode for your dynamic tables, see View dynamic table refresh mode.
FULL
Enforces a full refresh of the dynamic table, even if the dynamic table can be incrementally refreshed.
INCREMENTAL
Enforces an incremental refresh of the dynamic table. If the query that underlies the dynamic table can’t perform an incremental refresh, dynamic table creation fails and displays an error message.
Default:
AUTO
INITIALIZE
Specifies the behavior of the initial refresh of the dynamic table. This property cannot be altered after you create the dynamic table. To modify the property, replace the dynamic table with a CREATE OR REPLACE DYNAMIC TABLE command.
ON_CREATE
Refreshes the dynamic table synchronously at creation. If this refresh fails, dynamic table creation fails and displays an error message.
ON_SCHEDULE
Refreshes the dynamic table at the next scheduled refresh.
The dynamic table is populated when the refresh schedule process runs. No data is populated when the dynamic table is created. If you try to query the table using
SELECT * FROM DYNAMIC TABLE
, you might see the following error because the first scheduled refresh has not yet occured.Dynamic Table is not initialized. Please run a manual refresh or wait for a scheduled refresh before querying.
Default:
ON_CREATE
COMMENT 'string_literal'
Specifies a comment for the column.
(Note that comments can be specified at the column level or the table level. The syntax for each is slightly different.)
MASKING POLICY = policy_name
Specifies the masking policy to set on a column.
column_list
If you want to change the name of a column or add a comment to a column in the the dynamic table, include a column list that specifies the column names and, if needed, comments about the columns. You do not need to specify the data types of the columns.
If any of the columns in the dynamic table are based on expressions - for example, not simple column names - then you must supply a column name for each column in the dynamic table. For instance, the column names are required in the following case:
CREATE DYNAMIC TABLE product (pre_tax_profit, taxes, after_tax_profit) TARGET_LAG = '20 minutes' WAREHOUSE = mywh AS SELECT revenue - cost, (revenue - cost) * tax_rate, (revenue - cost) * (1.0 - tax_rate) FROM staging_table;
You can specify an optional comment for each column. For example:
CREATE DYNAMIC TABLE product (pre_tax_profit COMMENT 'revenue minus cost', taxes COMMENT 'assumes taxes are a fixed percentage of profit', after_tax_profit) TARGET_LAG = '20 minutes' WAREHOUSE = mywh AS SELECT revenue - cost, (revenue - cost) * tax_rate, (revenue - cost) * (1.0 - tax_rate) FROM staging_table;
CLUSTER BY ( expr [ , expr , ... ] )
Specifies one or more columns or column expressions in the dynamic table as the clustering key. Before you specify a clustering key for a dynamic table, you should understand micro-partitions. For more information, see Understanding Snowflake Table Structures.
Note the following when using clustering keys with dynamic tables:
Column definitions are required and must be explicitly specified in the statement.
By default, Automatic Clustering is not suspended for the new dynamic table, even if Automatic Clustering is suspended for the source table.
Clustering keys are not intended or recommended for all tables; they typically benefit very large (for example multi-terabyte) tables.
Specifying CLUSTER BY doesn’t cluster the data at creation time; instead, CLUSTER BY relies on Automatic Clustering to recluster the data over time.
For more information, see Clustering Keys & Clustered Tables.
Default: No value (no clustering key is defined for the table)
DATA_RETENTION_TIME_IN_DAYS = integer
Specifies the retention period for the dynamic table so that Time Travel actions (SELECT, CLONE) can be performed on historical data in the dynamic table. Time Travel behaves identically for dynamic tables as it does for traditional tables. For more details, see Understanding & using Time Travel.
For a detailed description of this object-level parameter, as well as more information about object parameters, see Parameters.
Values:
Standard Edition:
0
or1
Enterprise Edition:
0
to90
for permanent tables0
or1
for temporary and transient tables
Default:
Standard Edition:
1
Enterprise Edition (or higher):
1
(unless a different default value was specified at the schema, database, or account level)
Note
A value of
0
effectively disables Time Travel for the table.MAX_DATA_EXTENSION_TIME_IN_DAYS = integer
Object parameter that specifies the maximum number of days for which Snowflake can extend the data retention period for the dynamic table to prevent streams on the dynamic table from becoming stale.
For a detailed description of this parameter, see MAX_DATA_EXTENSION_TIME_IN_DAYS.
COMMENT = 'string_literal'
Specifies a comment for the dynamic table.
(Note that comments can be specified at the column level or the table level. The syntax for each is slightly different.)
Default: No value.
COPY GRANTS
Specifies to retain the access privileges from the original dynamic table when a new dynamic table is created using the CREATE DYNAMIC TABLE … CLONE variant.
This parameter copies all privileges, except OWNERSHIP, from the existing dynamic table to the new dynamic table. The new dynamic table does not inherit any future grants defined for the object type in the schema. By default, the role that executes the CREATE DYNAMIC TABLE statement owns the new dynamic table.
If this parameter is not included in the CREATE DYNAMIC TABLE statement, then the new table does not inherit any explicit access privileges granted on the original dynamic table, but does inherit any future grants defined for the object type in the schema.
Note:
With data sharing:
If the existing dynamic table was shared to another account, the replacement dynamic table is also shared.
If the existing dynamic table was shared with your account as a data consumer, and access was further granted to other roles in the account (using
GRANT IMPORTED PRIVILEGES
on the parent database), access is also granted to the replacement dynamic table.
The SHOW GRANTS output for the replacement dynamic table lists the grantee for the copied privileges as the role that executed the CREATE TABLE statement, with the current timestamp when the statement was executed.
The SHOW GRANTS output for the replacement dynamic table lists the grantee for the copied privileges as the role that executed the CREATE TABLE statement, with the current timestamp when the statement was executed.
The operation to copy grants occurs atomically in the CREATE DYNAMIC TABLE command (i.e. within the same transaction).
ROW ACCESS POLICY policy_name ON ( col_name [ , col_name ... ] )
Specifies the row access policy to set on a dynamic table.
TAG ( tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ] )
Specifies the tag name and the tag string value.
The tag value is always a string, and the maximum number of characters for the tag value is 256.
For information about specifying tags in a statement, see Tag quotas for objects and columns.
Access control requirements¶
A role used to execute this SQL command must have the following privileges at a minimum:
Privilege |
Object |
Notes |
---|---|---|
CREATE DYNAMIC TABLE |
Schema in which you plan to create the dynamic table. |
|
SELECT |
Tables, views, and dynamic tables that you plan to query for the new dynamic table. |
|
USAGE |
Warehouse that you plan to use to refresh the table. |
Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.
For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.
For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.
Usage notes¶
When you execute the CREATE DYNAMIC TABLE command, the current role in use becomes the owner of the dynamic table. This role is used to perform refreshes of the dynamic table in the background.
You cannot make changes to the schema after you create a dynamic table.
Dynamic tables are updated as underlying database objects change. Change tracking must be enabled on all underlying objects used by a dynamic table. See Enable change tracking.
If you want to replace an existing dynamic table and need to see its current definition, call the GET_DDL function.
Using ORDER BY in the definition of a dynamic table might produce results sorted in an unexpected order. You can use ORDER BY when querying your dynamic table to ensure that rows selected return in a specific order.
Snowflake doesn’t support using ORDER BY to create a view that selects from a dynamic table.
Some expressions, clauses, and functions are not currently supported in dynamic tables. For a complete list, see Known limitations for dynamic tables.
Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.
Examples¶
Create a dynamic table named product
:
CREATE OR REPLACE DYNAMIC TABLE product TARGET_LAG = '20 minutes' WAREHOUSE = mywh AS SELECT product_id, product_name FROM staging_table;
In the example above:
The dynamic table materializes the results of a query of the
product_id
andproduct_name
columns of thestaging_table
table.The target lag time is 20 minutes, which means that the data in the dynamic table should ideally be no more than 20 minutes older than the data in
staging_table
.The automated refresh process uses the compute resources in warehouse
mywh
to refresh the data in the dynamic table.
Create a dynamic Iceberg table named product
that reads from my_iceberg_table
:
CREATE DYNAMIC ICEBERG TABLE product (date TIMESTAMP_NTZ, id NUMBER, content STRING) TARGET_LAG = '20 minutes' WAREHOUSE = mywh EXTERNAL_VOLUME = 'my_external_volume' CATALOG = 'SNOWFLAKE' BASE_LOCATION = 'my_iceberg_table' AS SELECT product_id, product_name FROM staging_table;
Create a table with a multi-column clustering key:
CREATE DYNAMIC TABLE product (date TIMESTAMP_NTZ, id NUMBER, content VARIANT) TARGET_LAG = '20 minutes' WAREHOUSE = mywh CLUSTER BY (date, id) AS SELECT product_id, product_name FROM staging_table;
Clone a dynamic table as it existed exactly at the date and time of the specified timestamp:
CREATE DYNAMIC TABLE product_clone CLONE product AT (TIMESTAMP => TO_TIMESTAMP_TZ('04/05/2013 01:02:03', 'mm/dd/yyyy hh24:mi:ss'));