教程：为 SCD Type 1 工作负载优化动态表性能¶

简介¶

本教程向您展示如何在动态表管道中，识别和解决缓慢变化维度 (SCD) Type 1 工作负载的性能瓶颈。动态表会自动物化查询结果，并处理数据管道的计划和编排。优化动态表性能有助于保持数据新鲜度并控制成本。

关于 SCD Type 1 表¶

缓慢变化维度 (SCD) 表存储的数据会随着时间的推移偶尔发生不可预测的变化。常见示例包括跟踪客户地址或产品价格变化的表。

本教程实施了 SCD Type 1 表，通常称为“SCD-1 live”表。此类型会使用新数据覆盖旧数据，并且不保留过去值的历史记录。当您只关心每条记录的最新状态（例如客户的当前电话号码或产品的当前类别）时，SCD Type 1 表非常有用。

在现实世界的数据管道中，您通常会通过使用变更日志来构建一个 Type 1 SCD 表。

您将学习以下内容¶

在本教程中，您将学习如何完成以下任务：

使用产品变更数据创建示例源表。
构建两个 SCD Type 1 动态表：一个具有次优 SQL 模式，另一个具有优化模式和 QUALIFY 子句。
了解 QUALIFY 子句如何实现高效增量处理，并显著缩短刷新时间。
监控刷新持续时间和分区扫描等关键性能指标，以确定优化机会。
比较两个动态表对相同数据的增量刷新性能。

先决条件¶

您需要访问具有以下资源的 Snowflake 环境：

一个用于计算资源的仓库。我们建议使用超小型仓库。
创建数据库、架构和动态表所需的权限。有关更多信息，请参阅访问控制权限。

如果没有具有必要权限的用户，则请让具有必要权限的用户为您创建一个。具有 ACCOUNTADMIN 角色的用户可以创建新用户并授予他们所需的权限。

备注

为获得最佳体验，请在 Snowsight 中完成本教程，以便快速查看查询历史记录并监控动态表性能。

第 1 步：创建源数据¶

首先设置一个源表，其中包含模拟流产品变化的示例数据。

为教程创建数据库和架构，然后创建源表：

CREATE DATABASE IF NOT EXISTS dt_perf_demo_db;
CREATE SCHEMA IF NOT EXISTS dt_perf_demo_db.tutorial;

USE SCHEMA dt_perf_demo_db.tutorial;

CREATE OR REPLACE TABLE product_changes (
    product_code VARCHAR(50),
    product_name VARCHAR(200),
    price NUMBER(10, 2),
    price_start_date TIMESTAMP_NTZ(9)
);

Copy

接下来，将示例数据插入到 product_changes 源表中。以下命令通过重复 10,000 个唯一产品代码和名称，生成 1 亿行样本产品数据。它为每个产品分配一个每行略有变化的价格，并设置一个时间戳，每个新条目增加几分钟。

INSERT INTO product_changes (product_code, product_name, price, price_start_date)
  SELECT
      'PC-' || LPAD(TO_VARCHAR(MOD(SEQ4(), 10000) + 1), 3, '0') AS product_code,
      'Product ' || LPAD(TO_VARCHAR(MOD(SEQ4(), 10000) + 1), 3, '0') AS product_name,
      ROUND(10.00 + (MOD(SEQ4(), 10000) * 5) + (SEQ4() * 0.01), 2) AS price,
      DATEADD(MINUTE, SEQ4() * 5, '2025-01-01 00:00:00') AS PRICE_START_DATE
  FROM
      TABLE(GENERATOR(ROWCOUNT => 100000000));

Copy

第 2 步：创建用于比较的动态表¶

在此步骤中，您将创建两个使用源表的 SCD Type 1 动态表。第一个动态表使用次优 SQL 模式查找每种产品的最新价格变化，而第二个动态表使用优化模式。同时创建两个表使我们能够直接比较它们对相同数据的刷新性能。

创建次优动态表¶

使用 INNER JOIN 创建动态表，用子查询获取每个产品代码的最新时间戳。这是一种常见但效率低下的模式，每次更新都会触发成本高昂的重新计算。

备注

用仓库名称替换 my_warehouse。

CREATE DYNAMIC TABLE product_current_price_v1
    TARGET_LAG = DOWNSTREAM
    WAREHOUSE = <my_warehouse>
    INITIALIZE = ON_SCHEDULE
    REFRESH_MODE = INCREMENTAL
  AS
  SELECT
      h.product_code,
      h.product_name,
      h.price,
      h.price_start_date
  FROM product_changes h
  INNER JOIN (
      SELECT product_code, MAX(price_start_date) max_price_start_date
      FROM product_changes
      GROUP BY product_code
  ) m ON h.price_start_date = m.max_price_start_date AND h.product_code = m.product_code;

Copy

有关此动态表配置的关键详细信息：

此动态表使用 TARGET_LAG = DOWNSTREAM，这意味着它仅在下游表或查询需要新数据时刷新。此设置适用于管道中的中间表。
REFRESH_MODE = INCREMENTAL 设置告诉 Snowflake 仅处理已更改的数据，而不是重新计算整个表。

创建优化的动态表¶

现在，创建第二个动态表，名为 product_current_price_v2，具有优化的 SQL 模式。此表使用 QUALIFY 子句，以有效筛选每种产品的最新价格：

CREATE DYNAMIC TABLE product_current_price_v2
    TARGET_LAG = DOWNSTREAM
    WAREHOUSE = <my_warehouse>
    REFRESH_MODE = INCREMENTAL
    INITIALIZE = ON_SCHEDULE
  AS
  SELECT
      product_code,
      product_name,
      price,
      price_start_date
  FROM product_changes
  QUALIFY RANK() OVER (PARTITION BY product_code ORDER BY price_start_date DESC) = 1;

Copy

使用具有排名窗口函数（例如 RANK()）的 QUALIFY 子句，让 Snowflake 有效检测哪些产品分区发生了变化。引擎不会重新扫描所有历史数据，而是会找到受影响的分区，并仅针对这些特定产品重新计算排名。这样可以提高增量刷新的效率。

此优化之所以有效，是因为以下因素：

RANK、ROW_NUMBER 或 DENSE_RANK 等排名函数搭配 PARTITION BY 使用，让引擎可以按产品隔离变更。
筛选到 RANK() ... = 1 即只保留每个产品的最新记录，这正是 SCD Type 1 表需要的。
将 QUALIFY RANK() ... = 1 子句放在动态表查询的顶层（不在子查询中）可确保优化适用。
将 product_code 和 price_start_date 键作为动态表中的列进行持久化，让引擎可以跟踪刷新之间的分区变化，避免全表扫描。

此模式还表现出了良好的 数据局部性，描述 Snowflake 将具有匹配键的行存储在一起的紧密程度。此模式会隔离对特定分区键的变更并避免全表扫描。

刷新两个动态表¶

要填充两个表的初始数据，请手动进行刷新。这为下一步比较增量刷新性能建立了基线：

ALTER DYNAMIC TABLE product_current_price_v1 REFRESH;

ALTER DYNAMIC TABLE product_current_price_v2 REFRESH;

Copy

第 3 步：比较增量刷新性能¶

现在，比较一下每个表如何处理增量刷新。

向源表添加新数据¶

此步骤将模拟到达源表的新数据，就像在真实世界的流式传输场景中发生的那样。将 1,000 个新行插入到更新五种现有产品价格的 product_changes 源表：

INSERT INTO product_changes (product_code, product_name, price, price_start_date)
  SELECT
      'PC-' || LPAD(TO_VARCHAR(MOD(SEQ4(), 5) + 1), 3, '0') AS product_code,
      'Product ' || LPAD(TO_VARCHAR(MOD(SEQ4(), 5) + 1), 3, '0') AS product_name,
      ROUND(50.00 + (MOD(SEQ4(), 10) * 5) + ((SEQ4() + 100000000) * 0.01), 2) AS price,
      DATEADD(MINUTE, (SEQ4() + 100000000) * 5, '2025-01-01 00:00:00') AS price_start_date
  FROM
      TABLE(GENERATOR(ROWCOUNT => 1000));

Copy

监控刷新性能¶

动态表性能取决于以下几种因素：如何编写查询、如何组织数据以及分配的资源。要监控的关键指标是刷新持续时间、分区扫描和溢出字节数。在此步骤中，您将比较两个动态表实施的这些指标。

要获取变更，首先要刷新次优动态表：

ALTER DYNAMIC TABLE product_current_price_v1 REFRESH;

Copy

检查一下执行时间和扫描指标：

导航到 Transformation » Dynamic Tables。
通过先后选择 dt_perf_demo_db 数据库和 product_current_price_v1 筛选列表。
选择 Refresh History 选项卡，并注意最近刷新的 REFRESH DURATION 值。
选择 Show query profile 获取最新刷新条目。
查找 Statistics 部分并注意 Partitions scanned 值。

product_current_price_v1 表效率低下，因为子查询会重新计算所有 10,000 个产品的最大时间戳，即使只有五个产品收到了新的价格变化。这会迫使动态表引擎扫描不必要的分区，从而随着源表的增长，增加时间和成本。这种模式表现出较差的数据局部性，因为变更与增量处理的数据组织方式不一致。
现在，刷新一下优化的 product_current_price_v2 动态表：
```
ALTER DYNAMIC TABLE product_current_price_v2 REFRESH;
```
Copy
重复前面的步骤，查看优化表的 Refresh History：

比较两个刷新操作。优化 product_current_price_v2 动态表的完成速度应明显快于次优 product_current_price_v1 动态表。在示例结果中，次优表耗时 2.8 秒，而优化表仅耗时 804 毫秒。

打开 Query Profile 并比较 Statistics 部分：

product_current_price_v2 使用具有排名窗口函数的 QUALIFY 子句，让引擎可以有效识别和仅处理更改的五个产品，从而加快增量刷新速度。此查询模式具有良好的数据局部性，因为 Snowflake 可以隔离哪些分区键（产品代码）包含变更。

小技巧

即使在本教程的小规模使用中，这种优化也会带来显着的性能提升。在生产环境中，如果有数百万个产品和数十亿条记录，这种优化可以将刷新时间从数小时缩短到几秒钟。性能取决于变化产品所占的百分比，因此随着数据的增长，效率仍然很高。

更快的刷新速度会直接转化为更新的数据。如果您需要在几分钟内刷新数据，像这样的优化查询模式可以帮助您满足严格的目标滞后要求，而不会导致仓库规模过大。

清理¶

要删除根据本教程创建的所有对象，请运行以下 DROP 语句：

DROP DATABASE dt_perf_demo_db;

Copy

总结和其他资源¶

在本教程中，您通过将 SCD Type 1 表的次优子查询模式替换为高效的 QUALIFY RANK() = 1 模式，优化了动态表管道。这允许动态表引擎将性能优化应用于增量刷新，让管道运行更快、更便宜。更快的刷新意味着您可以在保持数据新鲜度的同时，满足更严格的目标滞后要求，而不会增加成本。

在此过程中，您完成了以下任务：

创建源表，其中包含模拟变更日志的示例产品数据。
创建次优 SCD Type 1 动态表，演示了配合使用嵌套查询与 MAX() 来查找最新记录的典型误区。
应用 QUALIFY 优化，通过高效增量处理显着提高动态表刷新性能。此模式通过允许引擎隔离对特定分区键的更改，提高了数据局部性。
监控刷新性能，方法是使用查询配置文件来比较不同实施的分区扫描和执行时间。这些指标可帮助您确定查询是否能高效使用增量刷新。

演示的关键性能概念：

增量刷新效率：优化查询仅处理已更改的数据，而次优查询则重新扫描整个数据集。
数据局部性：当变更与分区键（产品代码）一致时，增量刷新表现良好。当变更分散在多个键上或需要完全重新扫描时，性能就会受到影响。有关更多详细信息，请参阅提高数据局部性。
目标滞后和新鲜度：优化查询模式让您无需超大尺寸的仓库，即可满足更严格的数据新鲜度要求。

有关动态表和优化技术的更多信息，请探索以下源：

查询和管道优化：

增量刷新查询优化：了解哪些运算符在增量刷新方面表现良好，以及如何重组查询以获得更好的性能。请参阅优化增量刷新查询。
数据局部性：了解数据组织如何影响增量刷新性能以及如何聚类源表。请参阅提高数据局部性。
不可变性约束：为避免重新处理未更改的历史数据，请使用 IMMUTABLE WHERE 选项。这可以大大减少刷新成本和时间。

基础设施和监控：

目标滞后：了解如何通过选择适当的目标滞后设置来平衡数据新鲜度要求和计算成本。请参阅了解动态表目标滞后。
仓库规模调整：了解仓库大小如何影响刷新性能和成本。请参阅调整仓库配置。
性能监控：跟踪刷新持续时间、分区扫描和仓库利用率等关键指标，以确定优化机会。请参阅监控动态表性能。
刷新模式：了解何时使用增量刷新模式，何时使用完全刷新模式，以及 Snowflake 如何在两种模式之间做出选择。请参阅了解动态表初始化和刷新。
动态 Iceberg 表：将动态表与 Apache Iceberg™ 表结合使用，为数据湖构建可互操作的数据管道。请参阅创建动态 Apache Iceberg™ 表。