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 two models: completed_orders and not_completed_orders, 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 defines transformations applied to raw datasets. Typically, a model contains a SELECT query that structures, cleans, or aggregates data based on specific business logic.

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 completed_orders.

Step 1: Define the Model File

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

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

SELECT
   order_id,
   customer_id,
   order_date,
   total_amount
FROM demo.orders
WHERE status = 'Completed'

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/completed_orders.sql
• Avoid: models/completed.orders.sql 

Step 3: Run the Model

Execute the following command to materialize the model:

dbt run

After running the command, dbt compiles and materializes the completed_orders model as a view in the database.

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.