New components for incremental strategies

[jtcohen6]

Contributions

• I have read the contribution docs, and understand what's expected of me.

Link to the page on docs.getdbt.com requiring updates

• https://docs.getdbt.com/docs/building-a-dbt-project/building-models/configuring-incremental-models#configuring-incremental-strategy
• an Adapter page also

What part(s) of the page would you like to see updated?

New pattern

• incremental_strategy is a real node config
• If incremental_strategy is set to XYZ, dbt will look for a macro named get_incremental_XYZ_sql, to return the SQL (DML) it needs to run in the incremental materialization
• Default value of incremental_strategy config is None, in which case dbt will look for a macro named get_incremental_default_sql
• The default strategy for the base adapter is append, i.e. insert into (no upsert/merge, no unique_key / partition support). This is a change worth calling out: It used to be delete+insert, implicitly. Now it is append explicitly.

Built-in strategies 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 adapter maintainers

• Adapters declare which incremental strategy macro to use by default, by overriding get_incremental_default_sql, and passing in the appropriate macro (get_incremental_delete_insert_sql, get_incremental_merge_sql, etc)
• Adapters explicitly declare which incremental strategies are supported, via a valid_incremental_strategies method (Python) that returns a list of strategy names. They can also override any of the get_incremental_x_sql macros with a more database-friendly implementation.
• Adapter maintainers can add new strategies that are both "valid" + "builtin"
• For the current built-in strategies (append, delete+insert, merge, insert_overwrite), dbt will validate that the user has selected one that their adapter explicitly supports

For end users (covered by #4716)

• Users can also define their own "custom" strategies, by defining a macro get_incremental_silly_sql, and then configuring incremental_strategy: silly.
• 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.

The benefit to end users is that there's a lot less code involved than if creating an entirely new materialization

Toggle to show silly example

Here's an example of an incremental model that just appends (i.e., identical behavior as the append strategy).

Add a file named macros/whatever_file_name_you_want.sql:

{% macro get_incremental_silly_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="silly",
    unique_key="id",
) }}

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

Outstanding questions

• How can users or organization share their custom incremental strategies or overrides? Can they share in a dbt package on the dbt Package Hub for example?
• What is the resolution order when a project or a package has the same macro name as that for a built-in incremental strategy?

See dbt-core #8769 for a related conversation.

Additional information

Changes in dbt-core: dbt-labs/dbt-core#5359

Refactor in dbt-snowflake, to use core changes: dbt-labs/dbt-snowflake#196

Base compatibility (for now), without actually taking advantage of full core changes:

• Changes for incremental strategy refactor compatibility dbt-bigquery#223
• Change to support core incremental refactor dbt-spark#394

[mirnawong1]

Matt will review and verify if this was handled. See docs here

[mirnawong1]

hey @jtcohen6 , i believe the docs covers this now and is addressed (example here). please let us know if not though and we'll be happy to reopen!