Creating dbt Models Using Incremental Materialization

Introduction

dbt incremental models are essential when you need to perform append operations and only add new records in your dbt models.

In this lab, you will create two incremental dbt models following the two main strategies in order to do so.

Instructions

1. Click on the Explore icon to reach the folder containing the dbt project files under the PROJECT drop-down:

2. Create a file named f_orders.sql in the models folder by right-clicking on the folder name, and clicking on New File:

Note: You put the f_ just to clarify this model will be a fact according to the dimensional model.

3. Open the f_orders.sql file you just created, and enter the following SQL snippet:

Copy code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
with orders as (
    select
        order_id,
        customer_id,
        employee_id,
        order_date,
        required_date,
        shipped_date,
        ship_via,
        freight
    from {{ ref('stg_orders' ) }}
)

select *
from orders

The fact table represents all the orders made.

4. Insert the following snippet at the beginning of the f_orders.sql file:

Copy code
{{
    config(
        materialized='incremental',
        unique_key='order_id'
    )
}}

You have declared this model as an incremental one, and you defined order_id as the primary key of the model. This way, dbt will leverage it to understand whether a record needs to be added or not.

The whole f_orders.sql file should look like the following:

Copy code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
{{
    config(
        materialized='incremental',
        unique_key='order_id'
    )
}}

with orders as (
    select
        order_id,
        customer_id,
        employee_id,
        order_date,
        required_date,
        shipped_date,
        ship_via,
        freight
    from {{ ref('stg_orders' ) }}
)

select *
from orders

5. Create a file named f_orders_is_incremental.sql in the models folder by right-clicking on the folder name, and clicking on New File.

6. Open the f_orders_is_incremental.sql file and insert the following SQL snippets:

Copy code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
{{
    config(
        materialized='incremental'
    )
}}

with orders_incremental as (
    select
        order_id,
        customer_id,
        employee_id,
        order_date,
        required_date,
        shipped_date,
        ship_via,
        freight
    from {{ ref('stg_orders' ) }}

    {% if is_incremental() %}
        where order_date > ( select max(order_date) from {{ this }} )
    {% endif %}
)

select *
from orders_incremental

The {{ this }} is a Jinja snippet that references the model you are currently working on.

This model represents the same as the previous one, but it doesn't use the primary key in order to understand whether a record needs to be added or not; otherwise, it uses a condition on the order_date that is executed only if the incrementality is enabled.

7. Save the content of both the files by clicking on File -> Save All:

Summary

In this lab, you created two incremental dbt models following the two main strategies in order to do so.