How can I specify a table name in the DBT merge process?

Question

I'm trying to create a merge statement using dbt. Here is my code:

{{
    config(
        materialized='incremental',
        unique_key=(['five9_calls_hk', 'effective_from']),
        incremental_strategy='merge'
    )
}}

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
--select * 
    from {{ ref('five9_calls_sat') }}
--    where call_id = '8344511'
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

Running this, under the name "five9_calls_mrg" ultimately creates this statement:

merge into DEV_DW_DB.dbt_loswood.five9_calls_mrg as DBT_INTERNAL_DEST
        using DEV_DW_DB.dbt_loswood.five9_calls_mrg__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
        "FIVE9_CALLS_HK" = DBT_INTERNAL_SOURCE."FIVE9_CALLS_HK","EFFECTIVE_FROM" = DBT_INTERNAL_SOURCE."EFFECTIVE_FROM","EFFECTIVE_TO" = DBT_INTERNAL_SOURCE."EFFECTIVE_TO"
    

    when not matched then insert
        ("FIVE9_CALLS_HK", "EFFECTIVE_FROM", "EFFECTIVE_TO")
    values
        ("FIVE9_CALLS_HK", "EFFECTIVE_FROM", "EFFECTIVE_TO")

My problem is that I don't want to merge it into five9_calls_mrg, I want to merge it into five9_calls_sat. But I already have a different model called five9_calls_sat (the model that builds and loads that table). So I can't name this five9_calls_sat. How do I deal with that? Is there an element in the config that I can use?

Answer 1

Incremental models in DBT handle both the initial creation of the table and the updates over time. You want to move your five9_calls_mrg logic into five_calls_sat. You should be able to significantly simplify your combined model than what I have below, but here is a very ugly version:

{{
    config(
        materialized='incremental',
        unique_key=(['five9_calls_hk', 'effective_from']),
        incremental_strategy='merge'
    )
}}

{% if is_incremental() %}
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
--select * 
    from {{ this }}
--    where call_id = '8344511'
order by five9_calls_hk, effective_from desc
) ns
join {{ this }} 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 {{ this }}
),

updates AS (

    SELECT
        five9_calls_hk,
        effective_from,
        effective_to

    FROM using_clause

)

SELECT *

FROM updates
{% else %}
// logic for five9_calls_sat here
{% endif %}