用于 SCD Type 2 的 MERGE 语句

这是一份实用的分步指南，介绍如何使用 Goose 的 MERGE 语句（v1.4.0 引入）执行 upsert，并构建 Slowly Changing Dimension Type 2 (SCD Type 2) 表。Type 2 SCD 能在清晰标识当前版本的同时保留完整历史版本，非常适合审计追踪、数据仓库和分析型负载。当你需要知道主键数据历史值、变化时间以及维持时长时，Type 2 SCD 很有价值。

为什么在 Goose 中使用 MERGE？

使用单条 SQL 同时处理 INSERT、UPDATE 和软 DELETE（upsert + 失效标记）。
相比等价 Python/Pandas 逻辑更简洁、通常更高效。
无需硬删除即可完整追踪历史。
借助 Goose 的连接能力，可直接作用于 Parquet、CSV、数据库等来源。

前置条件

基础 SQL 知识

关键术语

Term	Meaning
Target table	你要更新的主表（例如 `master_ducks`）
Source table	新流入的数据表（例如 `incoming_ducks`）
MERGE INTO	指定目标表
USING	指定源表/源查询
ON	连接条件（通常是业务主键 + 当前版本标记）
WHEN MATCHED	两边都存在该行 -> 通常执行 UPDATE（或 DELETE）
WHEN NOT MATCHED BY TARGET	目标表不存在该行 -> 插入新行
WHEN NOT MATCHED BY SOURCE	源表不存在该行 -> 软删除/失效历史版本
RETURNING merge_action	可选：展示每行发生了什么动作（INSERT/UPDATE/DELETE）

构建 SCD Type 2 维度表

我们将跟踪 duck 数据，并在名称、品种或位置变化时保留历史。

第 1 步：创建 Incoming（源）表

该表表示当天的事务数据。

CREATE TABLE IF NOT EXISTS incoming_ducks (
    duck_id     INTEGER,
    duck_name   VARCHAR,
    breed       VARCHAR,
    location    VARCHAR,
    begin_date  DATE,
    end_date    DATE,
    is_current  BOOLEAN
);

INSERT INTO incoming_ducks VALUES
    (101, 'Quackers',   'Mallard',       'Pond B',      CURRENT_DATE - INTERVAL '1 day', NULL, true),
    (102, 'Waddles',    'Pekin',         'Pond A',      CURRENT_DATE - INTERVAL '1 day', NULL, true),
    (104, 'Splash',     'Muscovy',       'Pond C',      CURRENT_DATE - INTERVAL '1 day', NULL, true),
    (105, 'Puddles',    'Indian Runner', 'Relocated',   CURRENT_DATE - INTERVAL '1 day', NULL, true);

第 2 步：创建 Master（目标）表

该表表示 Type 2 SCD 数据（即带历史的事务数据）。

CREATE TABLE IF NOT EXISTS master_ducks (
    record_id   INTEGER PRIMARY KEY,
    duck_id     INTEGER NOT NULL,
    duck_name   VARCHAR,
    breed       VARCHAR,
    location    VARCHAR,
    begin_date  DATE NOT NULL,
    end_date    DATE,
    is_current  BOOLEAN NOT NULL DEFAULT true
);

CREATE SEQUENCE IF NOT EXISTS duck_record_seq START 1;

INSERT INTO master_ducks VALUES
    (nextval('duck_record_seq'), 101, 'Quackers', 'Mallard',       'Pond A', CURRENT_DATE - INTERVAL '2 days', NULL, true),
    (nextval('duck_record_seq'), 102, 'Waddles',  'Pekin',         'Pond A', CURRENT_DATE - INTERVAL '2 days', NULL, true),
    (nextval('duck_record_seq'), 103, 'Feathers', 'Rouen',         'Pond B', CURRENT_DATE - INTERVAL '2 days', NULL, true),
    (nextval('duck_record_seq'), 105, 'Puddles',  'Indian Runner', 'Pond A', CURRENT_DATE - INTERVAL '2 days', NULL, true);

第 3 步：执行 Merge 语句

该语句会执行 merge：比较目标与源数据差异，并按 WHEN MATCHED / WHEN NOT MATCHED 逻辑处理。

MERGE INTO master_ducks AS target
USING incoming_ducks AS source
ON target.duck_id = source.duck_id AND target.is_current = true

WHEN MATCHED AND (
       target.duck_name <> source.duck_name OR
       target.breed     <> source.breed     OR
       target.location  <> source.location
) THEN UPDATE SET
    end_date    = CURRENT_DATE - INTERVAL '1 day',
    is_current  = false

WHEN NOT MATCHED BY SOURCE AND target.is_current = true THEN UPDATE SET
    end_date    = CURRENT_DATE - INTERVAL '1 day',
    is_current  = false

WHEN NOT MATCHED BY TARGET THEN INSERT (
    record_id, duck_id, duck_name, breed, location,
    begin_date, end_date, is_current
) VALUES (
    nextval('duck_record_seq'),
    source.duck_id, source.duck_name, source.breed, source.location,
    source.begin_date, source.end_date, source.is_current
)

RETURNING merge_action, *;

第 4 步：为变更记录插入新的当前版本

该语句将新的当前记录插入 master 表。虽然也可借助 MERGE 的 RETURNING 子句达成同样效果，但两步法更直观、更易理解。

INSERT INTO master_ducks (
    record_id, duck_id, duck_name, breed, location,
    begin_date, end_date, is_current
)
SELECT
    nextval('duck_record_seq'),
    source.duck_id,
    source.duck_name,
    source.breed,
    source.location,
    CURRENT_DATE AS begin_date,
    NULL AS end_date,
    true AS is_current
FROM incoming_ducks AS source
INNER JOIN master_ducks AS target
    ON source.duck_id = target.duck_id
WHERE target.is_current = false
  AND target.end_date = CURRENT_DATE - INTERVAL '1 day';

第 5 步：查询结果

下面查询可用于检查 MERGE 后的数据结果。

-- All history
SELECT * FROM master_ducks ORDER BY duck_id, begin_date DESC;

-- Only current records
SELECT * FROM master_ducks WHERE is_current = true;

-- Only expired historical records
SELECT * FROM master_ducks WHERE is_current = false ORDER BY duck_id, begin_date DESC;

第 6 步：查看单个 Duck 的历史

为更直观地理解 Type 2 SCD 的价值，我们查看一只 duck。在执行 merge 语句和后续插入当前版本语句后，查询 master 表可看到 Quackers 的各版本行。

查看历史版本原始行：

SELECT * FROM master_ducks where duck_name = 'Quackers' and is_current = false;

record_id	duck_id	duck_name	breed	location	begin_date	end_date	is_current
1	101	Quackers	Mallard	Pond A	2025-11-24	2025-11-25	false

说明：

end date 为非 NULL，表示该 duck 数据被更新的日期。
is_current 为 false，表示这是历史记录。
将要变化的字段是 location：当前为 Pond A，将更新为 Pond B。

查看当前版本行：

SELECT * FROM master_ducks where duck_name = 'Quackers' and is_current = true;

record_id	duck_id	duck_name	breed	location	begin_date	end_date	is_current
10	101	Quackers	Mallard	Pond B	2025-11-26	NULL	true

说明：

end date 为 NULL，在这里表示这是该 duck_id 的最新记录。
is_current 为 true，同样表示当前记录。
location 已变为 Pond B。

查看 Quackers 的全部数据（同时包含当前与非当前行）：

SELECT * FROM master_ducks where duck_name = 'Quackers';

常见模式与变体

Use Case	Clause to Use
简单 upsert（无历史）	`WHEN MATCHED THEN UPDATE` 和 `WHEN NOT MATCHED BY TARGET THEN INSERT`
upsert 并删除缺失行	增加 `WHEN NOT MATCHED BY SOURCE THEN DELETE`
仅插入新行，不更新	省略 `WHEN MATCHED`
返回受影响行	增加 `RETURNING merge_action, *`

最佳实践

牢记：TARGET 是主表，SOURCE 是流入表或查询结果。
当前行保持 end_date 为 NULL（通常有利于查询性能）。
必要时将 MERGE 与后续 INSERT 放在同一事务中。
使用主键或代理键保证唯一性。
先用 RETURNING 验证结果再固化流程。

为什么在 Goose 中使用 MERGE？​

前置条件​

关键术语​

构建 SCD Type 2 维度表​

第 1 步：创建 Incoming（源）表​

第 2 步：创建 Master（目标）表​

第 3 步：执行 Merge 语句​

第 4 步：为变更记录插入新的当前版本​

第 5 步：查询结果​

第 6 步：查看单个 Duck 的历史​

常见模式与变体​

最佳实践​