You've got dbt running. Models execute, tests pass, and your data warehouse has some useful tables in it. But six months in, your project looks like a junk drawer — staging models that reference other staging models, mart models doing five joins and three CTEs worth of business logic, and a schema.yml file with 400 lines of configuration that nobody wants to touch. New analysts spend a week just trying to understand what calls what. Adding a new source requires hunting through six files to figure out where to make changes. Welcome to the dbt project that grew without a plan.
This is an architectural problem, and it's extremely common. dbt gives you immense flexibility in how you structure your project, which is both a gift and a curse. The tool won't stop you from building a rat's nest of interdependent models. What will stop you — and help you build something genuinely maintainable — is a deliberate layering strategy. The staging → intermediate → mart pattern isn't just a naming convention; it's a separation of concerns that maps directly to how data transformation work actually breaks down: source system concerns, business logic concerns, and consumption concerns are fundamentally different problems that deserve different treatment.
By the end of this lesson, you'll have a concrete mental model for how to structure a production dbt project, understand the principles behind each layer rather than just the rules, and be able to make confident architectural decisions when edge cases push back against the guidelines.
What you'll learn:
dbt_project.yml and directory layout to enforce these patternsThis lesson assumes you're past the basics. You should be comfortable with:
dbt run, dbt test, dbt build command cycle{{ ref() }} and {{ source() }} macrosschema.yml configuration for sources, models, and testsIf you're still getting comfortable with ref() and source(), build a few working models first and come back.
Before you look at any folder structure, you need to understand why this architecture exists. Otherwise you'll apply it mechanically and still end up confused at the edges.
Data transformation work has three genuinely different concerns:
Source fidelity concerns: Your source systems are inconsistent. Column names are snake_cased in one system and camelCased in another. Timestamps are stored as strings. Some tables have is_deleted flags, others use soft-delete patterns with a status column. The work of dealing with source systems — renaming, casting, deduplicating, filtering deleted records — is completely different from understanding what an "active customer" means to your business. These are plumbing concerns.
Business logic concerns: Once your data is clean and consistently formatted, you need to apply business rules: session attribution, revenue recognition, user lifetime value calculations, funnel stage assignment. This logic is complex, evolves over time, and often needs to be shared across multiple final outputs. A "converted session" might be used in a marketing dashboard, a cohort analysis, and an executive summary. The logic for defining it should live in exactly one place.
Consumption concerns: Different consumers have different needs. A BI tool wants a wide, denormalized table optimized for fast aggregation. A data scientist wants normalized grain with all the raw features available. A downstream pipeline wants a narrow table with exactly three columns. The shape of output for consumption is a separate concern from the logic that produces it.
Staging, intermediate, and mart layers map directly onto these three concerns. Staging handles source fidelity. Intermediate handles shared business logic. Marts handle consumption shaping. When you understand this mapping, edge cases become easier to reason about — you're not asking "which folder does this go in?" but "which concern is this model primarily addressing?"
Let's establish the canonical structure before we fill it in with details. A production dbt project for an e-commerce company — let's say it's called Northgate Commerce and it has data from Shopify, Stripe, Salesforce, and a custom PostgreSQL events database — looks like this:
northgate_analytics/
├── dbt_project.yml
├── packages.yml
├── profiles.yml # typically not in version control
├── models/
│ ├── staging/
│ │ ├── shopify/
│ │ │ ├── _shopify__sources.yml
│ │ │ ├── _shopify__models.yml
│ │ │ ├── stg_shopify__orders.sql
│ │ │ ├── stg_shopify__order_items.sql
│ │ │ ├── stg_shopify__customers.sql
│ │ │ └── stg_shopify__products.sql
│ │ ├── stripe/
│ │ │ ├── _stripe__sources.yml
│ │ │ ├── _stripe__models.yml
│ │ │ ├── stg_stripe__charges.sql
│ │ │ ├── stg_stripe__refunds.sql
│ │ │ └── stg_stripe__payment_intents.sql
│ │ ├── salesforce/
│ │ │ ├── _salesforce__sources.yml
│ │ │ ├── _salesforce__models.yml
│ │ │ ├── stg_salesforce__accounts.sql
│ │ │ └── stg_salesforce__opportunities.sql
│ │ └── events/
│ │ ├── _events__sources.yml
│ │ ├── _events__models.yml
│ │ ├── stg_events__pageviews.sql
│ │ └── stg_events__add_to_carts.sql
│ ├── intermediate/
│ │ ├── commerce/
│ │ │ ├── int_orders__attributed.sql
│ │ │ ├── int_order_items__enriched.sql
│ │ │ └── int_customers__order_history.sql
│ │ ├── finance/
│ │ │ ├── int_payments__reconciled.sql
│ │ │ └── int_refunds__categorized.sql
│ │ └── marketing/
│ │ ├── int_sessions__attributed.sql
│ │ └── int_customers__acquisition_channel.sql
│ └── marts/
│ ├── commerce/
│ │ ├── _commerce__models.yml
│ │ ├── orders.sql
│ │ ├── order_items.sql
│ │ └── customers.sql
│ ├── finance/
│ │ ├── _finance__models.yml
│ │ ├── payments.sql
│ │ └── revenue_by_day.sql
│ └── marketing/
│ ├── _marketing__models.yml
│ ├── customer_acquisition.sql
│ └── channel_performance.sql
├── macros/
├── seeds/
├── snapshots/
└── tests/
The double-underscore convention in filenames is deliberate and important. stg_shopify__orders reads as "staging layer, shopify source, orders entity." The prefix declares the layer, the first segment names the source system, the second segment names the entity. For intermediate models, the convention shifts: int_orders__attributed reads as "intermediate, orders entity, attributed variant." The source system disappears at the intermediate layer because these models are already crossing source system boundaries.
Mart models often drop prefixes entirely within the mart directory — they're just orders.sql, customers.sql — because the directory path provides the namespace context.
Your project configuration file is where you set defaults that govern the entire architecture. This is where layer-level decisions about materializations, schemas, and tags live.
# dbt_project.yml
name: northgate_analytics
version: "1.0.0"
config-version: 2
profile: northgate_analytics
model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]
target-path: "target"
clean-targets: ["target", "dbt_packages"]
models:
northgate_analytics:
staging:
+materialized: view
+schema: staging
+tags: ["staging"]
shopify:
+tags: ["staging", "shopify"]
stripe:
+tags: ["staging", "stripe"]
salesforce:
+tags: ["staging", "salesforce"]
events:
+tags: ["staging", "events"]
intermediate:
+materialized: ephemeral
+schema: intermediate
+tags: ["intermediate"]
commerce:
+tags: ["intermediate", "commerce"]
finance:
+tags: ["intermediate", "finance"]
marketing:
+tags: ["intermediate", "marketing"]
marts:
+materialized: table
+schema: marts
+tags: ["marts"]
commerce:
+tags: ["marts", "commerce"]
finance:
+tags: ["marts", "finance"]
revenue_by_day:
+materialized: incremental
marketing:
+tags: ["marts", "marketing"]
Three decisions embedded in this configuration deserve explanation:
Staging as views: Staging models should almost always be views. They add no storage overhead, always reflect the current state of the source, and the cost of the view layer is paid when the intermediate or mart model queries them — which only happens when those models are being rebuilt. The one exception is if your source tables are very large and staging queries are expensive (heavy type casting across hundreds of millions of rows). In that case, staging as tables is defensible, but treat it as a performance optimization, not the default.
Intermediate as ephemeral: This is the more contentious choice and deserves a real discussion. Ephemeral models exist only as CTEs inlined into downstream queries — they don't create physical objects in the warehouse. This means they have zero storage cost and zero query overhead for the simple case. But it also means they're not queryable directly, which makes debugging harder, and they can cause problems when multiple mart models reference the same intermediate model (the CTE gets duplicated in each generated query, not shared). The right answer depends on your situation:
A common production pattern is to start intermediate models as ephemeral during development and promote them to views once the project matures enough that query duplication becomes a real cost.
Marts as tables: Marts are the consumption layer. BI tools, analysts, and downstream pipelines query them frequently and expect fast response times. Tables with appropriate clustering/sorting keys are the right default. The revenue_by_day model being overridden to incremental illustrates the most common exception: append-only aggregated fact tables that grow continuously should be incremental to avoid reprocessing all historical data on every run.
Tip: The
+schemaconfiguration indbt_project.ymluses a custom schema suffix by default, not a full schema name. The actual schema your models land in will be{target_schema}_{custom_schema}(e.g.,analytics_staging) unless you override thegenerate_schema_namemacro. For a production project, you almost certainly want to customize this behavior — we'll cover that in the schema naming section below.
The staging layer has one job: take a raw source table and produce a clean, consistent, well-typed representation of that entity. Every decision made in a staging model should be answerable with "this is what the source system actually says, expressed clearly." Business logic has no place here.
Let's build the Shopify orders staging model:
-- models/staging/shopify/stg_shopify__orders.sql
with source as (
select * from {{ source('shopify', 'orders') }}
),
renamed as (
select
-- primary key
id as order_id,
-- foreign keys
customer_id,
location_id,
-- order metadata
name as order_number,
email as customer_email,
created_at as order_created_at,
updated_at as order_updated_at,
processed_at as order_processed_at,
closed_at as order_closed_at,
cancelled_at as order_cancelled_at,
-- financial amounts (Shopify stores in decimal strings, cast to numeric)
cast(total_price as numeric(12, 2)) as order_total_amount,
cast(subtotal_price as numeric(12, 2)) as order_subtotal_amount,
cast(total_discounts as numeric(12, 2)) as order_discounts_amount,
cast(total_tax as numeric(12, 2)) as order_tax_amount,
cast(total_shipping_price_set:shop_money:amount
as numeric(12, 2)) as order_shipping_amount,
-- status fields
financial_status,
fulfillment_status,
cancel_reason,
-- booleans (Shopify uses string 'true'/'false' in some API versions)
(taxes_included = 'true') as is_taxes_included,
(test = 'true') as is_test_order,
-- source tracking
source_name as order_source,
referring_site as referring_url,
landing_site as landing_page_url,
-- currency
currency as order_currency,
-- metadata
_fivetran_synced as _fivetran_synced_at
from source
),
filtered as (
-- Exclude test orders and orders from deleted customers
-- Note: we do NOT filter by financial_status here — that is business logic
select *
from renamed
where is_test_order = false
and _fivetran_synced_at is not null
)
select * from filtered
Notice what this model does and doesn't do:
What it does:
What it does NOT do:
The filtered CTE is worth discussing. Filtering is_test_order = false is appropriate here because test orders are a source system artifact — they don't represent real business events. But filtering financial_status = 'paid' would be business logic because "paid" is a business definition that might change based on context. When in doubt: if a data engineer at Shopify would agree the filter is correct, it belongs in staging. If it requires your company's CFO to agree, it's business logic.
The source YAML for this layer:
# models/staging/shopify/_shopify__sources.yml
version: 2
sources:
- name: shopify
description: "Raw Shopify data loaded via Fivetran"
database: raw_data
schema: shopify
loaded_at_field: _fivetran_synced
freshness:
warn_after: {count: 12, period: hour}
error_after: {count: 24, period: hour}
tables:
- name: orders
description: "One row per Shopify order"
columns:
- name: id
description: "Shopify's internal order ID"
tests:
- unique
- not_null
- name: created_at
tests:
- not_null
- name: order_items
description: "Line items within each order (called line_items in Shopify API)"
- name: customers
description: "Shopify customer records"
columns:
- name: id
tests:
- unique
- not_null
The staging model YAML lives in a separate file:
# models/staging/shopify/_shopify__models.yml
version: 2
models:
- name: stg_shopify__orders
description: "Cleaned and renamed Shopify orders. One row per order."
columns:
- name: order_id
tests:
- unique
- not_null
- name: order_total_amount
tests:
- not_null
- dbt_utils.accepted_range:
min_value: 0
Warning: A common mistake is to put tests only on the source tables and skip tests on staging models. Don't do this. Source tests validate that the raw data arrived correctly. Staging tests validate that your transformation logic is correct — that your casts worked, your renames are right, your keys are still unique after filtering. These are different failure modes and you need both.
The intermediate layer is the most misunderstood part of this architecture. Many dbt projects skip it entirely and end up with mart models that are enormous, complex, and impossible to reuse. Others use it only as a holding area for "complex" joins without thinking carefully about what makes something intermediate.
The purpose of intermediate models is to encode reusable business logic that multiple mart models need, or that is complex enough to deserve its own documented unit.
Let's build int_orders__attributed.sql, which combines orders with their acquisition channel from the events stream — logic that needs to feed both the commerce mart and the marketing mart:
-- models/intermediate/commerce/int_orders__attributed.sql
/*
Enriches orders with first-touch acquisition channel attribution.
Attribution logic: we assign the channel from the last session that occurred
before the order was placed and within a 30-day lookback window.
This model is the single source of truth for order attribution.
It feeds both marts/commerce/orders and marts/marketing/customer_acquisition.
*/
with orders as (
select * from {{ ref('stg_shopify__orders') }}
),
sessions as (
select * from {{ ref('int_sessions__attributed') }}
),
-- Find the most recent session before each order within the attribution window
order_sessions as (
select
o.order_id,
o.customer_id,
o.order_created_at,
o.order_total_amount,
s.session_id,
s.session_started_at,
s.attribution_channel,
s.attribution_source,
s.attribution_medium,
s.utm_campaign,
row_number() over (
partition by o.order_id
order by s.session_started_at desc
) as session_recency_rank
from orders o
left join sessions s
on o.customer_id = s.customer_id
and s.session_started_at <= o.order_created_at
and s.session_started_at >= dateadd(day, -30, o.order_created_at)
),
-- Take only the most recent session per order
attributed as (
select
order_id,
customer_id,
order_created_at,
order_total_amount,
session_id as attributed_session_id,
attribution_channel,
attribution_source,
attribution_medium,
utm_campaign,
case
when session_id is null then 'direct / none'
else attribution_channel
end as effective_channel
from order_sessions
where session_recency_rank = 1
or session_recency_rank is null -- orders with no matching session
)
select * from attributed
This model has a critical characteristic: it doesn't select all columns from orders. It selects only what it needs for attribution. The mart models that consume this intermediate model will join it back to the full orders staging model. Why? Because intermediate models that try to pass through all columns from their inputs become unmaintainable — every time a staging model adds a column, you have to update all intermediate models in the chain. Instead, intermediate models should be narrow and purposeful.
Here's int_customers__order_history.sql, which pre-computes customer lifetime metrics shared across multiple mart tables:
-- models/intermediate/commerce/int_customers__order_history.sql
with orders as (
select * from {{ ref('stg_shopify__orders') }}
),
payments as (
select * from {{ ref('int_payments__reconciled') }}
),
order_metrics as (
select
o.customer_id,
-- Order counts by status
count(distinct o.order_id) as lifetime_order_count,
count(distinct case
when o.financial_status = 'paid'
then o.order_id
end) as paid_order_count,
count(distinct case
when o.financial_status = 'refunded'
then o.order_id
end) as refunded_order_count,
-- Revenue metrics (using reconciled payments, not raw Shopify totals)
sum(p.net_amount) as lifetime_net_revenue,
avg(p.net_amount) as avg_order_value,
-- Timing metrics
min(o.order_created_at) as first_order_at,
max(o.order_created_at) as most_recent_order_at,
datediff(
day,
min(o.order_created_at),
max(o.order_created_at)
) as customer_lifetime_days,
-- Recency (days since last order as of today)
datediff(day, max(o.order_created_at), current_timestamp) as days_since_last_order
from orders o
left join payments p
on o.order_id = p.order_id
and p.payment_status = 'succeeded'
group by o.customer_id
)
select * from order_metrics
This model becomes the canonical source for customer lifetime value metrics. Instead of every mart model that shows customer data recomputing these metrics (and potentially computing them slightly differently), they all reference this single intermediate model.
Architecture decision: Should intermediate models be in the
intermediatefolder or can they be in source-specific subfolders? Intermediate models that pull from a single source system are rare — if you're only touching one source, ask yourself whether it should actually be a staging model. True intermediate work crosses sources or encodes domain logic that transcends a single source, which is why the subdirectory structure in intermediate uses business domains (commerce,finance,marketing) rather than source system names.
Mart models take the clean, logically-enriched data from staging and intermediate layers and shape it for consumption. The key insight about mart models is that they should be opinionated about shape, not about logic. The logic belongs upstream.
-- models/marts/commerce/orders.sql
with orders as (
select * from {{ ref('stg_shopify__orders') }}
),
order_items_summary as (
select
order_id,
count(*) as line_item_count,
sum(quantity) as total_quantity,
array_agg(product_id) as product_ids
from {{ ref('stg_shopify__order_items') }}
group by order_id
),
attribution as (
select * from {{ ref('int_orders__attributed') }}
),
customers as (
select * from {{ ref('stg_shopify__customers') }}
),
order_history as (
select * from {{ ref('int_customers__order_history') }}
),
final as (
select
-- order identifiers
o.order_id,
o.order_number,
o.customer_id,
-- customer context (denormalized for BI)
c.customer_first_name,
c.customer_last_name,
c.customer_email,
c.customer_city,
c.customer_country_code,
-- order details
o.order_created_at,
o.order_processed_at,
o.financial_status,
o.fulfillment_status,
o.order_currency,
o.order_source,
-- financial amounts
o.order_total_amount,
o.order_subtotal_amount,
o.order_discounts_amount,
o.order_tax_amount,
o.order_shipping_amount,
-- line items
coalesce(ois.line_item_count, 0) as line_item_count,
coalesce(ois.total_quantity, 0) as total_quantity,
-- attribution
attr.attributed_session_id,
attr.effective_channel as acquisition_channel,
attr.attribution_source,
attr.attribution_medium,
attr.utm_campaign,
-- customer lifetime context (at time of this order)
oh.lifetime_order_count as customer_order_count_at_time,
case
when oh.lifetime_order_count = 1 then 'new'
when oh.lifetime_order_count between 2 and 5 then 'returning'
else 'loyal'
end as customer_segment,
-- metadata
o._fivetran_synced_at
from orders o
left join customers c
on o.customer_id = c.customer_id
left join order_items_summary ois
on o.order_id = ois.order_id
left join attribution attr
on o.order_id = attr.order_id
left join order_history oh
on o.customer_id = oh.customer_id
)
select * from final
This mart model is wide by design. BI tools like Looker, Metabase, and Tableau perform best against wide, pre-joined tables. The mart is the place to denormalize — and to include contextual columns (like customer_segment) that are derived classifications rather than raw data.
Notice that customer_segment logic lives in the mart, not in an intermediate model. Why? Because this particular segmentation is specific to how the commerce dashboard wants to display customers. The finance team might define "loyal" differently. If we encoded this in an intermediate model, we'd be baking one team's definition into shared infrastructure. The mart is the right place for consumption-specific classifications.
One of the trickiest questions in multi-layer dbt projects is whether marts can reference other marts. The short answer is: sparingly, with clear justification, and never in a way that creates circular dependencies.
The stronger position, advocated by dbt Labs themselves, is that marts should not reference other marts at all — if two marts need the same data, extract the shared logic into an intermediate model.
Here's when it's actually tempting to reference a mart from another mart:
-- The temptation: marketing mart referencing the commerce mart
-- models/marts/marketing/customer_acquisition.sql
-- ANTI-PATTERN: Don't do this
with orders as (
select * from {{ ref('orders') }} -- referencing the commerce mart
),
...
The problem with this pattern is coupling. If the commerce mart changes its grain, adds a filter, or renames a column, the marketing mart breaks. More subtly, if you ever want to run dbt build --select tag:marketing, you'll need to run the commerce mart first — which means marketing is no longer independently deployable.
The correct fix is to push the shared logic to intermediate:
-- models/intermediate/marketing/int_customers__acquisition_channel.sql
-- Pulls directly from staging and intermediate/commerce, not from the mart
with customers as (
select * from {{ ref('stg_shopify__customers') }}
),
order_history as (
select * from {{ ref('int_customers__order_history') }}
),
attribution as (
select * from {{ ref('int_orders__attributed') }}
),
...
Both the commerce and marketing marts then reference int_customers__acquisition_channel independently. The dependency is in the intermediate layer, where it belongs.
By default, dbt appends your custom schema names to your target schema. If you're deploying to a Snowflake database where your target schema is analytics, your staging models land in analytics_staging and your mart models land in analytics_marts. This works fine for a single-environment project but quickly becomes awkward.
A more sophisticated pattern uses a custom generate_schema_name macro:
-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
{%- set default_schema = target.schema -%}
{%- if target.name == 'prod' -%}
{%- if custom_schema_name is none -%}
{{ default_schema }}
{%- else -%}
{{ custom_schema_name | trim }}
{%- endif -%}
{%- else -%}
{%- if custom_schema_name is none -%}
{{ default_schema }}
{%- else -%}
{{ default_schema }}_{{ custom_schema_name | trim }}
{%- endif -%}
{%- endif -%}
{%- endmacro %}
With this macro:
target.name == 'prod'), the custom schema names are used directly: staging, intermediate, martsdbt_jsmith_staging, dbt_jsmith_intermediate, dbt_jsmith_martsThis gives you clean, predictable schema names in production while keeping development environments isolated per developer.
High-volume mart models — event-level data, daily revenue aggregations, log tables — need incremental materialization to avoid reprocessing terabytes of historical data on every run.
-- models/marts/finance/revenue_by_day.sql
-- materialized as incremental (set in dbt_project.yml)
{{
config(
materialized='incremental',
unique_key='revenue_date',
incremental_strategy='merge',
cluster_by=['revenue_date']
)
}}
with payments as (
select * from {{ ref('int_payments__reconciled') }}
{% if is_incremental() %}
-- Only process payments from the last 3 days (2-day buffer for late arrivals)
where payment_date >= dateadd(day, -3, current_date)
{% endif %}
),
daily_revenue as (
select
date_trunc('day', payment_date) as revenue_date,
payment_currency,
sum(gross_amount) as gross_revenue,
sum(refund_amount) as total_refunds,
sum(net_amount) as net_revenue,
sum(stripe_fee_amount) as total_processing_fees,
count(distinct order_id) as paid_order_count,
count(distinct customer_id) as paying_customer_count
from payments
where payment_status = 'succeeded'
group by 1, 2
)
select * from daily_revenue
The 3-day lookback window in the incremental filter is intentional. Payment processors sometimes deliver late-arriving updates (chargebacks, adjustments) for 48-72 hours after the original transaction. A naive incremental model that only processed today's data would miss these updates. The 3-day buffer ensures we reprocess and merge any late arrivals.
Warning: A common incremental model mistake is using
is_incremental()filters on the wrong model. Theis_incremental()filter should be on the source data query within your model, not on the final select. If you filter in the final select, you'll insert fewer rows but won't correct already-inserted rows that had late-arriving updates.
You're going to build a three-layer dbt model chain for a simplified SaaS subscription analytics use case. Assume you have access to a DuckDB database (or any warehouse) with the following raw tables:
raw.subscriptions — columns: id, customer_id, plan_id, status, started_at, cancelled_at, mrr_amount, billing_intervalraw.customers — columns: id, email, company_name, created_at, country_code, account_tierraw.events — columns: event_id, customer_id, event_type, occurred_at, properties (JSON)Task 1: Build the staging layer
Create three staging models:
stg_app__subscriptions.sql — rename columns, cast mrr_amount to numeric, add a boolean is_active derived from status = 'active'stg_app__customers.sql — rename columns, handle potential nulls in company_namestg_app__events.sql — rename columns, parse properties JSON to extract page_name and feature_name keysTask 2: Build the intermediate layer
Create int_customers__subscription_metrics.sql that calculates per-customer:
mrr_amount for active subscriptions)Task 3: Build the mart
Create marts/saas/customers.sql that joins staging customers with your intermediate subscription metrics to produce a wide table suitable for a SaaS metrics dashboard. Include a computed customer_health_score column that assigns 'at_risk' if days since last subscription start > 365 and churn history exists, 'stable' otherwise.
Validation questions:
is_active acceptable in staging but customer_health_score should live in the mart?churned_customers mart and a revenue_at_risk mart both needed customer subscription metrics, where should that logic live and why?int_customers__subscription_metrics if it was referenced by 8 different mart models?Mistake 1: Business logic in staging models
The most common violation. You're building stg_shopify__orders.sql and you think "I'll just add a filter for financial_status = 'paid' while I'm here." Three months later, someone builds a refunds mart that needs all orders regardless of status and they don't realize staging is filtering them out. Every staging model should have a clear single-source-of-truth scope and no business filters.
Mistake 2: Overly wide intermediate models
Building an intermediate model that does select * from its inputs and passes everything through is a code smell. Intermediate models should select only what they produce — the columns that represent the specific enrichment or calculation they're responsible for. Downstream models join back to staging for the rest.
Mistake 3: Skipping the intermediate layer entirely
You end up with mart models that are 200-line SQL files with 8 CTEs, complex window functions, and logic that's copy-pasted across multiple mart models with slight variations. When the attribution logic changes, you have to update it in 4 places and hope they stay consistent.
Mistake 4: Ephemeral models that are referenced by many models
If 6 different mart models reference the same ephemeral intermediate model, that CTE is being materialized 6 separate times in 6 separate queries. What you thought was "efficient" becomes the opposite. Promote heavily-referenced intermediate models to views or tables.
Mistake 5: Circular ref() dependencies
dbt builds a Directed Acyclic Graph (DAG) and will error if it detects a cycle. The staging → intermediate → mart flow is inherently acyclic when you follow the layers. Cycles typically appear when you let mart models reference other mart models, or when intermediate models within the same domain start referencing each other without careful design.
Troubleshooting circular dependencies: Run dbt ls --select +my_failing_model to see the full DAG for a model. If you see models at the same layer referencing each other, you need to extract the shared logic to a new intermediate model that both can reference downstream.
Mistake 6: Inconsistent naming conventions
A project where some staging models are stg_orders, some are stg_shopify_orders, and some are staging_shopify__orders_v2 is a maintenance nightmare. Use a linter (SQLFluff with dbt dialect, or dbt's own dbt ls with naming checks) to enforce conventions from the start.
Mistake 7: No tests at the intermediate layer
Intermediate models contain your most critical business logic. If the attribution join produces duplicate order_id rows, every mart that references it will be wrong. Test uniqueness and referential integrity at intermediate models, not just at the mart.
# models/intermediate/commerce/_commerce__intermediate_models.yml
version: 2
models:
- name: int_orders__attributed
description: "Orders enriched with acquisition channel attribution"
columns:
- name: order_id
tests:
- unique
- not_null
- name: effective_channel
tests:
- not_null
You've built a complete mental model for structuring a production dbt project. Let's consolidate the key principles:
The three layers map to three concerns:
Materializations follow from the concerns:
The dbt_project.yml is your architecture document: Set layer-level defaults there. Let individual models override only when they have a justified reason.
Logic duplication is the enemy: Any time you're copy-pasting a CTE from one mart to another, that CTE wants to be an intermediate model.
Where to go from here:
dbt build --select state:modified+, environment-specific configurations, and PR checksunique and not_null to custom schema tests, dbt-expectations, and audit-helper for regression testingref() to maintain clean interfaces between domainsThe architecture pattern you've learned here scales from a five-model project to a five-hundred-model project. The principles don't change — only the discipline required to apply them consistently.