About incremental strategy

There are various strategies to implement the concept of incremental materializations. The value of each strategy depends on:

• The volume of data.
• The reliability of your unique_key.
• The support of certain features in your data platform.

An optional incremental_strategy config is provided in some adapters that controls the code that dbt uses to build incremental models.

Supported incremental strategies by adapter

Click the name of the adapter in the below table for more information about supported incremental strategies.

The merge strategy is available in dbt-postgres and dbt-redshift beginning in dbt v1.6.

Configuring incremental strategy

The incremental_strategy config can either be defined in specific models or for all models in your dbt_project.yml file:

dbt_project.yml

models:
  +incremental_strategy: "insert_overwrite"

or:

models/my_model.sql

{{
  config(
    materialized='incremental',
    unique_key='date_day',
    incremental_strategy='delete+insert',
    ...
  )
}}

select ...

Built-in strategies

Before diving into custom strategies, it's important to understand the built-in incremental strategies in dbt and their corresponding macros:

incremental_strategy	Corresponding macro
append	get_incremental_append_sql
delete+insert	get_incremental_delete_insert_sql
merge	get_incremental_merge_sql
insert_overwrite	get_incremental_insert_overwrite_sql

For example, a built-in strategy for the append can be defined and used with the following files:

macros/append.sql

{% macro get_incremental_append_sql(arg_dict) %}

  {% do return(some_custom_macro_with_sql(arg_dict["target_relation"], arg_dict["temp_relation"], arg_dict["unique_key"], arg_dict["dest_columns"], arg_dict["incremental_predicates"])) %}

{% endmacro %}


{% macro some_custom_macro_with_sql(target_relation, temp_relation, unique_key, dest_columns, incremental_predicates) %}

    {%- set dest_cols_csv = get_quoted_csv(dest_columns | map(attribute="name")) -%}

    insert into {{ target_relation }} ({{ dest_cols_csv }})
    (
        select {{ dest_cols_csv }}
        from {{ temp_relation }}
    )

{% endmacro %}

Define a model models/my_model.sql:

{{ config(
    materialized="incremental",
    incremental_strategy="append",
) }}

select * from {{ ref("some_model") }}

Custom strategies

Questions from the Community

Ask the Community