Creating dbt Models Using Table Materialization

Lab Steps

lock
Understanding Full-Refresh dbt Models
lock
Connecting to the dbt Web IDE
lock
Creating dbt Models Using Table Materialization
lock
Executing Full-Refresh dbt Models
Need help? Contact our support team

Here you can find the instructions for this specific Lab Step.

If you are ready for a real environment experience please start the Lab. Keep in mind that you'll need to start from the first step.

Introduction

dbt full-refresh models are very useful if you need to have always fresh records. dbt table materialization allows you to do so.

In this lab step, you will create two dbt models using the table materialization.

 

Instructions

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

alt

 

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

alt

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

 

3. Open the d_usa_customers.sql file you just created, and enter the following SQL snippets:

Copy code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
with usa_customers as (
    select
        now() as model_datetime,
        customer_id,
        company_name,
        contact_name,
        contact_title,
        city,
        country
    from {{ ref('stg_customers') }}
    where country = 'USA'
)

select *
from usa_customers

This model represents the customers based in the USA. As you can see, you didn't get all the fields available in the stg_customers model but just a few. Feel free to open the stg_ustomers.sql file to view the full table definition with all fields.

If you executed your model now, dbt would materialize it as a view because this is the default materialization type.

 

4. Enter the following code snippet above the SQL statements to tell dbt to materialize the d_usa_customers model as a table:

Copy code
{{
    config(
        materialized='table'
    )
}}

The config function is very important because it allows you to specify different configuration parameters related to the model you are building. By using it, you can specify parameters for the materialization, primary key, sort key, and distribution key. The materialized field allows you to specify a model materialization different from the default one.

To get deeper insights on the config dbt function, you can reference the official dbt docs.

The whole d_usa_customers.sql will look like this:

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

with usa_customers as (
    select
        now() as model_datetime,
        customer_id,
        company_name,
        contact_name,
        contact_title,
        city,
        country
    from {{ ref('stg_customers') }}
    where country = 'USA'
)

select *
from usa_customers

 

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

 

6. Open the d_usa_suppliers.sql file you just created, and enter 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
{{
    config(
        materialized='table'
    )
}}

with usa_suppliers as (
    select
        now() as model_datetime,
        supplier_id,
        company_name,
        contact_name,
        contact_title,
        country
    from {{ ref('stg_suppliers') }}
    where country = 'USA'
)

select *
from usa_suppliers

This model represents the suppliers based in the USA. As you can see, you didn't get all the fields available in the stg_suppliers model but just a few.

 

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

alt

 

Summary

In this lab step, you created two dbt models using the table materialization.

Validation checks
1Checks
Created the Full-Refresh dbt Models

Check whether the full-refresh dbt models have been created.

Data build tool (dbt)