is_incremental() macro in dbt - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to check if the model is running incrementally.

dbt

SELECT * FROM {{ source('my_schema', 'my_table') }} WHERE [1]

Drag options to blanks, or click blank then click option'

Ais_full_refresh()

Bis_incremental()

Cis_snapshot()

Dis_table()

Attempts:

3 left

💡 Hint

Common Mistakes

Using is_full_refresh() instead, which checks for full refresh runs.

Confusing is_snapshot() with incremental runs.

The is_incremental() macro returns true when the model runs incrementally.

2fill in blank

medium

Complete the code to add a filter only when running incrementally.

dbt

SELECT * FROM {{ ref('my_model') }} WHERE [1] AND updated_at > (SELECT MAX(updated_at) FROM {{ this }})

Drag options to blanks, or click blank then click option'

Ais_full_refresh()

Bis_snapshot()

Cis_table()

Dis_incremental()

Attempts:

3 left

💡 Hint

Common Mistakes

Using is_full_refresh() which is true during full refreshes, not incremental runs.

The filter should apply only during incremental runs, so is_incremental() is used.

3fill in blank

hard

Fix the error in the code to correctly check for incremental runs.

dbt

{% if [1] %} SELECT * FROM source_table {% else %} SELECT * FROM full_table {% endif %}

Drag options to blanks, or click blank then click option'

Ais_incremental()

Bis_incremental

Dis_incremental()()

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting parentheses after the macro name.

Adding extra parentheses causing syntax errors.

The macro is_incremental() must be called with parentheses to execute properly.

4fill in blank

hard

Fill both blanks to add a condition that runs only during incremental loads and filters new records.

dbt

SELECT * FROM {{ ref('orders') }} WHERE {% if [1] %} order_date > (SELECT MAX(order_date) FROM [2]) {% else %} TRUE {% endif %}

Drag options to blanks, or click blank then click option'

Ais_incremental()

Bis_full_refresh()

Corders

Dref('orders')

Attempts:

3 left

💡 Hint

Common Mistakes

Using is_full_refresh() instead of is_incremental().

Using plain table name instead of ref() macro.

Use is_incremental() to check incremental run and ref('orders') to refer to the current model.

5fill in blank

hard

Fill all three blanks to create an incremental model that inserts only new records based on id and updated_at.

dbt

WITH new_data AS ( SELECT * FROM {{ ref('source_data') }} ), filtered_data AS ( SELECT * FROM new_data WHERE {% if [1] %} updated_at > ( SELECT MAX(updated_at) FROM [2] ) {% else %} TRUE {% endif %} ) SELECT * FROM [3]

Drag options to blanks, or click blank then click option'

Ais_incremental()

Bthis

Cfiltered_data

Dref('source_data')

Attempts:

3 left

💡 Hint

Common Mistakes

Using ref('source_data') instead of this for existing data.

Selecting from new_data instead of filtered_data.

Use is_incremental() to check incremental run, this to refer to the current model table, and select from filtered_data to output new records.

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