[SOLVED] Incremental load logic failed in DBT for Initial load

Incremental load logic failed in DBT for Initial load

I have model dest_pos.sql where dest_pos snowflake table is empty for the first initial load and i am trying to insert into dest_pos snowflake table from stg_kopr table. When i build the model in dbt it runs successfully but it does not insert into dest_pos snowflake table.

It looks like the where condition is not getting satisfied and i need to use for example OR condition in where clause to handle empty dest_pos table for the first time but dont know how to do it.

where load_timestamp > (select max(load_timestamp) from {{ this }})

Below is the model in dbt:

{{
    config(
        materialized='incremental',
        unique_key= 'resd',
        incremental_strategy='merge',
        merge_update_columns = ['resd']
    )
}}

with stg_kopr as (

    select * from {{ ref('stg_kopr') }}
)

select resd,
        pord,
        edt
       
from stg_kopr

{% if is_incremental() %}

where load_timestamp > (select max(load_timestamp) from {{ this }})

{% endif %}

Solution

If it is possible for the initial load of model dest_pos to be empty, and the issue is that {{ this }} will be empty as as a result, you can always set a default value for max(load_timestamp) if it can return null.

where load_timestamp > (select nvl(max(load_timestamp),'1900-01-01') from {{ this }})