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 }})

Need a 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

Need a 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

Need a 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

Need a hint?

Use a print statement with the exact text.