拉链表（SCD2）怎么设计与回刷

难度与前置

难度：中级
前置：已读数仓分层、分区策略；知道维表快照是什么。
读完能做什么：判断要不要上拉链，定好区间与写入契约，按日关旧开新，并能量化验收、安全回刷。

适用场景

需要回答「某一天当时是什么状态」的维度属性，且变化不频繁，例如：

渠道结算主体 / 合作状态
用户等级、商户类目
剧本上下架、运营归属

不适用：

只关心最新值 → 用 SCD1（覆盖）即可
每次事件都要完整流水 → 用变更明细/事件表，不要硬做拉链
一天内多次变更却只存到「日」精度 → 时间粒度不够，历史顺序会糊

设计前先写死三个答案：

业务主键是什么
哪些属性变化要开新版本
下游按日还是按时点追溯（决定 valid_from 用日期还是时间戳）

真实场景（一条）

投放主题里，渠道维表要服务两件事：

日报：按「入账业务日当天」的结算主体汇总消耗
对账：渠道中途改了结算主体，要按变更前后拆开核对

若维表只有最新快照，历史日报重跑会把旧消耗算到新主体上。用 SCD2 拉链表后，任意 biz_date 都能取到「当天有效」的渠道属性。

怎么做（步骤清单）

1. 选定 SCD 类型（一句话）

类型	做法	何时用
SCD1	原地覆盖	纠错、不关心历史
SCD2（拉链）	关旧链 + 开新链	要按历史时点还原
SCD3	加「上一次」列	只关心最近一次变更

本文只落地 SCD2。

2. 定版本字段与时间区间

推荐字段：

字段	含义
业务主键	如 `channel_id`
受管属性	如 `settle_entity`、`coop_status`
`valid_from`	本版本生效时刻
`valid_to`	本版本失效时刻
`is_current`	是否当前版（可选，须与 `valid_to` 规则一致）
`src_updated_at`	源端更新时间（便于排障）

时间区间建议左闭右开：[valid_from, valid_to)

查询某时点：

sql

valid_from <= biz_time
AND biz_time < valid_to

当前版统一：valid_to = '9999-12-31'（或时间戳上限），且 is_current = true。

TIP

相邻版本应首尾相接。用闭区间两端都包含交界日，容易同一天命中两行。

3. DDL 骨架

sql

CREATE TABLE IF NOT EXISTS dim_ad_channel_zipper (
  channel_id      STRING  COMMENT '业务主键',
  channel_name    STRING,
  settle_entity   STRING  COMMENT '结算主体（受管属性）',
  coop_status     STRING  COMMENT '合作状态（受管属性）',
  valid_from      STRING  COMMENT '生效日 yyyy-MM-dd，含',
  valid_to        STRING  COMMENT '失效日 yyyy-MM-dd，不含；当前版=9999-12-31',
  is_current      BOOLEAN COMMENT '是否当前版',
  src_updated_at  TIMESTAMP,
  etl_ts          TIMESTAMP
)
COMMENT '渠道维 SCD2 拉链表';

日级精度示例用 STRING 日期；若一天多变，改成 TIMESTAMP 并保持同一套左闭右开。

4. 日增量：识别变更 → 关旧 → 开新

输入：昨日当前链（is_current = true）+ 今日源端维表快照。

对每个变化键做两件事（同一作业内完成）：

关旧：当前版 valid_to = '${biz_date}'，is_current = false
开新：插入新行，valid_from = '${biz_date}'，valid_to = '9999-12-31'，is_current = true

未变化主键：不动。
源端删除：按业务约定——闭链保留最后状态，或闭链并开一条「已失效」状态；禁止默认静默丢历史。

比较受管属性判断是否变化，不要只看 updated_at。

text

读 is_current 行 C
读今日快照 S
对每个 key：
  若 key 仅在 S → 开新链
  若 key 仅在 C → 按删除语义闭链
  若受管属性不同 → 闭 C 且开新
  否则 → 跳过

最小可跑示意（先算出变更集 tmp_changed，再拼「未变当前版 + 已闭旧版 + 新开链」后 INSERT OVERWRITE 全表；生产可改成按主键合并写入）：

sql

-- tmp_changed: 今日相对 is_current 有受管属性变化的主键（示意）
-- CREATE TEMP TABLE tmp_changed AS
-- SELECT c.channel_id, ...新属性...
-- FROM dim_ad_channel_zipper c
-- JOIN src_ad_channel_snapshot s ON c.channel_id = s.channel_id
-- WHERE c.is_current = true
--   AND (c.settle_entity <> s.settle_entity OR c.coop_status <> s.coop_status);

-- 关旧：当前版闭到 biz_date（不含当日新开）
-- 开新：valid_from = biz_date，valid_to = 9999-12-31
INSERT OVERWRITE TABLE dim_ad_channel_zipper
SELECT channel_id, channel_name, settle_entity, coop_status,
       valid_from, valid_to, is_current, src_updated_at, etl_ts
FROM (
  -- 1) 未变更的当前版：原样保留
  SELECT c.*
  FROM dim_ad_channel_zipper c
  LEFT JOIN tmp_changed x ON c.channel_id = x.channel_id
  WHERE c.is_current = true AND x.channel_id IS NULL

  UNION ALL
  -- 2) 变更键：旧当前版闭链
  SELECT
    c.channel_id, c.channel_name, c.settle_entity, c.coop_status,
    c.valid_from,
    '${biz_date}' AS valid_to,
    false AS is_current,
    c.src_updated_at,
    current_timestamp() AS etl_ts
  FROM dim_ad_channel_zipper c
  INNER JOIN tmp_changed x ON c.channel_id = x.channel_id
  WHERE c.is_current = true

  UNION ALL
  -- 3) 变更键：开新链（属性取自今日快照/变更集）
  SELECT
    x.channel_id, x.channel_name, x.settle_entity, x.coop_status,
    '${biz_date}' AS valid_from,
    '9999-12-31' AS valid_to,
    true AS is_current,
    x.src_updated_at,
    current_timestamp() AS etl_ts
  FROM tmp_changed x

  UNION ALL
  -- 4) 历史非当前版：不动
  SELECT *
  FROM dim_ad_channel_zipper
  WHERE is_current = false
) u;

新增主键、删除语义可在同作业加两段 UNION ALL；关键是关旧与开新必须同次提交，避免只开不关。

5. 回刷（幂等）清单

明确影响日：biz_date = D（以及是否连带重算 D 之后）
回滚「valid_from = D 的新开链」以及「因 D 被错误改写的 valid_to」
从可信快照重放 D 日关旧开新
跑验收 SQL 后再级联下游按日重跑

禁止：只插新行不闭旧链；禁止对同一 biz_date 重复关开导致零长度区间。
建议约束：业务主键 + valid_from 唯一。

6. 查询模板

sql

-- 某业务日当天有效的渠道属性（左闭右开）
SELECT
  channel_id,
  settle_entity,
  coop_status
FROM dim_ad_channel_zipper
WHERE valid_from <= '${biz_date}'
  AND '${biz_date}' < valid_to;

-- 只取当前版
SELECT *
FROM dim_ad_channel_zipper
WHERE is_current = true;

事实表关联时：用事实表的业务日去贴拉链区间，不要永远 Join is_current。

常见坑

交界日重叠：旧 valid_to 与新 valid_from 双边闭区间，同一天两行命中。统一左闭右开。
只看 updated_at 就开链：无意义碰触更新产生空版本。必须比较受管属性。
回刷只插不修：断链、重叠或多条 is_current。按影响范围重放。
把拉链当流水明细：高频事件进拉链会版本爆炸。事件进 DWD，维表才拉链。
下游永远 Join is_current：历史重跑被「今天」污染。按业务日贴区间。

如何验收（可量化）

[ ] 文档写清：主键、受管属性、时间精度、左闭右开、删除语义
[ ] 任一主键同一时刻最多一个有效版本
[ ] 相邻版本无重叠、无非预期断隙（用下方 SQL D 核对：同主键排序后上一版 valid_to = 下一版 valid_from）
[ ] 每个主键至多一条 is_current = true，且 valid_to 为上限值
[ ] 抽样变更前后两天，还原结果与源日快照一致
[ ] 同一 biz_date 重跑两遍，版本行数与关键属性不变

sql

-- A. 同一主键同一天命中多个版本（应为 0 行）
SELECT channel_id, COUNT(*) AS cnt
FROM dim_ad_channel_zipper
WHERE valid_from <= '${biz_date}'
  AND '${biz_date}' < valid_to
GROUP BY channel_id
HAVING COUNT(*) > 1;

-- B. 多个当前版（应为 0 行）
SELECT channel_id, COUNT(*) AS cnt
FROM dim_ad_channel_zipper
WHERE is_current = true
GROUP BY channel_id
HAVING COUNT(*) > 1;

-- C. 历史版倒挂或零长度（应为 0 行）
SELECT *
FROM dim_ad_channel_zipper
WHERE valid_to <> '9999-12-31'
  AND valid_from >= valid_to;

-- D. 非预期断隙/重叠：同主键按 valid_from 排序后，上一版 valid_to 应等于下一版 valid_from（应为 0 行）
SELECT
  a.channel_id,
  a.valid_from AS prev_from,
  a.valid_to   AS prev_to,
  b.valid_from AS next_from,
  b.valid_to   AS next_to
FROM dim_ad_channel_zipper a
JOIN dim_ad_channel_zipper b
  ON a.channel_id = b.channel_id
 AND a.valid_from < b.valid_from
LEFT JOIN dim_ad_channel_zipper mid
  ON mid.channel_id = a.channel_id
 AND mid.valid_from > a.valid_from
 AND mid.valid_from < b.valid_from
WHERE mid.channel_id IS NULL          -- b 是 a 的相邻下一版
  AND a.valid_to <> b.valid_from;     -- 不相等即断隙或重叠错位

选型不确定时，先读快照表 vs 拉链何时用哪个。

拉链表（SCD2）怎么设计与回刷 ​

适用场景 ​

真实场景（一条） ​

怎么做（步骤清单） ​

1. 选定 SCD 类型（一句话） ​

2. 定版本字段与时间区间 ​

3. DDL 骨架 ​

4. 日增量：识别变更 → 关旧 → 开新 ​

5. 回刷（幂等）清单 ​

6. 查询模板 ​

常见坑 ​

如何验收（可量化） ​

相关面试题 ​