使用主键优化动态表管道

Snowflake 可以使用主键来跟踪动态表中的行级更改，而无需依赖更改跟踪列。这将为运行插入覆盖工作负载的管道启用增量刷新，包括完全刷新动态表，这些工作负载通常会阻止下游增量处理。

主键在以下情况下特别有效：INSERTOVERWRITE 在基表上执行，其中只有一小部分数据实际发生了更改。在这些情况下，基于主键的变更跟踪仅处理已更改的行，而不是重新计算整个表。主键提供了一个稳定的行标识符，该标识符存在于所有覆盖中。

有关概念背景，请参阅 了解动态表中的主键。

提高 INSERTOVERWRITE 工作负载的性能

当定期通过 INSERT OVERWRITE 重写基表时，标准变更跟踪列将重置，使用基表的动态表将看到基表中所有行的一组插入和删除。

在以下示例中，外部进程会定期重写 dimension_table，但大多数行保持不变：

CREATE TABLE dimension_table (
  dim_id INT PRIMARY KEY RELY,
  dim_name VARCHAR,
  category VARCHAR
);

CREATE TABLE fact_table (
  fact_id INT,
  dim_id INT,
  measure FLOAT,
  ts TIMESTAMP
);

CREATE DYNAMIC TABLE enriched_facts
  TARGET_LAG = '30 minutes'
  WAREHOUSE = mywh
  REFRESH_MODE = INCREMENTAL
AS
  SELECT f.fact_id, f.measure, d.dim_name, d.category, f.ts
  FROM fact_table f
  INNER JOIN dimension_table d ON f.dim_id = d.dim_id;

当通过 INSERT OVERWRITE 重写维度表时，Snowflake 使用主键来识别哪些维度行实际发生了变更，并仅刷新受影响的事实，而不是重新计算整个连接。

在完全刷新动态表下游启用增量刷新

通常情况下，包含 REFRESH_MODE = INCREMENTAL 的动态表无法从包含 REFRESH_MODE = FULL 的动态表中读取。当完全刷新动态表具有系统派生的唯一键时，您可以将刷新模式显式设置为 INCREMENTAL。

示例：使用基表主键

创建具有主键的基表，并设置 RELY 属性，以便 Snowflake 将其用于行级变更跟踪：

CREATE TABLE raw_events (
  event_id INT PRIMARY KEY RELY,
  event_type VARCHAR,
  payload VARIANT,
  created_at TIMESTAMP
);

创建从基表中读取的完全刷新动态表。由于基表具有可靠的主键，Snowflake 可以从基表派生唯一键，并将其注册为动态表的唯一约束：

CREATE DYNAMIC TABLE transformed_events
  TARGET_LAG = '10 minutes'
  WAREHOUSE = mywh
  REFRESH_MODE = FULL
AS
  SELECT event_id, event_type, payload:user_id::STRING AS user_id, created_at
  FROM raw_events;

在下游创建增量动态表。这是有效的，因为上游表具有系统派生的可靠唯一键：

CREATE DYNAMIC TABLE event_summary
  TARGET_LAG = '10 minutes'
  WAREHOUSE = mywh
  REFRESH_MODE = INCREMENTAL
AS
  SELECT user_id, COUNT(*) AS event_count, MAX(created_at) AS last_event
  FROM transformed_events
  GROUP BY user_id;

示例：使用查询派生的主键

当动态表的查询包含 GROUP BY 子句时，Snowflake 会自动从分组列派生唯一键。下游表可以使用此派生键进行基于主键的变更跟踪，并启用增量刷新。

CREATE DYNAMIC TABLE daily_sales
  TARGET_LAG = '1 hour'
  WAREHOUSE = mywh
  REFRESH_MODE = FULL
AS
  SELECT DATE_TRUNC('day', sale_ts) AS sale_day, product_id, SUM(amount) AS total_sales
  FROM sales
  GROUP BY sale_day, product_id;

由于 GROUP BY 可保证每个组合一行，因此 daily_sales 表在 (sale_day, product_id) 上具有派生的唯一键。下游表可以增量刷新：

CREATE DYNAMIC TABLE product_trends
  TARGET_LAG = '1 hour'
  WAREHOUSE = mywh
  REFRESH_MODE = INCREMENTAL
AS
  SELECT product_id, AVG(total_sales) AS avg_daily_sales, COUNT(*) AS days_with_sales
  FROM daily_sales
  GROUP BY product_id;

检查动态表上系统派生的唯一键

要查看动态表是否具有派生的唯一键，请使用 SHOW UNIQUE KEYS 命令：

SHOW UNIQUE KEYS IN daily_sales;

如果输出包含唯一键，则动态表支持基于主键的变更跟踪。下游动态表可以使用 REFRESH_MODE = INCREMENTAL 从中读取数据，即使它使用完全刷新模式。

您还可以通过使用 REFRESH_MODE = INCREMENTAL 创建下游动态表来验证是否支持。如果上游表没有可靠的唯一键，则创建将失败并显示错误。