了解不可变性约束

不可变性约束允许您将动态表的某些部分标记为静态。定义不可变性约束时，Snowflake 会在刷新期间跳过这些行，从而提高性能，特别适用于包含大量历史数据的表。

You define immutability constraints with the IMMUTABLE WHERE clause when you create or alter a dynamic table. The clause specifies a condition, or predicate, that identifies which rows are immutable.

关键行为：

  • Initial refresh: Snowflake ignores the IMMUTABLE WHERE predicate during the initial refresh but applies to all subsequent refreshes.
  • Full refresh mode: The predicate limits recomputation to only the rows that don’t match the condition.
  • Incremental refresh: Streams and incremental refresh dynamic tables can read from full refresh dynamic tables that have immutability constraints.
  • 克隆和复制：Snowflake 会复制 IMMUTABLEWHERE 约束，没有任何限制。

For information about compute costs, see Compute cost for immutability constraints.

To see whether or not a row is mutable, use the METADATA$IS_IMMUTABLE column. For example, SELECT * , METADATA$IS_IMMUTABLE FROM my_dynamic_table.

何时使用不可变性约束

不可变性约束在以下场景中很有用：

Avoid reprocessing historical data

当动态表包含您不想重新处理的历史数据时，请将较旧的行标记为不可变。

Optimize full refresh mode

Dynamic tables that use full refresh mode normally recompute all rows during each refresh. Immutability constraints limit recomputation to only the mutable rows, significantly reducing work when most data is historical.

Facilitate incremental downstream refreshes

某些查询结构，例如 Python 用户定义的表函数，需要动态表采用全量刷新模式。通常，这会阻止下游表从增量刷新中受益。当上游表具有不可变性约束时，下游表仍然可以从增量处理中受益。

使用具有不可变性的回填

回填扩展了不可变性约束。回填是一种零复制操作，让您可以立即将现有数据复制到动态表，而无需重新计算。借此可以迁移现有管道、更改动态表定义，或在创建包含多年历史数据的表时避免昂贵的初始化。

回填的数据在未来刷新期间无法更改。

When you create a dynamic table with both IMMUTABLE WHERE and BACKFILL FROM:

  • Backfill copies the immutable region from the source table. The immutable region consists of rows that match the IMMUTABLE WHERE condition.
  • 查询定义会计算 可变区域。可变区域包含与条件不匹配的行。

与主键和唯一约束条件的交互 (RELY)

Dynamic tables can have primary key and unique constraints with the RELY property. When both of the following are true on a dynamic table:

  • An IMMUTABLE WHERE predicate is set, and
  • 至少一个主键或唯一约束条件有 RELY 属性集，

then 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 on that table. Only constraints with the RELY property are included in set of allowed columns, if any are present. Consider the following examples:

  • If the table has a RELY primary key on column A and a NORELY unique constraint on column B, the IMMUTABLE WHERE predicate may only reference column A (or a subset of the RELY constraint columns).
  • If the table has a RELY primary key on column A, a RELY unique constraint on column B, and a NORELY unique constraint on column C, the IMMUTABLE WHERE predicate may only reference A and B or any subset of these columns.

Validity is checked when a RELY constraint or the IMMUTABLE WHERE predicate is added or changed. If the resulting state would violate the rule (e.g., the predicate references a column not in any RELY constraint), the statement fails with an error.

后续步骤

For implementation guidance and examples, see Backfill examples and Immutability constraints.