如何在Azure Data Factory中实现基于源表last_modified_date的SCD Type 2逻辑？

以下是适配你需求的具体实现步骤，核心是替换默认的系统日期逻辑，改用源表的last_modified_date控制维度记录的生效与过期：

1. 准备数据源与目标表 #

• 源表：需包含业务主键、待追踪变化的维度字段，以及核心的last_modified_date字段（记录数据新增/修改的时间）
• 目标维度表：需包含业务主键、维度字段，以及start_date（记录生效时间）、end_date（记录过期时间）、is_current（可选，标记当前有效记录）字段

2. 构建数据流核心逻辑 #

2.1 关联源数据与当前有效维度记录 #

• 添加Lookup转换：从目标表拉取所有is_current = 'Y'（或end_date IS NULL）的当前有效记录，关联条件设为业务主键相等
• 添加Join转换：将源数据集与Lookup结果做全外连接，关联字段为业务主键，以此区分三类记录：
  • 新增记录：Lookup结果为空（业务主键在目标表不存在）
  • 变更记录：Lookup结果存在，且源表维度字段与目标记录不一致（需额外判断源.last_modified_date > 目标.start_date，避免重复处理历史变更）
  • 无变化记录：Lookup结果存在且维度字段完全一致，直接过滤丢弃

2.2 处理新增记录 #

添加Derived Column转换，为新增记录设置维度字段：

• start_date = last_modified_date（直接取源表的时间字段）
• end_date = '9999-12-31'（或设为NULL，根据你的数据规范选择）
• is_current = 'Y'
  之后将该分支以插入模式写入目标表

2.3 处理变更记录 #

变更记录需要分两个分支处理：

分支1：过期原有有效记录

添加Derived Column转换，更新原有记录的过期属性：

• end_date = 源.last_modified_date（用源表的修改时间作为原有记录的过期时间）
• is_current = 'N'
  之后将该分支以更新模式写入目标表，匹配条件设为业务主键 = 目标.业务主键 AND is_current = 'Y'

分支2：插入新的当前有效记录

添加Derived Column转换，生成新的维度记录：

• start_date = last_modified_date（取源表的修改时间作为新记录的生效时间）
• end_date = '9999-12-31'（或NULL）
• is_current = 'Y'
  之后将该分支以插入模式写入目标表

3. 用内置SCD转换简化配置（可选） #

如果使用ADF内置的SCD转换，需修改默认日期逻辑：

• 在SCD转换的「Type 2 Settings」中：
  • 将Start Date Column的默认表达式currentDate()替换为源.last_modified_date
  • 将End Date Column的默认表达式currentDate()替换为源.last_modified_date
  • 确保「Expire existing rows」的触发条件为维度字段变更，且日期值取对应源记录的last_modified_date

4. 验证逻辑 #

• 新增记录：检查目标表中对应记录的start_date与源表last_modified_date完全一致
• 变更记录：检查原有记录的end_date等于源表修改时间，新插入记录的start_date为该时间且is_current = 'Y'

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