Snowflake中SCD Type2实现时where子句时间戳比较不生效问题

• 时间类型隐式转换不匹配：你直接给变量赋值字符串时，Snowflake会根据会话参数隐式转换为默认时间类型，如果CUSTOMER_TARGET表中TO_TIMESTAMP字段的类型（TIMESTAMP_LTZ/TIMESTAMP_TZ）和变量实际的TIMESTAMP_NTZ类型不一致，对比时会自动做时区转换，导致相等判断逻辑失效。
• 变量没有显式指定类型：SET EXPIRY_DATE_NTZ = '9999-08-17 07:31:29.901000000'的写法没有显式声明类型，变量实际类型可能和预期不符，进一步加剧隐式转换的误差。
• 字段名和内置函数重名：你用了Snowflake内置函数名TO_TIMESTAMP作为字段名，SQL解析时可能出现歧义，也会导致条件判断异常。

1. 先确认表字段的时间类型，执行DESC TABLE CUSTOMER_TARGET查看TO_TIMESTAMP字段的具体类型，保证变量类型和字段类型完全一致，修改变量赋值逻辑：

-- 如果字段是TIMESTAMP_NTZ类型
SET EXPIRY_DATE_NTZ = TO_TIMESTAMP_NTZ('9999-08-17 07:31:29.901000000');
SET CURRENT_DATE_NTZ = TO_TIMESTAMP_NTZ(CURRENT_TIMESTAMP());

-- 如果是TIMESTAMP_LTZ/TIMESTAMP_TZ，对应改成TO_TIMESTAMP_LTZ()/TO_TIMESTAMP_TZ()即可

2. 时间比较时显式转换两边类型，避免隐式转换干扰，修改WHERE子句的时间条件：

-- 两边都显式转换为和字段一致的类型，这里以NTZ为例
AND TO_TIMESTAMP_NTZ(CT.TO_TIMESTAMP) = TO_TIMESTAMP_NTZ($EXPIRY_DATE_NTZ)

3. 建议修改和内置函数重名的字段，将TO_TIMESTAMP改为EXPIRY_TS等自定义名称，彻底避免解析歧义。
4. 调试时可以先将UPDATE语句改为SELECT查询，验证匹配逻辑是否符合预期：

SELECT 
    CT.C_CUSTOMER_ID, 
    CT.TO_TIMESTAMP, 
    $EXPIRY_DATE_NTZ, 
    CT.TO_TIMESTAMP = $EXPIRY_DATE_NTZ AS MATCH_FLAG
FROM CUSTOMER_TARGET CT
JOIN POC.SNOWFLAKE_POC.CUSTOMER_STAGE CS
ON CT.C_CUSTOMER_ID = CS.C_CUSTOMER_ID
WHERE (CT.C_FIRST_NAME <> CS.C_FIRST_NAME OR CT.C_LAST_NAME <> CS.C_LAST_NAME OR CT.C_BIRTH_YEAR 
<> CS.C_BIRTH_YEAR OR CT.C_BIRTH_COUNTRY <> CS.C_BIRTH_COUNTRY OR CT.C_LAST_REVIEW_DATE<>CS.C_LAST_REVIEW_DATE)

查看MATCH_FLAG列的返回值即可快速定位判断逻辑失效的具体原因。

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