同SQL查询在Snowflake与Redshift返回结果差异的根因排查

我在Redshift中执行以下SQL仅返回18条记录，但在Snowflake中执行相同查询却返回27000条记录。已确认两个数据库中team_incite.gmi_grace_prima_temp和team_incite.gmi_grace_translated两张表的总记录数一致，请求排查差异原因。

执行的查询语句 #

SELECT CUR.datasource
       , CUR.reqid
       , CUR.respid
       , CUR.caseno
       , CUR.category
       , CUR.topic
       , CUR.contact_type
       , CUR.division
       , CUR.gender
       , CUR.city
       , CUR.region
       , CUR.country
       , CUR.entrydate_loc
       , CUR.entrydate_loc_month
       , CUR.product_name
       , CUR.product_description
       , CUR.req_mode
       , CUR.languages
       , CUR.CODE
       , CUR.CODE_ORIG
       , CUR.dept
       , CUR.inquiry_text
       , CUR.subregion
       , CUR.subregion_amer_la
       , CUR.therapeutic_area
       , CUR.ae_flag
       , CUR.pc_flag
       , CUR.facilitator_flag
       , CUR.resolution
       , CUR.inbound_channel1
       , CUR.inbound_channel2
       , CUR.state_or_region
       , CUR.specialty
       , cur.load_date
 FROM team_incite.gmi_grace_prima_temp CUR
 full outer join team_incite.gmi_grace_translated PREV
  ON CUR.REQID=PREV.REQID AND CUR.RESPID=PREV.RESPID
 WHERE PREV.REQID IS NULL AND PREV.RESPID IS NULL   

计数验证语句 #

select count(*) from team_incite.gmi_grace_translated;
select count(*) from team_incite.gmi_grace_prima_temp;

可能的差异原因排查方向 #

• NULL值处理差异：Redshift和Snowflake对NULL=NULL的判断逻辑可能不同，部分场景下NULL无法匹配成功。可以分别统计两张表中REQID、RESPID为NULL的记录数，对比两个库的数据是否一致。
• 数据类型不匹配：检查REQID和RESPID在两个数据库中的数据类型是否完全一致。比如Redshift中是VARCHAR(50)、Snowflake中是STRING，或数值类型精度不同，可能导致隐式转换后匹配结果不同。
• 重复记录影响：虽然总记录数一致，但两张表中(REQID, RESPID)的唯一组合数可能不同。比如Redshift中PREV表该组合重复更多，导致更多CUR记录能匹配到PREV记录被过滤；Snowflake中则相反。可执行以下语句统计唯一组合数：

  -- 统计PREV表唯一组合数
  SELECT COUNT(DISTINCT CONCAT(REQID, '|', RESPID)) FROM team_incite.gmi_grace_translated;
  -- 统计CUR表唯一组合数
  SELECT COUNT(DISTINCT CONCAT(REQID, '|', RESPID)) FROM team_incite.gmi_grace_prima_temp;
  

• 大小写敏感性差异：若REQID/RESPID是字符串类型，Redshift和Snowflake的大小写敏感规则可能不同。比如Redshift默认不敏感，Snowflake默认敏感，导致'ABC'和'abc'匹配结果不同。可尝试在JOIN条件中统一转换大小写验证：

  ON LOWER(CUR.REQID)=LOWER(PREV.REQID) AND LOWER(CUR.RESPID)=LOWER(PREV.RESPID)
  

• 执行计划差异：两个数据库的查询优化器生成的执行计划可能不同，比如Redshift提前过滤了部分数据，或FULL JOIN的实现逻辑有差异。可查看各自的执行计划对比细节。

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