CREATE DYNAMIC TABLE¶

Creates a dynamic table, based on a specified query.

This command supports the following variants:

• CREATE OR ALTER DYNAMIC TABLE: Creates a dynamic table if it doesn’t exist or alters an existing dynamic table.
• CREATE DYNAMIC TABLE FROM BACKUP SET: Restores a dynamic table from a back up.
• CREATE DYNAMIC TABLE …CLONE: Creates a clone of an existing dynamic table.
• CREATE DYNAMIC ICEBERG TABLE: Creates a dynamic Apache Iceberg™ table.

See also:

ALTER DYNAMIC TABLE, DESCRIBE DYNAMIC TABLE, DROP DYNAMIC TABLE , SHOW DYNAMIC TABLES, CREATE OR ALTER <object>

语法

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 ] PROJECTION POLICY <policy_name> ]
      [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
      [ COMMENT '<string_literal>' ]
      [ WITH CONTACT ( <purpose> = <contact_name> [ , <purpose> = <contact_name> ... ] ) ]

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

  )
  TARGET_LAG = { '<num> { seconds | minutes | hours | days }' | DOWNSTREAM }
  [ SCHEDULER = DISABLE | ENABLE ]
  WAREHOUSE = <warehouse_name>
  [ INITIALIZATION_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>' ]
  [ COPY GRANTS ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
  [ [ WITH ] AGGREGATION POLICY <policy_name> [ ENTITY KEY ( <col_name> [ , <col_name> ... ] ) ] ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ REQUIRE USER ]
  [ IMMUTABLE WHERE ( <expr> ) ]
  [ BACKFILL FROM ]
  [ EXECUTE AS USER <user_name>
    [ USE SECONDARY ROLES { ALL | NONE | <role> [ , ... ] } ]
  ]
  [ ROW_TIMESTAMP = { TRUE | FALSE } ]
  AS <query>

变体语法

CREATE OR ALTER DYNAMIC TABLE¶

CREATE OR ALTER DYNAMIC TABLE <name> (
  -- Column definition
  <col_name> <col_type>
    [ COLLATE '<collation_specification>' ]
    [ COMMENT '<string_literal>' ]

  -- Additional column definitions
  [ , <col_name> <col_type> [ ... ] ]
  )
  TARGET_LAG = { '<num> { seconds | minutes | hours | days }' | DOWNSTREAM }
  [ SCHEDULER = DISABLE | ENABLE ]
  WAREHOUSE = <warehouse_name>
  [ REFRESH_MODE = { AUTO | FULL | INCREMENTAL } ]
  [ IMMUTABLE WHERE ( <expr> ) ]
  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
  [ COMMENT = '<string_literal>' ]
  [ ROW_TIMESTAMP = { TRUE | FALSE } ]
  AS <query>

Creates a dynamic table if it doesn’t exist, or alters it according to the dynamic table definition. The CREATE OR ALTER DYNAMIC TABLE syntax follows the rules of a CREATE DYNAMIC TABLE statement and has the same limitations as an ALTER DYNAMIC TABLE statement.

For more information, see CREATE OR ALTER <object>.

Changes to the following dynamic table properties and parameters preserve data:

• TARGET_LAG
• WAREHOUSE
• CLUSTER BY
• DATA_RETENTION_TIME_IN_DAYS
• MAX_DATA_EXTENSION_TIME_IN_DAYS
• COMMENT
• IMMUTABLE WHERE
  • When specified, only the mutable region is reinitialized and data in the immutable region is preserved. For more information, see Understanding immutability constraints.

Changes to the following dynamic table properties and parameters trigger a reinitialization:

• REFRESH_MODE
• Changes to the query or column list:
  • Dropping existing columns is supported.
  • Adding new columns is supported, but they can only be added at the end of existing columns.
  • Dropping columns that are used in an IMMUTABLE WHERE predicate or as clustering keys isn’t supported.

For more information, see CREATE OR ALTER TABLE usage notes.

CREATE DYNAMIC TABLE FROM BACKUP SET¶

CREATE DYNAMIC TABLE <name> FROM BACKUP SET <backup_set> IDENTIFIER '<backup_id>'

The FROM BACKUP SET clause restores a dynamic table from a backup. You don’t specify other table properties because they’re all the same as in the backed-up table.

This form doesn’t have a CREATE OR REPLACE clause. You typically either restore the dynamic table under a new name and recover any data or other objects from this new table, or rename the original table and then restore the table under the original name.

For more information about backups, see Backups for disaster recovery and immutable storage.

backup_set

Specifies the name of a backup set created for a specific dynamic table. You can use the SHOW BACKUP SETS command to locate the right backup set.

backup_id

Specifies the identifier of a specific backup within that backup set. You can use the SHOW BACKUPS IN BACKUP SET command to locate the right identifier within the backup set, based on the creation date and time for the backup.

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.

Cloned dynamic tables, whether cloned directly or as part of a cloned database or schema, are suspended by default. In DYNAMIC_TABLE_GRAPH_HISTORY, this appears as CLONED_AUTO_SUSPENDED in the SCHEDULING_STATE column. Any downstream dynamic tables are also suspended, shown as UPSTREAM_CLONED_AUTO_SUSPENDED. For more information, see Automatic dynamic table suspension.

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 <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>
    EXECUTE AS USER <user_name>
      USE SECONDARY ROLES { ALL | NONE | <role> [ , ... ] }
  ]

如果源动态表具有群集密钥，则克隆的动态表具有群集密钥。默认情况下，即使来源表的自动聚类未暂停，新表的自动聚类也会暂停。

For more details about cloning, see CREATE <object> … CLONE.

CREATE DYNAMIC ICEBERG TABLE¶

Creates a new dynamic Apache Iceberg™ table. For information about Iceberg tables, see Apache 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 = '<optional_directory_for_table_files>' ]
[ TARGET_FILE_SIZE = '{ AUTO | 16MB | 32MB | 64MB | 128MB }' ]
[ PARTITION BY ( partitionExpression [, partitionExpression , ...] ) ]
[ PATH_LAYOUT = { FLAT | HIERARCHICAL } ]
[ ICEBERG_VERSION = <integer> ]
[ REFRESH_MODE = { AUTO | FULL | INCREMENTAL } ]
[ IMMUTABLE WHERE ( <expr> ) ]
[ 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>' ]
[ COPY GRANTS ]
[ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
[ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
[ REQUIRE USER ]
[ EXECUTE AS USER <user_name>
  [ USE SECONDARY ROLES { ALL | NONE | <role> [ , ... ] } ]
]
AS <query>

Where:

partitionExpression ::=
  <col_name> -- identity transform
  | BUCKET ( <num_buckets> , <col_name> )
  | TRUNCATE ( <width> , <col_name> )
  | YEAR ( <col_name> )
  | MONTH ( <col_name> )
  | DAY ( <col_name> )
  | HOUR ( <col_name> )

For more information about usage and limitations, see Create dynamic Apache Iceberg™ tables.

必填参数

name

指定动态表的标识符（即名称）；对于在其中创建动态表的架构必须唯一。

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 }

为动态表指定滞后时间：

'num seconds | minutes | hours | days'

指定动态表内容应滞后于源表更新的最长时间。

例如：

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

必须至少为 60 秒。如果该动态表依赖于另一个动态表，则最小目标滞后必须大于或等于所依赖动态表的目标滞后。

DOWNSTREAM

指定动态表只有在依赖于它的动态表被刷新时才被刷新。

Required when SCHEDULER = ENABLE.

For information on how target lag affects refresh frequency and costs, see Identify the right target lag.

WAREHOUSE = warehouse_name

指定提供计算资源以刷新动态表的仓库的名称。

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.

For guidance on choosing a warehouse for optimal refresh performance, see Adjust your warehouse configuration.

AS query

指定动态表应包含其结果的查询。

可选参数

SCHEDULER = { DISABLE | ENABLE }

Specifies whether the dynamic table is to be refreshed automatically by Snowflake’s dynamic table scheduler.

DISABLE

Excludes the dynamic table from automatic background refresh. The table isn’t refreshed on a schedule, either directly or through downstream dependencies.

• Manual control: Refreshing must be triggered manually by using ALTER DYNAMIC TABLE ... REFRESH.
• Isolation: A manual refresh of a disabled table doesn’t automatically refresh its upstream dependencies. This creates a “isolation boundary,” allowing external orchestrators, like dbt, to manage specific table refreshes in isolation without triggering the entire pipeline.
• TARGET_LAG can’t be defined when SCHEDULER = DISABLE.

ENABLE

Enables the automated background scheduler for the dynamic table. The scheduler ensures that the table is refreshed alongside its dependencies to maintain snapshot consistency. In this mode, Snowflake automatically calculates the optimal refresh frequency based on the defined TARGET_LAG. With this setting, TARGET_LAG must be set.

If not specified, the dynamic table is scheduler-managed by default. SHOW DYNAMIC TABLES displays NULL for the SCHEDULER column when the attribute isn’t explicitly set.

INITIALIZATION_WAREHOUSE = warehouse_name

Specifies a warehouse to use for all dynamic table initializations and reinitializations.

If this parameter isn’t included in the CREATE DYNAMIC TABLE statement, the dynamic table uses the warehouse that is specified by the required WAREHOUSE parameter for all refreshes.

You must use a role that has the USAGE privilege on this warehouse for you to create the dynamic table. For limitations and more information, see Privileges to create a dynamic table.

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.

创建动态表后无法更改此属性。要修改属性，请使用 CREATE OR REPLACE DYNAMIC TABLE 命令重新创建动态表。

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, see Dynamic table refresh modes and Choose a refresh mode.

要确定适合您用例的最佳模式，请尝试不同的刷新模式和自动建议。要在不同的 Snowflake 版本中保持一致的行为，可在所有动态表上显式设置刷新模式。

To verify the refresh mode for your dynamic tables, see Refresh mode.

FULL

强制动态表完全刷新，即使动态表可以增量刷新。

INCREMENTAL

强制动态表的增量刷新。如果作为动态表基础的查询无法执行增量刷新，则动态表创建失败并显示错误消息。

For information about how operators affect incremental refresh, see Optimize queries for incremental refresh.

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

创建时同步刷新动态表。如果此刷新失败，则动态表创建失败并显示错误消息。

ON_SCHEDULE

在下次计划刷新时刷新动态表。

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.

Expand code block

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

Show lessSee more

Scroll to top

Default: ON_CREATE

COMMENT 'string_literal'

指定列的注释。

（请注意，可以在列级别或表级别指定注释。相应的语法略有不同。）

MASKING POLICY = policy_name

Specifies the masking policy to set on a column.

PROJECTION POLICY policy_name

Specifies the projection policy to set on a column.

This parameter is not supported by the CREATE OR ALTER variant syntax.

column_list

如果您希望在动态表中更改列的名称或向列添加注释，请包含一个列列表，该列表指定列名和（如果需要）关于列的注释。无需指定列的数据类型。

如果动态表中的任何列都基于表达式（例如，不仅是简单的列名），则必须为动态表中的每个列提供列名。例如，在以下情况下，列名是必需的：

Copy codeExpand code block

CREATE DYNAMIC TABLE my_dynamic_table (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;

Show lessSee more

Scroll to top

您可以为每列指定可选注释。例如：

Copy codeExpand code block

CREATE DYNAMIC TABLE my_dynamic_table (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;

Show lessSee more

Scroll to top

PARTITION BY ( partitionExpression [ , partitionExpression , ... ] )

Specifies one or more partition expressions for the dynamic Iceberg table. For parameter details, see Partition expression parameters (`partitionExpression`) in CREATE ICEBERG TABLE (Snowflake as the Iceberg catalog).

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.

将群集密钥与动态表一起使用时请注意以下几点：

• 列定义是必需的，必须在语句中明确指定。
• 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.

默认：无值（未为表定义群集密钥）

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, see Parameters.

值：

• Standard Edition: 0 or 1
• Enterprise Edition：
  • 0 to 90 for permanent tables
  • 0 or 1 for temporary and transient tables

默认：

• 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

一个对象参数，用于设置 Snowflake 可延长数据保留期的最长天数，以防止动态表上的数据流过时。

For a detailed description of this parameter, see MAX_DATA_EXTENSION_TIME_IN_DAYS.

COMMENT = 'string_literal'

指定动态表的注释。

（请注意，可以在列级别或表级别指定注释。相应的语法略有不同。）

默认：无值。

COPY GRANTS

指定在使用以下 CREATE DYNAMIC TABLE 变体创建新动态表时保留原始表的访问权限：

• CREATE OR REPLACE DYNAMIC TABLE
• CREATE OR REPLACE DYNAMIC ICEBERG TABLE
• CREATE OR REPLACE DYNAMIC TABLE … CLONE

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.

如果语句要替换同名的现有表，则从要替换的表中复制授权。如果没有该名称的现有表，则复制授权。

For example, the following statement creates a dynamic table dt1 cloned from dt0 with all grants copied from dt0. The first time you run the command, dt1 copies all grants from dt0. If you run the same command again, dt1 will copy all grants from dt1 and not dt0.

Copy codeExpand code block

CREATE OR REPLACE DYNAMIC TABLE dt1 CLONE dt0
  COPY GRANTS;

Show lessSee more

Scroll to top

请注意以下事项：

• With data sharing:

  • 如果现有动态表已共享到另一个账户，则替换动态表也会共享。
  • 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.

• 复制授权的操作在 CREATE DYNAMIC TABLE 命令中会以原子方式发生（即在同一事务中）。

Important

COPY GRANTS 参数可以放在 CREATE [ OR REPLACE ] DYNAMIC TABLE 命令中的任何地方，查询定义之后除外。

例如，以下动态表将无法创建：

Copy codeExpand code block

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

Show lessSee more

Scroll to top

ROW ACCESS POLICY policy_name ON ( col_name [ , col_name ... ] )

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

AGGREGATION POLICY policy_name [ ENTITY KEY ( col_name [ , col_name ... ] ) ]

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.

This parameter is not supported by the CREATE OR ALTER variant syntax.

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 for INITIALIZE.

IMMUTABLE WHERE

Specifies a condition that defines the immutable portion of the dynamic table. For more information, see Understanding immutability constraints.

BACKFILL FROM <name>

指定要从中回填数据的表。

Only data defined by the IMMUTABLE WHERE immutability constraint can be backfilled because the backfill data must remain unchanged, even if it differs from the upstream source.

For more information, see Backfill examples.

EXECUTE AS USER user_name

Refreshes the dynamic table as the specified user.

To specify EXECUTE AS USER, you must use a role that has been granted the IMPERSONATE privilege on the user_name user. To grant this privilege, run the GRANT <privileges> … TO ROLE command.

USE SECONDARY ROLES \{ ALL | NONE | <role> [ , ... ] \}

Specifies the secondary roles to use on the dynamic table. Can be used to override the default secondary roles that are otherwise used in execution.

Can only be used with the EXECUTE AS USER option.

For more information, see Refresh dynamic tables with specific user privileges and secondary roles.

ROW_TIMESTAMP = { TRUE | FALSE }

Specifies whether to enable row timestamps on the table. You must use a role with the OWNERSHIP privilege.

For more information, see Use row timestamps to measure latency in your pipelines.

WITH DATA METRIC FUNCTION dmf_name ON ( col_name [ , col_name ... ] ) [ , dmf_binding ... ]

Associates one or more data metric functions (DMFs) with the dynamic table at creation time. The DMF begins running on the schedule configured for the table as soon as it is created.

You can attach multiple DMF bindings by separating them with commas. Each binding accepts the same properties as ALTER TABLE … ADD DATA METRIC FUNCTION, including EXECUTE AS ROLE, ANOMALY_DETECTION, SENSITIVITY, DATA_QUALITY_NOTIFICATION, and EXPECTATION.

For a description of each property, see Data metric function actions.

For usage guidance and examples, see Attach DMFs at creation time.

Default: No value (no DMF is associated with the dynamic table)

访问控制要求

使用说明

• 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.
• 创建动态表后不能对架构进行变更。
• 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.
• To influence the order in which rows are stored in a dynamic table, consider enabling clustering.
• Some expressions, clauses, and functions are not currently supported in dynamic tables. For a complete list, see Dynamic table limitations.
• You can use DYNAMIC_TABLE_REFRESH_BOUNDARY() in the definition query to prevent an upstream dynamic table from being refreshed together with this dynamic table. The upstream dynamic table is treated as belonging to a separate pipeline, which means cascading refreshes and snapshot isolation do not apply across the boundary. For more information, see Dynamic table refresh boundary.
• Using OR REPLACE is the equivalent to using DROP DYNAMIC TABLE on the existing dynamic table and then creating a new dynamic table with the same name. However, Snowflake doesn’t drop the old dynamic table until it has created the new dynamic table, including the initial refresh if INITIALIZE = ON_CREATE is specified. Instead, the new dynamic table is created as a hidden table, the refresh is run, then Snowflake atomically swaps it in for the existing dynamic table.

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

CREATE OR ALTER DYNAMIC TABLE usage notes¶

• All limitations of the ALTER DYNAMIC TABLE command apply.

Limitations¶

The following actions aren’t supported:

• Swapping dynamic tables by using the SWAP WITH parameter.
• Renaming a dynamic table by using the RENAME TO parameter.
• Creating a clone of a dynamic table by using the CLONE parameter.
• Suspending or resuming by using the SUSPEND and RESUME parameters.
• Converting a TRANSIENT dynamic table into a non-TRANSIENT dynamic table, or vice versa.
• Adding or changing tags and policies. Any existing tags and policies are preserved, and other statements might still add or remove tags and policies.
• Creating or altering dynamic Apache Iceberg™ tables.
• Time Travel clone for times that are before the latest definition or refresh mode change.

Additionally, modifying the values for the REFRESH_MODE and INITIALIZE properties after the dynamic table has been created isn’t supported. You can switch between the AUTO refresh mode and the specific INCREMENTAL and FULL refresh modes, but doing so doesn’t change the actual physical refresh mode of the dynamic table.

例如：

• If you create a dynamic table with AUTO refresh mode, the system immediately assigns a concrete mode (INCREMENTAL or FULL). When you run a subsequent CREATE OR ALTER DYNAMIC TABLE statement, you can specify AUTO or the concrete refresh mode that is chosen by the engine at creation. However, this doesn’t alter the assigned refresh mode; it remains the same.
• If you create a dynamic table with a specific refresh mode (INCREMENTAL or FULL), you can later specify AUTO in a CREATE OR ALTER DYNAMIC TABLE statement to enable forward compatibility. For example, if your dynamic table was created with FULL mode and is version-controlled, specifying AUTO in a CREATE OR ALTER DYNAMIC TABLE statement enables new tables to use AUTO, while existing tables remain in FULL mode without breaking compatibility.

No implicit refreshes¶

If you change an existing dynamic table by using the CREATE OR ALTER DYNAMIC TABLE command, the command doesn’t trigger a refresh of the dynamic table. The dynamic table is refreshes according to its normal schedule.

However, if you create a new dynamic table by using the CREATE OR ALTER DYNAMIC TABLE command and you specify INITIALIZE = ON_CREATE, the command triggers a refresh of the dynamic table.

Atomicity¶

The CREATE OR ALTER DYNAMIC TABLE command doesn’t guarantee atomicity. This means that if a CREATE OR ALTER DYNAMIC TABLE statement fails during execution, it’s possible that a subset of changes might have been applied to the table. If there’s a possibility of partial changes, in most cases, the error message includes the following text:

CREATE OR ALTER execution failed. Partial updates may have been applied.

For example, suppose that you wanted to change the TARGET_LAG property and add a clustering key for a dynamic table, but you change your mind and terminate the statement. In this case, the TARGET_LAG property might still change while the clustering key isn’t applied.

When changes are partially applied, the resulting table is in a valid state. In the previous example, you can use additional ALTER DYNAMIC TABLE statements to complete the original set of changes.

To recover from partial updates, try the following recovery methods:

• Fix forward: Re-execute the CREATE OR ALTER DYNAMIC TABLE statement. If the statement succeeds on the second attempt, the target state is achieved.

  If the statement doesn’t succeed, investigate the error message. If possible, fix the error and re-execute the CREATE OR ALTER DYNAMIC TABLE statement.

• Roll back: If it isn’t possible to fix forward, manually roll back the partial changes:

  • Investigate the state of the table by using the DESCRIBE DYNAMIC TABLE and SHOW DYNAMIC TABLES commands. Determine which partial changes were applied, if any.

    If partial changes were applied, execute the appropriate ALTER DYNAMIC TABLE statements to transform the dynamic table back to its original statement.

For additional help, contact Snowflake Support.

IMMUTABLE WHERE usage notes¶

• You can set only one IMMUTABLE WHERE predicate per dynamic table. Setting another predicate replaces the existing one.

• IMMUTABLE WHERE constraints can’t contain the following items:

  • Subqueries
  • Nondeterministic functions (except timestamp functions like CURRENT_TIMESTAMP or CURRENT_DATE)
  • User-defined or external functions
  • Metadata columns (those starting with METADATA$)
  • Columns that result from aggregates, window functions, or nondeterministic functions
  • Columns that are passed through a window function operator and not a PARTITION BY column. Example: col2 in SUM(col1) OVER (PARTITION BY col1 ORDER BY col2)

• When you use timestamp functions, the immutable region can’t shrink over time. For example, TIMESTAMP_COL < CURRENT_TIMESTAMP() is allowed, but TIMESTAMP_COL > CURRENT_TIMESTAMP() is not.

• Columns referenced in the IMMUTABLE WHERE condition must be columns in the dynamic table, not columns from the base table.

• When the dynamic table has both an IMMUTABLE WHERE predicate and at least one primary key or unique constraint with the RELY property, the columns referenced in the IMMUTABLE WHERE predicate must be a subset of the columns referenced in the set of all RELY primary key and RELY unique constraints. Only RELY constraints are considered. For details, see Interaction with primary key and unique constraints (RELY).

• The following limitations apply when you work with immutability constraints and backfilled data:

  • Currently, only regular and dynamic tables can be used for backfilling.
  • You can’t specify policies or tags in the new dynamic table because they are copied from the backfill table.
  • Clustering keys in the new dynamic table and backfill table must be the same.

示例

Create a dynamic table named my_dynamic_table:

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

在上面的例子中：

• 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 dynamic Iceberg table named my_dynamic_table that reads from my_iceberg_table:

CREATE DYNAMIC ICEBERG TABLE my_dynamic_table (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 DYNAMIC TABLE my_dynamic_table (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;

克隆在指定时间戳的日期和时间存在的动态表：

CREATE DYNAMIC TABLE my_cloned_dynamic_table CLONE my_dynamic_table AT (TIMESTAMP => TO_TIMESTAMP_TZ('04/05/2013 01:02:03', 'mm/dd/yyyy hh24:mi:ss'));

将动态表配置为要求用户刷新，然后刷新动态表：

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

ALTER DYNAMIC TABLE my_dynamic_table REFRESH COPY SESSION;

Create a dynamic table with an immutability constraint:

CREATE DYNAMIC TABLE my_dynamic_table
  TARGET_LAG = '1 hour'
  WAREHOUSE = my_warehouse
  IMMUTABLE WHERE (ts < CURRENT_TIMESTAMP() - INTERVAL '1 day')
AS
  SELECT * FROM source_table;

Create a dynamic table by using the CREATE OR ALTER DYNAMIC TABLE command:

CREATE OR ALTER DYNAMIC TABLE my_dynamic_table
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = mywh
  AS
    SELECT a, b FROM t;

Alter a dynamic table to set the DATA_RETENTION_TIME_IN_DAYS parameter and add a clustering key:

CREATE OR ALTER DYNAMIC TABLE my_dynamic_table
 TARGET_LAG = DOWNSTREAM
 WAREHOUSE = mywh
 DATA_RETENTION_TIME_IN_DAYS = 2
 CLUSTER BY (a)
 AS
   SELECT a, b FROM t;

Modify the target lag and change the warehouse:

CREATE OR ALTER DYNAMIC TABLE my_dynamic_table
 TARGET_LAG = '5 minutes'
 WAREHOUSE = my_other_wh
 DATA_RETENTION_TIME_IN_DAYS = 2
 CLUSTER BY (a)
 AS
   SELECT a, b FROM t;

Unset the DATA_RETENTION_TIME_IN_DAYS parameter. The absence of a parameter in the modified CREATE OR ALTER DYNAMIC TABLE statement results in unsetting it. In this case, unsetting the DATA_RETENTION_TIME_IN_DAYS parameter for the dynamic table resets it to the default value of 1:

CREATE OR ALTER DYNAMIC TABLE my_dynamic_table
 TARGET_LAG = '5 minutes'
 WAREHOUSE = my_other_wh
 CLUSTER BY (a)
 AS
   SELECT a, b FROM t;

Write a v3 Snowflake-managed Iceberg table

CREATE DYNAMIC ICEBERG TABLE my_dynamic_iceberg_v3_table (
    num_orders NUMBER(10,0),
    order_day
  )
  TARGET_LAG = '20 minutes'
  WAREHOUSE = my_warehouse
  EXTERNAL_VOLUME = 'my_external_volume'
  CATALOG = 'SNOWFLAKE'
  BASE_LOCATION = 'my_dynamic_iceberg_v3_table'
  ICEBERG_VERSION = 3
  AS
    SELECT
        COUNT(DISTINCT order_id)
        DATE_TRUNC('DAY', order_timestamp_ns) AS order_day
      FROM staging_v3_iceberg_table;