CREATE DYNAMIC TABLE | Snowflake Documentation (original) (raw)

Creates a dynamic table, based on a specified query.

Syntax¶

CREATE [ OR REPLACE ] [ TRANSIENT ] DYNAMIC TABLE [ IF NOT EXISTS ] ( -- Column definition [ [ WITH ] MASKING POLICY [ USING ( , , ... ) ] ] [ [ WITH ] PROJECTION POLICY ] [ [ WITH ] TAG ( = '' [ , = '' , ... ] ) ] [ COMMENT '' ]

-- Additional column definitions
[ , <col_name> <col_type> [ ... ] ]

) TARGET_LAG = { ' { seconds | minutes | hours | days }' | DOWNSTREAM } WAREHOUSE = [ REFRESH_MODE = { AUTO | FULL | INCREMENTAL } ] [ INITIALIZE = { ON_CREATE | ON_SCHEDULE } ] [ CLUSTER BY ( [ , , ... ] ) ] [ DATA_RETENTION_TIME_IN_DAYS = ] [ MAX_DATA_EXTENSION_TIME_IN_DAYS = ] [ COMMENT = '' ] [ [ WITH ] ROW ACCESS POLICY ON ( [ , ... ] ) ] [ [ WITH ] AGGREGATION POLICY [ ENTITY KEY ( [ , ... ] ) ] ] [ [ WITH ] TAG ( = '' [ , = '' , ... ] ) ] [ REQUIRE USER ] AS

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. The cloned dynamic table inherits the source’s scheduling state.

You can also clone a dynamic table as it existed at a specific point in the past. For more information, see Cloning considerations.

CREATE [ OR REPLACE ] [ TRANSIENT ] DYNAMIC TABLE CLONE [ { AT | BEFORE } ( { TIMESTAMP => | OFFSET => | STATEMENT => } ) ] [ COPY GRANTS TARGET_LAG = { ' { seconds | minutes | hours | days }' | DOWNSTREAM } WAREHOUSE = ]

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 … CLONE.

CREATE DYNAMIC ICEBERG TABLE¶

Creates a new dynamic Apache Iceberg™ table. For information about Iceberg tables, seeApache Iceberg™ tables and CREATE ICEBERG TABLE (Snowflake as the Iceberg catalog).

CREATE [ OR REPLACE ] DYNAMIC ICEBERG TABLE ( -- Column definition [ [ WITH ] MASKING POLICY [ USING ( , , ... ) ] ] [ [ WITH ] TAG ( = '' [ , = '' , ... ] ) ] [ COMMENT '' ]

-- Additional column definitions [ , [ ... ] ]

) TARGET_LAG = { ' { seconds | minutes | hours | days }' | DOWNSTREAM } WAREHOUSE = [ EXTERNAL_VOLUME = '' ] [ CATALOG = 'SNOWFLAKE' ] [ BASE_LOCATION = '' ] [ REFRESH_MODE = { AUTO | FULL | INCREMENTAL } ] [ INITIALIZE = { ON_CREATE | ON_SCHEDULE } ] [ CLUSTER BY ( [ , , ... ] ) ] [ DATA_RETENTION_TIME_IN_DAYS = ] [ MAX_DATA_EXTENSION_TIME_IN_DAYS = ] [ COMMENT = '' ] [ [ WITH ] ROW ACCESS POLICY ON ( [ , ... ] ) ] [ [ WITH ] TAG ( = '' [ , = '' , ... ] ) ] [ REQUIRE USER ] AS

For more information about usage and limitations, seeCreate dynamic Apache 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 source 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.

If the dynamic table depends on another dynamic table, the minimum target lag must be greater than or equal to the target lag of the dynamic table it depends on.

DOWNSTREAM

Specifies that the dynamic table should be refreshed only when dynamic tables that depend on it are refreshed.

WAREHOUSE = _warehousename_

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, recreate the dynamic table with a CREATE OR REPLACE DYNAMIC TABLE command.

AUTO

When refresh mode is AUTO, the system attempts to apply an incremental refresh by default. However, when incremental refresh isn’t supported or expected to perform well, the dynamic table automatically selects full refresh instead. For more information, seeLimitations on incremental refresh.

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

Dynamic Table is not initialized. Please run a manual refresh or wait for a scheduled refresh before querying.

Default: ON_CREATE

COMMENT '_stringliteral_'

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 = _policyname_

Specifies the masking policy to set on a column.

PROJECTION POLICY _policyname_

Specifies the projection policy to set on a column.

_columnlist_

If you want to change the name of a column or add a comment to a column in 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 the same way for dynamic tables as it behaves for traditional tables. For more information, see Understanding & using Time Travel.

For a detailed description of this object-level parameter, as well as more information about object parameters, seeParameters.

Values:

Standard Edition: 0 or 1
Enterprise Edition:
- 0 to 90 for permanent tables
- 0 or 1 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_

An object parameter that sets the maximum number of days Snowflake can extend the data retention period 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 = '_stringliteral_'

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

Important

The COPY GRANTS parameter can be placed anywhere in a CREATE [ OR REPLACE ] DYNAMIC TABLE command, except after the query definition.

For example, the following dynamic table will fail to create:

CREATE OR REPLACE DYNAMIC TABLE product TARGET_LAG = DOWNSTREAM WAREHOUSE = mywh AS SELECT * FROM staging_table COPY GRANTS;

ROW ACCESS POLICY _policyname_ ON ( _colname_ [ , _colname_ ... ] )

Specifies the row access policy to set on a dynamic table.

TAG ( _tagname_ = '_tagvalue_' [ , _tagname_ = '_tagvalue_' , ... ] )

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.

AGGREGATION POLICY _policyname_ [ ENTITY KEY ( _colname_ [ , _colname_ ... ] ) ]

Specifies an aggregation policy to set on a dynamic table. You can apply one or more aggregation policies on a table.

Use the optional ENTITY KEY parameter to define which columns uniquely identity an entity within the dynamic table. For more information, see Implementing entity-level privacy with aggregation policies. You can specify one or more entity keys for an aggregation policy.

REQUIRE USER

When specified, the dynamic table cannot run unless a user is specified. The dynamic table is not able to refresh unless a user is set in a manual refresh with the COPY SESSION parameter specified.

If this option is enabled, the dynamic table must be created with the ON_SCHEDULE parameter forINITIALIZE.

Access control requirements¶

A role used to execute this operation must have the followingprivileges 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.

The USAGE privilege on the parent database and schema are required to perform operations on any object in a 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 onsecurable 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. SeeEnable 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.
The OR REPLACE and IF NOT EXISTS clauses are mutually exclusive. They can’t both be used in the same statement.
CREATE OR REPLACE statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.

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 and product_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'));

Configure a dynamic table to require a user for refreshes and then refresh the dynamic table:

CREATE DYNAMIC TABLE product TARGET_LAG = 'DOWNSTREAM' WAREHOUSE = mywh INITIALIZE = on_schedule REQUIRE USER AS SELECT product_id, product_name FROM staging_table;

ALTER DYNAMIC TABLE product REFRESH COPY SESSION;