Incremental update of specific columns of data in dbt

Question

Given a table with say, 20 columns, my current model already inserts all the records into the table. However, I'd like to focus my incremental updates on a couple of columns specifically.

For example, given the following table model. I'd like to incrementally update only column C with new values when new values are available, not delete and re-insert the whole row which is more costly on indexes / performance perspective.

Existing:

id	B	C
n1	X	1
n2	Y	2
n3	Z	3

New:

id	B	C
n1	X	1.2
n2	Y	2.1
n3	Z	3.5

user_10181754 · Answer

It seems this feature will be added to the next version of dbt (v0.20.0). But just to summarize, a new option (merge_update_columns) to specify the columns will be available in the model’s config block.

{{
  config(
    materialized = 'incremental',
    unique_key = 'id',
    merge_update_columns = ['c'],
    ...
  )
}}

select ...

Check the docs for more details: https://next.docs.getdbt.com/docs/building-a-dbt-project/building-models/configuring-incremental-models#strategy-specific-configs

Incremental update of specific columns of data in dbt

1 Answers