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

Starting from dbt version 1.2 and onwards, users have an easier alternative to creating an entirely new materialization. They define and use their own "custom" incremental strategies by:

1. Defining a macro named get_incremental_STRATEGY_sql. Note that STRATEGY is a placeholder and you should replace it with the name of your custom incremental strategy.
2. Configuring incremental_strategy: STRATEGY within an incremental model.

dbt won't validate user-defined strategies, it will just look for the macro by that name, and raise an error if it can't find one.

For example, a user-defined strategy named insert_only can be defined and used with the following files:

macros/my_custom_strategies.sql

{% macro get_incremental_insert_only_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 %}

models/my_model.sql

{{ config(
    materialized="incremental",
    incremental_strategy="insert_only",
    ...
) }}

...

Custom strategies from a package

To use the merge_null_safe custom incremental strategy from the example package:

• Install the package
• Add the following macro to your project:

macros/my_custom_strategies.sql

{% macro get_incremental_merge_null_safe_sql(arg_dict) %}
    {% do return(example.get_incremental_merge_null_safe_sql(arg_dict)) %}
{% endmacro %}

Questions from the Community

Ask the Community