Incremental strategies (append, merge, delete+insert) in dbt - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to set the incremental strategy to append in a dbt model.

dbt

config(materialized='incremental', incremental_strategy='[1]')

Drag options to blanks, or click blank then click option'

Asnapshot

Bappend

Cdelete+insert

Dmerge

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'merge' when only adding rows is needed.

Confusing 'append' with 'delete+insert'.

The append strategy adds new rows without updating existing ones.

2fill in blank

medium

Complete the code to specify the unique key for the merge incremental strategy.

dbt

config(materialized='incremental', incremental_strategy='merge', unique_key='[1]')

Drag options to blanks, or click blank then click option'

Aid

Btimestamp

Cdate

Dvalue

Attempts:

3 left

💡 Hint

Common Mistakes

Using a non-unique column as the unique_key.

Leaving unique_key empty for merge strategy.

The unique_key identifies rows to update or insert during a merge.

3fill in blank

hard

Fix the error in the incremental filter condition to only process new data.

dbt

where updated_at > (select max(updated_at) from [1])

Drag options to blanks, or click blank then click option'

Asource_table

Bincremental_table

Ctarget_table

Dstaging_table

Attempts:

3 left

💡 Hint

Common Mistakes

Comparing with the source or staging table instead of the target.

Using a table that does not exist in the context.

The filter should compare with the max timestamp in the target_table to avoid reprocessing old data.

4fill in blank

hard

Fill both blanks to configure a delete+insert incremental strategy with a unique key.

dbt

config(materialized='incremental', incremental_strategy='[1]', unique_key='[2]')

Drag options to blanks, or click blank then click option'

Adelete+insert

Bappend

Cid

Dmerge

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'append' instead of 'delete+insert' for updates.

Not specifying a unique key.

The delete+insert strategy deletes existing rows matching the unique_key before inserting new data.

5fill in blank

hard

Fill all four blanks to write a merge incremental model with unique key and filter for new data.

dbt

config(materialized='incremental', incremental_strategy='[1]', unique_key='[2]')

select * from source_table
where updated_at > (select max([3]) from [4])

Drag options to blanks, or click blank then click option'

Amerge

Bid

Cupdated_at

Dtarget_table

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong incremental strategy.

Not filtering new data correctly.

This code sets the merge strategy with id as the unique key and filters rows where updated_at is newer than the max in the target table.