使用不可变性约束

要告诉 Snowflake 动态表中的某些行不会更改，请使用 CREATE DYNAMIC TABLE 或 ALTER DYNAMIC TABLE 语句中的 IMMUTABLE WHERE 子句。

不可变性通过跳过未更改的行来加速刷新。回填 与不可变性相结合，既能带来立竿见影的性能优势，也能提供持续的性能提升。

  • 初始创建：回填会立即复制历史数据，没有计算成本。这使得包含多年历史数据的表立即可用，而不需要成本昂贵的初始刷新。

  • 持续刷新：不可变性约束可防止回填数据在未来刷新期间重新处理。仅刷新可变区域，即使表增长，也能保持快速刷新。

有关概念背景，请参阅 了解不可变性约束。

基本示例

示例：防止维度表更改时重新计算

更新维度表中的行时，仅重新处理可变期间的事实：

CREATE DYNAMIC TABLE joined_data
  TARGET_LAG = '1 minute'
  WAREHOUSE = mywh
  IMMUTABLE WHERE (timestamp_col < CURRENT_TIMESTAMP() - INTERVAL '1 day')
AS
  SELECT F.primary_key primary_key, F.timestamp_col timestamp_col, D.value dim_value
  FROM fact_table F
  LEFT OUTER JOIN dimension_table D USING (primary_key);

示例：数据保留时间长于源表

创建一个动态表，该表保留已解析数据的时间长于暂存表，并使用以下任务删除旧的暂存数据：

CREATE TABLE staging_data (raw TEXT, ts TIMESTAMP);

CREATE DYNAMIC TABLE parsed_data
  TARGET_LAG = '1 minute'
  WAREHOUSE = mywh
  IMMUTABLE WHERE (ts < CURRENT_TIMESTAMP() - INTERVAL '7 days')
AS
  SELECT
    parse_json(raw):event_id::string event_id,
    parse_json(raw):name::string name,
    parse_json(raw):region::string region,
    ts
  FROM staging_data
  WHERE region = 'US';

CREATE TASK delete_old_staging_data
  WAREHOUSE = mywh
  SCHEDULE = '24 hours'
AS
  DELETE FROM staging_data WHERE ts < CURRENT_TIMESTAMP() - INTERVAL '30 days';

示例：让下游表能够基于全量刷新表进行增量刷新

某些查询结构（如 Python 用户定义的表函数）需要全量刷新模式。不可变性约束允许下游表仍然使用增量刷新：

CREATE DYNAMIC TABLE udtf_dt
  TARGET_LAG = '1 hour'
  WAREHOUSE = mywh
  REFRESH_MODE = FULL
  IMMUTABLE WHERE (ts < current_timestamp() - interval '1 day')
AS
  SELECT ts, data, output, join_key
  FROM input_table, TABLE(my_udtf(data));

CREATE DYNAMIC TABLE incremental_join_dt
  TARGET_LAG = '1 hour'
  WAREHOUSE = mywh
  REFRESH_MODE = INCREMENTAL
  IMMUTABLE WHERE (ts < current_timestamp() - interval '1 day')
AS
  SELECT * FROM udtf_dt JOIN dim_table USING (join_key);

回填示例

以下示例展示了如何从回填数据的表中创建新的动态表。

回填表必须包含与动态表列顺序一致、数据类型兼容的匹配列。Snowflake 不会从回填表中复制表属性或权限。

如果指定 Time Travel 参数 AT | BEFORE，Snowflake 会在指定时间从回填表复制数据。

使用 不可变性约束 和回填数据时，以下限制适用：

  • 目前，只有常规表和动态表可用于回填。

  • 在新建动态表时，您不能指定策略或标签，因为它们会从回填表中复制。

  • 新建动态表与回填表的群集密钥必须相同。

示例：从表的一部分回填

以下示例将 my_dynamic_table 的不可变区域从 my_backfill_table 回填，而可变区域则从动态表的定义计算得出。

重新初始化此动态表时：

  • 增量刷新模式：Snowflake 会删除所有可变行，并仅重新填充可变区域。

  • 全量刷新模式：Snowflake 执行全量刷新，效果相同。

CREATE DYNAMIC TABLE my_dynamic_table (day TIMESTAMP, totalSales NUMBER)
  IMMUTABLE WHERE (day < '2025-01-01')
  BACKFILL FROM my_backfill_table
  TARGET_LAG = '20 minutes'
  WAREHOUSE = 'mywh'
  AS SELECT DATE_TRUNC('day', ts) AS day, sum(price)
    FROM my_base_table
    GROUP BY day;

示例：使用回填在动态表中恢复或修改数据

您无法直接编辑动态表的数据或定义。要恢复或修复数据，请按照以下替代步骤操作：

  1. 将动态表克隆为一个常规表。

  2. 根据需要修改克隆表。

  3. 从已编辑的表回填到新的动态表。

在以下示例中，my_dynamic_table 从销售基表 sales 汇总每日销售数据：

CREATE OR REPLACE TABLE sales(item_id INT, ts TIMESTAMP, sales_price FLOAT);

INSERT INTO sales VALUES (1, '2025-05-01 01:00:00', 10.0), (1, '2025-05-01 02:00:00', 15.0), (1, '2025-05-01 03:00:00', 11.0);
INSERT INTO sales VALUES (1, '2025-05-02 00:00:00', 11.0), (1, '2025-05-02 05:00:00', 13.0);


CREATE DYNAMIC TABLE my_dynamic_table
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE = mywh
  INITIALIZE = on_create
  IMMUTABLE WHERE (day <= '2025-05-01')
  AS
    SELECT item_id, date_trunc('DAY', ts) day, count(sales_price) AS sales_count FROM sales
    GROUP BY item_id, day;

SELECT item_id, to_char(day, 'YYYY-MM-DD') AS day, sales_count FROM my_dynamic_table;

+---------+------------+-------------+
| ITEM_ID | DAY        | SALES_COUNT |
|---------+------------+-------------|
| 1       | 2025-05-01 | 3           |
| 1       | 2025-05-02 | 2           |
+---------+------------+-------------+

您还可以选择将旧数据归档，以节省存储成本：

DELETE FROM sales WHERE ts < '2025-05-02';

ALTER DYNAMIC TABLE my_dynamic_table REFRESH;

SELECT item_id, to_char(day, 'YYYY-MM-DD') AS day, sales_count FROM my_dynamic_table;

之后，您发现 2025-05-01 的销售数据存在错误，其中 sales_count 应为 2。要修正该问题，请执行以下操作：

  1. 将 my_dynamic_table 克隆为常规表：

    CREATE OR REPLACE TABLE my_dt_clone_table CLONE my_dynamic_table;

  2. 更新克隆的表：

    UPDATE my_dt_clone_table SET
  sales_count = 2
  WHERE day = '2025-05-01';

SELECT item_id, to_char(day, 'YYYY-MM-DD') AS day, sales_count FROM my_dt_clone_table;

    +---------+------------+-------------+
| ITEM_ID | DAY        | SALES_COUNT |
|---------+------------+-------------|
| 1       | 2025-05-01 | 2           |
| 1       | 2025-05-02 | 2           |
+---------+------------+-------------+

  3. 使用已编辑的克隆表作为回填源，重新创建该动态表。

    CREATE OR REPLACE DYNAMIC TABLE my_dynamic_table
  BACKFILL FROM my_dt_clone_table
  IMMUTABLE WHERE (day <= '2025-05-01')
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE = mywh
  INITIALIZE = on_create
  AS
    SELECT item_id, date_trunc('DAY', ts) day, count(sales_price) AS sales_count FROM sales
    GROUP BY item_id, day;

    利用这种方法，无需修改基表即可在动态表中恢复或更正数据：

    SELECT item_id, to_char(day, 'YYYY-MM-DD') AS day, sales_count FROM my_dynamic_table;

    +---------+------------+-------------+
| ITEM_ID | DAY        | SALES_COUNT |
|---------+------------+-------------|
| 1       | 2025-05-01 | 2           |
| 1       | 2025-05-02 | 2           |
+---------+------------+-------------+

示例：使用回填修改动态表的架构

您无法直接更改动态表的架构。要更新架构（例如，添加一列），请按照以下步骤操作：

  1. 将动态表克隆为一个常规表。以下示例使用基于 sales 创建的 my_dynamic_table`（见 :ref:`上文 <label-data-surgery-example>）。

    CREATE OR REPLACE TABLE my_dt_clone_table CLONE my_dynamic_table;

  2. 修改克隆表的架构：

    ALTER TABLE my_dt_clone_table ADD COLUMN sales_avg FLOAT;

SELECT item_id, to_char(day, 'YYYY-MM-DD') as DAY, SALES_COUNT, SALES_AVG FROM my_dt_clone_table;

  3. （可选）将数据添加到新列。

  4. 使用已编辑的克隆表作为回填源，重新创建该动态表。

    CREATE OR REPLACE DYNAMIC TABLE my_dynamic_table
  BACKFILL FROM my_dt_clone_table
  IMMUTABLE WHERE (day <= '2025-05-01')
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE = mywh
  INITIALIZE = on_create
  AS
    SELECT item_id, date_trunc('DAY', ts) day, count(sales_price) AS sales_count, avg(sales_price) as sales_avg FROM sales
    GROUP BY item_id, day;

  5. 验证新列是否出现在动态表中：

    SELECT item_id, to_char(day, 'YYYY-MM-DD') as DAY, SALES_COUNT, SALES_AVG, metadata$is_immutable as IMMUTABLE from my_dynamic_table ORDER BY ITEM_ID, DAY;

    +---------+------------+-------------+-----------+-----------+
| ITEM_ID | DAY        | SALES_COUNT | SALES_AVG | IMMUTABLE |
|---------+------------+-------------|-----------|-----------|
| 1       | 2025-05-01 | 3           | NULL      | TRUE      |
| 1       | 2025-05-02 | 2           | 12        | FALSE     |
+---------+-------------+------------+-----------+-----------+

检查不可变性状态

要检查动态表中的行是否可变，请查询 METADATA$IS_IMMUTABLE 列：

SELECT *, METADATA$IS_IMMUTABLE FROM my_dynamic_table;

要查看动态表的不可变性约束，请运行 SHOW DYNAMIC TABLES 并检查 immutable_where 列。