Intermediate Models

The Mental Model

Intermediate models hold reusable transformation logic that is too complex for staging but not final enough for business users.

Intermediate models are the prep bowls in a kitchen. They are useful while cooking, but you do not serve them as the final dish.

Dataset reference: ecommerce tables and grain assumptions.

Aggregate refunds before a reusable join

An order can have several refunds. Produce one refund total per order before sharing that logic with finance and customer-support marts.

with orders(order_id, amount) as (values (101, 100), (102, 50)),
refunds(order_id, amount) as (values (101, 10), (101, 15)),
refund_totals as (
  select order_id, sum(amount) as refunded
  from refunds group by order_id
)
select o.order_id, o.amount - coalesce(r.refunded, 0) as net_amount
from orders o left join refund_totals r on r.order_id = o.order_id;

Expected rows: (101, 75) and (102, 50). The left join preserves an order with no refunds. In dbt, put the reusable result behind a ref and test its order_id for uniqueness. Decide explicitly how pending refunds and cross-currency refunds are handled before applying this pattern to production data.

Interactive Check

Question: Two marts need the same order refund calculation. Should both copy the SQL?

Practice: Extract Shared Logic

Move repeated refund and order status logic into one intermediate model.

Use the guided lab below to record your result, assumptions, and the check that would catch an incorrect result.