In this lesson, we’ll explore dbt models, a core component of dbt that enables efficient and scalable data transformations. You’ll apply these concepts to the model: stg_orders.sql, gaining hands-on experience in structuring and materializing models in dbt.

By the end of this lesson, you’ll have a solid understanding of how dbt models work and how to implement them effectively in your projects.

What Is a dbt Model?

A dbt model is a SQL file stored in the models/ directory that describes how to transform your raw data into a cleaner, more useful structure. Most models contain a simple SELECT query that filters, cleans, or aggregates data according to business needs.

While dbt primarily supports SQL-based transformations, Python-based transformations are also possible but require specific database adapters.

Steps to create a model

Now that we understand what a dbt model is, let’s walk through the process of creating one.

1. Create the Model File

  • Ensure your dbt project has a models/ directory (if it doesn’t exist, create one).
  • Inside the models/ directory, add a new .sql file.
  • Write the SQL query defining your model.

2. Configure the Materialization Strategy

  • Choose between view, table, incremental, or ephemeral materialization.
  • Define this strategy in your dbt_project.yml file or within the model file using the config block.

Materialization will be explored in the next lesson. 

3. Run the Model

Execute the model using dbt run command.

Creating a Model

Let’s create a model named stg_orders.sql.

Step 1: Define the Model File

Create a file named models/stg_orders.sql and insert the following SQL statement:

 

The SQL statement bellow retrieve all completed orders from a table demo.orders

stg_orders.sql 
{{ config(materialized='table') }} 

select
  cast(order_id as bigint)      as order_id,
  cast(customer_id as bigint)   as customer_id,
  cast(order_date as date)      as order_date,
  cast(total_amount as numeric) as total_amount,
  lower(status)                 as status
from {{ source('dev', 'orders') }}

Step 2: Understanding Model Naming Conventions

  • The SQL filename determines the table or view name in the database.
  • Avoid using dots (.) in filenames, as some database environments may not support them. Instead, use underscores (_).
  • Correct format: models/stg_orders.sql
  • Avoid: models/stg.orders.sql 

Step 3: Run the Model

Execute the following command to materialize the model:

dbt run

The command above, running the command, dbt compiles and materializes the stg_orders model as a table in the database.

 

Model stg_orders
Model stg_orders

Conclusion

In this lesson, you learned:

  • What a dbt model is and how it transforms raw data
  • The steps to create and configure a model
  • How to run and materialize models effectively

dbt models are the foundation of data transformation workflows. Mastering them will help you build scalable, efficient, and maintainable data pipelines, optimizing performance, storage, and data freshness in your warehouse.

PreviousNext