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:
models:
+incremental_strategy: "insert_overwrite"
or:
{{
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:
{% 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:
- Defining a macro named
get_incremental_STRATEGY_sql
. Note thatSTRATEGY
is a placeholder and you should replace it with the name of your custom incremental strategy. - 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:
{% 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 %}
{{ 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:
{% macro get_incremental_merge_null_safe_sql(arg_dict) %}
{% do return(example.get_incremental_merge_null_safe_sql(arg_dict)) %}
{% endmacro %}