Snowflake中关联同步stg_tbl的finance_data至target_tbl失败求助

核心问题 #

你编写的左关联查询仅用于生成临时查询结果，并未将stg_tbl的finance_data值写入target_tbl的新字段。当前target_tbl中finance_data字段显示的0，是Snowflake在添加FLOAT类型字段且未指定默认值时，自动填充的默认值。

解决步骤 #

1. 确认字段默认值 #

先验证target_tbl的finance_data字段默认值是否为0：

SELECT COLUMN_NAME, DATA_TYPE, DEFAULT_VALUE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'TARGET_TBL' AND COLUMN_NAME = 'FINANCE_DATA';

2. 执行同步更新操作 #

使用Snowflake的MERGE语句（推荐，适配全量/增量同步场景）将stg_tbl的finance_data同步到target_tbl：

MERGE INTO target_tbl a
USING stg_tbl b
ON a.person_id = b.person_id AND a.start_dt = b.start_dt
WHEN MATCHED THEN
  UPDATE SET a.finance_data = b.finance_data;

也可使用UPDATE结合子查询实现：

UPDATE target_tbl a
SET finance_data = (
  SELECT b.finance_data 
  FROM stg_tbl b 
  WHERE a.person_id = b.person_id AND a.start_dt = b.start_dt
)
WHERE EXISTS (
  SELECT 1 
  FROM stg_tbl b 
  WHERE a.person_id = b.person_id AND a.start_dt = b.start_dt
);

3. 验证同步结果 #

查询target_tbl确认数据已更新：

SELECT person_id, start_dt, finance_data 
FROM target_tbl 
WHERE finance_data IS NOT NULL;

额外排查点 #

若更新后仍显示0，检查以下内容：

• 源数据是否本身为0：确认stg_tbl中关联行的finance_data实际值：

  SELECT person_id, start_dt, finance_data 
  FROM stg_tbl 
  WHERE finance_data <> 0;
  

• 关联键是否匹配：检查person_id或start_dt是否存在类型/格式不一致（如字符串vs数字、日期格式不统一）导致关联失败：

  -- 查找target_tbl中无法匹配stg_tbl的行
  SELECT a.person_id, a.start_dt
  FROM target_tbl a
  LEFT JOIN stg_tbl b
  ON a.person_id = b.person_id AND a.start_dt = b.start_dt
  WHERE b.person_id IS NULL;
  

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