[Core] More robust example for incremental runs

[dbeatty10]

Link to the page(s) on docs.getdbt.com requiring updates

https://docs.getdbt.com/docs/build/incremental-models

Tell us more about this update

The docs currently have the following example:

{% if is_incremental() %}
  where event_time >= (select max(event_time) from {{ this }})
{% endif %}

But this assumes that the the table is not empty.

Improved logic handles the case when the table is empty by using a default date in the distant past:

  where event_time >= (select coalesce(max(event_time), '1900-01-01') from {{ this }})

Reviewers/Stakeholders/SMEs

@b-per

Related GitHub issues

dbt-labs/dbt-core#9997 (comment)

Additional information

Tip

Instead of '1900-01-01', the ideal scenario would be if there were a cross-database way to specify the minimum date / timestamp_tz / timestamp_ntz / timestamp_ltz supported by the database engine. Then we'd just include that in our example instead.

[BenameurZyad]

We used something like this in our case :

where event_time >= ( select coalesce(max(event_time), '' ) from {{ this }} )

[dbeatty10]

@BenameurZyad coalesce(max(event_time), '' ) didn't work for me when I tried it out -- which database did you use? And are you certain it worked as intended when the incremental table is empty?

I've got a draft PR to update the docs here: #5326

[BenameurZyad]

Ouch. Sorry @dbeatty10 !
this is our actual implementation :
coalesce ( select (max(event_time) fom {{ this }} ), '' )

I changed it a bit to align with your logic above without trying, thinking it would be the same. Sorry for the confusion.
This one in this comment works for sure for us in Snowflake.

[dbeatty10]

That doesn't look like it would work if I copy-pasted it. i.e., fom vs. from

For sake of completeness and correctness, would you mind copy-pasting your exact logic that you think would work in dbt-snowflake?

[BenameurZyad]

here it is :

with raw as (
    select
        properties,                 -- this is a json (stored as VARIANT type)
        content,                    -- this is a json (stored as VARIANT type)
        __INGESTED_AT               -- this is a timestamp UTC (stored as TEXT)
    from {{source('********','********')}}

    {% 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)

    -- this should prevent a bug where the incremental model would not work if the target table is empty
    where __INGESTED_AT >= coalesce((select max(__INGESTED_AT) from {{ this }}), '')

    {% endif %}
),