缓慢变化维与拉链表

本章目标：能根据业务问题选择 SCD1、SCD2、SCD3，能手写拉链表的开链与闭链更新 SQL，并查询任意历史时点的维度状态。 前置知识：维度建模、窗口函数 预计用时：90 分钟

维度不是永远不变的。用户会升级会员、商品会换品类、商家会改名字。如果只保存最新值，历史分析就会悄悄失真。拉链表就是解决这个问题的标准方案。

1. 问题：只存最新状态会丢什么

项目里的 customers 只有一份当前快照：

假设 C100001 在 2017 年下单时还是普通会员，2018 年 6 月升级成金牌。今天做“2017 年各等级用户贡献 GMV”的报表，如果按当前等级关联，会把 2017 年的订单算到金牌会员头上。

这类“会变化、但变化不频繁”的维度叫 缓慢变化维（Slowly Changing Dimension，SCD）。它带来的三个问题：

1. 历史不可追溯：查不到“某笔订单发生时，用户是什么等级”。
2. 归因会漂移：用户等级一升级，所有历史订单的等级归属都变了。
3. 无法审计：不知道等级是哪天改的、改之前是什么。

2. SCD 的三种常见方案

以 C100001 从普通会员升到银牌会员为例。

2.1 SCD1：直接覆盖

UPDATE dim_customer
SET customer_level = '银牌会员'
WHERE customer_id = 'C100001';

实现简单，表不会变长，但历史版本永久丢失。适合“改错了要纠正”的属性，例如手机号、地址；不适合需要历史归因的会员等级。

2.2 SCD2：增加版本行

保留多行，每一行代表一个有效期：

这就是拉链表。它能回答任意时点的状态，代价是表会增长，查询必须带时间条件。

2.3 SCD3：增加旧值列

customer_id | current_level | previous_level | change_date
C100001     | 银牌会员      | 普通会员        | 2018-06-01

只保留一个历史版本，适合“只关心上一次是什么”的场景，例如商品调价前后对比。变化超过两次，历史仍然丢失。

2.4 三种方案对比

会员等级直接影响历史 GMV 归因，项目应该用 SCD2 拉链表。

3. 拉链表的结构

3.1 表设计

CREATE TABLE dim_customer_zip (
    customer_id     STRING,
    customer_level  STRING,
    start_date      DATE,
    end_date        DATE
);

每一行是一个版本，start_date 到 end_date 是该版本的生效区间，闭区间。业务主键是 customer_id + start_date。

3.2 开链与闭链

• 开链：新版本写入时，start_date = 变化日期，end_date = 9999-12-31，表示当前仍然有效。
• 闭链：旧版本失效时，把 end_date 改成“新版本开始日期的前一天”。

C100001 普通会员  [2017-01-01 ─────────────── 2018-05-31]
C100001 银牌会员                          [2018-06-01 ────── 9999-12-31]

9999-12-31 是数仓约定俗成的“无穷远”日期，表示开链。不要用 NULL 表示当前有效，因为 BETWEEN、比较运算遇到 NULL 会得到 NULL，查询容易漏行。

3.3 三个不变量

一张合格的拉链表必须满足：

1. 每个客户在任意时点最多有一个版本，版本之间不能重叠。
2. 每个客户只有一个 end_date = 9999-12-31 的开链行。
3. 相邻版本满足“上一行 end_date + 1 天 = 下一行 start_date”，除业务允许的空档外不能断档。

注意：区间用闭区间，所以旧版本结束日是“新开始日减 1 天”，不是新开始日当天。写成同一天会导致同一天两个版本。

4. 每日更新：开链与闭链

4.1 初始表与增量数据

初始拉链表：

2018-06-01 的增量快照 ods_customers_delta：

C100001 等级变了，C100002 没变，C100004 是新用户，C100003 今天没出现在增量里。

4.2 更新后的结果

4.3 四段式 UNION ALL 写法

把结果拆成四部分，思路最清楚：

CREATE OR REPLACE TABLE dim_customer_zip_new AS
-- 1. 已经闭链的历史行，原样保留
SELECT customer_id, customer_level, start_date, end_date
FROM dim_customer_zip
WHERE end_date < DATE '9999-12-31'

UNION ALL

-- 2. 当前开链、且今天没变化（或今天没出现）的行，原样保留
SELECT z.customer_id, z.customer_level, z.start_date, z.end_date
FROM dim_customer_zip z
LEFT JOIN ods_customers_delta d ON z.customer_id = d.customer_id
WHERE z.end_date = DATE '9999-12-31'
  AND (d.customer_id IS NULL
       OR COALESCE(d.customer_level, '未知') = COALESCE(z.customer_level, '未知'))

UNION ALL

-- 3. 当前开链、但等级发生变化的行：闭链，结束日 = 新开始日 - 1
SELECT z.customer_id, z.customer_level, z.start_date,
       CAST(d.dt - INTERVAL '1' DAY AS DATE) AS end_date
FROM dim_customer_zip z
JOIN ods_customers_delta d ON z.customer_id = d.customer_id
WHERE z.end_date = DATE '9999-12-31'
  AND COALESCE(d.customer_level, '未知') <> COALESCE(z.customer_level, '未知')

UNION ALL

-- 4. 新用户或等级变化的用户：开一条新链
SELECT d.customer_id, d.customer_level, d.dt, DATE '9999-12-31'
FROM ods_customers_delta d
LEFT JOIN dim_customer_zip z
       ON d.customer_id = z.customer_id
      AND z.end_date = DATE '9999-12-31'
WHERE z.customer_id IS NULL
   OR COALESCE(d.customer_level, '未知') <> COALESCE(z.customer_level, '未知');

写完新表后，校验行数和开链数，再替换旧表：

DROP TABLE dim_customer_zip;
ALTER TABLE dim_customer_zip_new RENAME TO dim_customer_zip;

注意：DuckDB 支持 d.dt - INTERVAL '1' DAY。Hive 用 date_sub(d.dt, 1)，且传统表不支持原地 DROP/RENAME 换表。生产上更常见的是建一张按天分区的全量拉链快照表（DDL 需加 PARTITIONED BY (dt STRING)，本节 3.1 的示例没有分区），每天 INSERT OVERWRITE ... PARTITION (dt = '2018-06-01') 写入当天快照，而不是原地替换一张非分区表。

4.4 为什么用 UNION ALL 而不是逐行 UPDATE

逐行 UPDATE 在 Hive 传统表上不支持，在分布式环境里也很慢。UNION ALL 是“重算一份新快照再覆盖”，天然幂等：同一天跑十次，结果一样。

代价是每次都要扫全表。拉链表通常不大（维度表），这个代价可以接受。真正大的事实表不会用这种方式更新。

5. 查询历史时点状态

5.1 查某个客户在某天的状态

SELECT customer_id, customer_level, start_date, end_date
FROM dim_customer_zip
WHERE customer_id = 'C100001'
  AND start_date <= DATE '2018-03-15'
  AND end_date >= DATE '2018-03-15';

结果应返回“普通会员”。如果返回多行，说明版本区间重叠，数据质量有问题。

5.2 订单按下单时点关联会员等级

这是拉链表最典型的用法：

SELECT
    o.order_id,
    o.order_date,
    o.customer_id,
    z.customer_level
FROM orders o
LEFT JOIN dim_customer_zip z
       ON o.customer_id = z.customer_id
      AND CAST(o.order_date AS DATE) BETWEEN z.start_date AND z.end_date
WHERE o.order_status NOT IN ('canceled', 'unavailable')
  AND o.order_date >= DATE '2017-01-01'
  AND o.order_date < DATE '2018-09-01';

这样 2018 年 5 月之前的订单会关联到普通会员，2018 年 6 月之后的订单关联到银牌会员，历史归因不会随今天的等级变化而漂移。

5.3 查某天各等级人数

SELECT customer_level, COUNT(*) AS customer_cnt
FROM dim_customer_zip
WHERE start_date <= DATE '2018-07-01'
  AND end_date >= DATE '2018-07-01'
GROUP BY customer_level
ORDER BY customer_cnt DESC;

5.4 查当前状态

SELECT *
FROM dim_customer_zip
WHERE end_date = DATE '9999-12-31';

不要用 end_date IS NULL，也不要用 end_date > CURRENT_DATE，这两种写法容易把未来预约生效的版本也算进来。

另一种常见写法是增加布尔列 is_current（或 is_valid），查询时 WHERE is_current = TRUE。它读起来更直观，但和 end_date 表达的是同一件事，必须在同一条更新语句里一起维护；漏改一个字段就会出现“is_current = TRUE 但 end_date 已闭链”的矛盾。本节的闭区间写法只有一个事实来源，质量检查也更容易写。

5.5 质量检查

重叠检查：

SELECT a.customer_id,
       a.start_date AS a_start, a.end_date AS a_end,
       b.start_date AS b_start, b.end_date AS b_end
FROM dim_customer_zip a
JOIN dim_customer_zip b
  ON a.customer_id = b.customer_id
 AND a.start_date < b.start_date
 AND a.end_date >= b.start_date;

多个开链检查：

SELECT customer_id, COUNT(*) AS open_cnt
FROM dim_customer_zip
WHERE end_date = DATE '9999-12-31'
GROUP BY customer_id
HAVING COUNT(*) > 1;

起止日期非法：

SELECT *
FROM dim_customer_zip
WHERE start_date > end_date;

三条检查都应该返回 0 行。

6. 项目没做拉链表，怎么补

6.1 现状与影响

项目的 customers 只保存当前会员等级，dwd_order_detail 直接关联当前等级。这意味着：

• “历史订单发生时的等级”无法还原。
• 用户一升级，历史 GMV 的等级归因会被重写。
• 1,242 个没有有效消费的客户被标成“未知”，但无法区分“当时没有等级”和“后来等级被清空”。

这不是项目 bug，而是数据源只提供了快照。不知道历史时，不能假装知道。

6.2 补救步骤

1. 立即开始留快照：每天把 customers 落到 ods_customers_snapshot 的 dt 分区，哪怕全量有 99,441 行。
2. 从快照生成拉链：第一天所有用户开链；之后每天对比前一天快照，等级变化的闭链并开新链。
3. 有变更日志则回填：如果源库有 customer_level_update_log，按变更时间回填历史版本。
4. 没有历史就标注边界：拉链表从开始采集的日期生效，之前的区间统一标记为“未知（无历史）”，不要伪造等级。
5. 改造 DWD 关联：dwd_order_detail 按订单日期做区间关联，而不是只关联当前开链行。
6. 加质量校验：重叠、多个开链、非法日期、断档四类检查纳入每日任务。

6.3 最小可行方案

如果只能做一件事，做第 1 步：每天保存一份用户快照。有了快照，未来任何时候都能重建拉链表；没有快照，历史永远补不回来。

提示：快照表按天分区全量存，9.9 万行一年约 3,630 万行，成本很低。不要为了省这点存储，丢掉未来做历史归因的能力。

7. 常见坑

1. 用 NULL 表示当前版本：比较运算返回 NULL，查询漏行，统一用 9999-12-31。
2. 结束日写成新开始日当天：同一天出现两个版本，区间重叠。
3. 只改旧行不追加新行：旧版本闭链了，但新状态没写进去，客户从某天起“消失”。
4. 关联不带时间条件：订单 JOIN 拉链表后行数翻倍，GMV 被放大。
5. 用支付日期代替下单日期：归因时点不同，等级可能已经变化。
6. 空值比较直接用 <>：NULL <> '银牌' 结果是 NULL，变化检测会漏。要用 COALESCE 或空安全比较。
7. 忘记处理新用户：只更新已有客户，新客户没有开链行。

8. 在线练习

自测：

• 能判断会员等级、手机号、商品价格分别适合 SCD1、SCD2 还是 SCD3，并说明理由。
• 能写出按 valid_from / valid_to 查询历史状态的 SQL。
• 能解释拉链表里 is_current 和开闭区间两种写法的区别。
• 能说明为什么没有历史变化需求的维表不必强行套拉链表。

9. 面试延伸

面试题：拉链表和每天存全量快照，怎么选？

是什么：拉链表用 start_date / end_date 只存变化版本；全量快照每天存一份完整维度。

解决什么问题：拉链表省空间，适合变化不频繁的维度；全量快照实现简单，适合小维度，也方便直接查任意一天。9.9 万行的用户表每天存一份，一年约 3,630 万行，成本完全可以接受。

我的实操边界：项目里只存了当前等级，没有实现拉链表；我手写过四段式开链闭链 SQL，理解快照和拉链的取舍，但没有在生产调度里维护过拉链任务。

面试题：拉链表每天怎么更新？

是什么：拉链表按版本保存维度历史，当前版本 end_date = 9999-12-31。

解决什么问题：每天拿增量快照和当前开链行比较：没变的保留；变化的旧行闭链到新开始日减一天，再追加一条新开链行；新用户直接开链。

我的实操边界：我用 UNION ALL 四段式在本地实现过，包含历史闭链行、未变化行、变化闭链行、新增开链行四部分；没有处理过迟到变更和跨天补数。

面试题：维度数据迟到或变更日期不准怎么办？

是什么：迟到数据是指今天的增量里出现了昨天甚至更早生效的变更。

解决什么问题：如果直接按今天开链，历史区间会错。需要按业务生效日期重算受影响区间，或者重跑受影响日期的分区。常见做法是保留变更日志，按生效日期排序后重建该客户的版本链。

我的实操边界：我理解要按业务生效日期而不是入库日期开链，但没有在生产环境处理过迟到维度的回刷。

10. 本章验收

• 能说出 SCD1、SCD2、SCD3 的区别，并为三个属性分别选型
• 能解释 9999-12-31 的含义，以及为什么不用 NULL
• 能独立写出拉链表四段式更新 SQL
• 能写出“订单按下单时点关联历史等级”的 SQL
• 能写出重叠、多个开链、非法日期三类质量检查
• 能说明项目没有拉链表的影响，并给出至少三步补救方案