DBT转换后表与原查询结果不一致，疑与窗口函数相关求助

我使用以下DBT查询转换数据集，生成silver_customer_address表：

with source as (
    select * from {{source("datalake", "customer_address")}}
),

step_0 as (
    select        
        cast("id"                      as int)          as  id,                                    
        cast("customer_id"             as int)          as  customer_id,                                   
        cast("zipcode"                 as varchar)      as  zipcode,                                    
        cast("neighborhood"            as varchar)      as  neighborhood,                                  
        cast("number"                  as varchar)      as  address_number,                                    
        cast("additional_address"      as varchar)      as  additional_address,                                   
        cast("street"                  as varchar)      as  street,                                   
        cast("city"                    as varchar)      as  city,                                   
        cast("state"                   as varchar)      as  state,                                   
        TRY_CAST(created_at            AS timestamp)    as  customer_address_created

    from source 
    
),

final as(

    SELECT
        id,                                    
        customer_id,                                   
        zipcode,                                    
        neighborhood,                                  
        address_number,                                    
        additional_address,                                   
        street,                                   
        city,                                   
        state,                                   
        customer_address_created
    FROM (
        SELECT 
            s0.*,
            ROW_NUMBER() OVER 
                (PARTITION BY
                    customer_id, 
                    zipcode, 
                    neighborhood, 
                    address_number, 
                    additional_address, 
                    street,
                    state
                 ORDER BY 
                    customer_id,
                    customer_address_created desc
                ) as row_num
        FROM step_0 s0
    )
    WHERE row_num = 1
    )

select * from final

转换过程无异常，但对比原查询与silver_customer_address表的结果时发现不一致，例如id列的求和结果分别为：

• 原查询结果：1734316709196
• silver_customer_address表结果：1734335121317

我预期转换后的表应与原查询结果完全一致，推测差异可能和窗口函数有关，但不清楚具体原因，希望得到解答。

可能的原因及验证方法 #

1. 窗口函数排序的歧义性 #

你的ROW_NUMBER()窗口函数中，ORDER BY包含customer_id（但它是分区键，同一分区内值相同），实际排序逻辑仅由customer_address_created desc决定。如果同一分区内存在多条customer_address_created完全相同的记录，ROW_NUMBER()会随机分配行号（不同执行场景可能选中不同的行），导致最终返回的id不同，求和结果自然出现差异。

验证SQL：

SELECT 
    customer_id, zipcode, neighborhood, address_number, additional_address, street, state,
    customer_address_created,
    COUNT(*) as record_count
FROM step_0
GROUP BY 1,2,3,4,5,6,7,8
HAVING COUNT(*) > 1;

2. 源数据的时序差异 #

如果原查询是手动执行的，而DBT任务执行时，源表datalake.customer_address已经有新数据写入，两次查询的源数据本身就不一致，结果必然不同。可以对比两次查询的源数据行数、created_at的最大/最小值，确认数据是否有变化。

3. 数据类型转换的隐性问题 #

TRY_CAST(created_at AS timestamp)遇到无效格式的created_at值会返回NULL。不同执行时机下，源数据中若存在不同的无效created_at记录，会导致customer_address_created为NULL的记录排序位置变化（不同SQL引擎对NULL的排序规则可能有差异，比如有的将NULL放在最前，有的放在最后），进而影响ROW_NUMBER()的结果。

验证SQL：

SELECT * FROM source WHERE TRY_CAST(created_at AS timestamp) IS NULL;

4. DBT增量更新策略的影响 #

如果silver_customer_address模型配置了增量更新（materialized: incremental），而非全量覆盖（materialized: table），表中会保留之前批次的数据，与全量执行原查询的结果不一致。可以查看模型的dbt_project.yml或模型文件头部的配置，确认物化策略。

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