Full refresh vs incremental in dbt - Quick Revision & Key Differences

Recall & Review

beginner

What is a full refresh in dbt?

A full refresh means rebuilding the entire table from scratch every time the model runs. It deletes old data and loads all data fresh.

beginner

What does incremental loading mean in dbt?

Incremental loading means adding only new or changed data to the existing table, instead of rebuilding the whole table.

intermediate

Why use incremental models instead of full refresh?

Incremental models save time and resources by processing only new or updated data, making runs faster and cheaper.

intermediate

When should you use a full refresh in dbt?

Use full refresh when data changes are complex, or when you want to fix data issues by rebuilding the entire table.

advanced

What is a common challenge with incremental models?

Incremental models can be tricky if data updates or deletes happen, because they only add new data and may miss changes.

What happens during a full refresh in dbt?

A Only new data is added to the table

B Data is appended without deleting old data

C The entire table is rebuilt from scratch

D Only data changes are updated

A full refresh deletes the old table and rebuilds it completely with fresh data.

Which is a benefit of incremental models?

A They save time by processing only new data

B Runs are slower but more accurate

C They rebuild the entire table every time

D They delete all old data before loading

Incremental models add only new or changed data, making runs faster and more efficient.

When is a full refresh preferred over incremental?

A When data updates are simple

B When you want to fix data issues by rebuilding

C When you want to save time

D When only new data arrives

Full refresh is used to fix data problems by rebuilding the entire table.

What is a risk of using incremental models?

A They may miss data updates or deletes

B They cannot handle new data

C They delete all data before loading

D They always take longer to run

Incremental models may miss changes if data is updated or deleted because they only add new data.

Which dbt command triggers a full refresh?

A dbt seed

B dbt run --incremental

C dbt test

D dbt run --full-refresh

The --full-refresh flag tells dbt to rebuild tables fully.

Explain the difference between full refresh and incremental models in dbt.

Think about how much data is processed each time.

When would you choose to run a full refresh instead of incremental in your data pipeline?

Consider situations needing a fresh start.

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