BigQuery数据对比SQL在SQLFluff中报错的修复方法咨询

我有一段用于对比两张表数据的代码，在BigQuery中运行完全正常：

with source1 as (
select 
b.id, 
b.qty, 
a.price 
from <table> as a
,unnest <details> as b
 where b.status != 'canceled'
),

source2 as (
select id_, qty_, price_  from <table2>
where city != 'delhi'
) 

select *
from source1 s1
full outer join source2 s2
on id = id_
where format('%t', s1) != format('%t', s2)

但这段代码在SQLFluff（一款无法跳过或关闭的SQL格式规则检查工具）中触发错误，错误信息如下：
SQLFluff错误信息：

• 's1' found in select with more than one referenced table/view'
• 's2' found in select with more than one referenced table/view

请问如何修复该问题？

SQLFluff报错的核心原因是它不支持在多表关联的查询中，直接将整个表别名（如s1、s2）作为参数传入函数。以下两种方案可以解决这个问题：

方案1：逐个字段对比 #

把原语句中引用整个表别名的对比逻辑，替换为逐个字段的显式对比，覆盖所有需要校验的字段：

with source1 as (
select 
b.id, 
b.qty, 
a.price 
from <table> as a
,unnest <details> as b
 where b.status != 'canceled'
),

source2 as (
select id_, qty_, price_  from <table2>
where city != 'delhi'
) 

select *
from source1 s1
full outer join source2 s2
on s1.id = s2.id_
where 
    -- 捕获一侧存在数据、另一侧不存在的情况
    (s1.id is null or s2.id_ is null)
    -- 逐个对比字段值差异
    or s1.qty != s2.qty_
    or s1.price != s2.price_

方案2：用EXCEPT/UNION ALL实现差异对比 #

如果两张表的字段可以对齐，这种方式更简洁，还能标记差异来源：

with source1 as (
select 
b.id, 
b.qty, 
a.price 
from <table> as a
,unnest <details> as b
 where b.status != 'canceled'
),

source2 as (
-- 先把source2的字段名对齐到source1
select id_ as id, qty_ as qty, price_ as price  from <table2>
where city != 'delhi'
) 

-- 取出source1独有的数据
select 'source1_only' as diff_type, * from source1
except distinct
select 'source1_only' as diff_type, * from source2

union all

-- 取出source2独有的数据
select 'source2_only' as diff_type, * from source2
except distinct
select 'source2_only' as diff_type, * from source1

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