I haven’t found much information about how people deal with incremental models in a query with many subqueries. Example:
{{ config( materialized='incremental') }}
with a as (
select * from table_a
{% if is_incremental() %}
where a_insert_date>= (select max(a_insert_date) from {{ this }} )
{% endif %}
),
b as (select * from table_b
{% if is_incremental() %}
where a_insert_date>= (select max(a_insert_date) from {{ this }} )
{% endif %}
)
select *
from a
left join b on key_a = key_b
I would like to use the unique_key=‘my_unique_key’ so if the key is updated the new data is replaced.
But because now I have multiple subqueries, there are multiple unique keys per query.
My approach would be to add another condition per subquery:
and my_unique_key_a NOT IN (SELECT my_unique_key_a FROM table_a )
But there have to be smarter ways. What is your approach ?
Thanks in advance!