基于Databricks Delta Live Tables实现多源表Type 2缓慢变化维度

针对多源表构建SCD Type2的需求，推荐采用分层处理+统一变更捕获的方案，替代复杂Union或多流关联，逻辑更清晰且维护性更强：

步骤1：为每张源表构建CDC变更捕获层 #

先通过DLT流处理捕获每张源表的新增/变更记录，同时标记变更时间和来源表，确保后续能追踪到各表的最新状态：

-- 捕获person表的变更（按需保留关联所需字段）
CREATE STREAMING LIVE TABLE person_cdc
COMMENT "CDC流：捕获person表的变更记录"
AS SELECT
  PersonID,
  current_timestamp() AS change_timestamp,
  'person' AS source_table
FROM STREAM(live.person); -- 替换为实际源表路径/名称

-- 捕获personDetail表的变更
CREATE STREAMING LIVE TABLE person_detail_cdc
COMMENT "CDC流：捕获personDetail表的变更记录"
AS SELECT
  PersonID,
  detail,
  current_timestamp() AS change_timestamp,
  'personDetail' AS source_table
FROM STREAM(live.personDetail);

-- 捕获job表的变更（若job表通过JobID关联，需调整关联键逻辑）
CREATE STREAMING LIVE TABLE job_cdc
COMMENT "CDC流：捕获job表的变更记录"
AS SELECT
  PersonID, -- 假设job表通过PersonID与主表关联，实际按业务调整
  jobName,
  current_timestamp() AS change_timestamp,
  'job' AS source_table
FROM STREAM(live.job);

步骤2：构建统一的全量整合层 #

基于CDC层，通过窗口函数获取每个PersonID在各表的最新记录，再通过全外关联组合成完整的维度快照，确保任意一张源表变更时，都能生成最新的全量维度数据：

CREATE LIVE TABLE unified_person_dim_source
COMMENT "整合层：合并各表最新数据，生成完整维度快照"
AS WITH latest_person AS (
  -- 获取每个PersonID的最新person表记录
  SELECT PersonID, change_timestamp
  FROM (
    SELECT 
      PersonID, 
      change_timestamp,
      ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY change_timestamp DESC) AS rn
    FROM live.person_cdc
  ) WHERE rn = 1
),
latest_detail AS (
  -- 获取每个PersonID的最新personDetail表记录
  SELECT PersonID, detail, change_timestamp
  FROM (
    SELECT 
      PersonID, 
      detail,
      change_timestamp,
      ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY change_timestamp DESC) AS rn
    FROM live.person_detail_cdc
  ) WHERE rn = 1
),
latest_job AS (
  -- 获取每个PersonID的最新job表记录
  SELECT PersonID, jobName, change_timestamp
  FROM (
    SELECT 
      PersonID, 
      jobName,
      change_timestamp,
      ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY change_timestamp DESC) AS rn
    FROM live.job_cdc
  ) WHERE rn = 1
)
-- 全外关联合并，生成完整维度数据
SELECT
  COALESCE(p.PersonID, d.PersonID, j.PersonID) AS PersonID,
  d.detail,
  j.jobName,
  GREATEST(p.change_timestamp, d.change_timestamp, j.change_timestamp) AS effective_timestamp
FROM latest_person p
FULL OUTER JOIN latest_detail d ON p.PersonID = d.PersonID
FULL OUTER JOIN latest_job j ON COALESCE(p.PersonID, d.PersonID) = j.PersonID;

步骤3：基于整合层构建SCD Type2表 #

利用DLT原生的APPLY CHANGES INTO语法，自动处理SCD Type2的版本生成、过期标记，无需手动编写复杂的增量逻辑：

CREATE LIVE TABLE person_dim_scd2
TBLPROPERTIES (
  'delta.enableChangeDataFeed' = 'true'
)
COMMENT "Type2缓慢变化维度：Person全量维度数据";

-- 自动应用变更，生成SCD Type2版本
APPLY CHANGES INTO live.person_dim_scd2
FROM live.unified_person_dim_source
KEYS (PersonID) -- 维度主键
SEQUENCE BY effective_timestamp -- 版本排序依据（变更时间）
COLUMNS (detail, jobName) -- 需要追踪变更的字段
STORED AS SCD TYPE 2;

方案优势 #

• 分层架构清晰，CDC层→整合层→SCD层的拆分便于问题排查和维护
• 自动捕获任意源表的变更，只要某张表的字段发生变化，整合层就会生成最新快照，触发SCD版本更新
• 避免了复杂Union带来的重复数据问题，通过窗口函数确保每个PersonID只保留各表的最新记录
• 利用DLT原生语法简化SCD逻辑，无需手动处理过期标记、增量对比等细节

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