SCD2 合并模板是什么
SCD2 合并模板把拉链维表的增量更新固定成三段：变更识别、关闭旧版本、插入新版本。变更识别用 hashdiff 或逐列比较，配合源端水位只读增量数据；关闭旧版本写 end_date 并把当前标志置 0；插入新版本时同一批次统一取批次日期作为 start_date，避免同一批生成多个小版本。三段必须在同一批次内完成且可重复执行，否则会出现版本区间重叠或断档。
基本信息
| 三步 | 识别变更、关闭旧行、插入新行 |
|---|---|
| 水位 | 用源端 last_load_ts 或批次号过滤增量 |
| 批次日期 | 同批统一取一个 start_date，防止多小版本 |
| 幂等 | 按批次记录来源，重跑结果一致 |
| 前置条件 | 自然键唯一、已有 hashdiff 列与 is_current 标志 |
典型使用场景
- 新建拉链维表：按模板落地增量作业，避免每次重新设计
- 批量改造：把历史逐列比较写法统一替换为模板，降低漏列风险
- 调度重跑：作业失败后按批次号重跑，结果与首次一致
- 批次合并：一天多批数据统一取当日日期作为 start_date
- 多表复用：客户、商品、门店维度共用同一套模板，只在配置里换表名
- 问题复盘：版本区间异常时按三步逐段排查，定位是哪一环出的问题
三步模板
三步模板
-- 步骤 1：标出变更的自然键（增量读源 + 哈希比较）
WITH src AS (
SELECT customer_id, name, phone,
md5(concat_ws('|', coalesce(name,'#'), coalesce(phone,'#'))) AS row_hash
FROM stg_customer
WHERE load_ts > :last_load_ts),
chg AS (
SELECT s.customer_id
FROM src s
JOIN dim_customer d
ON s.customer_id = d.customer_id AND d.is_current = 1
WHERE s.row_hash <> d.row_hash)
-- 步骤 2：关闭旧版本（条件必须带 is_current = 1）
UPDATE dim_customer d
JOIN chg c ON d.customer_id = c.customer_id
SET d.end_date = :batch_date - interval '1' day,
d.is_current = 0
WHERE d.is_current = 1;
-- 步骤 3：插入新版本，同批统一 start_date
INSERT INTO dim_customer
(customer_sk, customer_id, name, phone, row_hash,
start_date, end_date, is_current)
SELECT next_sk(), s.customer_id, s.name, s.phone, s.row_hash,
:batch_date, '9999-12-31', 1
FROM src s JOIN chg c ON s.customer_id = c.customer_id;实现方式对照
| 方式 | 特点 | 风险 |
|---|---|---|
| MERGE 单语句 | 原子性好，逻辑集中 | 部分引擎对同表读写支持有限 |
| 三步 SQL 事务 | 通用性最好 | 忘记事务包裹会留下中间态 |
| 全量重算 | 实现最简单 | 成本随历史增长，且有停用窗口 |
| 视图按需计算 | 无需物理拉链 | 大表查询慢，无法支撑高频报表 |
| 流式增量 | 时效最好 | 乱序与重复事件处理复杂 |
同批统一取批次日期是一个容易被忽略但很关键的细节：若逐行取 current_timestamp，同一批数据会生成时间相差毫秒的多个版本，区间看似连续实际碎片化，后续按区间查询与校验都会变得难以解释。