Incremental_strategy = 'merge'

Help
, ,
1

I understand that, when i’m using incremental_strategy = ‘merge’ and specifying unique_key then by default, dbt will entirely overwrite the old record with a new record if a unique key already exists in the target table. But, based on the code below, that is not the case, because dbt still appends the new record to the target table, while maintaining the old record.
I’m using dbt-core 1.7.1 and postgres adapter 1.7.1

My code

{{
    config(
        materialized = 'incremental'
        , unique_id = ['listing_id', 'reviewer_name', 'review_date']
        , incremental_strategy = 'merge'
    )
}}
with src_reviews as(
    select * from {{ref('src_reviews')}}
)
select
{{dbt_utils.generate_surrogate_key(['listing_id', 'review_date', 'reviewer_name', 'review_text'])}} review_id
, *
from src_reviews
where review_text is not null
{%if is_incremental()%}
    and review_date > (select max(review_date) from {{this}})
{%endif%}
2

What version of Postgres are you using? The merge incremental strategy is only supported on Postgres >= 15

Note: @Owen originally posted this reply in Slack. It might not have transferred perfectly.

1 Like
4

Thank, I’m currently using version 10.23

5

Yeah that’d be why. Postgres itself doesn’t support the SQL merge operation until version 15: https://www.postgresql.org/docs/release/15.0/

Note: @Owen originally posted this reply in Slack. It might not have transferred perfectly.

1 Like