如何在DBT的Merge增量策略中指定自定义目标表名？

DBT默认的增量模型会将数据合并到当前模型对应的物理表中，若要合并到已存在的five9_calls_sat表（且该表已有对应模型，无法让当前模型重命名或映射到该表），可以通过自定义增量策略来实现，具体步骤如下：

修改模型配置与代码 #

将原模型的incremental_strategy改为custom，并通过merge_sql参数自定义Merge语句，指定目标表为five9_calls_sat：

{{
    config(
        materialized='incremental',
        unique_key=(['five9_calls_hk', 'effective_from']),
        incremental_strategy='custom',
        merge_sql="""
            merge into {{ ref('five9_calls_sat') }} as DBT_INTERNAL_DEST
            using {{ this }}__dbt_tmp as DBT_INTERNAL_SOURCE
            on (
                DBT_INTERNAL_SOURCE.five9_calls_hk = DBT_INTERNAL_DEST.five9_calls_hk
            ) and (
                DBT_INTERNAL_SOURCE.effective_from = DBT_INTERNAL_DEST.effective_from
            )

            when matched then update set
                "EFFECTIVE_TO" = DBT_INTERNAL_SOURCE."EFFECTIVE_TO"
            
            when not matched then insert
                ("FIVE9_CALLS_HK", "EFFECTIVE_FROM", "EFFECTIVE_TO")
            values
                (DBT_INTERNAL_SOURCE."FIVE9_CALLS_HK", DBT_INTERNAL_SOURCE."EFFECTIVE_FROM", DBT_INTERNAL_SOURCE."EFFECTIVE_TO")
        """
    )
}}

WITH

using_clause AS (

select 
    ns.five9_calls_hk,
    ns.effective_from,
    ns.next_effective_to::timestamp_tz(9) effective_to
from
(
select 
    five9_calls_hk,
    effective_from,
    effective_to,
    lead(effective_from) over (partition by five9_calls_hk order by load_datetime) lef,
    row_number() over (partition by five9_calls_hk order by load_datetime desc) rnum,
    ifnull(dateadd(millisecond, -1, lead(effective_from) over (partition by five9_calls_hk order by load_datetime)), cast('9999-12-31' as timestamp)) next_effective_to
    from {{ ref('five9_calls_sat') }}
order by five9_calls_hk, effective_from desc
) ns
join {{ ref('five9_calls_sat') }} s
on ns.five9_calls_hk = s.five9_calls_hk and ns.effective_from = s.effective_from
and ns.effective_to <> ns.next_effective_to
except
select five9_calls_hk, effective_from, effective_to from five9_calls_sat
),

updates AS (

    SELECT
        five9_calls_hk,
        effective_from,
        effective_to

    FROM using_clause

)

SELECT *

FROM updates

关键修改说明 #

1. 切换到自定义增量策略：将incremental_strategy设为custom，告诉DBT使用你提供的自定义Merge语句而非默认生成的语句。
2. 自定义Merge语句：
   • 目标表指定为{{ ref('five9_calls_sat') }}，直接引用已有模型对应的物理表。
   • 源表使用DBT生成的临时表{{ this }}__dbt_tmp（{{ this }}指代当前模型five9_calls_mrg）。
   • 匹配条件保持原有的unique_key逻辑。
   • 更新逻辑仅修改EFFECTIVE_TO字段（因为FIVE9_CALLS_HK和EFFECTIVE_FROM是唯一键，无需重复更新）。
   • 插入逻辑保留原有字段映射，确保临时表数据正确插入目标表。

注意事项 #

• 确保当前模型的运行账号对five9_calls_sat表有更新和插入权限。
• 避免原five9_calls_sat模型与当前模型的运行时间冲突，防止并发操作导致的数据异常。
• 运行前建议先通过dbt run --models five9_calls_mrg --full-refresh测试全量运行逻辑，再执行增量更新。

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