多源数据向data warehouse执行upsert操作的典型设计模式是什么

• 全局唯一业务主键方案
  解决多源表主键冲突的核心手段是新增通用数据源标识字段，和源表原生主键拼接为全局唯一的业务主键，拼接逻辑可以用CONCAT(数据源编码, '_', 源表主键值)实现，不同数据源的编码提前定义唯一值即可，不用修改源表的任何结构。
• 增量规则元数据配置模式
  额外维护一张数仓同步元数据配置表，存储所有待同步源表的属性：数据源编码、源表名、主键字段名、创建时间字段名、更新时间字段名、同步周期、是否启用同步等。
  夜间跑批任务先读取该配置表动态生成每个表的增量抽取逻辑，不用为每张表单独硬编码同步脚本：
  • 对有可靠更新时间字段的表，直接抽取更新时间 >= 上次跑批结束时间或者创建时间 >= 上次跑批结束时间的增量数据
  • 对没有可靠时间字段的表，自动切换为全量抽取主键+整行哈希，和数仓存量数据比对的逻辑
• 分层增量处理架构
  数仓ODS层保留源表全量原始字段，同时新增3个通用系统字段：
  • dw_source_code：数据来源的数据源编码
  • dw_load_time：数据写入ODS层的时间
  • dw_row_hash：源表所有原生字段拼接后计算的哈希值，可使用HASHBYTES函数实现
    到DWD层做合并操作时，直接基于全局唯一业务主键做关联，行哈希不一致就触发更新，主键不存在就触发插入，通用合并逻辑参考伪代码：

  MERGE INTO dwd_业务表 tgt
  USING (
      SELECT 
          CONCAT(dw_source_code, '_', 源主键字段) AS global_biz_key,
          业务字段1, 业务字段2, ...,
          HASHBYTES('SHA2_256', CONCAT_WS('|', 业务字段1, 业务字段2, ...)) AS dw_row_hash
      FROM ods_当日增量表
  ) src
  ON tgt.global_biz_key = src.global_biz_key
  WHEN MATCHED AND tgt.dw_row_hash != src.dw_row_hash THEN
      UPDATE SET 业务字段1 = src.业务字段1, 业务字段2 = src.业务字段2, ..., dw_update_time = CURRENT_TIMESTAMP
  WHEN NOT MATCHED THEN
      INSERT (global_biz_key, 业务字段1, 业务字段2, ..., dw_create_time, dw_update_time, dw_row_hash)
      VALUES (src.global_biz_key, src.业务字段1, src.业务字段2, ..., CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, src.dw_row_hash)
  

• CDC适配兼容层（可选扩展）
  如果后续源数据库支持CDC能力（比如SQL Server自带CDC、MySQL的Binlog），可以新增统一的CDC适配层，把不同数据库的变更日志转换成统一格式的增量数据，标记新增/更新/删除操作类型，相比时间戳抽取的准确率更高，还能捕获源表物理删除的记录，适合对数据一致性要求高的场景。

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