Incremental strategies (append, merge, delete+insert) in dbt - Step-by-Step Execution

Concept Flow - Incremental strategies (append, merge, delete+insert)

Start: New Data Available

↓

Choose Incremental Strategy

↓

Append

↓

Add new

↓

Incremental Table Updated

↓

End

This flow shows how new data is added to an existing table using three strategies: append adds only new rows, merge updates existing rows and adds new ones, and delete+insert removes old data before adding fresh data.

Execution Sample

dbt

select * from source_table
where updated_at > (select max(updated_at) from target_table)

This query selects only new or updated rows from the source to add to the target table incrementally.

Execution Table

Step	Action	Source Data	Target Table Before	Target Table After	Notes
1	Initial target table	[A1, A2, A3]	[A1, A2]	[A1, A2]	Target has 2 rows initially
2	Append new rows	[A1, A2, A3]	[A1, A2]	[A1, A2, A3]	A3 appended, no updates
3	Merge update A2	[A1, A2(updated), A3]	[A1, A2, A3]	[A1, A2(updated), A3]	A2 row updated, others unchanged
4	Delete + Insert all	[A1, A2(updated), A3]	[A1, A2, A3]	[A1, A2(updated), A3]	Old rows deleted, all re-inserted fresh
5	End	-	[A1, A2(updated), A3]	[A1, A2(updated), A3]	Incremental update complete

💡 All new and updated data processed; incremental table is current.

Variable Tracker

Variable	Start	After Append	After Merge	After Delete+Insert	Final
target_table_rows	2	3	3	3	3
updated_rows	0	0	1	3	3
deleted_rows	0	0	0	3	3

Key Moments - 3 Insights

Why does the append strategy not update existing rows?

Because append only adds new rows without checking for existing data, as shown in step 2 of the execution_table where A3 is added but A2 remains unchanged.

How does the merge strategy handle updated rows?

Merge updates existing rows and adds new ones, demonstrated in step 3 where A2 is updated in the target table while A1 and A3 stay the same.

What happens in the delete + insert strategy before adding new data?

All old rows are deleted first, then fresh data is inserted, as seen in step 4 where the target table is cleared and then reloaded with updated rows.

Visual Quiz - 3 Questions

Look at the execution_table at step 2, how many rows does the target table have after append?

A 3

B 2

C 1

D 4

💡 Hint

Check the 'Target Table After' column at step 2 in the execution_table.

At which step does the target table get updated with a changed row?

A Step 2

B Step 3

C Step 4

D Step 1

💡 Hint

Look for the step where 'A2(updated)' appears in the 'Target Table After' column.

If we skip deleting old rows, which strategy are we NOT using?

A Append

B Merge

C Delete + Insert

D All strategies

💡 Hint

Refer to step 4 where old rows are deleted before inserting new data.

Concept Snapshot

Incremental strategies add new data efficiently:
- Append: add only new rows
- Merge: update existing + add new
- Delete + Insert: remove old, then add fresh
Choose based on data freshness and update needs.

Full Transcript

Incremental strategies in dbt help update tables efficiently. Append adds only new rows without changing existing ones. Merge updates existing rows and adds new ones. Delete plus insert removes all old data before inserting fresh data. This process keeps data current without full reloads. The execution table shows how the target table changes step-by-step with each strategy. Variables track rows added, updated, or deleted. Understanding these helps choose the right method for your data pipeline.