Why Incremental strategies (append, merge, delete+insert) in dbt? - Purpose & Use Cases

The Big Idea

What if you could update massive data tables in seconds without reloading everything?

The Scenario

Imagine you have a huge spreadsheet that keeps growing every day with new sales data. Every time you want to update your report, you open the file, scroll through thousands of rows, and try to add only the new sales without messing up the old data.

The Problem

Doing this by hand is slow and tiring. You might accidentally add duplicate rows or miss some updates. It's easy to make mistakes, and fixing them takes even more time. Plus, as the data grows, the process becomes almost impossible to manage manually.

The Solution

Incremental strategies in dbt let you update your data automatically and smartly. Instead of reloading everything, you add only new rows (append), update existing ones (merge), or replace changed parts (delete+insert). This saves time, reduces errors, and keeps your data fresh and accurate.

Before vs After

✗ Before

Open spreadsheet, copy new rows, paste at bottom, check for duplicates

✓ After

{{ config(materialized='incremental') }}
{% if is_incremental() %}
  -- merge new data with existing table
{% else %}
  -- load full data
{% endif %}

What It Enables

It enables fast, reliable updates to large datasets without reprocessing everything, making data workflows efficient and scalable.

Real Life Example

A retail company uses incremental strategies to update daily sales data in their warehouse. Instead of reloading all sales every day, they append only new transactions and merge updates for returns or corrections, keeping reports accurate and timely.

Key Takeaways

Manual updates are slow and error-prone for growing data.

Incremental strategies add or update only changed data automatically.

This approach saves time and keeps data accurate and fresh.