Common table expression (CTE)
Also: CTE · WITH clause
A named, temporary result set defined with a WITH clause that you reference later in the same query — the primary tool for writing readable, layered, reviewable SQL.
A CTE lets you name a subquery and reference it by that name, instead of nesting subqueries inside subqueries. You define it with `WITH name AS (...)` at the top of the query and then select from `name` below. You can chain several, each building on the last.
For analytics engineers, CTEs are mostly about readability and reviewability. A 200-line transformation written as a chain of well-named CTEs reads top-to-bottom like steps in a recipe; the same logic as nested subqueries is unreviewable. dbt models lean heavily on this style.
Modern warehouses optimize CTEs well, so the old advice to avoid them for performance rarely applies. Write for the human reading the pull request.
with paid_orders as (
select * from orders where status = 'paid'
),
customer_totals as (
select customer_id, sum(amount) as lifetime_value
from paid_orders
group by 1
)
select * from customer_totals where lifetime_value > 1000Readable SQL is reviewable SQL, and analytics engineering runs on code review. CTEs are the single biggest lever for turning unreadable nested queries into something a teammate can actually check.
They're also the backbone of how dbt models are structured — import CTEs at the top, logical CTEs in the middle, a final select at the bottom.
- Avoiding CTEs for imagined performance reasons on modern warehouses, producing unreadable nested subqueries instead.
- Writing one giant CTE that does everything instead of a chain of small, named steps.
- Are CTEs slower than subqueries?
- On modern cloud warehouses, generally no — they're optimized comparably. Prefer CTEs for readability; reach for alternatives only if profiling shows a specific problem.
Learn this by building, not memorizing.
Definitions get you the vocabulary. The platform gets you the skill — graded exercises, real projects, and a portfolio capstone.