BigQuery实现SCD Type2合并逻辑时目标表出现重复数据问题排查
SCD Type2 BigQuery实现重复插入问题排查与修复
问题根因
- 第三步筛选逻辑存在核心漏洞:第一步MERGE操作已经将发生变更的历史活跃记录标记为
current_flg = 'N',而第三步的WHERE条件会匹配所有y.current_flg = 'N'且校验和不一致的记录,被标记为非活跃的历史记录会永久满足该匹配条件。只要源表数据没有发生新的变更,每次运行存储过程都会匹配到同一条非活跃历史记录,反复插入相同的新版本数据。 - 额外逻辑漏洞:第三步插入操作没有前置校验逻辑,未判断目标表中是否已经存在对应指纹、相同校验和的活跃记录,直接触发插入导致重复。
修复方案
方案1:修改第三步筛选逻辑(最小改动)
仅需要调整第三步的子查询逻辑,增加校验排除已经存在对应活跃新版本的场景即可:
-- insert into newly updated records INSERT `processed.dim_material_sales` ( <Source table columns> source_type, material_sales_fingerprint, type2_checksum, start_datetime, end_datetime, current_flg, created_by, created_datetime, modified_by, modified_datetime ) SELECT <Source table columns> ST.source_type, ST.material_sales_fingerprint, ST.type2_checksum, current_datetime(), parse_date('%Y%m%d', '99991231'), 'Y', CAST(run_id AS STRING), current_datetime(), CAST(run_id AS STRING), current_datetime() FROM ( SELECT x.* FROM `transient.<Source table>` x -- 新增校验:仅当目标表没有对应指纹、相同校验和的活跃记录时才匹配 WHERE NOT EXISTS ( SELECT 1 FROM `processed.<Destination table>` t WHERE t.<Source table>_fingerprint = x.<Source table>_fingerprint AND t.type2_checksum = x.type2_checksum AND t.current_flg = 'Y' ) ) ST
方案2:优化为单MERGE实现(更简洁稳定)
可以将三步逻辑合并为单条MERGE语句,减少拆分带来的逻辑漏洞:
CREATE OR REPLACE PROCEDURE `processed.sp_merge_example` (job_run_id INT64) OPTIONS (strict_mode = false) BEGIN DECLARE run_id INT64 DEFAULT job_run_id; MERGE INTO `processed.<Destination table>` AS T USING `transient.<Source table>` AS S ON T.<Destination table>_fingerprint = S.<Source table>_fingerprint -- 匹配到活跃旧版本,标记为非活跃 WHEN MATCHED AND S.type2_checksum != T.type2_checksum AND T.current_flg = 'Y' THEN UPDATE SET T.modified_datetime = current_datetime(), T.modified_by = CAST(run_id AS STRING), T.end_datetime = current_datetime(), T.current_flg = 'N' -- 没有对应活跃新版本时插入新记录/更新后的记录 WHEN NOT EXISTS ( SELECT 1 FROM `processed.<Destination table>` t2 WHERE t2.<Destination table>_fingerprint = S.<Source table>_fingerprint AND t2.type2_checksum = S.type2_checksum AND t2.current_flg = 'Y' ) THEN INSERT ( <Source table columns> source_type, material_sales_fingerprint, type2_checksum, start_datetime, end_datetime, current_flg, created_by, created_datetime, modified_by, modified_datetime ) VALUES ( <Source table values> S.source_type, S.material_sales_fingerprint, S.type2_checksum, current_datetime(), parse_date('%Y%m%d', '99991231'), 'Y', CAST(run_id AS STRING), current_datetime(), CAST(run_id AS STRING), current_datetime() ); END;
内容的提问来源于stack exchange,提问作者prasanna kumar
相关产品推荐
相关产品推荐

