Creating dbt Models Using Ephemeral Materialization

Introduction

Dbt ephemeral models are useful when you need to define intermediate models that can be leveraged by others, without the need to store them in the database.

In this lab step, you will create an ephemeral model, and you will then create a full-refresh model based on the ephemeral one.

Instructions

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

2. Create a folder named intermediate into the models folder by right-clicking on the folder name, and clicking on New Folder:

3. Create a file named int_more_two_orders.sql into the intermediate folder by right-clicking on the folder name, and clicking on New File.

Note: The int_ prefix stands for intermediate. Because most of the ephemeral models represent intermediate logic, you should use this prefix.

4. Open the int_more_two_orders.sql file and insert the following SQL snippet:

Copy code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
with int_more_two_orders as (
    select
        orders.order_id,
        orders.order_date
    from {{ ref('stg_orders') }} as orders
        inner join {{ ref('stg_order_details') }} as order_details
        on orders.order_id = order_details.order_id
    group by
        orders.order_id,
        orders.order_date
    having count(*) > 2
)
select *
from int_more_two_orders

The ephemeral model represents the information about orders that have at least two different products.

5. Insert the following snippet at the beginning of the int_more_two_orders.sql file:

Copy code
1
2
3
4
5
{{
    config(
        materialized='ephemeral'
    )
}}

You have declared this model as an ephemeral one. This way, dbt will handle it as a CTE (Common Table Expression) and won't store it in the database.

The whole int_more_two_orders.sql file will 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
{{
    config(
        materialized='ephemeral'
    )
}}
with int_more_two_orders as (
    select
        orders.order_id,
        orders.order_date
    from {{ ref('stg_orders') }} as orders
        inner join {{ ref('stg_order_details') }} as order_details
        on orders.order_id = order_details.order_id
    group by
        orders.order_id,
        orders.order_date
    having count(*) > 2
)
select *
from int_more_two_orders

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

7.  Open the f_orders_avg_discount_yearly.sql file and insert the following SQL snippet:

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
26
27
{{
    config(
        materialized='table'
    )
}}
with more_two_orders_avg_discount as (
    select
        more_two_orders.order_id,
        more_two_orders.order_date,
        avg(order_details.discount) as avg_order_discount
    from {{ ref('int_more_two_orders') }} as more_two_orders
        inner join {{ ref('stg_order_details') }} as order_details
        on more_two_orders.order_id = order_details.order_id
    group by
        more_two_orders.order_id,
        more_two_orders.order_date
),
more_two_orders_yearly_avg_discount as (
    select
        extract(year from order_date) as year,
        avg(avg_order_discount) * 100 as yearly_discount_average
    from more_two_orders_avg_discount
    group by
        extract(year from order_date)
)
select *
from more_two_orders_yearly_avg_discount

This model calculates, for each year, the average discount taken off of orders containing at least two different products.

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

Summary

In this lab step, you created an ephemeral model, and you then created a full-refresh model based on the ephemeral one.