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.

Practice

1. What does the is_incremental() macro in dbt indicate when used inside a model?

easy

A. If the model has any syntax errors

B. Whether the model is running in incremental mode or full-refresh mode

C. The total number of rows in the model

D. The database connection status

Solution

1. Step 1: Understand the purpose of is_incremental()

   This macro returns true if the model is running incrementally, false if running a full-refresh.

2. Step 2: Identify what it indicates in the model

   It tells the model whether to add only new data or rebuild fully.

3. Final Answer:

   Whether the model is running in incremental mode or full-refresh mode -> Option B

4. Quick Check:

   is_incremental() = incremental mode check [OK]

Hint: Remember: is_incremental() checks incremental run status [OK]

Common Mistakes:

• Thinking it checks for errors
• Confusing it with row count
• Assuming it checks connection status

2. Which of the following is the correct way to use is_incremental() in a dbt model SQL file?

easy

A. WHERE is_incremental = true

B. SELECT * FROM table WHERE is_incremental() = true

C. {% if is_incremental() %} WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }}) {% endif %}

D. SELECT * FROM table WHERE incremental = is_incremental()

Solution

1. Step 1: Recognize correct Jinja syntax for is_incremental()

   It must be used inside a Jinja if statement with {% %} tags.

2. Step 2: Check the options for correct usage

   {% if is_incremental() %} WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }}) {% endif %} uses {% if is_incremental() %} correctly to conditionally add a WHERE clause.

3. Final Answer:

   {% if is_incremental() %} WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }}) {% endif %} -> Option C

4. Quick Check:

   Use Jinja {% if is_incremental() %} for conditional SQL [OK]

Hint: Use {% if is_incremental() %} to add incremental filters [OK]

Common Mistakes:

• Using is_incremental() directly in SQL WHERE clause
• Missing Jinja tags {% %}
• Using is_incremental as a column or variable

3. Given the following dbt model snippet, what rows will be selected when the model runs incrementally?

SELECT * FROM source_table
{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

medium

A. Only rows with updated_at greater than the max in the existing model

B. Only rows with updated_at less than the max in the existing model

C. No rows will be selected

D. All rows from source_table every time

Solution

1. Step 1: Understand the condition inside the incremental block

   When running incrementally, the WHERE clause filters rows with updated_at greater than the max in the current model.

2. Step 2: Determine which rows are selected

   This means only new or updated rows after the last run are selected.

3. Final Answer:

   Only rows with updated_at greater than the max in the existing model -> Option A

4. Quick Check:

   Incremental filter selects newer rows [OK]

Hint: Incremental runs add only newer rows based on max timestamp [OK]

Common Mistakes:

• Thinking all rows are selected every time
• Confusing greater than with less than
• Ignoring the existing model's max timestamp

4. You wrote this dbt model code but it fails to run incrementally:

SELECT * FROM source_table
{% if is_incremental %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

What is the error?

medium

A. Using {{ }} instead of {% %} for if statement

B. Incorrect SQL syntax in WHERE clause

C. No error, code is correct

D. Missing parentheses after is_incremental macro call

Solution

1. Step 1: Check the usage of is_incremental

   The macro must be called with parentheses: is_incremental().

2. Step 2: Identify the error in the if statement

   The code uses is_incremental without parentheses, causing a syntax error.

3. Final Answer:

   Missing parentheses after is_incremental macro call -> Option D

4. Quick Check:

   Always call is_incremental() with parentheses [OK]

Hint: Remember: is_incremental() needs parentheses [OK]

Common Mistakes:

• Forgetting parentheses on macro calls
• Confusing {{ }} and {% %} tags
• Assuming macro is a variable

5. You want to create a dbt model that updates only new rows incrementally but also handles the case when the model is run for the first time (empty table). Which code snippet correctly uses is_incremental() to achieve this?

hard

A. SELECT * FROM source_table
{% if is_incremental() %}
WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% else %}
-- full refresh: select all rows
{% endif %}

B. SELECT * FROM source_table
WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})

C. {% if is_incremental() %}
SELECT * FROM source_table
{% else %}
SELECT * FROM source_table WHERE updated_at < NOW()
{% endif %}

D. SELECT * FROM source_table
{% if is_incremental() %}
WHERE updated_at < (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

Solution

1. Step 1: Understand incremental and full-refresh logic

   When running incrementally, filter new rows; on full-refresh, select all rows.

2. Step 2: Check which option handles both cases correctly

   SELECT * FROM source_table {% if is_incremental() %} WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }}) {% else %} -- full refresh: select all rows {% endif %} uses is_incremental() with an else block for full-refresh selecting all rows.

3. Final Answer:

   SELECT * FROM source_table {% if is_incremental() %} WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }}) {% else %} -- full refresh: select all rows {% endif %} -> Option A

4. Quick Check:

   Use is_incremental() with else for full-refresh [OK]

Hint: Use else block with is_incremental() for full-refresh [OK]

Common Mistakes:

• Not handling full-refresh case
• Using wrong comparison operators
• Filtering incorrectly on first run