is_incremental() macro in dbt - Mini Project: Build & Apply

Using the is_incremental() Macro in dbt

📖 Scenario: You are working on a data transformation project using dbt. You want to create a model that only adds new data when the model runs incrementally, instead of rebuilding the entire table every time.

🎯 Goal: Build a dbt model SQL file that uses the is_incremental() macro to load only new records when running incrementally.

📋 What You'll Learn

Create a source table called raw_sales with columns sale_id and sale_date

Create a dbt model SQL file that selects all records from raw_sales

Use the is_incremental() macro to filter only new records when running incrementally

Print the final SQL query that will run in dbt

💡 Why This Matters

🌍 Real World

Incremental models in dbt help process only new or changed data, saving time and computing resources in data pipelines.

💼 Career

Understanding how to use the is_incremental() macro is essential for data engineers and analysts working with dbt to build efficient data transformations.

1

Create the source table data

Create a table called raw_sales with these exact rows: (1, '2024-01-01'), (2, '2024-01-02'), (3, '2024-01-03').

dbt

# Create the raw_sales table with sale_id and sale_date
# Your code here

Hint

Use CREATE TABLE and INSERT INTO statements with exact values.

2

Set the incremental filter date

Create a variable called last_loaded_date and set it to '2024-01-01' to represent the last date loaded in the incremental model.

dbt

CREATE TABLE raw_sales (sale_id INT, sale_date DATE);
INSERT INTO raw_sales VALUES (1, '2024-01-01'), (2, '2024-01-02'), (3, '2024-01-03');

-- Define last_loaded_date variable
-- Your code here

Hint

Use dbt Jinja syntax to set the variable.

3

Write the incremental model SQL using is_incremental()

Write a dbt model SQL query that selects all columns from raw_sales. Use if is_incremental() to filter rows where sale_date > last_loaded_date only when running incrementally.

dbt

CREATE TABLE raw_sales (sale_id INT, sale_date DATE);
INSERT INTO raw_sales VALUES (1, '2024-01-01'), (2, '2024-01-02'), (3, '2024-01-03');

{% set last_loaded_date = '2024-01-01' %}

-- Write the SELECT query using is_incremental()
-- Your code here

Hint

Use Jinja {% if is_incremental() %} block to add the WHERE clause only for incremental runs.

4

Print the final SQL query

Print the full SQL query string that includes the SELECT statement and the is_incremental() filter.

dbt

CREATE TABLE raw_sales (sale_id INT, sale_date DATE);
INSERT INTO raw_sales VALUES (1, '2024-01-01'), (2, '2024-01-02'), (3, '2024-01-03');

{% set last_loaded_date = '2024-01-01' %}

SELECT * FROM raw_sales
{% if is_incremental() %}
WHERE sale_date > '{{ last_loaded_date }}'
{% endif %}

-- Print the final SQL query
# Your code here

Hint

Print the SQL query string that includes the incremental filter.

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