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.

If you’d like to dive deeper into dbt (data build tool), our book Building Modern Data Pipelines with dbt: From Raw Data to Gold Standard with the Medallion Architecture provides a hands-on guide to designing modern data pipelines. It covers dbt’s core concepts and best practices, including building Bronze, Silver, and Gold layers with the Medallion Architecture. It also serves as a hands-on study guide for the dbt Analytics Engineering Certification.

View on Amazon

💻 Practical Example for CTEs and Subqueries

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

🟡 Using Subqueries