is_incremental() macro in dbt - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the is_incremental() macro do in dbt?

The is_incremental() macro checks if the current dbt run is an incremental run, meaning it only processes new or changed data instead of rebuilding the entire table.

beginner

Why use is_incremental() in a dbt model?

You use is_incremental() to write logic that runs only during incremental runs, allowing you to add new data without rebuilding the whole dataset, saving time and resources.

intermediate

Example usage of is_incremental() in a dbt SQL model?

Inside your model SQL, you can write:

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

This means only new or updated rows are added during incremental runs.

beginner

What happens if you don't use is_incremental() in an incremental model?

If you don't use is_incremental(), your model will rebuild the entire table every time, losing the benefits of incremental loading and increasing run time.

intermediate

Can is_incremental() be used outside incremental models?

No, is_incremental() returns false in full-refresh or non-incremental runs, so its logic only applies when the model is set to incremental mode.

What does is_incremental() return during a full-refresh run?

A True

B Depends on the model

C An error

D False

is_incremental() returns false during full-refresh runs because the model rebuilds fully, not incrementally.

Why is is_incremental() useful in dbt models?

A To check if the model is running in incremental mode

B To speed up full-refresh runs

C To delete old data automatically

D To create new tables

is_incremental() helps you write code that runs only during incremental loads, improving efficiency.

Which of these is a common use of is_incremental() in SQL?

A Dropping tables before loading

B Filtering rows to only new or updated data

C Creating indexes

D Changing column types

You use is_incremental() to filter data so only new or changed rows are processed during incremental runs.

If you want your model to always rebuild fully, what should is_incremental() return?

A Null

B True

C False

D Depends on the data

During full rebuilds, is_incremental() returns false, meaning incremental logic is skipped.

What happens if you use incremental logic without checking is_incremental()?

A The model may miss data or error

B The model creates duplicate tables

C The model runs faster

D Nothing changes

Without checking is_incremental(), incremental logic might run during full-refresh causing errors or missing data.

Explain what the is_incremental() macro does and why it is important in dbt incremental models.

Think about how incremental models avoid rebuilding everything.

Describe a simple example of how you would use is_incremental() in a dbt model SQL file.

Consider filtering data to only new rows during incremental loads.

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