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.