is_incremental() macro in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of is_incremental() in a full-refresh run

Consider a dbt model using the is_incremental() macro. What will be the output of the following code snippet during a full-refresh run?

select case when {{ is_incremental() }} then 'Incremental' else 'Full Refresh' end as run_type

dbt

select case when {{ is_incremental() }} then 'Incremental' else 'Full Refresh' end as run_type

A Full Refresh

B NULL

C Incremental

D Syntax Error

💡 Hint

Think about what is_incremental() returns when the model is run with a full-refresh flag.

The is_incremental() macro returns true only when the model is run incrementally. During a full-refresh run, it returns false, so the output is 'Full Refresh'.

❓ data_output

intermediate

Rows processed in incremental vs full-refresh runs

A dbt incremental model uses is_incremental() to insert only new rows:

select * from source_table where updated_at > (select max(updated_at) from {{ this }})

What is the expected number of rows processed during a full-refresh run compared to an incremental run?

dbt

select * from source_table where updated_at > (select max(updated_at) from {{ this }})

A Full-refresh processes all rows; incremental processes only new rows

B Both full-refresh and incremental process all rows

C Full-refresh processes only new rows; incremental processes all rows

D Neither processes any rows

💡 Hint

Consider what data is available in the target table during each run type.

During a full-refresh, the target table is empty or replaced, so all rows are processed. During incremental runs, only rows with updated timestamps greater than the max in the target table are processed.

🔧 Debug

advanced

Why does this incremental model fail on full-refresh?

This dbt model uses is_incremental() to insert new data:

select * from source_table where {{ 'true' if is_incremental() else 'false' }}

When run with full-refresh, it fails with a syntax error. Why?

dbt

select * from source_table where {{ 'true' if is_incremental() else 'false' }}

A The macro returns NULL causing runtime error

B The macro <code>is_incremental()</code> is undefined during full-refresh

C The model tries to select from a non-existent table

D The else clause outputs 'false' without a valid condition, causing SQL syntax error

💡 Hint

Check what the SQL looks like after macro expansion during full-refresh.

The else clause outputs the string 'false' directly in the WHERE clause, which is invalid SQL syntax. It should output a valid condition like '1=1' or 'true'.

🚀 Application

advanced

Using is_incremental() to update only changed rows

You want to write a dbt incremental model that updates only rows changed since the last run. Which SQL snippet correctly uses is_incremental() to achieve this?

Options:

A select * from source_table where updated_at &gt; (select max(updated_at) from {{ this }}) and {{ is_incremental() }}

B {% if is_incremental() %}select * from source_table where updated_at > (select max(updated_at) from {{ this }}){% else %}select * from source_table{% endif %}

C select * from source_table where updated_at &gt; (select max(updated_at) from {{ this }}) or not {{ is_incremental() }}

D select * from source_table where updated_at &gt; (select max(updated_at) from {{ this }}) if not {{ is_incremental() }} else select * from source_table

💡 Hint

Think about how to conditionally run different queries based on incremental or full-refresh mode.

Option B correctly uses a Jinja if-else to run a filtered query during incremental runs and full data during full-refresh. Other options misuse the macro in SQL conditions.

🧠 Conceptual

expert

Understanding is_incremental() behavior in dbt runs

Which statement best describes the behavior of the is_incremental() macro in dbt models?

A <code>is_incremental()</code> returns true during both incremental and full-refresh runs

B <code>is_incremental()</code> returns false only when the model is run incrementally

C <code>is_incremental()</code> returns true only when the model is run incrementally, and false during full-refresh or initial runs

D <code>is_incremental()</code> returns true only during the first run of the model

💡 Hint

Recall the purpose of incremental models and how dbt distinguishes run types.

is_incremental() is designed to detect incremental runs only. It returns true during incremental runs and false during full-refresh or initial runs.