Incremental model depends on union all view

Help
1

The problem I’m having

I am trying to run an incremental model which refers to non-incremental views upstream and it seems to be always running a full refresh - hence taking a long time to run.

The context of why I’m trying to do this

I am using dbt to build a metric store, ie. we have a data warehouse setup and want to store pre-computed KPIs in a separate KPI model. The KPIs I am interested about are mainly immutable so I would very rarely need a full refresh. Instead I only want to compute them on new rows, I chose an incremental approach as the computation is quite expensive. However, since I am referring other non-incremental models upstream I seem to be getting no benefit of using an incremental model, the is_incremental() WHERE statement does not seem to work correctly as I am using a union all upstream (at least that is my guess).

What I’ve already tried

I already tried to include is_incremental() WHERE upstream and changed the upstream models from views to ephemeral, but I see in the compiled SQL that the WHERE statements do not populate in the compiled code.

Some example code or error messages

stg_sys1_orders.sql

SELECT customer_id, order_id, order_date, item_id, item_value FROM {{source("sys1", "orders")}}

stg_sys2_orders.sql

SELECT customer_id, order_id, order_date, item_id, item_value FROM {{source("sys2", "orders")}}```

fct_orders.sql

SELECT * FROM {{ ref('stg_sys1_orders') }}
UNION ALL
SELECT * FROM {{ ref('stg_sys2_orders') }}

kpi_customer_order_value.sql

{{ config(
    materialized="incremental",
)}}
SELECT customer_id, order_id, sum(item_value) FROM {{ ref('fct_orders') }}
{% if is_incremental() %}
WHERE order_date >= SELECT(COALESCE(max(order_date),'1900-01-01') FROM {{ this }} )
{% endif %}
GROUP BY customer_id, order_id
2

A late reply here. I think the issue is the WHERE clause in the incremental macro. dbt has no way to know the result of the COALESCE function, so it must evaluate all records int fct_orders table to get the result of the function, and only then can it compare it to order date.