#041: A simple 4-step process for creating dbt models

The four step process for a dbt model is: import every source or upstream model you need as its own CTE at the top of the file, write your custom transformations in one or more CTEs below that, gather the columns you want to return in a CTE named final, and close with a single select * from final. The imports idea is borrowed from Python, where the modules a script depends on sit at the top where anyone can see them. Following the same four steps in every model gives the whole project one shape, which makes development faster, code review quicker and debugging easier because you can point that last select at any CTE to inspect it. A select star inside a CTE costs nothing on a modern warehouse, since the query engine only pulls the columns the final select actually uses. It looks redundant on a simple model and pays for itself on every model after that.

Step 1: Imports

Every dbt model starts as a blank SQL file, and what you do in the first few lines shapes how the project turns out. The first thing I add is an imports section.

The name comes from Python, where you import the modules you depend on at the top of the script before writing any logic. In a dbt model the dependencies aren't modules, they're sources and other models, and each one gets its own CTE.

CTEs and select star

A CTE, or common table expression, is a named subquery defined with with that the rest of the query can refer to by name. Throughout this process we lean on CTEs and on select star.

Modern warehouses are smart enough to pull only the columns a query ends up using, so a select * inside a CTE is not a performance problem. The engine prunes what it doesn't need.

with customers as (

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

),

orders as (

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

),

If the model reads from a raw table rather than another model, the import uses source() instead of ref(). The shape is identical: one CTE, one select star, one alias.

Why the imports section earns its place

This section earns its place for three reasons:

• Visibility. Anyone opening the file can see at a glance what data the model uses without reading the whole query.
• Cleaner joins. You alias each input once, up here, and every join below uses that short name.
• A correct DAG. Every dependency goes through ref or source, so if a model is used anywhere in the file, dbt knows about it.

Step 2: Custom logic

Most models need some transformation, so the next step is one or more CTEs that hold custom logic. This will usually be the largest part of the file and where most of the work happens.

Grain decides when a CTE is needed. If the result of a piece of logic will be at a different level of detail than the final model, give it its own CTE.

A customers model is one row per customer. Counting orders per customer produces a different shape from the raw orders table, so it gets a CTE of its own.

customer_orders as (

    select
        customer_id,
        count(order_id) as order_count,
        sum(order_amount) as total_order_amount
    from orders
    group by customer_id

),

Because the inputs were imported and aliased once at the top, you can use orders here and again further down without re-aliasing. That consistency is small on one model and significant across a hundred of them, because every file starts to have the same look and feel.

Name every CTE for what it holds

Name each CTE for what it holds. customer_orders tells the next reader what's inside, a generic name like cte_2 tells them nothing, and the chain of names is what makes the file readable top to bottom.

Don't hesitate to split complex logic across several CTEs. Readability and organization are the goal, and a long chain of short, named steps is easier to follow than one dense block.

Step 3: The final CTE

Once the custom logic is in place, the next step is to bring everything together into one last CTE. I name it final so there's no question about which one holds the columns the model returns.

final as (

    select
        customers.customer_id,
        customers.customer_name,
        coalesce(customer_orders.order_count, 0) as order_count,
        coalesce(customer_orders.total_order_amount, 0) as total_order_amount
    from customers
    left join customer_orders
        on customers.customer_id = customer_orders.customer_id

)

If you have transformations that are at the same grain as the output, it's fine to do them here rather than in a separate CTE. That covers things like:

• A coalesce to fill nulls.
• A rename.
• A case statement on a column that's already one row per customer.

The grain rule from step two is what decides: different grain gets its own CTE, same grain can sit in final.

Why wrap the output at all

Wrapping the output in a CTE looks odd the first time. At this point nothing is returned yet, everything is still inside a named subquery. The reason becomes clear in the last step.

Step 4: Select star from final

To finish the model, add one line.

select * from final

That's what returns the result set and creates the model. Yes, it's redundant in the sense that final could have been the query itself. But it makes future troubleshooting much easier.

Debugging by swapping the last line

The last line is your debugger. Say the model errors or returns numbers you don't expect, and you're not sure where in the chain it goes wrong.

Swap the last line for select * from customer_orders and run it. You see that CTE's output directly, and you move the select up or down the chain until you find the step that misbehaves.

This works because the query engine only evaluates what the outer select needs. If the last line doesn't touch final, the engine disregards it. No commenting out blocks, no copying half the query into a scratch file.

Follow it even on simple models

Personally, even when a model has one import and one final, I still follow all four steps. The value is in every model looking the same, and a model that skips the structure because it was simple today tends to be the one that grows messy later.

Why this saves time

The biggest cost is maintenance, not the first draft. Two things get expensive as a project grows: code review and debugging. Both get cheaper when every model follows the same structure.

In code review

In review, a reader knows where to look:

• Dependencies at the top.
• Logic in the middle.
• Output at the bottom.

They can check the imports against the DAG, scan the custom CTEs for the grain changes, and confirm the final select without reading the file three times.

It also lowers the bar for the next person. A new teammate who learns the layout from one model has learned it for every model in the project.

In debugging

In debugging, the select star swap means anyone, not just the author, can walk the chain of CTEs and isolate the broken step in a minute.

Across a project this adds up. Models get built faster because you're never deciding how to lay the file out, and they get maintained faster because the layout tells you where everything is.

Related reading

• SQL vs dbt Models (& the value of CTEs)
• What are "intermediate" models in dbt?
• The Power of Naming in Data Projects (especially w/ dbt)
• 5 Tips for a Successful dbt Project

Final takeaway

This is the structure I write every dbt model in and the first convention I put in place on client projects, because it's the cheapest way to make a project readable to the next person. Teams that adopt it tend to notice the difference in code review within a few weeks.