视频讲解/数仓建模

SCD 和拉链表

用户搬家了历史咋办?

章节

  • 0:00 开场
  • 0:25 一句话回答
  • 0:52 痛点 · 维度会变
  • 1:41 Type 1 · 直接覆盖
  • 2:13 Type 2 · 拉链表
  • 3:40 Type 3 · 加列
  • 4:04 三种方式对比
  • 4:40 拉链表每日更新 SQL
  • 5:31 补充 ① · 拉链 vs 全量快照
  • 6:11 补充 ② · Hive 实现技巧
  • 6:44 补充 ③ · 查询性能优化
  • 7:08 完整决策树
  • 7:31 高频追问
  • 9:02 面试回答模板

文稿

开场

面试官问你:说说缓慢变化维和拉链表?你脱口而出:SCD 有 Type 1、Type 2、Type 3 三种。面试官追问:那你现场写一段拉链表的更新 SQL?项目里选了哪种,为什么?大部分人就卡在这里。今天用动画,把这道数仓建模必考题讲透。

一句话回答

先给一句话答案:缓慢变化维,解决的是维度属性会变化的问题,比如用户改了城市、商品调了价格。三种处理方式:Type 1 直接覆盖,丢历史;Type 2 拉链表,保留完整变更历史;Type 3 加列,只留最近一次。生产用得最多的是拉链表:用 start_date 和 end_date 标记有效期,能还原任意时刻的维度状态。

痛点 · 维度会变

先搞清楚:维度变化,为什么是个问题?张三1月1日在北京注册,1月15日下了一笔1000元的订单。3月1日,他搬到了上海,用户表的城市被更新成上海。6月1日,老板要看一张报表:1月份各城市的 GMV 是多少?如果维度表直接覆盖了城市,那笔订单 JOIN 后就算到上海:北京少了1000,上海多了1000。核心问题:不记录历史,事实表 JOIN 到的永远是最新值,历史分析就是错的。就像身份证地址改成了上海浦东,如果只留最新地址,你在北京办过的业务,全显示上海浦东。这类变化不频繁,但确实会变:几年搬一次家,几个月调一次价,所以叫缓慢变化维。

Type 1 · 直接覆盖

第一种,Type 1:直接覆盖。属性变了,就用新值覆盖旧值,不保留任何历史。SQL 就一句 UPDATE,把1001号用户的城市改成上海,执行后,北京就消失了。优点:简单,表不膨胀。缺点:历史丢失,回答不了张三1月下单时在哪个城市。它适合不需要历史分析的场景,比如修正录入错误,名字打错了改一下。就像手机换号,直接换成新号码,旧号没了,谁也不知道你以前用什么号。

Type 2 · 拉链表

第二种,Type 2:拉链表。保留完整历史,也是生产最常用的。属性每变化一次,就新增一行,用 start_date 和 end_date 标记这一行的有效期。建表时,在业务字段后面加上生效日期、失效日期,9999-12-31 表示当前有效,再加 is_active 标记。张三在拉链表里有三行。第一行:1月1日到1月31日,在北京,等级普通。第二行:2月1日到2月28日,升了 VIP,还在北京。第三行:3月1日至今,搬到上海,end_date 是 9999-12-31,is_active 等于1。查任意时刻的状态很简单。查1月15日的城市:日期落在 start_date 和 end_date 之间,结果是北京。查现在的状态,直接过滤 is_active 等于1,结果是上海 VIP。事实表关联时,用订单的下单时间,去匹配拉链表的有效期,就能拿到下单那一刻的城市。优点:完整保留历史。缺点:每次变更多一行,表会膨胀,JOIN 还要加时间范围条件。它是生产环境的首选:用户城市、会员等级、商品价格,这些要追踪历史的维度都用它。就像个人信用报告:每次住址、工作变更都有记录,银行想查你三年前住哪,一翻就知道。

Type 3 · 加列

第三种,Type 3:加列,只保留最近一次变更。在维度表里加一个 previous_city 列,城市变了,先把旧值存进去,再用新值覆盖 city。优点:简单,不增加行数。缺点:只记得最近一次,张三从北京到上海再到深圳,北京就丢了。它适合只看变更前后的场景,比如用户升级前后的消费对比。

三种方式对比

把三种方式放在一起对比。历史保留:Type 1 不保留,Type 2 保留完整历史,Type 3 只留最近一次。表行数:Type 2 每次变更加一行,会膨胀;另外两种不变。实现复杂度:Type 1 一句 UPDATE,Type 3 加列再 UPDATE,Type 2 要写 MERGE 逻辑,最复杂。查询上,Type 2 要做时间范围 JOIN;存储开销,也是它最大。但使用频率 Type 2 最高,是生产首选;Type 1 用来修错数据,Type 3 用来做变更前后对比。

拉链表每日更新 SQL

接下来,是面试最常被要求现场写的 SQL:拉链表的每日更新。假设每天凌晨,把用户全量快照同步到 ods_user_snapshot,拉链表叫 dim_user_zipper。第一步,比:快照 LEFT JOIN 拉链表的有效记录,关联不上的是新增用户。城市或等级不一样的是变化用户,一起放进 changed_users。第二步,关链:变化用户当前有效的旧记录,end_date 设为昨天,is_active 设为0。第三步,开链:插入新状态,start_date 是今天,end_date 是 9999-12-31,is_active 是1。整个过程三个字:比、关、开。比出变化,关掉旧记录,开出新记录。

补充 ① · 拉链 vs 全量快照

补充一:另一种保留历史的方式,是每天存一份全量快照,每天一个分区。最简单粗暴,但存储浪费极大。算笔账:500万用户存365天,就是18.25亿行,约500 GB。拉链表只存变化:500万,加上每天变化5万乘365,一共2325万行,约6 GB。存储只有全量快照的80分之一。用户量越大、变化越少,差距越夸张。但全量快照查询简单,直接按分区查,排查也方便。所以有些公司 ODS 层留快照,DIM 层用拉链表。

补充 ② · Hive 实现技巧

补充二:Hive 不支持 UPDATE,关链没法用 UPDATE,得用 INSERT OVERWRITE 全量重写。写成三段 UNION ALL。第一段:没变化的用户,记录原样保留。第二段:变化用户的旧记录,end_date 改成昨天,is_active 改成0,完成关链。第三段:变化用户的新记录,start_date 是今天,end_date 是 9999-12-31,完成开链。虽然是全量重写,但 Hive 会利用分区和 ORC 的列裁剪来优化,实际开销可控。

补充 ③ · 查询性能优化

补充三:拉链表查询要加时间范围条件,数据量大时会比较慢。常用两个优化。手段一:加 is_active 列,再分桶。查当前状态直接过滤 is_active 等于1,不扫历史数据。手段二:按 end_date 分区,9999-12-31 的记录单独一个分区,查当前状态只扫它,速度极快。

完整决策树

串成一棵决策树:维度属性变了,先问要不要保留历史?不需要,就用 Type 1 直接覆盖。需要的话,回溯任意时刻用 Type 2 拉链表;只要最近一次,用 Type 3 加列。用了拉链表,再做优化:按 end_date 分区,加 is_active 列,ODS 留快照、DIM 用拉链表。

高频追问

再来看四个高频追问。一:一天内同一个用户变了多次,怎么办?这取决于快照粒度。每天只同步一次全量快照,就只能捕获最后一次,中间过程丢了。要更细的粒度,就用 CDC:通过 Binlog 捕获每一次变更,每次变更都在拉链表新增一行。Flink CDC 加拉链表,是实时场景的主流组合,能做到分钟级甚至秒级的追踪。二:拉链表怎么初始化?把业务库全量数据导入,start_date 设为上线日期或数仓起始日期。end_date 设为 9999-12-31,is_active 设为1。从第二天开始,每天执行比、关、开。如果已经有历史快照,也可以拿相邻两天的快照做对比,回溯出拉链表。三:ETL 跑错了能回滚吗?能,但要提前准备:每天更新前先备份,或用分区保留前一天的快照。跑错了就用备份恢复,重跑当天逻辑。用了 Iceberg、Delta Lake,还能 Time Travel 直接回退。四:拉链表和全量快照怎么选?看规模和变化率。几万条的配置表每天变30%,用快照更简单。500万用户每天只变1%,用拉链表,存储只有快照的80分之一。经验公式:每日变化率低于5%、总行数超过100万,拉链表 ROI 最高。也有公司 ODS 层留近30天的快照,方便排查,DIM 层用拉链表,省空间。

面试回答模板

最后,面试回答模板来了。缓慢变化维,解决维度属性会变化的问题。Type 1 直接覆盖,简单但丢历史,只适合修正录入错误。Type 2 拉链表,每次变更新增一行,用 start_date、end_date 标记有效期,生产用得最多。事实表用下单时间和有效期做范围 JOIN,就能关联到下单时刻的真实城市。Type 3 加 previous 字段,只记最近一次,用得少。拉链表日常维护三步:比、关、开;查询优化,按 end_date 分区,查最新状态只扫一个分区。记住一句话:拉链表不覆盖历史,只给每个版本标上有效期。

看完去做题