dbt incremental models not working

Help
,
1

The problem I’m having

I have this model in dbt core and I’m trying to run it using incremental mode but it’s not working and I followed the checklist
1- model exist in dbt core
2- the incremental config is applied
3- the table exists in the database

The context of why I’m trying to do this

I used this for debugging SELECT {{ 'true' if is_incremental() else 'false' }} AS is_incremental_flag, and it’s compiled to False

Environrment

OS:

  • linux
    Core:
  • installed: 1.8.4
    Plugins:
  • snowflake: 1.8.3

Full model code

{{ config(
    materialized='incremental',
    unique_key='USER_ADV_ID',
    on_schema_change='sync_all_columns',
    incremental_strategy='merge'
) }}


WITH source_archive AS (
    SELECT a.USER_ADV_ID, a.CAT_ID, a.USER_ID, a.DESCRIPTION, a.DEVICE_ID, a.DATE_PUBLISHED, a.date_modified, 
           a.REGION_ID, a.date_expired,
           CASE WHEN regexp_like(a.ad_asking_price, '[0-9]+') THEN a.ad_asking_price ELSE 0 END AS AD_ASKING_PRICE,
           a.SOURCE, a.SOURCE_ITEM_ID, a.IMAGES, a.CACHE, a.DELETE_REASON, a.PLAN_ID, a.STATUS, 
           a.REPOST_AUTO_RENEW, a.IS_DRAFT, a.VIDEO, a.TITLE, a.DATE_CREATED, a.PHONE, a.IS_PAID_LISTING,
           a.CONTACT_NO, a.DISTRICT_ID, 'archive' AS source_table, vertical_id
    FROM {{ ref('clas_user_adv_archive') }} a
    LEFT JOIN {{ ref('cat_dim') }} cat_dim ON a.cat_id = cat_dim.cat_id

    {% if is_incremental() %}
    WHERE a.date_modified > (SELECT MAX(date_modified) FROM {{ this }})
    {% endif %}
),

source_active AS (
    SELECT b.USER_ADV_ID, b.CAT_ID, b.USER_ID, b.DESCRIPTION, b.DEVICE_ID, b.DATE_PUBLISHED, b.date_modified, 
           b.REGION_ID, b.date_expired, b.AD_ASKING_PRICE,
           b.SOURCE, b.SOURCE_ITEM_ID, b.IMAGES, b.CACHE, b.DELETE_REASON, b.PLAN_ID, b.STATUS, 
           b.REPOST_AUTO_RENEW, b.IS_DRAFT, b.VIDEO, b.TITLE, b.DATE_CREATED, b.PHONE, b.IS_PAID_LISTING,
           b.CONTACT_NO, b.DISTRICT_ID, 'active' AS source_table, vertical_id
    FROM {{ ref('clas_user_adv') }} b
    LEFT JOIN {{ ref('cat_dim') }} cat_dim ON b.cat_id = cat_dim.cat_id

    {% if is_incremental() %}
    WHERE b.date_modified > (SELECT MAX(date_modified) FROM {{ this }})
    {% endif %}
)

SELECT   {{ 'true' if is_incremental() else 'false' }} AS is_incremental_flag,
,* FROM source_archive
UNION
SELECT   {{ 'true' if is_incremental() else 'false' }} AS is_incremental_flag,
,* FROM source_active