Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 41 ยท CI and Best Practices

Project Structure and Naming Conventions

Organize a dbt project in layers: staging, intermediate and marts, with naming conventions, folder configs, one-to-one staging models and a DAG you can reason about.

dbt makes it very easy to add a model, and that is the danger: after a year, hundreds of models call each other in ways nobody remembers. A clear structure and consistent names keep the project readable and make the dependency graph a tree you can follow rather than a tangle. This lesson describes the layered layout most mature teams converge on.

Three layers

Text
models/
  staging/
    shop/
      _shop__sources.yml
      _stg_shop__models.yml
      stg_shop__orders.sql
      stg_shop__customers.sql
  intermediate/
    int_orders_enriched.sql
  marts/
    finance/
      fct_orders.sql
      dim_customers.sql
flowchart LR
  S["Sources"] --> ST["Staging: one per source table"]
  ST --> I["Intermediate: reusable joins and logic"]
  I --> M["Marts: facts and dimensions for people"]
  ST --> M

Data flows left to right; models only depend on the layer to their left (or the same layer sparingly).

Staging

One staging model per source table, and nothing else. A staging model is a clean mirror of the source: rename columns to a standard, cast types, convert units, and give consistent boolean and timestamp columns. It does not join, aggregate or filter business rows.

SQL
-- models/staging/shop/stg_shop__orders.sql
with source as (
    select * from {{ source('shop', 'orders') }}
),

renamed as (
    select
        id            as order_id,
        customer_id,
        ordered_at    as ordered_at,
        status        as order_status,
        shipping      as shipping_amount
    from source
)

select * from renamed

Materialize staging as views (cheap, always fresh), or as incrementals when the source is huge. Only staging models may use source(). Everything else uses ref().

SQL A staging model, run as plain SQL ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The source and renamed CTEs are a dbt convention: first import, then transform, then one final select *.

Intermediate

Models that exist to make marts simpler: joins that several marts need, pivots, deduplication, complex calculations. Name them int_<entity>_<verb>, for example int_orders_pivoted_by_status. They are not for end users, so many teams keep them as ephemeral or views, in a folder that is not exposed to BI tools.

Marts

Models people and tools use. Two common shapes: facts (fct_) are events or transactions (orders, payments), dimensions (dim_) describe entities (customers, products). Group marts by business area (finance, marketing) rather than by source. Materialize them as tables or incrementals.

Here is a small mart built on staging-style CTEs:

SQL A fact table from staging pieces ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Naming conventions

Thing Convention Example
Staging model stg_<source>__<table> (double underscore) stg_shop__orders
Intermediate int_<description> int_orders_enriched
Fact fct_<plural or event> fct_orders
Dimension dim_<entity> dim_customers
Primary key column <entity>_id order_id
Booleans is_ / has_ prefix is_cancelled
Timestamps <verb>_at (UTC), dates <verb>_date ordered_at
Money name the unit price_usd, amount_cents

Pick a convention, write it down, and enforce it with a linter. The specific choice matters less than consistency. Use plural for table-like models, snake_case everywhere, and avoid reserved words as column names.

Folder-level configuration

Set materialization, schemas and tags per folder in dbt_project.yml, so individual files need no config block:

YAML
models:
  shop:
    staging:
      +materialized: view
      +schema: staging
    intermediate:
      +materialized: ephemeral
    marts:
      +materialized: table
      +schema: marts
      finance:
        +tags: ['finance']

Keep YAML files next to the models they describe, one per folder (_stg_shop__models.yml, underscore prefix so it sorts first), and put sources in their own __sources.yml.

Rules that keep the DAG healthy

  • Staging models depend only on sources, never on other models.
  • Marts do not depend on other marts in a circular or deeply stacked way; share logic through intermediate models.
  • Avoid joins in staging and joining many tables in one giant model; split large models into CTEs or intermediate steps.
  • Each model has one clear grain, stated in its description.
  • Do not reference source() from marts.
  • Tests: primary key unique + not_null on every model.

The dbt_project_evaluator package checks many of these rules automatically in CI (see CI).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What belongs in a staging model?

    Staging models mirror one source table, standardized, so everything downstream starts from clean inputs.
  2. 2. Which layer may call source()?

    Restricting source() to staging means a source change is fixed in exactly one place.
  3. 3. What does the prefix fct_ usually denote?

    Facts are events like orders or payments; dimensions (dim_) describe entities.
  4. 4. Why group marts by business area?

    Business-area folders map to teams and use cases and keep ownership clear.

Summary

  • Use three layers: staging (one per source table), intermediate (reusable logic), marts (facts and dimensions).
  • Follow consistent names and the import-CTE then final-select style.
  • Configure materializations per folder.
  • Keep dependencies flowing in one direction and enforce the rules automatically.

Next: Model Contracts, Versions and Performance