Incremental strategies (append, merge, delete+insert) in dbt - Mini Project: Build & Apply

Incremental Strategies in dbt: Append, Merge, and Delete+Insert

📖 Scenario: You work as a data analyst for a retail company. You have a sales data table that updates daily. Instead of rebuilding the entire table every day, you want to update it efficiently using incremental strategies.This project will teach you how to use three common incremental strategies in dbt: append, merge, and delete+insert.

🎯 Goal: You will build three dbt models using incremental strategies to update a sales data table efficiently:Create an append model that adds new rows only.Create a merge model that updates existing rows and adds new ones.Create a delete+insert model that deletes outdated rows and inserts fresh data.

📋 What You'll Learn

Use dbt incremental materializations

Write SQL queries with incremental filters

Use unique keys for merge strategy

Use delete and insert statements for delete+insert strategy

💡 Why This Matters

🌍 Real World

Incremental models help update large datasets efficiently without rebuilding entire tables daily, saving time and computing resources.

💼 Career

Data engineers and analysts use incremental strategies in dbt to maintain up-to-date data warehouses and improve data pipeline performance.

1

Create an append incremental model

Create a dbt model SQL file named sales_append.sql. Write a query selecting all columns from the source table raw_sales. Add a filter to select only rows where sale_date is greater than the maximum sale_date in the target table {{ this }}. Use the append incremental strategy in the model config.

dbt

-- Create a dbt model with incremental append strategy
-- Select all columns from raw_sales
-- Filter rows where sale_date is greater than max sale_date in target

{{ config(materialized='incremental', incremental_strategy='append') }}

select * from raw_sales
where sale_date > (select coalesce(max(sale_date), '1900-01-01') from {{ this }})

Hint

Use {{ config(materialized='incremental', incremental_strategy='append') }} at the top.

Filter new rows by comparing sale_date with the max date in the target table.

2

Create a merge incremental model with unique key

Create a dbt model SQL file named sales_merge.sql. Write a query selecting all columns from raw_sales. Use the merge incremental strategy in the model config. Set the unique key to sale_id. Add a filter to select rows where sale_date is greater than the maximum sale_date in the target table {{ this }}.

dbt

{{ config(materialized='incremental', incremental_strategy='append') }}

select * from raw_sales
where sale_date > (select coalesce(max(sale_date), '1900-01-01') from {{ this }})

-- Change config to use merge strategy with unique_key 'sale_id'
-- Adjust query if needed
# Your code here

Hint

Change incremental_strategy to 'merge' and add unique_key='sale_id' in the config.

3

Create a delete+insert incremental model

Create a dbt model SQL file named sales_delete_insert.sql. Use the delete+insert incremental strategy in the model config. Write a query selecting all columns from raw_sales. Add a filter to select rows where sale_date is greater than the maximum sale_date in the target table {{ this }}.

dbt

{{ config(materialized='incremental', incremental_strategy='merge', unique_key='sale_id') }}

select * from raw_sales
where sale_date > (select coalesce(max(sale_date), '1900-01-01') from {{ this }})

-- Change config to use delete+insert incremental strategy
# Your code here

Hint

Set incremental_strategy to 'delete+insert' in the config.

4

Print confirmation of incremental models

Print the text 'Incremental models for append, merge, and delete+insert created successfully.' to confirm completion.

dbt

{{ config(materialized='incremental', incremental_strategy='delete+insert') }}

select * from raw_sales
where sale_date > (select coalesce(max(sale_date), '1900-01-01') from {{ this }})

# Print confirmation message
# Your code here

Hint

Use a print statement with the exact text.

Practice

1. Which incremental strategy in dbt only adds new rows without modifying existing ones?

easy

A. delete+insert

B. merge

C. append

D. truncate

Solution

1. Step 1: Understand the append strategy

   The append strategy adds only new rows to the existing table without changing any existing data.

2. Step 2: Compare with other strategies

   Merge updates existing rows and adds new ones; delete+insert replaces matching rows; truncate removes all data first.

3. Final Answer:

   append -> Option C

4. Quick Check:

   append = add new rows only [OK]

Hint: Append means add new rows only, no updates [OK]

Common Mistakes:

• Confusing merge with append
• Thinking delete+insert only adds rows
• Assuming truncate is an incremental strategy

2. Which of the following is the correct syntax to define an incremental model using the merge strategy in dbt?

easy

A. config(materialized='incremental', incremental_strategy='append')

B. config(materialized='incremental', strategy='merge')

C. config(materialized='table', incremental_strategy='merge')

D. config(materialized='incremental', incremental_strategy='merge')

Solution

1. Step 1: Identify correct config keys for incremental merge

   dbt requires materialized='incremental' and incremental_strategy='merge' to use merge strategy.

2. Step 2: Check each option for correctness

   config(materialized='incremental', incremental_strategy='append') uses 'append' strategy, not merge; config(materialized='table', incremental_strategy='merge') uses materialized='table' which is not incremental; config(materialized='incremental', strategy='merge') uses wrong key 'strategy'.

3. Final Answer:

   config(materialized='incremental', incremental_strategy='merge') -> Option D

4. Quick Check:

   incremental_strategy='merge' with materialized='incremental' [OK]

Hint: Use incremental_strategy='merge' inside config() for merge [OK]

Common Mistakes:

• Using wrong materialized type
• Misspelling incremental_strategy key
• Confusing strategy key name

3. Given this dbt incremental model snippet using merge strategy:

select id, value from source_table where updated_at > (select max(updated_at) from {{ this }})

What will happen when this model runs?

medium

A. Only new rows with updated_at greater than max in target are added

B. Rows with matching id are updated or inserted based on updated_at filter

C. All rows from source_table replace the target table

D. No rows are added or updated because filter excludes all

Solution

1. Step 1: Understand the filter condition

   The query selects rows from source_table where updated_at is greater than the max updated_at in the target table ({{ this }}).

2. Step 2: Understand merge behavior

   Merge updates existing rows with matching keys and inserts new rows. The filter ensures only changed rows are processed.

3. Final Answer:

   Rows with matching id are updated or inserted based on updated_at filter -> Option B

4. Quick Check:

   merge updates/inserts filtered rows [OK]

Hint: Merge updates or inserts rows filtered by updated_at [OK]

Common Mistakes:

• Thinking it replaces entire table
• Assuming only new rows are appended
• Ignoring the filter condition effect

4. You wrote this incremental model using delete+insert strategy:

config(materialized='incremental', incremental_strategy='delete+insert')

select * from source_table

But the model fails with an error about missing unique key. What is the likely cause?

medium

A. You did not specify the unique_key config required for delete+insert

B. The source_table is empty

C. delete+insert strategy does not support incremental models

D. You must use append strategy instead

Solution

1. Step 1: Recall delete+insert requirements

   Delete+insert requires a unique_key config to identify rows to delete before insert.

2. Step 2: Analyze error cause

   Missing unique_key causes dbt to error because it cannot identify which rows to delete.

3. Final Answer:

   You did not specify the unique_key config required for delete+insert -> Option A

4. Quick Check:

   delete+insert needs unique_key [OK]

Hint: Always set unique_key for delete+insert strategy [OK]

Common Mistakes:

• Ignoring unique_key requirement
• Assuming delete+insert works without keys
• Confusing append with delete+insert

5. You have a large sales table that updates daily with new sales and corrections to existing sales records. Which incremental strategy should you choose in dbt to efficiently update the table without full reloads?

hard

A. merge, because it updates existing rows and adds new ones

B. delete+insert, because it deletes all rows and inserts fresh data

C. append, because it only adds new rows

D. full refresh, because incremental strategies are not suitable

Solution

1. Step 1: Analyze data update needs

   The table has new sales and corrections, so existing rows need updates and new rows need inserts.

2. Step 2: Match strategy to needs

   Append only adds new rows, so it won't update corrections. Delete+insert replaces matching rows but can be costly. Merge efficiently updates and inserts rows incrementally.

3. Final Answer:

   merge, because it updates existing rows and adds new ones -> Option A

4. Quick Check:

   merge = update + insert for corrections and new data [OK]

Hint: Use merge for updates plus new rows efficiently [OK]

Common Mistakes:

• Choosing append and missing updates
• Thinking delete+insert always best
• Ignoring performance of full refresh