Google Cloud BigQuery实现SCD2报错：未识别名称及字段赋值问题

一、Unrecognized name错误的原因及解决 #

你遇到的Unrecognized name错误，本质是BigQuery MERGE语法的限制：在UPDATE分支里，不能直接引用目标表（T）的字段来和源表（S）字段组合赋值——因为UPDATE分支的作用是修改目标表的现有记录，而SCD2逻辑里，我们只需要把旧记录标记为非活跃即可，不需要修改其他字段。

错误写法示例（会触发字段未识别报错）：

MERGE INTO dim_address T
USING staging_address S
ON T.address_ref = S.address_ref
WHEN MATCHED AND (T.street != S.street OR T.city != S.city) THEN
  UPDATE SET 
    is_active = FALSE,
    block = T.block -- 此处引用T.block会触发Unrecognized name错误

正确的UPDATE分支只做失效标记：

WHEN MATCHED AND (T.street != S.street OR T.city != S.city OR T.zip != S.zip) THEN
  UPDATE SET 
    is_active = FALSE,
    end_date = CURRENT_TIMESTAMP() -- 若有end_date字段则更新

二、实现部分字段取源表新值、其余保留目标表原值 #

SCD2的核心是标记旧记录失效，插入新记录。要实现部分字段保留目标表原值，需在INSERT分支区分两种场景：

• 场景1：源表记录匹配到目标表的活跃旧记录，新记录部分字段取源表新值，其余沿用目标表旧记录的值；
• 场景2：源表记录未匹配到目标表任何记录，新记录全取源表的值。

具体SQL实现 #

假设dim_address字段为address_ref, street, city, zip, block, is_active, start_date, end_date, created_at，其中block, created_at需保留目标表原值，street, city, zip取源表新值：

WITH old_active_records AS (
  SELECT 
    address_ref,
    block,
    created_at,
    street,
    city,
    zip
  FROM dim_address
  WHERE is_active = TRUE
)
MERGE INTO dim_address T
USING (
  SELECT 
    S.*,
    O.block AS old_block,
    O.created_at AS old_created_at,
    -- 判断是否有匹配的活跃旧记录，以及记录是否有变化
    O.address_ref IS NOT NULL AS has_old_record,
    (O.address_ref IS NOT NULL AND (O.street != S.street OR O.city != S.city OR O.zip != S.zip)) AS has_changed
  FROM staging_address S
  LEFT JOIN old_active_records O ON S.address_ref = O.address_ref
) S
ON T.address_ref = S.address_ref AND T.is_active = TRUE
WHEN MATCHED AND S.has_changed THEN
  -- 标记旧记录为非活跃
  UPDATE SET 
    is_active = FALSE,
    end_date = CURRENT_TIMESTAMP()
WHEN NOT MATCHED OR S.has_changed THEN
  -- 插入新记录：根据是否有旧记录选择字段取值
  INSERT (
    address_ref, street, city, zip, block, is_active, start_date, end_date, created_at
  )
  VALUES (
    S.address_ref,
    S.street,
    S.city,
    S.zip,
    IF(S.has_old_record, S.old_block, S.block),
    TRUE,
    CURRENT_TIMESTAMP(),
    '9999-12-31', -- 永久有效用该值标记
    IF(S.has_old_record, S.old_created_at, CURRENT_TIMESTAMP())
  );

关键说明 #

1. old_active_records提前查询目标表的活跃旧记录，避免重复子查询；
2. 子查询中通过has_old_record和has_changed标记插入逻辑；
3. INSERT分支用IF函数区分场景：有旧记录则保留目标表字段值，无旧记录则取源表值。

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