PostgreSQL下dbt测试双查询结果一致及非空校验问题排查

问题根因 #

你原有SQL的问题核心在于：当query1和query2同时为空时，FULL JOIN的结果本身就是0行，后续SELECT语句里的CASE判断根本没有执行机会，自然不会返回预期的1。只有当其中一个查询有数据、另一个为空时，FULL JOIN才会生成带NULL的行，触发你写的空判断逻辑。

修复后的SQL #

可以把空校验逻辑和差异行校验逻辑拆成独立的部分，用UNION ALL合并结果，完全规避JOIN逻辑的影响：

with query1 as (
    select id from table1
),
query2 as (
    select id from table2
),
-- 单独处理空表校验逻辑，只要任意查询为空就返回1
empty_check as (
    select 1 as id
    where not exists(select * from query1) OR not exists(select * from query2)
),
-- 处理两个查询都非空时的差异行校验
diff_check as (
    select COALESCE(query1.id, query2.id) as id
    from query1
    full join query2 on query1.id = query2.id
    where query1.id is null OR query2.id is null
)
-- 合并两个校验结果，只要满足任意失败条件就会返回行
select id from empty_check
union all
select id from diff_check

另外提示下，原有语句里的WHERE query1 IS NULL最好改成query1.id IS NULL，部分数据库不支持直接判断整行是否为NULL，容易出现兼容性问题。

场景验证 #

上述SQL完全符合你要求的所有场景：

• 两个查询都非空且结果完全一致：两个校验子查询都无返回，最终结果为空，dbt测试通过
• 两个查询都非空但结果有差异：diff_check返回差异ID，测试失败
• 任意一个查询为空或两个都为空：empty_check返回1，测试失败

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