#053: What are "intermediate" models in dbt?

Intermediate models in dbt are supporting models that sit between the staging layer and your marts, and their job is to take complexity out of a single downstream model. A staging model cleans up one source table one to one, and a mart is the table end users query. An intermediate model holds the join, the aggregation or the change of grain that would otherwise make that mart hard to read. They are named with an int_ prefix, usually materialized as ephemeral so they compile into a CTE rather than a warehouse object, and built to feed exactly one model downstream. Used that way they make a project easier to read and debug, and used as a general reuse layer they turn the DAG into a tangle that gets harder to manage every month.

What the intermediate layer is

I heard about intermediate models for a long time before I understood them, so it helps to picture the pipeline rather than start from the documentation.

On the left are your sources, the raw tables already sitting in the database. On top of those are staging models, one per source, which are views that clean up and rename a single table.

On the far right are your marts: the tables people actually use, whether that's a dimensional model or one big table. In a generic entity focused layout that's a customers mart, an orders mart, an employees mart, each joining several staging models together.

Why the layer exists

They support one model. Sometimes that join logic gets to be too much for one file, and offloading some of it is the whole purpose of the intermediate layer.

What makes an intermediate model different from a staging model or a macro is the intent: it exists for one downstream mart. You don't join int_customers_and_locations_joined into three different marts, it exists for customers, full stop.

The complexity moves out of one query and into a file you can manage separately. That is the entire idea.

Where they live and how to name them

Within models/ you can add an intermediate/ directory and break it down further by business grouping, or organize it around the mart each model supports. Both work.

What matters more is the naming, because everything in dbt comes back to clarity. The less time someone spends working out what a file is, the more time they spend adding useful logic.

The convention I follow is int_, then the entities involved, then a verb for what is happening to them:

• int_customers_and_locations_joined
• int_orders_pivoted
• int_sessions_funnel_created

Your company's conventions win if you have them, but this is a solid starting point when you don't.

How to materialize them

Intermediate models are typically materialized as ephemeral. An ephemeral model is never deployed to the warehouse.

When another model refs it, dbt compiles it into a CTE inside that model's SQL, which keeps the warehouse clean but makes debugging slightly harder because there's no object to query directly.

models:
  my_project:
    intermediate:
      +materialized: ephemeral

The alternative is to materialize them as views in a custom schema. You get something you can query while developing, and it still stays out of the way of end users.

models:
  my_project:
    intermediate:
      +materialized: view
      +schema: intermediate

Either way the rule is the same. These are internal to the project, and nobody outside the data team should be building a dashboard on an intermediate model.

What they're for

Three use cases cover most of it:

• Structural simplification, where a mart is pulling together so many pieces that splitting a few out makes the file readable again.
• Regraining, where you need to bring data to a different level of detail, say summing orders up to the customer, before it can join cleanly into the mart.
• Isolating a complex operation, because the more a query grows the harder it is to interpret, and a separate model gives that operation a name and a boundary.

Nothing changes in the warehouse. Under the hood the query compiles the same. The benefit is entirely for the humans maintaining it.

A sample use case

Here is a customers mart that pulls in two intermediate models. The first joins customers to their locations. The second regrains orders to the customer level.

with customers_and_locations_joined as (

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

),

orders_regrained_to_customer as (

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

),

final as (

    select
        c.customer_id,
        c.customer_name,
        c.city,
        c.state,
        o.order_count,
        o.total_order_amount
    from customers_and_locations_joined as c
    left join orders_regrained_to_customer as o
        on c.customer_id = o.customer_id

)

select * from final

The first intermediate model is just the join, moved out of the mart.

with customers as (

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

),

locations as (

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

),

final as (

    select
        customers.customer_id,
        customers.customer_name,
        locations.city,
        locations.state
    from customers
    left join locations
        on customers.location_id = locations.location_id

)

select * from final

The second does the regrain, summing order rows up to one row per customer so it joins to the mart without multiplying rows.

with orders as (

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

),

final as (

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

)

select * from final

Honestly, neither of these is complex enough that I'd break it out on a real project. They're here to show the shape.

What the mart gained

But look at what happened to the mart: there's less logic in it, less to keep in your head, and you can focus on the final result.

With the intermediate directory set to ephemeral in dbt_project.yml, dbt takes each of those two files and drops them into the mart as CTEs at compile time, as if you'd written them inline. You get separate files to work in and one query in the warehouse.

Things to avoid

Optimizing too early

The example above is a good illustration of what not to do. The whole point is to make the project easier to work with and stop you hopping between files to find what's going on.

If the mart reads fine and nobody is struggling to debug it, don't split it. Treat intermediate models as an option you reach for when a query actually gets confusing, not a layer every project needs on day one.

Exposing them to end users

Intermediate models are an internal feature of the project. If they show up as tables in the schema your BI tool points at, you've cluttered the warehouse and invited people to build on something that was never meant to be stable.

Ephemeral materialization avoids this by design. If you use views, put them in a schema end users don't have access to.

Widening the DAG

Think of the DAG, your workflow of models, as an arrow. It should go from wide to narrow: many sources, fewer staging models feeding each mart, one mart at the tip. Multiple inputs, one output at this stage.

Reuse is the trap. The temptation with an intermediate model is to use it somewhere else, because it already does the join you need. Do that and the arrow sprawls back out. One intermediate model feeding three marts means a change for one mart silently changes the other two.

Staging models and macros are built to be reused. Intermediate models aren't. If you find yourself wanting the same intermediate logic in several places, that's a signal it belongs somewhere else:

• In staging, if it's cleanup that every consumer of that source needs.
• In a macro, if it's a piece of logic you want to apply in more than one model.
• In a mart of its own, if several marts need the same result.

Keeping that line clear is what stops the intermediate layer from becoming the thing that makes your project harder to manage instead of easier.

Related reading

• How to Create a 3 Layer Data Model Pipeline
• A simple 4-step process for creating dbt models
• The Power of Naming in Data Projects (especially w/ dbt)
• SQL vs dbt Models (& the value of CTEs)

Final takeaway

It took me several projects and a few years to see the value of this layer. Now it shows up on most of the dbt implementations I work on, usually after a mart has grown past the point where a new team member can read it in one sitting.