CREATE DYNAMIC TABLE¶

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 [ 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>
  ]

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 have the USAGE privilege on this warehouse in order to create the 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 type 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.

You can verify the refresh mode using the SHOW DYNAMIC TABLES statement. The refresh_mode column shows the refresh mode in use, while the text column shows the user-specified 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;

Copy

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;

Copy

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

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:

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;

Copy

In the example above:

• The dynamic table materializes the results of a query of the product_id and product_name columns of the staging_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 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;

Copy

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

Copy