2026-10-21
#49 数仓建模 中等

数仓里的拉链表是什么？每天怎么更新？和每日快照怎么选？

拉链表SCD Type2生效区间每日快照

🎯 考察意图

考察候选人会不会把「要当时状态」落成一张可按日重跑的历史表：开闭区间怎么定、每天和全量快照怎么对、关链与开链怎么写。听的是更新算法和查询约定，不是背 SCD 名词。

📐 回答框架

  1. 一句话定义：拉链表用 start_dt / end_dt 把同一主键的每一次有效版本串成一条时间轴，当前行 end_dt 用哨兵日期。
  2. 说清约定：半开半闭 [start_dt, end_dt)、当前行 end_dt='9999-12-31'，避免重叠和空洞。
  3. 讲日更四类变化：不变续命、变更关旧开新、新增开链、消失关链。
  4. 对比每日全量快照：拉链省空间、查任意一天稍复杂；快照简单但存储随天数线性涨。
  5. 主动补查询与回刷：as-of 用区间过滤；重跑某日要能按分区或按日覆盖，不能只 append。

💬 参考答案（口述版）

一句话：拉链表是把维度（或状态）的每一次变更收成「一段有效期」——同一业务主键多行，靠 start_dt、end_dt 串起来；查某一天的状态，就是找那天落在区间里的那一行。

它是 SCD Type 2 在离线数仓里的落地形态。用户改城市、商品改类目、订单改状态，分析要「下单当时」而不是「此刻最新」，就必须留历史。拉链把历史留在一张表里，而不是每天拍一张全量照片。

先把约定钉死，再谈 SQL。 业界常用半开半闭：
- start_dt：本版本生效日（含）
- end_dt：本版本失效日（不含）
- 当前仍有效：end_dt = '9999-12-31'（或 is_current=1）
同一主键任意一天最多命中一行：as_of >= start_dt AND as_of < end_dt。闭区间 end_dt=昨天 也行，但必须全链路同一套，混用就会双计或空洞。

每天怎么更新（T 日跑批，对照 T 日全量快照）。 拿「拉链里仍开着的行」（end_dt=9999）和今日全量快照按主键对：
1. 两边都在且属性没变：什么都不改，继续开着。
2. 两边都在但属性变了：旧行关链，end_dt 改成 T（半开闭下今天起不再有效）；再插一行新版本，start_dt=T，end_dt=9999。
3. 只在今日快照：新开链，插入 start_dt=T, end_dt=9999。
4. 只在开着的拉链里（今天没了）：关链，end_dt=T。删除是软关，不是物理删，历史仍可查。

口述级伪 SQL（日更，分区覆盖当天增量结果再合并）：

-- 当前开链
WITH current AS (
  SELECT * FROM dim_user_zip WHERE end_dt = DATE '9999-12-31'
),
-- 今日全量快照 snap(user_id, city, level, dt=T)
joined AS (
  SELECT
    COALESCE(s.user_id, c.user_id) AS user_id,
    s.user_id IS NOT NULL AS in_today,
    c.user_id IS NOT NULL AS in_zip,
    -- 比较业务属性，不要比代理键或加载时间
    (s.city IS DISTINCT FROM c.city
     OR s.level IS DISTINCT FROM c.level) AS changed
  FROM snap s
  FULL OUTER JOIN current c ON s.user_id = c.user_id
  WHERE s.dt = DATE '2026-10-21'
)
-- 关链：changed 或只在拉链（消失）
-- 开链：changed 或只在今日（新增）
-- 不变行原样保留

落地时不要对整张拉链做行级 UPDATE 当唯一手段：离线仓更稳的是「历史已关闭的行不动 + 当天受影响的开/关链结果用分区或 INSERT OVERWRITE 重写」。这样 T 日任务失败重跑，区间不会被关两次。

和每日全量快照怎么选。
- 每日快照：dim_user_snap PARTITION (dt)，实现简单，查某日就是 WHERE dt=as_of。代价是维度几乎不变也整表存一遍，一年 365 份。
- 拉链：空间近似「当前全量 + 变更次数」，适合属性变得慢、又要任意一天状态的维表。查 as-of 是区间过滤，要会写；近线高 QPS 看板仍常先落一份当日快照。
口诀：变得慢、要当时、存得起历史用拉链；变得勤、只要最近几天、查询要极简用快照。 两者可以并存：拉链是历史真相，近 7 日快照是加速层。

收尾：面试官听三句话——区间约定、四类变化、快照和拉链的取舍。能把「关旧开新」说成可重跑的日更，这题就过了。

🔍 关键词

start_dt/end_dt半开半闭关链开链9999-12-31as-of 查询

💭 面试官可能的追问

追问 1：查「2026-10-01 当时的用户城市」SQL 怎么写？区间为什么要用半开半闭？

半开闭：WHERE user_id=? AND DATE '2026-10-01' >= start_dt AND DATE '2026-10-01' < end_dt。闭区间就要写成 <= end_dt 且当前行的 end_dt 不能再用 9999 以外的「今天」，变更日当天归属哪一版必须全表一致。半开闭的好处是相邻两段首尾相接、不重叠：旧版 [D1, T)，新版 [T, 9999)，as-of=T 只命中新版。用闭区间却两端都含 T，关联事实表会把同一笔订单翻成两行。

追问 2：拉链表怎么按日重跑？中途发现 T-3 的快照错了，怎么回刷？

拉链的正确性依赖「从某日起的变更序列」。回刷 T-3 不能只改那一天的开链行，而要从 T-3 起重放：用 T-3 的快照把当时开着的链重新对一遍，再依次跑 T-2、T-1、T。工程上会把拉链按 start_dt 分区或保留每日快照作上游，回刷就是从出错日把下游拉链任务重跑到今天。不要在已关闭的历史行上点对点 UPDATE，区间会被改乱。更稳的做法是出错日之后的拉链分区全部覆盖生成。

追问 3：事实表关联拉链维度时怎么避免翻倍？代理键还要不要？

关联必须带时间：fact.event_dt >= dim.start_dt AND fact.event_dt < dim.end_dt，只 join 主键会把所有历史版本都挂上来，GMV 直接翻倍。也可以在事实落表时把当时的维度属性退化进去（下单城市、当时等级），查询不再回挂拉链，这是高 QPS 汇总的常用做法。代理键：每开一条新链生成新的 dim_sk，事实保存 dim_sk，查询就不用写区间。拉链（自然键+区间）和代理键（事实挂 sk）可以同时存在，前者方便回放，后者方便星型查询。

⚠️ 常见踩坑

  • 开闭区间混用，变更日当天同一主键命中两行或零行
  • 用加载时间或代理键比较「有没有变」，真实业务属性没变却天天开新链
  • 关联事实表只 join 业务主键，不带 as-of，历史版本把指标撑爆
  • 对整表行级 UPDATE 关链，任务重跑把 end_dt 改第二次，区间出现空洞

🧠 记忆锚点

拉链用区间留当时状态：半开闭约定死，每天关旧开新，关联事实必须带 as-of。