Incremental strategies (append, merge, delete+insert) in dbt - Deep Dive

Overview - Incremental strategies (append, merge, delete+insert)

What is it?

Incremental strategies in dbt are ways to update data models efficiently by only processing new or changed data instead of rebuilding everything from scratch. The three main strategies are append, merge, and delete+insert. Append adds new rows, merge updates existing rows and adds new ones, and delete+insert replaces data by deleting old rows and inserting fresh ones. These methods help save time and computing resources when working with large datasets.

Why it matters

Without incremental strategies, every data update would require rebuilding entire tables, which can be slow and costly. Incremental updates let data teams refresh only what changed, making pipelines faster and more efficient. This means quicker insights and less strain on data warehouses, which is crucial for businesses relying on timely data decisions.

Where it fits

Learners should first understand basic SQL and dbt models before tackling incremental strategies. After mastering these, they can explore advanced data engineering topics like data freshness, partitioning, and orchestration. Incremental strategies are a key step toward building scalable, maintainable data pipelines.

Mental Model

Core Idea

Incremental strategies update data by processing only new or changed parts instead of rebuilding everything.

Think of it like...

It's like updating a photo album by adding new pictures, fixing some existing ones, or replacing old pages, instead of printing a whole new album every time.

┌───────────────┐
│ Full Refresh  │
│ (Rebuild all) │
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│ Incremental Strategies       │
│ ┌───────────┐ ┌───────────┐ │
│ │ Append    │ │ Merge     │ │
│ │ (Add new) │ │ (Add+Upd) │ │
│ └───────────┘ └───────────┘ │
│          ┌───────────────┐  │
│          │ Delete+Insert │  │
│          │ (Replace data)│  │
│          └───────────────┘  │
└─────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

What is Incremental Loading

Concept: Introduce the basic idea of incremental loading as updating data by adding only new information.

Imagine you have a list of sales every day. Instead of rewriting the whole list every time, you just add the new day's sales to the end. This is incremental loading — adding new data without touching old data.

Result

You save time and resources by not reprocessing old data.

Understanding incremental loading helps you see why full rebuilds are often unnecessary and inefficient.

2

Foundation

dbt Incremental Model Basics

Concept: Learn how dbt supports incremental models to update tables efficiently.

In dbt, you can write models that only process new or changed data by using the 'is_incremental()' function. This lets dbt know when to append or update data instead of rebuilding the whole table.

Result

dbt runs faster and uses less warehouse resources during incremental runs.

Knowing how dbt detects incremental runs is key to writing efficient models.

3

Intermediate

Append Strategy Explained

🤔Before reading on: do you think append can update existing rows or only add new ones? Commit to your answer.

Concept: Append adds only new rows to the existing table without changing old rows.

Append strategy assumes data never changes once added. For example, daily logs are added as new rows. The model filters new data and inserts it at the end of the existing table.

Result

The table grows by adding new rows, old data stays untouched.

Understanding append clarifies when it's safe to just add data without checking for updates.

4

Intermediate

Merge Strategy Explained

🤔Before reading on: do you think merge can handle both new and updated rows? Commit to your answer.

Concept: Merge updates existing rows if they changed and adds new rows if they are new.

Merge uses a unique key to find matching rows. If a row exists, it updates it; if not, it inserts it. This is useful when data can change, like customer info or product prices.

Result

The table stays up-to-date with both new and changed data.

Knowing merge helps you handle data that changes over time, not just grows.

5

Intermediate

Delete+Insert Strategy Explained

🤔Before reading on: do you think delete+insert removes all data or just some? Commit to your answer.

Concept: Delete+insert removes specific old data and replaces it with fresh data.

This strategy deletes rows matching a condition (like a date range) and inserts new rows for that range. It's useful when data corrections or reprocessing are needed.

Result

The affected data is fully refreshed without rebuilding the entire table.

Understanding delete+insert shows how to handle data corrections efficiently.

6

Advanced

Choosing the Right Incremental Strategy

🤔Before reading on: which strategy would you pick for data that never changes? Commit to your answer.

Concept: Different data scenarios require different incremental strategies for best performance and accuracy.

For append-only data, use append. For data that changes, use merge. For data needing full refresh in parts, use delete+insert. Consider data volume, update frequency, and warehouse capabilities.

Result

You select the most efficient and correct strategy for your data pipeline.

Knowing how to match strategy to data type prevents errors and optimizes performance.

7

Expert

Handling Complex Incremental Edge Cases

🤔Before reading on: do you think incremental models always guarantee perfect data consistency? Commit to your answer.

Concept: Incremental models can face challenges like late-arriving data, duplicates, or partial updates requiring careful handling.

Sometimes data arrives late or updates overlap. You may need to combine strategies or add logic to detect and fix duplicates. Also, warehouse-specific SQL features affect how merges and deletes work.

Result

You build robust incremental models that handle real-world data quirks.

Understanding edge cases prepares you to avoid subtle bugs and maintain data quality in production.

Under the Hood

dbt incremental models run SQL that checks if the table exists and if the run is incremental. For append, it inserts filtered new rows. For merge, it uses SQL MERGE or equivalent commands to update and insert rows based on keys. For delete+insert, it deletes rows matching criteria then inserts fresh data. The warehouse executes these commands efficiently using indexes and partitions.

Why designed this way?

Incremental strategies were designed to reduce resource use and speed up data pipelines by avoiding full table rebuilds. Early data tools rebuilt everything, which was slow and costly. Incremental methods balance freshness and efficiency, adapting to different data change patterns.

┌───────────────────────────────┐
│ dbt Incremental Model Process  │
├───────────────┬───────────────┤
│ Check if table exists          │
│ and if run is incremental     │
├───────────────┴───────────────┤
│ If full refresh:               │
│   Rebuild entire table        │
│ Else if incremental:          │
│   ┌───────────────┐           │
│   │ Append        │           │
│   │ Insert new rows│           │
│   ├───────────────┤           │
│   │ Merge         │           │
│   │ Update+Insert │           │
│   ├───────────────┤           │
│   │ Delete+Insert │           │
│   │ Delete old +  │           │
│   │ Insert new    │           │
│   └───────────────┘           │
└───────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does append strategy update existing rows or only add new ones? Commit to yes or no.

Common Belief: Append strategy can update existing rows if data changes.

Reality: Append only adds new rows and never updates existing ones.

Why it matters: Using append when data changes leads to stale or duplicate data, causing inaccurate reports.

Quick: Does merge strategy always guarantee no duplicates? Commit to yes or no.

Common Belief: Merge strategy automatically prevents all duplicates.

Reality: Merge relies on correct unique keys; if keys are wrong or missing, duplicates can still occur.

Why it matters: Assuming merge fixes duplicates without proper keys can cause data integrity issues.

Quick: Is delete+insert the same as a full table rebuild? Commit to yes or no.

Common Belief: Delete+insert is just a full rebuild under a different name.

Reality: Delete+insert only replaces specific partitions or subsets, not the entire table.

Why it matters: Misunderstanding this can lead to inefficient full rebuilds when partial refreshes would suffice.

Quick: Do incremental models always run faster than full refreshes? Commit to yes or no.

Common Belief: Incremental models are always faster than full refreshes.

Reality: If incremental logic is complex or data changes widely, incremental runs can be slower or as slow as full refreshes.

Why it matters: Blindly trusting incremental speed can cause unexpected pipeline delays and resource use.

Expert Zone

1

Incremental strategies depend heavily on the uniqueness and stability of keys; changing keys can break merges.

2

Warehouse-specific SQL features like MERGE syntax or partition pruning affect how incremental strategies perform and must be understood deeply.

3

Combining incremental strategies with snapshotting or change data capture can create powerful, reliable pipelines but requires careful orchestration.

When NOT to use

Incremental strategies are not suitable when data changes affect large portions of the table or when data lineage requires full rebuilds for accuracy. In such cases, full refresh or snapshot models are better alternatives.

Production Patterns

In production, teams often use append for log data, merge for slowly changing dimensions, and delete+insert for partitioned fact tables with late-arriving data. They also automate incremental runs with freshness checks and alerting to catch failures early.

Connections

Change Data Capture (CDC)

Incremental strategies build on CDC by applying changes efficiently to data models.

Understanding CDC helps grasp how incremental updates track and apply only data changes, improving pipeline efficiency.

Database Indexing

Incremental merges rely on indexes to quickly find and update rows.

Knowing indexing principles explains why merge operations can be fast or slow depending on key design.

Version Control Systems (e.g., Git)

Incremental updates in data are similar to commits in version control, where only changes are saved.

Seeing incremental data updates like code commits clarifies the value of tracking and applying only differences.

Common Pitfalls

#1 Using append strategy on data that changes over time.

Wrong approach:

if is_incremental():
select * from source where date > (select max(date) from target)

Correct approach:

if is_incremental():
merge into target using source on key
when matched then update
when not matched then insert

Root cause:

Misunderstanding that append only adds new rows and cannot update existing data.

#2 Not defining a unique key for merge strategy.

Wrong approach:

merge into target using source on source.date = target.date
when matched then update set ...

Correct approach:

merge into target using source on source.primary_key = target.primary_key
when matched then update set ...

Root cause:

Assuming any column can serve as a unique key without ensuring uniqueness.

#3 Deleting entire table instead of partition for delete+insert.

Wrong approach:

delete from target;
insert into target select * from source where date = '2024-01-01'

Correct approach:

delete from target where date = '2024-01-01';
insert into target select * from source where date = '2024-01-01'

Root cause:

Confusing full table refresh with partial partition refresh.

Key Takeaways

Incremental strategies update data efficiently by processing only new or changed data, saving time and resources.

Append adds new rows only, merge updates existing rows and adds new ones, and delete+insert replaces specific data subsets.

Choosing the right strategy depends on data characteristics like mutability and volume.

Understanding warehouse SQL features and data keys is crucial for reliable incremental models.

Incremental updates are powerful but require careful design to avoid data quality issues.

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