SQL vs dbt Models (& the value of CTEs)

Writing SQL that returns the right answer and designing a well structured dbt model are two different skills, and the gap between them shows up most clearly in how a team uses common table expressions. A CTE is a named subquery declared with with at the top of a query that later parts of the query can reference by name. A well structured dbt model opens with one CTE per upstream model, each a plain select * from a ref, does its real work in one or more logic CTEs in the middle, gathers everything into a CTE called final, and ends with select * from final. That looks wasteful if you come from a traditional database background, but modern cloud warehouses treat those import CTEs as passthroughs with no performance cost. What you get in return is a model that reads like a Python file with its imports at the top, and a query you can debug by changing one line instead of commenting out half the file.

Why CTEs are a tell

A lot of what separates a SQL script from a good dbt model is subtle. No single habit makes or breaks a project, but a handful of them combined have a big effect on whether the project stays maintainable.

The use, or absence, of CTEs is the one I notice first when I open a team's repository.

What the habit signals

It's not because CTEs make queries dramatically faster. They don't, and that's the point. Using them the dbt way signals that the team understands how a cloud warehouse actually executes a query, and how dbt is built to take advantage of that.

A team that writes every model as one long nested subquery, or that avoids select * on principle, is usually carrying habits over from a database that no longer applies.

CTEs are passthroughs

If you open a typical dbt model, the top looks like a wall of select * statements wrapped in CTEs. Coming from a traditional background that looks wild. Why would you select every column from three tables when you only need four of them?

What the research found

Tristan Handy from dbt Labs published a set of findings on the dbt discourse that answer this directly. The premise is that CTEs are passthroughs, and the query optimizers in modern cloud platforms are smart enough not to materialize all of those columns.

Three things came out of that work:

• A select * inside a CTE is a reference, not a copy. The optimizer treats it as a pointer to the upstream relation, and any column that is never selected at a later point in the query is never read at all.
• The pattern holds at depth. In his testing it held even with a chain of around ten CTEs before an aggregation finally happened.
• The summary is blunt. All modern analytical database optimizers treat import CTEs as passthroughs, and they have no impact on performance.

The cost you'd expect isn't there. That finding is why the pattern is everywhere in dbt projects, and why the dbt style guide recommends it.

Creating imports

Think of it like Python imports. At the top of a Python file you import the modules you depend on. You don't think about it loading every function in every file; you reference what you need and the rest is inert.

Import CTEs in a dbt model work the same way. Each one is a single select * from {{ ref('some_model') }}, and that one line does three things at once:

• Declares the dependency. A ref is a pointer to another model in the project, and dbt uses those refs to build the dependency graph and run models in the right order.
• Gives you a clean alias. Instead of a long, schema qualified table name throughout the query, you reference customers or orders.
• Keeps the final join clean. By the time you get to the join, every input is already a tidy named relation and there's nothing else going on in that part of the query.

The structure of a well formed model

Put together, a model built this way has a recognizable shape:

• Imports at the top, one CTE per upstream model.
• Logic CTEs in the middle, each doing a specific unit of work.
• A final CTE that handles the joins and selects exactly the columns the model should expose.
• select * from final at the very bottom.

A worked example

Here's a representative model that builds a customer summary from staged customers and orders.

with customers as (

    select * from {{ ref('stg_customers') }}

),

orders as (

    select * from {{ ref('stg_orders') }}

),

customer_orders as (

    select
        customer_id,
        min(order_date)  as first_order_date,
        max(order_date)  as most_recent_order_date,
        count(order_id)  as number_of_orders
    from orders
    group by 1

),

final as (

    select
        customers.customer_id,
        customers.first_name,
        customers.last_name,
        customer_orders.first_order_date,
        customer_orders.most_recent_order_date,
        coalesce(customer_orders.number_of_orders, 0) as number_of_orders
    from customers
    left join customer_orders
        on customers.customer_id = customer_orders.customer_id

)

select * from final

Reading it top to bottom:

• The two import CTEs pull in the staging models.
• customer_orders is the unit of work, an aggregation of orders per customer.
• final joins the aggregation back to the customer list and names the output columns.
• The last line exposes it.

The question people ask about that last line is whether it's redundant. You've already built final, so why not make it the outer query and be done? The answer is the next section.

Easier debugging

The closing select * from final exists to make your debugging life easier. Because CTEs are passthroughs, the warehouse only does work for the CTE you ultimately select from.

You change one word. When something in the model looks wrong, you don't comment out the join, or the final column list, or any other part of the file. You edit the last line.

select * from customer_orders

Run the model with that as the last line and you get the output of the aggregation step and nothing else. The final CTE is still declared, but it isn't executed, because nothing references it.

Confirm the numbers look right, switch the last line back to final, and move on.

Where it pays off

The example above is simple enough that this might not seem like much. Picture a model with eight logic CTEs where the error is somewhere in the middle.

Stepping through the query one CTE at a time, by editing the last line only, is the difference between finding the bug in two minutes and spending half an hour commenting and uncommenting blocks until the query parses again.

In dbt you can pair this with dbt show or a preview in your editor and work through a model from top to bottom without ever leaving the file.

The less technical benefit

If you want to look like someone who has worked in dbt before, write your models this way.

It matches the style guide, it's what experienced reviewers expect to see, and it stands out against code from people who are winging it.

Related reading

• A Simple 4-Step Process for Creating dbt Models
• What Are Intermediate Models in dbt?
• How to Run Queries from the Terminal (dbt Show Command)
• dbt vs Stored Procedures (3 Key Differences)

Final takeaway

This is one of the first things I look at when I review a client's dbt project, because it tells me quickly whether the team is writing dbt models or just storing SQL scripts in a dbt folder. Teams that adopt the pattern tend to find their own bugs faster and onboard new people with less friction.