Full refresh vs incremental in dbt - Hands-On Comparison

Full Refresh vs Incremental in dbt

📖 Scenario: You work as a data analyst managing sales data in a data warehouse. You want to build a dbt model that either fully refreshes the entire sales summary table or incrementally updates it with only new sales data.This helps save time and resources by not reprocessing all data every time.

🎯 Goal: Build a simple Python simulation to understand the difference between full refresh and incremental update approaches on sales data.You will create initial sales data, set a refresh mode, apply the update logic, and print the final sales summary.

📋 What You'll Learn

Create a dictionary called sales_data with sales IDs as keys and amounts as values

Create a variable called refresh_mode set to either 'full' or 'incremental'

Write logic to update a sales_summary dictionary based on refresh_mode

Print the final sales_summary dictionary

💡 Why This Matters

🌍 Real World

Data teams use full refresh to rebuild tables from scratch and incremental updates to save time by processing only new data.

💼 Career

Understanding these concepts helps data analysts and engineers optimize data pipelines and improve performance in tools like dbt.

1

Create initial sales data

Create a dictionary called sales_data with these exact entries: 101: 250, 102: 450, 103: 300

dbt

# Create the sales_data dictionary with given sales IDs and amounts
# Your code here

Hint

Use curly braces to create a dictionary with keys 101, 102, 103 and their amounts.

2

Set the refresh mode

Create a variable called refresh_mode and set it to the string 'incremental'

dbt

sales_data = {101: 250, 102: 450, 103: 300}
# Set refresh_mode to 'incremental'
# Your code here

Hint

Assign the string 'incremental' to the variable refresh_mode.

3

Apply update logic based on refresh mode

Create an empty dictionary called sales_summary. Then write an if statement that checks if refresh_mode is 'full'. If yes, copy all sales_data into sales_summary. Otherwise, add only new sales with IDs greater than 102 from sales_data into sales_summary.

dbt

sales_data = {101: 250, 102: 450, 103: 300}
refresh_mode = 'incremental'
# Create sales_summary and update it based on refresh_mode
# Your code here

Hint

Use copy() to duplicate the dictionary for full refresh. Use a for loop and condition to add only new sales for incremental.

4

Print the final sales summary

Write a print statement to display the sales_summary dictionary.

dbt

sales_data = {101: 250, 102: 450, 103: 300}
refresh_mode = 'incremental'
sales_summary = {}
if refresh_mode == 'full':
    sales_summary = sales_data.copy()
else:
    for sale_id, amount in sales_data.items():
        if sale_id > 102:
            sales_summary[sale_id] = amount
# Print the sales_summary dictionary
# Your code here

Hint

Use print(sales_summary) to show the final dictionary.

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