CTEs vs Subqueries in dbt: Why CTEs make your SQL cleaner

📘Introduction

In this hands-on dbt tutorial, we’ll walk you step-by-step through how to make your SQL cleaner and more maintainable by using CTEs (Common Table Expressions) instead of deeply nested subqueries. 

✅ Prerequisites

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

🟡 What are Subqueries?

A subquery is a SQL query nested inside another query, usually in the FROM or WHERE clause. Subqueries allow you to perform intermediate calculations or filtering before the main query executes.

🔵 What are CTEs (Common Table Expressions)?

A CTE is a temporary result set defined using the WITH clause. CTEs let you break a complex query into logical steps, making your SQL easier to read and maintain.

💻 Practical Example for CTEs and Subqueries

In the following example, we’ll transform student data using both approaches.

🟡 Using Subqueries