Incremental strategies (append, merge, delete+insert) in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Append Incremental Strategy

In dbt, the append incremental strategy adds new rows to the target table without modifying existing rows. Which of the following statements best describes a limitation of using the append strategy?

A It cannot update existing rows if source data changes, leading to potential duplicates.

B It deletes all rows before inserting new data, causing longer run times.

C It merges rows based on a unique key, updating existing records automatically.

D It requires manual deletion of old data before each run to avoid duplicates.

💡 Hint

Think about what happens if a row changes in the source after it was already appended.

The append strategy only adds new rows and does not update or remove existing rows. This can cause duplicates if source data changes.

❓ Predict Output

intermediate

Output of a Merge Incremental Model

Consider a dbt incremental model using the merge strategy with a unique key id. The source table has rows:

id | value
1  | 100
2  | 200
3  | 300

The target table initially has:

id | value
1  | 90
2  | 200

After running the incremental model, what will be the content of the target table?

dbt

merge into target_table using source_table on target_table.id = source_table.id
when matched then update set target_table.value = source_table.value
when not matched then insert (id, value) values (source_table.id, source_table.value);

A [{'id': 1, 'value': 90}, {'id': 2, 'value': 200}]

B [{'id': 1, 'value': 90}, {'id': 2, 'value': 200}, {'id': 3, 'value': 300}]

C [{'id': 1, 'value': 100}, {'id': 2, 'value': 200}]

D [{'id': 1, 'value': 100}, {'id': 2, 'value': 200}, {'id': 3, 'value': 300}]

💡 Hint

Remember that merge updates matching rows and inserts new ones.

The merge strategy updates existing rows with matching keys and inserts new rows, so id=1's value updates from 90 to 100, id=2 stays the same, and id=3 is added.

❓ data_output

advanced

Result of Delete+Insert Incremental Strategy

A dbt incremental model uses the delete+insert strategy to refresh data for a specific date partition. The source data for date '2024-06-01' has 3 rows. The target table initially has 5 rows for that date. After running the model, how many rows for '2024-06-01' will the target table have?

A 3

B 5

C 0

D 8

💡 Hint

Delete+insert removes old rows for the partition before inserting new ones.

The delete+insert strategy deletes existing rows for the partition and inserts fresh data, so the final count matches the source rows, which is 3.

🔧 Debug

advanced

Identify the Error in Incremental Merge SQL

Review this dbt incremental merge SQL snippet:

merge into target_table t
using source_table s
on t.id = s.id
when matched then update set value = s.value
when not matched then insert (id, value) values (s.id, s.value)

What is the error in this code?

A Using 'update set' instead of 'update set column = value'.

B Missing closing parenthesis in the insert values clause.

C Missing semicolon at the end of the statement.

D Incorrect alias usage for source_table.

💡 Hint

Check the parentheses carefully in the insert statement.

The insert values clause is missing a closing parenthesis, causing a syntax error.

🚀 Application

expert

Choosing the Best Incremental Strategy for Slowly Changing Data

You manage a sales data table that updates daily. Some rows may change values (like price) after initial insert. You want to keep the target table accurate without full reloads. Which incremental strategy is best?

A Append strategy, because it is fastest and adds new rows only.

B Delete+insert strategy, because it deletes all data and reloads fresh.

C Merge strategy, because it updates existing rows and inserts new ones.

D No incremental strategy; always do full reloads to ensure accuracy.

💡 Hint

Think about how to update existing rows efficiently.

The merge strategy updates existing rows and inserts new ones, making it ideal for data that changes over time without full reloads.

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