Full refresh vs incremental in dbt - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Full Refresh in dbt

Which statement best describes what happens during a full refresh in dbt?

A dbt updates only the changed rows in the existing table using merge operations.

B dbt deletes the existing table and rebuilds it completely from the source data.

C dbt only adds new rows to the existing table without modifying existing data.

D dbt skips the model and uses cached results from the previous run.

💡 Hint

Think about what happens when you want to start fresh with your data model.

A full refresh means dbt drops the existing table and rebuilds it entirely from the source data. This ensures the table is fully up to date but can be slower for large datasets.

🧠 Conceptual

intermediate

Incremental Model Behavior

What is the main advantage of using an incremental model in dbt?

A It processes only new or changed data, reducing runtime and resource use.

B It rebuilds the entire table every time to ensure data accuracy.

C It automatically archives old data to a separate table.

D It disables all data validations during the run.

💡 Hint

Consider how to save time when working with large datasets that update frequently.

Incremental models update only new or changed data, which makes runs faster and uses fewer resources compared to rebuilding the entire table.

❓ data_output

advanced

Output of Incremental Model Run

Given this incremental model SQL snippet, what will be the content of the target table after running the model twice?

-- model.sql
{{ config(materialized='incremental', unique_key='id') }}

select id, value from source_table where updated_at > (select max(updated_at) from {{ this }})

Assume source_table initially has rows with ids 1 and 2, then a new row with id 3 is added before the second run.

dbt

import pandas as pd

# Initial source_table data
source_table_1 = pd.DataFrame({'id': [1, 2], 'value': ['a', 'b'], 'updated_at': ['2024-01-01', '2024-01-02']})

# After first run, target table content
first_run = source_table_1[['id', 'value']]

# New data added
source_table_2 = pd.DataFrame({'id': [1, 2, 3], 'value': ['a', 'b', 'c'], 'updated_at': ['2024-01-01', '2024-01-02', '2024-01-03']})

# After second run, incremental adds only new row
second_run = pd.concat([first_run, source_table_2[source_table_2['id'] == 3][['id', 'value']]], ignore_index=True)

second_run

A [{'id': 1, 'value': 'a'}, {'id': 2, 'value': 'b'}, {'id': 3, 'value': 'c'}]

B [{'id': 3, 'value': 'c'}]

C [{'id': 1, 'value': 'a'}, {'id': 2, 'value': 'b'}]

D []

💡 Hint

Think about how incremental models add new rows without deleting existing ones.

After the first run, the table has rows with ids 1 and 2. The second run adds only the new row with id 3, so the table contains all three rows.

🔧 Debug

advanced

Error in Incremental Model Unique Key

Consider this dbt incremental model configuration:

{{ config(materialized='incremental', unique_key='user_id') }}

The source data has duplicate user_id values in the new data batch. What error or issue will most likely occur when running this model?

A dbt will silently drop duplicate rows without warning.

B dbt will fail with a syntax error due to duplicate keys.

C dbt will raise a <strong>unique key violation</strong> error during the merge step.

D dbt will rebuild the entire table ignoring incremental logic.

💡 Hint

Think about what happens if the unique key is not unique in the incremental data.

If the unique key column has duplicates in the new data, the merge operation will fail because it cannot uniquely identify rows to update or insert.

🚀 Application

expert

Choosing Between Full Refresh and Incremental

You manage a dbt model that processes millions of rows daily. The source data sometimes has late-arriving updates for past dates. Which approach best balances performance and data accuracy?

A Disable incremental and run only snapshots to track changes.

B Always use full refresh to ensure all data is accurate despite longer runtimes.

C Use incremental models without any reprocessing window and rely on source data correctness.

D Use incremental models with a window of days to reprocess recent data and full refresh weekly.

💡 Hint

Consider how to handle late data updates while keeping runtimes reasonable.

Incremental models with a reprocessing window allow updating recent data that might have changed, while full refreshes less frequently ensure overall accuracy without excessive runtime every day.

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