在BigQuery中基于历史表创建带生效起止日期的表（含dbt需求）

针对你的仅追加式源表，构建带生效时间维度的目标表，有两种高效实现方式：

方法一：使用dbt Snapshot（官方推荐） #

dbt的Snapshot功能专为缓慢变化维度（SCD2）设计，无需手动编写复杂窗口函数，通过配置即可生成目标结构。

步骤1：创建Snapshot文件 #

在dbt项目的snapshots目录下新建supplier_scd2.sql：

{% snapshot supplier_scd2 %}

{{
    config(
      target_schema='your_target_schema', -- 替换为你的目标schema
      strategy='check',
      unique_key='Supplier_ID',
      check_cols=['Supplier_Name', 'Supplier_Contact'], -- 监控变化的字段
    )
}}

select 
    Supplier_ID,
    Supplier_Name,
    Supplier_Contact,
    Last_Modified as dbt_valid_from -- 用源表修改时间作为生效起始时间
from {{ source('your_source_schema', 'source_supplier_table') }} -- 替换为你的源表引用

{% endsnapshot %}

步骤2：查询Snapshot生成目标格式 #

dbt会自动为Snapshot表生成dbt_valid_to字段（最新记录值为null），只需简单转换即可得到你需要的结构：

select
    Supplier_ID,
    Supplier_Name,
    Supplier_Contact,
    dbt_valid_from as Effective_From,
    coalesce(dbt_valid_to, '9999-12-21 00:00:00') as Effective_To,
    case when dbt_valid_to is null then 'Y' else 'N' end as is_active
from {{ ref('supplier_scd2') }}

方法二：自定义增量模型（手动实现SCD2） #

如果更倾向于自定义逻辑，可编写一个增量模型，用窗口函数计算每条记录的失效时间：

{{ config(materialized='incremental') }}

with source_data as (
    select
        Supplier_ID,
        Supplier_Name,
        Supplier_Contact,
        Last_Modified,
        row_number() over (partition by Supplier_ID order by Last_Modified desc) as rn
    from {{ source('your_source_schema', 'source_supplier_table') }}
    {% if is_incremental() %}
        -- 增量更新：仅处理比目标表最新记录晚的数据
        where Last_Modified > (select max(Effective_From) from {{ this }})
    {% endif %}
),

record_ranges as (
    select
        Supplier_ID,
        Supplier_Name,
        Supplier_Contact,
        Last_Modified as Effective_From,
        -- 获取同一供应商下一条记录的修改时间，作为当前记录的失效时间（减1秒避免时间重叠）
        lead(Last_Modified) over (partition by Supplier_ID order by Last_Modified) as next_modified,
        rn
    from source_data
)

select
    Supplier_ID,
    Supplier_Name,
    Supplier_Contact,
    Effective_From,
    coalesce(date_add(next_modified, interval -1 second), '9999-12-21 00:00:00') as Effective_To,
    case when rn = 1 then 'Y' else 'N' end as is_active
from record_ranges

关键注意事项 #

• 确保Last_Modified是精确到秒的时间戳，避免同一供应商在同一时间出现重复记录
• 增量模型首次运行会全量生成数据，后续仅处理新增记录，性能更优
• 若源表存在同一Supplier_ID、同一Last_Modified的重复数据，可将row_number()替换为rank()，并添加额外去重逻辑

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