Databricks Common Table Expressions (CTEs): A Simple Guide

If you read our recent guide on Databricks SQL subq u eries, you know that nesting queries inside one another can get messy very quickly. If you nest them three or four levels deep, your code becomes incredibly hard to read and debug.

This is where Databricks Common Table Expressions (CTEs) save the day.

CTEs are the secret to writing clean, professional, and easily maintainable SQL. In this guide, we will look at what CTEs are, why you should use them over subqueries, and how to write them in Databricks.

Page Content

What is a Common Table Expression (CTE)?

Why Use CTEs Instead of Subqueries?

A Basic Databricks CTE

CTEs vs. Temporary Views

Conclusion

What is a Common Table Expression (CTE)?

A CTE is essentially a temporary, named result set that you create right before your main query. You define it using the WITH clause.

Unlike a Databricks temporary table (which lasts for your entire session), a CTE only exists for the duration of that single query. As soon as the query finishes running, the CTE vanishes.

Think of a CTE as a way to define your variables at the top of a script before you actually write the main logic.

Why Use CTEs Instead of Subqueries?

While subqueries and CTEs often achieve the exact same result, data engineers generally prefer CTEs for a few big reasons:

Top-Down Readability: Subqueries force you to read code from the inside out. CTEs allow you to read code logically from top to bottom.
Reusability: If you need to use the exact same intermediate data twice in one query, a subquery forces you to run it twice. A CTE lets you define it once and reference it as many times as you want.
Easier Debugging: If a massive query is broken, you can easily highlight and run individual CTE blocks to find out exactly where the data went wrong.

A Basic Databricks CTE

Example 1: A Basic Databricks CTE

Creating a CTE is simple. You start with the WITH keyword, give your CTE a name, and wrap your query in parentheses.

Here is a simple example where we filter for high-value customers before joining them to an orders table:

WITH high_value_customers AS (
    SELECT customer_id, customer_name 
    FROM customers 
    WHERE lifetime_spend > 10000
)

SELECT 
    hvc.customer_name, 
    o.order_date, 
    o.total_amount
FROM high_value_customers hvc
JOIN orders o 
  ON hvc.customer_id = o.customer_id;

Example 2: Chaining Multiple CTEs Together

The real power of Databricks Common Table Expressions (CTEs) unlocks when you need multiple steps. You can chain as many CTEs as you need by simply separating them with a comma.

Even better, a later CTE can actually reference an earlier CTE!

Here is how you chain them:

WITH regional_sales AS (
    SELECT region_id, SUM(sales_amount) as total_sales
    FROM sales
    GROUP BY region_id
),

top_regions AS (
    -- Notice how this CTE queries the CTE we just made above!
    SELECT region_id 
    FROM regional_sales 
    WHERE total_sales > 500000
)

SELECT 
    r.region_name, 
    tr.region_id
FROM top_regions tr
JOIN region_dimensions r 
  ON tr.region_id = r.region_id;

CTEs vs. Temporary Views

It is easy to get these confused, so here is a quick rule of thumb:

Use a CTE if you only need the intermediate data for one specific query.
Use a Temporary Views if you need to query that intermediate data multiple times across different queries in your current session.

Conclusion

If you want to write SQL that your fellow data engineers will actually enjoy reading, start using Databricks Common Table Expressions (CTEs). They turn massive, complicated data transformations into easy-to-follow, step-by-step logic.