Get Snowflake Projects Live Faster. Reliably.

Register for webinar
Back to Product Updates

Incremental predicates support for incremental models

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 ...

Learn more about incremental models →