Full refresh vs incremental in dbt - Visual Side-by-Side Comparison

Concept Flow - Full refresh vs incremental

Start dbt run

↓

Check model config

↓

Run full refresh

↓

Drop and recreate table

↓

Load all data

↓

Finish

↓

Run incremental

↓

Check for new/updated data

↓

Append or update only changed data

↓

Finish

The flow checks if a full refresh is requested; if yes, it rebuilds the entire table. Otherwise, it processes only new or changed data incrementally.

Execution Sample

dbt

model_config:
  materialized: incremental
  full_refresh: false

-- SQL logic for incremental load

This config runs the model incrementally, loading only new or changed data unless full_refresh is set to true.

Execution Table

Step	full_refresh flag	Action	Data Processed	Resulting Table State
1	false	Check full_refresh flag	N/A	No full refresh, proceed incremental
2	false	Identify new/updated rows	Only new/changed rows	Prepare incremental load
3	false	Append/update incremental data	New/changed rows added	Table updated incrementally
4	false	Finish run	N/A	Incremental table ready
5	true	Check full_refresh flag	N/A	Full refresh triggered
6	true	Drop existing table	N/A	Table dropped
7	true	Load all data fresh	All rows	Table rebuilt fully
8	true	Finish run	N/A	Full refresh table ready

💡 Execution stops after finishing either full refresh or incremental load.

Variable Tracker

Variable	Start	After Step 1	After Step 5	Final
full_refresh	false	false	true	true
data_processed	none	new/changed rows	all rows	all rows
table_state	empty or old	incrementally updated	dropped	fully rebuilt

Key Moments - 3 Insights

Why does the full refresh drop the table before loading data?

Because full refresh means rebuilding the entire table from scratch, so the old data must be removed first (see execution_table rows 6 and 7).

What happens if full_refresh is false but the source data has no new rows?

The incremental load finds no new or changed rows, so no data is added or updated, and the table remains the same (see execution_table row 2 and 3).

Can incremental mode handle updates to existing rows?

Yes, incremental mode can update existing rows if configured properly, by identifying changed rows and applying updates (see execution_table row 3).

Visual Quiz - 3 Questions

Look at the execution table, what is the table state after step 3 when full_refresh is false?

A Table updated incrementally

B Table dropped

C Table fully rebuilt

D Table empty

💡 Hint

Refer to execution_table row 3 under 'Resulting Table State'

At which step does the full refresh drop the existing table?

A Step 4

B Step 6

C Step 2

D Step 8

💡 Hint

Check execution_table row 6 for the drop action

If full_refresh is set to true, which steps will be executed?

A Steps 1 to 4

B Steps 5 to 8

C Only step 1

D Steps 2 and 3 only

💡 Hint

Look at execution_table rows 5 to 8 for full refresh flow

Concept Snapshot

Full refresh rebuilds the entire table by dropping and reloading all data.
Incremental loads only new or changed data, appending or updating the table.
Use full_refresh: true to force full rebuild.
Use materialized: incremental for efficient updates.
Full refresh is slower but ensures clean state.
Incremental is faster for ongoing data loads.

Full Transcript

This visual execution shows how dbt decides between full refresh and incremental runs. When full_refresh is true, dbt drops the existing table and reloads all data, ensuring a fresh start. When false, it only processes new or changed rows, updating the table incrementally. Variables like full_refresh flag and data_processed change during execution. Key moments include understanding why full refresh drops the table and how incremental handles updates. The quiz tests knowledge of table states and step actions. This helps beginners see exactly what happens step-by-step during dbt runs.

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