Azure Databricks中SCD Type1合并操作致历史数据异常求助

1. 核查Merge Into语句的核心逻辑 #

• 确认语句是否误包含WHEN MATCHED THEN DELETE分支，该逻辑会直接删除匹配的历史行，是导致行数减少的最直接原因。示例错误语句：

  MERGE INTO target_table t
  USING source_table s
  ON t.business_id = s.business_id
  WHEN MATCHED THEN DELETE -- 此分支会删除匹配的历史记录
  WHEN NOT MATCHED THEN INSERT *
  

• 验证匹配键是否为唯一业务主键，若使用非唯一键（如非唯一的分类字段），会导致多条历史记录被错误匹配更新，甚至因后续数据过滤逻辑间接减少行数。

2. 检查源数据的匹配键重复情况 #

• 统计2023年11月29日源数据中匹配键的重复次数，重复键可能引发异常更新逻辑：

  SELECT business_id, COUNT(*) AS duplicate_count
  FROM source_20231129
  GROUP BY business_id
  HAVING duplicate_count > 1
  

3. 验证目标表的分区与数据清理规则 #

• 若目标表为分区表，检查是否存在自动清理（如TTL）或手动删除分区的操作，导致20231128分区数据丢失：

  SELECT partition_date, COUNT(*) AS row_count
  FROM target_table
  GROUP BY partition_date
  

  对比首次加载后与二次加载后的分区行数，确认是否为分区数据丢失导致历史行数减少。

4. 分析Merge操作的执行日志 #

• 在Databricks作业日志中查看Merge操作的详细统计：
  • 重点关注updated rows、inserted rows、deleted rows的数值，若存在deleted rows统计，说明语句包含Delete逻辑。
  • 验证行数变化是否符合预期：正常SCD Type1的总行数应为初始行数 + (源数据行数 - 匹配更新行数)。

5. 排查目标表的约束与触发器 #

• 确认目标表是否存在主键约束或自定义触发器，某些约束可能在更新时触发隐性数据清理（如Delta Lake强制执行主键约束时，若源数据匹配多条历史行，可能导致部分行被标记为无效）。

6. 对比两次加载前后的目标表数据 #

• 定位消失的500行数据，核查其匹配键是否存在于11月29日的源数据中：

  -- 找出首次加载后存在、二次加载后消失的记录
  SELECT *
  FROM target_table_initial_20231128
  EXCEPT
  SELECT *
  FROM target_table_after_20231129
  

  检查这些记录的匹配键是否在源数据中，确认是否被误删除或覆盖。

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