Full refresh vs incremental in dbt - When to Use Which

The Big Idea

What if you could update huge datasets in minutes instead of hours without risking mistakes?

The Scenario

Imagine you have a huge spreadsheet that tracks daily sales. Every day, you add new sales data. Now, you want to update your report. You can either rewrite the entire spreadsheet from scratch or just add the new sales data.

The Problem

Rewriting the whole spreadsheet every day takes a lot of time and computer power. It can also cause mistakes if you accidentally delete or overwrite data. On the other hand, adding new data manually can be confusing and easy to miss, leading to incomplete reports.

The Solution

Using full refresh vs incremental methods in dbt helps automate this process. Full refresh rebuilds the entire dataset when needed, ensuring everything is fresh. Incremental updates only add or change new data, saving time and reducing errors.

Before vs After

✗ Before

DELETE FROM sales_report;
INSERT INTO sales_report SELECT * FROM daily_sales;

✓ After

SELECT * FROM daily_sales
WHERE date > (SELECT COALESCE(MAX(date), '1900-01-01') FROM sales_report);

What It Enables

This concept lets you keep your data up-to-date efficiently, handling large datasets without wasting time or resources.

Real Life Example

A retail company updates its sales dashboard daily. Using incremental updates, they only process new sales data each day, making the dashboard fast and reliable.

Key Takeaways

Full refresh rebuilds all data, ensuring completeness.

Incremental updates add only new or changed data, saving time.

Choosing the right method improves data freshness and efficiency.

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