[Feature] Incremental Strategy - Edge Case Safeguarding on Merge Incremental Model

[zvijayakumar]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

Edge Case Safeguarding in dbt Core Incremental Merge Stragerty

Refer this Documentation

If the event_time column is NULL or the table is truncated, the following situation occurs:

• When event_time is NULL, the query select * from stg_source_table where event_time > NULL returns no results because the condition evaluates to NULL.
• This results in no data being loaded, and dbt considers it an incremental load, which can lead to model failure.

Currently, dbt does not handle this edge case, which can cause issues during incremental loads.

Thanks, @mirnawong1 for the advice to open an issue in dbt core as suggested in PR

Expected Behavior

dbt should handle this edge case by ensuring that if event_time is NULL or the table is truncated, the incremental load does not fail. Suggested improvements include adding logic to handle NULL values in the event_time column. As suggested by @mirnawong1 in the PR

Steps To Reproduce

{{
    config(
        materialized='incremental',
        unique_key='date_day'
    )
}}

select
    date_trunc('day', event_at) as date_day,
    count(distinct user_id) as daily_active_users

from {{ ref('app_data_events') }}


{% if is_incremental() %}

  -- this filter will only be applied on an incremental run
  -- (uses >= to include records arriving later on the same day as the last run of this model)
  where date_day >= (select event_time from {{ this }})

{% endif %}

group by 1

1. Truncate the destination table.
2. As the table already exists in the database schema. dbt considers it as the incremental load.
3. Where condition date_day >= NULL it is always True and returns No result. It will leads to No data load
4. It's edge case that has to handled in the dbt.
5. To handle this case I have added the sample code in the dbt documentation Doc Link

Relevant log output

No response

Environment

- OS:
- Python:
- dbt:

Which database adapter are you using with dbt?

snowflake

Additional Context

No response

[dbeatty10]

Thanks for reaching out @zvijayakumar !

This looks similar to #9997 which we closed in favor of updating the documentation (dbt-labs/docs.getdbt.com#5321):

• Handle case when an incremental table is empty docs.getdbt.com#5326

Anything related to this particular edge case needs to be handled in dbt product documentation rather than in dbt-core.

There's two key reasons why:

1. Changing when is_incremental() is True would be a breaking change, and we don't plan on doing it
2. We also don't plan on modifying any Jinja logic that is provided by the user within is_incremental() conditional logic