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.