Full refresh vs incremental in dbt - Trade-offs & Expert Analysis

Overview - Full refresh vs incremental

What is it?

Full refresh and incremental are two ways to update data in a database using dbt. A full refresh rebuilds the entire dataset from scratch every time. Incremental updates only add or change the new or modified data since the last update. These methods help keep data fresh and accurate for analysis.

Why it matters

Without these update methods, data would become outdated or require too much time and computing power to refresh. Full refresh ensures complete accuracy but can be slow for large data. Incremental saves time and resources by updating only what changed. Choosing the right method affects how fast and reliable your data is for decisions.

Where it fits

Learners should know basic SQL and how dbt models work before this. After understanding full refresh and incremental, learners can explore advanced dbt features like snapshots and incremental merge strategies.

Mental Model

Core Idea

Full refresh rebuilds everything every time, while incremental updates only add or change what is new or different.

Think of it like...

It's like cleaning a room: full refresh is cleaning the entire room from top to bottom every time, while incremental is just tidying up the parts that got messy since last time.

┌───────────────┐       ┌───────────────┐
│   Full Refresh│       │  Incremental  │
├───────────────┤       ├───────────────┤
│ Delete all old│       │ Keep old data │
│ Rebuild all   │──────▶│ Add new data  │
│ data from raw │       │ Update changed│
│ sources       │       │ data only     │
└───────────────┘       └───────────────┘

Build-Up - 7 Steps

1

Foundation

What is a full refresh

Concept: Full refresh means rebuilding the entire dataset from scratch.

In dbt, a full refresh deletes the existing table and recreates it completely using the source data. This ensures the data is fully up to date but can take longer for large datasets.

Result

The database table contains only fresh data from the latest run, with no leftovers from before.

Understanding full refresh shows how dbt guarantees data accuracy by starting clean each time.

2

Foundation

What is incremental update

Concept: Incremental update means adding or changing only new or modified data.

Instead of rebuilding the whole table, dbt incremental models add new rows or update changed rows based on a unique key or timestamp. This saves time and resources.

Result

The table grows or changes only where needed, keeping old data intact.

Knowing incremental updates helps you optimize data refresh speed and reduce computing costs.

3

Intermediate

How dbt implements full refresh

🤔Before reading on: do you think dbt keeps old data during full refresh or deletes it? Commit to your answer.

Concept: dbt deletes the existing table before rebuilding it during a full refresh.

When you run dbt with the --full-refresh flag, it drops the target table and recreates it from the source data. This means all previous data is removed before loading fresh data.

Result

The table is fully replaced with new data, ensuring no stale data remains.

Understanding this prevents confusion about why full refresh can be slow and resource-heavy.

4

Intermediate

How dbt implements incremental models

🤔Before reading on: do you think incremental models always append data or can they update existing rows? Commit to your answer.

Concept: dbt incremental models can both append new rows and update existing rows based on logic you define.

In dbt, you write SQL that selects only new or changed data. dbt merges this data into the existing table using unique keys. This can be simple append or more complex update logic.

Result

The table is updated efficiently without rebuilding everything.

Knowing this helps you write incremental models that keep data consistent and fast to update.

5

Intermediate

Choosing between full refresh and incremental

🤔Before reading on: do you think incremental is always better than full refresh? Commit to your answer.

Concept: Choosing depends on data size, update frequency, and accuracy needs.

Full refresh is simple and safe but slow for big data. Incremental is faster but requires careful logic to avoid errors. Sometimes full refresh is needed to fix data issues or after schema changes.

Result

You pick the best method for your project needs balancing speed and correctness.

Understanding tradeoffs helps you design efficient and reliable data pipelines.

6

Advanced

Handling schema changes in incremental models

🤔Before reading on: do you think incremental models automatically handle column changes? Commit to your answer.

Concept: Incremental models do not automatically handle schema changes; you must manage them carefully.

If you add or remove columns, incremental models may fail or produce wrong data. You often need to run a full refresh or write migration logic to handle schema updates safely.

Result

Proper schema management avoids broken pipelines and data errors.

Knowing this prevents common production bugs when evolving data models.

7

Expert

Advanced incremental merge strategies

🤔Before reading on: do you think incremental merges always use simple append? Commit to your answer.

Concept: Advanced incremental merges use database-specific features like MERGE statements for upserts.

Some databases support MERGE or UPSERT commands that combine insert and update in one step. dbt can leverage these for efficient incremental updates, reducing complexity and improving performance.

Result

Incremental models become more robust and faster with advanced merge logic.

Understanding database capabilities unlocks powerful incremental update patterns beyond simple append.

Under the Hood

Full refresh works by dropping the entire target table and recreating it from source data, ensuring a clean slate. Incremental updates run a query that selects only new or changed rows and merges them into the existing table using keys. This merge can be an insert or update depending on the logic. Internally, dbt compiles SQL to perform these operations efficiently on the database engine.

Why designed this way?

Full refresh was designed for simplicity and correctness, guaranteeing fresh data but at a cost. Incremental was introduced to optimize performance for large datasets by avoiding full rebuilds. The design balances ease of use with efficiency, letting users choose based on their needs. Alternatives like change data capture exist but are more complex to implement.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ Source Data   │──────▶│ Full Refresh  │──────▶│ Drop & Rebuild│
│ (Raw tables)  │       │ Process      │       │ Entire Table  │
└───────────────┘       └───────────────┘       └───────────────┘

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ Source Data   │──────▶│ Incremental   │──────▶│ Merge New &   │
│ (Raw tables)  │       │ Process      │       │ Changed Rows  │
└───────────────┘       └───────────────┘       └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does incremental update always guarantee fully accurate data? Commit yes or no.

Common Belief: Incremental updates always keep data perfectly accurate without issues.

Reality: Incremental updates can miss changes or cause duplicates if keys or logic are incorrect.

Why it matters: Relying blindly on incremental can lead to wrong analysis and bad decisions.

Quick: Does full refresh always take longer than incremental? Commit yes or no.

Common Belief: Full refresh is always slower than incremental updates.

Reality: For small datasets or after many incremental runs, full refresh can be faster and simpler.

Why it matters: Choosing incremental blindly can waste time if full refresh is actually more efficient.

Quick: Do incremental models automatically handle schema changes? Commit yes or no.

Common Belief: Incremental models adapt automatically to schema changes like new columns.

Reality: Schema changes often break incremental models unless handled explicitly.

Why it matters: Ignoring this causes pipeline failures and data loss.

Quick: Is full refresh always necessary after any data change? Commit yes or no.

Common Belief: You must run full refresh every time data changes to be safe.

Reality: Incremental updates can handle many changes efficiently without full refresh.

Why it matters: Overusing full refresh wastes resources and slows down workflows.

Expert Zone

1

Incremental models require careful choice of unique keys and update logic to avoid data duplication or loss.

2

Some databases support advanced merge commands that can simplify incremental updates but require custom SQL.

3

Full refresh can be combined with incremental by scheduling periodic full refreshes to reset data state.

When NOT to use

Avoid incremental models when data sources lack reliable unique keys or when schema changes frequently. Use full refresh or snapshots instead. Incremental is also not ideal for very small datasets where full refresh is fast enough.

Production Patterns

In production, teams often use incremental models for daily updates and schedule full refreshes weekly or monthly. They also implement tests to detect incremental failures and use database-specific merge features for performance.

Connections

Change Data Capture (CDC)

Builds-on

Incremental updates in dbt are a simplified form of CDC, capturing only changes to update data efficiently.

ETL Pipelines

Same pattern

Full refresh and incremental updates are core patterns in ETL pipelines to manage data freshness and resource use.

Software Version Control

Opposite pattern

Full refresh is like resetting to a clean commit, while incremental updates are like applying patches or commits incrementally.

Common Pitfalls

#1 Running incremental model without a unique key causes duplicate rows.

Wrong approach:

SELECT * FROM source_table WHERE updated_at > (SELECT MAX(updated_at) FROM target_table)

Correct approach:

SELECT * FROM source_table WHERE updated_at > (SELECT MAX(updated_at) FROM target_table) AND unique_id IS NOT NULL

Root cause:

Not enforcing a unique key means dbt cannot correctly merge or update rows, causing duplicates.

#2 Running incremental model after schema change without full refresh causes errors.

Wrong approach:

Run incremental model after adding a new column without adjusting model or refreshing.

Correct approach:

Run dbt with --full-refresh after schema change to rebuild table with new columns.

Root cause:

Incremental logic does not handle schema changes automatically, requiring manual refresh.

#3 Using full refresh on very large datasets daily causes slow pipelines.

Wrong approach:

dbt run --full-refresh every day on multi-million row tables.

Correct approach:

Use incremental models for daily updates and schedule full refresh less frequently.

Root cause:

Not balancing resource use and data freshness leads to inefficient workflows.

Key Takeaways

Full refresh rebuilds the entire dataset from scratch, ensuring complete accuracy but can be slow.

Incremental updates add or change only new or modified data, saving time and resources.

Choosing between full refresh and incremental depends on data size, update frequency, and schema stability.

Incremental models require careful design of unique keys and update logic to avoid errors.

Advanced incremental merges use database features like MERGE for efficient upserts in production.

Practice

1.

What is the main difference between a full refresh and an incremental model in dbt?

easy

A. Full refresh only runs once; incremental runs every time.

B. Full refresh loads only new data; incremental reloads all data.

C. Full refresh updates data in place; incremental deletes and reloads all data.

D. Full refresh reloads all data; incremental loads only new or changed data.

Solution

1. Step 1: Understand full refresh behavior

   A full refresh reloads the entire dataset, replacing all existing data.

2. Step 2: Understand incremental behavior

   An incremental model only adds or updates new or changed data, saving time.

3. Final Answer:

   Full refresh reloads all data; incremental loads only new or changed data. -> Option D

4. Quick Check:

   Full refresh = reload all, Incremental = load changes [OK]

Hint: Full refresh = all data; incremental = only new or changed [OK]

Common Mistakes:

• Confusing which method reloads all data
• Thinking incremental deletes all data first
• Assuming full refresh only runs once

2.

Which of the following is the correct way to configure a dbt model for incremental loading?

-- model.sql
{{ config(
    materialized = '???'
) }}

easy

A. 'incremental'

B. 'table'

C. 'view'

D. 'ephemeral'

Solution

1. Step 1: Identify materialization types in dbt

   dbt supports 'table', 'view', 'incremental', and 'ephemeral' materializations.

2. Step 2: Select the incremental materialization

   To enable incremental loading, set materialized = 'incremental'.

3. Final Answer:

   'incremental' -> Option A

4. Quick Check:

   Incremental config = 'incremental' [OK]

Hint: Incremental models use materialized = 'incremental' [OK]

Common Mistakes:

• Using 'table' instead of 'incremental'
• Confusing 'view' with incremental
• Forgetting to set materialized property

3.

Given this incremental model SQL snippet, what will happen when you run dbt run --full-refresh?

{{ config(materialized='incremental') }}

select id, updated_at, data
from source_table

{% if is_incremental() %}
  where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

medium

A. The model will fail because is_incremental() is not allowed.

B. Only new rows with updated updated_at will be added.

C. The entire table will be rebuilt from source_table.

D. Nothing will change; incremental logic is ignored.

Solution

1. Step 1: Understand --full-refresh effect

   Running with --full-refresh forces dbt to drop and rebuild the entire incremental model.

2. Step 2: Analyze the SQL logic

   The is_incremental() block is skipped during full refresh, so no filter applies; all rows are selected.

3. Final Answer:

   The entire table will be rebuilt from source_table. -> Option C

4. Quick Check:

   Full refresh rebuilds all data [OK]

Hint: Full refresh rebuilds entire incremental model [OK]

Common Mistakes:

• Thinking incremental filter still applies on full refresh
• Assuming model fails with is_incremental()
• Believing no data changes on full refresh

4.

Identify the error in this incremental model SQL snippet:

{{ config(materialized='incremental') }}

select id, updated_at, data
from source_table

{% if is_incremental() %}
  where updated_at > (select max(updated_at) from source_table)
{% endif %}

medium

A. The filter compares updated_at to max from source_table instead of the target table.

B. The is_incremental() block is missing an else clause.

C. The model should use materialized='table' instead of incremental.

D. The select statement is missing a group by clause.

Solution

1. Step 1: Check the incremental filter logic

   The filter uses max(updated_at) from source_table, which is the source, not the target incremental table.

2. Step 2: Understand correct incremental filter

   The filter should compare to max(updated_at) from the target table ({{ this }}) to avoid reloading all data.

3. Final Answer:

   The filter compares updated_at to max from source_table instead of the target table. -> Option A

4. Quick Check:

   Incremental filter must use target table max date [OK]

Hint: Use target table max date in incremental filter [OK]

Common Mistakes:

• Using source table max date in incremental filter
• Forgetting to use {{ this }} in subquery
• Confusing incremental and full refresh logic

5.

You have a large sales table that updates daily. You want to use dbt incremental models to update only new sales but also occasionally rebuild the entire table to fix data issues. Which approach is best?

1. Use materialized='incremental' with is_incremental() filter on sale_date.
2. Run dbt run daily for incremental updates.
3. Run dbt run --full-refresh when you need a full rebuild.

What is the main benefit of this approach?

hard

A. It always reloads all data, ensuring no data is missed.

B. It saves time by only processing new data daily and allows full rebuilds when needed.

C. It never rebuilds the table, so data issues cannot be fixed.

D. It requires manual deletion of old data before each run.

Solution

1. Step 1: Understand incremental daily updates

   Using incremental materialization with a filter on sale_date loads only new sales daily, saving time.

2. Step 2: Understand full refresh usage

   Running with --full-refresh rebuilds the entire table to fix data issues when needed.

3. Step 3: Identify main benefit

   This approach balances efficiency and data correctness by updating incrementally and allowing full rebuilds.

4. Final Answer:

   It saves time by only processing new data daily and allows full rebuilds when needed. -> Option B

5. Quick Check:

   Incremental + full refresh = efficient and flexible [OK]

Hint: Incremental daily + full refresh occasionally = best practice [OK]

Common Mistakes:

• Thinking full refresh runs every day
• Believing incremental never rebuilds data
• Assuming manual data deletion is required