incremental_predicates offer an advanced approach for managing large-volume data in incremental models, justifying further performance optimization efforts. This configuration accepts a list of valid SQL expressions. Note that Y42 does not verify the syntax of these SQL statements.
{{
config(
materialized = 'incremental',
unique_key = 'id',
cluster_by = ['session_start'],
incremental_strategy = 'merge',
incremental_predicates = [
"target_alias.session_start > dateadd(day, -7, current_date)"
]
)
}}
..
The above configuration will generate the following MERGE command:
merge into <existing_table> target_alias
from <temp_table_with_new_records> source_alias
on
-- unique key
target_alias.id = source_alias.id
and
-- custom predicate: limits data scan in the "old" data / existing table
target_alias.session_start > dateadd(day, -7, current_date)
when matched then update ...
when not matched then insert ...