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

Need a 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

Need a 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

Need a 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

Need a hint?

Print the SQL query string that includes the incremental filter.