Requesting a check of my understanding of is_incremental logic over SCDs

Help
, ,
1

My work has a legacy Teradata data warehouse that we’re looking to implement dbt core on so we can build models over our existing tables with a better framework than our old one. Our bronze layer is basically a range of type 2 SCDs that are like snapshots with each containing a record start date, record end date, and record deleted flag, and I plan to use this as the foundation of any new models.

I’m currently getting my head around incremental models and am getting an odd result when using the “default” date check within the is_incremental block.

{% if is_incremental() %}
  and start_date > (select max(start_date) from {{ this }})
{% endif %}

What I’ve worked out is that the date check is clearly looking for the max start date for the whole target table, not the max for the given unique id (e.g. an account id), so records where the most recent is older aren’t being assessed and that doesn’t seem right for an SCD.

I’ve noodled about and got it working the way I expected with the below statement; however, I’ve not seen anything similar in searches so I wonder if I’ve got something philosophically wrong. Could anyone please advise?

{{
    config(
        materialized='incremental',
        incremental_strategy='merge',
        unique_key=['account_id', 'record_start_date'],
    )
}}

select src.* from {{ ref('stg_accounts') }} as src
{% if is_incremental() %}
    inner join {{ this }} as tgt on
        src.account_id = tgt.account_id
        and src.record_start_date >= tgt.record_start_date
        and tgt.record_end_date is null
{% endif %}