更改现有动态表

This section describes making changes to existing dynamic tables using the ALTER DYNAMIC TABLE command:

  • 更改动态表的仓库或目标滞后
  • 为动态表重命名、交换或添加群集密钥

更改动态表的仓库或目标滞后

Adjust your dynamic tables’ warehouse for cost efficiency or performance boost. For more information, see Compute costs and Understand warehouse usage for dynamic tables.

在以下情况下，调整动态表的目标滞后以获得更新的数据：

  • 您需要更新的数据：减少目标滞后以触发更频繁的刷新。
  • You want to reduce cost: Data that doesn’t need near real-time freshness can use a longer target lag. For example, a dynamic table that refreshes every 20 minutes but only needs to be within one hour of the source tables can use a one-hour target lag to reduce compute costs.
  • Your pipeline has misaligned schedules: When your dynamic table depends on other tables with longer refresh intervals, align the target lag with those dependencies to avoid unnecessary refreshes.
  • You’re seeing skipped refreshes: When refreshes take longer than your target lag, Snowflake skips some refreshes. Increase the target lag to match realistic refresh durations.

For more information, see Understanding dynamic table target lag.

To change the warehouse or target lag for a dynamic table, use the ALTER DYNAMIC TABLE command. For example:

-- Change the warehouse for my_dynamic_table to my_other_wh:
ALTER DYNAMIC TABLE my_dynamic_table SET
  WAREHOUSE = my_other_wh;
-- Specify the downstream target lag for a dynamic table:
ALTER DYNAMIC TABLE my_dynamic_table SET
  TARGET_LAG = DOWNSTREAM;

重命名动态表

如果您的脚本或应用程序依赖于特定表名称，并且您希望在不更改现有脚本的情况下更新动态表，则重命名动态表可能很有用。例如，如果您有一个引用特定动态表名的脚本，通过重命名表，您可以在不更改脚本的情况下更换基础表。这确保了连续性，并避免了跨脚本或进程更新多个引用的麻烦。

To rename a dynamic table, use the ALTER DYNAMIC TABLE … RENAME TO command. For example:

ALTER DYNAMIC TABLE my_dynamic_table RENAME TO my_new_dynamic_table;

交换动态表

交换动态表可以实现数据集或表版本之间的无缝过渡，而不会干扰工作流程或修改依赖的脚本。例如，如果您正在开发表的新版本，但希望保持现有名称以供正在进行的流程使用，通过交换，您可以将旧表替换为新表。这种方法确保了连续性，同时使得在最小的停机时间或干扰下进行更新、测试或升级成为可能。

To swap a dynamic table, use the ALTER DYNAMIC TABLE … SWAP WITH command. Note that you can only swap a dynamic table with another dynamic table.

例如：

-- Swap my_dynamic_table with the my_new_dynamic_table:
ALTER DYNAMIC TABLE my_dynamic_table SWAP WITH my_new_dynamic_table;

向动态表中添加群集密钥

向动态表中添加群集密钥可以通过提高查询效率和刷新操作来增强性能：

  • Query efficiency: Clustering keys can help speed up queries, just like with regular tables, by clustering on common join keys or filter columns.
  • Refresh operations: Clustering keys can help speed up refreshes if the clustering keys align with frequent change patterns; for example, clustering by user ID can be effective when you have updates where a handful of users change.

Clustering keys can be specified for a dynamic table with incremental or full refresh mode. In full refresh, the clustering is performed during the refresh and background reclustering isn’t needed.

To cluster a dynamic table, use the ALTER DYNAMIC TABLE … CLUSTER BY command:

ALTER DYNAMIC TABLE my_dynamic_table CLUSTER BY (date);