ETL数据表增量更新场景下SCD2方案适用性及优势咨询

你当前的需求是典型的增量同步更新场景，现有全量重建目标表的实现方式仅适合小批量测试场景，针对生产环境有更成熟的优化方案：

轻量化方案（仅需保留最新数据时使用） #

如果你的业务只需要保留数据的最新状态，不需要追溯历史变更，直接使用数据库原生的**Upsert（更新插入）**能力即可，相比全量重建的方式IO开销降低90%以上，完全满足你提的三个更新要求：

• 插入不存在的新增记录
• 更新存在变更的已有记录
• 保留未发生变动的原有记录

主流数据库都支持该能力，通用ANSI SQL语法为MERGE，示例代码如下：

MERGE INTO NAMES t -- 目标表（原有存量数据表）
USING NEW_NAMES s -- 增量源表
ON t.Id = s.Id -- 主键匹配规则
WHEN MATCHED THEN -- 主键匹配到，说明是存量变更，执行更新
  UPDATE SET 
    t.Name = s.Name, 
    t.updated_date = '2021-08-17' -- 可替换为增量数据的更新时间、或当前系统时间，按业务规则定
WHEN NOT MATCHED THEN -- 主键未匹配到，说明是新增数据，执行插入
  INSERT (Id, Name, updated_date) 
  VALUES (s.Id, s.Name, '2021-08-17');

不同数据库的方言实现：

• MySQL：INSERT ... ON DUPLICATE KEY UPDATE
• PostgreSQL：INSERT ... ON CONFLICT(主键字段) DO UPDATE
• Oracle、Hive、Spark SQL都原生支持MERGE语法

SCD2方案（需要保留历史变更时使用） #

如果你有历史数据追溯需求（比如需要知道某条数据什么时间发生了什么变更、查询任意历史时间点的数据快照），SCD2（缓慢变化维类型2）是行业通用的标准实现方案。

实现逻辑 #

需要在原表结构基础上新增3个字段：

• start_date：当前版本的生效开始时间
• end_date：当前版本的生效结束时间
• is_current：标识当前版本是不是最新的有效版本
  表结构示例：

CREATE TABLE NAMES_SCD2(
  Id integer, 
  Name text, 
  start_date DATE,
  end_date DATE,
  is_current BOOLEAN,
  PRIMARY KEY (Id, start_date) -- 联合主键，支持同一个ID存储多版本历史
);

同步时的处理规则：

1. 增量源表和存量表主键匹配、且字段值有变更：将存量表中该ID的最新版本的end_date设为本次同步的时间，is_current设为false，再插入新版本的记录，start_date设为本次同步时间，end_date设为'9999-12-31'，is_current设为true
2. 增量源表的主键在存量表中不存在：直接插入新版本记录，参数同上
3. 数据没有发生变更的记录：完全保留原有状态即可

SCD2的核心优势 #

• 完整保留所有数据变更轨迹，支持任意历史时间点的快照查询，比如要查询2021-08-10时ID为1的用户姓名，只需要执行SELECT Name FROM NAMES_SCD2 WHERE Id=1 AND start_date <= '2021-08-10' AND end_date > '2021-08-10'即可拿到结果
• 符合数据仓库建设的规范，适配各类分析类业务需求
  如果你的业务完全没有历史追溯需求，不需要额外使用SCD2，用前面的Upsert方案性价比更高。

内容的提问来源于stack exchange，提问作者0004