Build your first SQL model in dbt: A Step-by-Step Tutorial

📘Introduction

If you're new to dbt (data build tool) and want to transform raw data into clean, analytics-ready tables — you're in the right place. In this step-by-step tutorial, we’ll walk you through how to build your first SQL model in dbt, step by step.

📌 This is a must-know topic for the dbt Analytics Engineering Certification Exam, so mastering it now puts you one step closer to passing the exam and leveling up your data engineering skills! 👨‍🎓

✅ Prerequisites

Before you start, make sure you have:

☑️ A dbt project set up
☑️ Set up Medallion Architecture
☑️ Source data loaded into your data warehouse
☑️ Source configurations defined in sources.yml

💡What are SQL Models?

In dbt, a SQL model is simply a .sql file that contains a SELECT statement. When you run dbt run, dbt executes the SQL and materializes the results into your data warehouse.

Models can be for example views, tables, or incremental models, depending on the materialization defined in dbt_project.ymlor in model-level configurations.

✍️1️⃣ Specify Requirements

Let’s define the objective of our model:

We want to create a cleaned version of the student table from the source udc. This source table exists in the schema landing in our data warehouse.

The table student contains the following data:

Here’s what we want our model to do:

✅ Select the columns: ID, Name, Major, Number
✅ Rename all column names to lowercase
✅ Add a new column named source_name with a hardcoded value of 'udc'

📁2️⃣ Create SQL model

In your dbt project, navigate to the models folder. Create a new .sql file in the appropriate layer. In our example we create a file named cleaned_student.sql within the folder 02_bronze.