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") }}