Using CTEs in your dbt models to ensure modularity and follow DRY principles

📘 Introduction

In this hands-on dbt tutorial, we'll walk you through how to use Common Table Expressions (CTEs) effectively in dbt models - an essential concept for modular, readable, and testable SQL.

📌 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 CTEs?

A Common Table Expression (CTE) is a SQL feature that allows you to structure your queries using temporary, named result sets defined with the WITH clause. 

Splitting your SQL query into multiple CTEs helps organize logic into clear, reusable steps. CTEs break down complex transformations into smaller, readable chunks that are easier to debug, test, and maintain. By using CTEs, you write cleaner, modular code that aligns with dbt’s best practices for building maintainable and testable analytics workflows.

💭1️⃣ Specify Data Requirement

We have already created two SQL models in the silver layer named transformed_course and transformed_course_mapping.

The table transformed_course contains the following data:

The table transformed_course_mapping contains the following data:

We want to create a new model dim_courses in the gold layer that that combines and enriches data from upstream sources. It should include all columns from transformed_course and add the name column from transformed_course_mapping.

To do this, we’ll perform a left join on the code column, ensuring each course record is matched with its corresponding name.

🗂️2️⃣ Create a new dbt model

In your models/gold/ directory, create a new file:dim_course.sql:

⚙️3️⃣ Use CTEs in your dbt model

Let’s implement the logic step-by-step using CTEs, building a clean pipeline: