#033: dbt Project Naming Conventions - Recommendations

The naming convention I recommend for a dbt project is the one in dbt's own documentation, because it has held up on every project I've used it on. Split your models into three layers, staging, intermediate and marts, and give each layer its own directory. Name staging models stg_<source>__<table>, intermediate models int_<entities>_<what happened>, and marts by the plain business entity they represent, like customers or orders. Start every schema file with an underscore so it sorts to the top of its directory. None of this is clever. It's a set of defaults that keeps a project readable when it grows from ten models to several hundred.

The three layers

How you design your dbt directories and name your files has a huge impact on how successful you are with the tool, and on how you feel about it day to day. The dbt documentation has a lot of good notes on this. What follows are the pieces I've used on my own projects.

The structure starts with three general layers:

• Staging. One-to-one with your source data. Every source table gets a staging view on top of it with light transformations only.
• Intermediate. Where more complicated logic lives, often as ephemeral models, so the later queries stay simple.
• Marts. Where it all comes together into entity-focused tables the organization cares about, the ones you connect to a reporting tool or reverse ETL.

That's a simplistic view, and real projects get more complicated. But if you're just getting started, or you're looking to refactor, this is the route I'd recommend. Every naming rule below follows from it.

A sample project layout

Here's what the models directory looks like with those layers in place. The source names are illustrative, the shape is what matters.

models/
├── staging/
│   ├── jaffle_shop/
│   │   ├── _jaffle_shop__sources.yml
│   │   ├── _jaffle_shop__models.yml
│   │   ├── stg_jaffle_shop__customers.sql
│   │   ├── stg_jaffle_shop__orders.sql
│   │   └── stg_jaffle_shop__locations.sql
│   └── stripe/
│       ├── _stripe__sources.yml
│       ├── _stripe__models.yml
│       └── stg_stripe__payments.sql
├── intermediate/
│   ├── _intermediate__models.yml
│   └── int_customers_and_locations_joined.sql
└── marts/
    ├── _marts__models.yml
    ├── _marts__docs.md
    ├── customers.sql
    └── orders.sql

One directory per source

Within staging I create a directory for each source system instead of dropping everything under staging directly. It keeps the layer organized, and it pays off later when you use selectors, because you can run, test or document one source at a time by pointing at its directory.

Each model in there is a view with light transformations against one source table, and the naming is consistent for all of them.

Rinse and repeat

The structure is the same in every layer. Look at it from a distance and the naming of the files and the shape of the directories repeat. That gives the project a clean look, instead of different names and different approaches scattered everywhere, which is a recipe for chaos.

So the workflow for growth is simple:

• New source: add a directory under staging and a staging model per table.
• Need to combine sources: join them in a mart.
• Join gets too complex: break the heavy pieces out into intermediate models.

Model naming

Now the specific conventions for the models themselves, layer by layer. These also follow the dbt recommendations.

Staging: stg, source, table

Every staging model is stg_, then the source name, then two underscores, then the table name. In my projects table names are plural, which is a style guide choice you can make either way.

stg_jaffle_shop__customers.sql
stg_jaffle_shop__orders.sql
stg_stripe__payments.sql

The names get long, and your source names may be longer than these. I'd still avoid acronyms. Someone brand new to the team should read stg_stripe__payments and know it's a staging model, from Stripe, over the payments table.

That matters because a real project gets big. It also matters in the database, since this is the name the model deploys under, so it's just as readable there.

Intermediate: int, entities, action

Intermediate models aren't separated by source, so the source drops out of the name. Instead it's int_, the entities involved, and what's going on with them.

int_customers_and_locations_joined.sql

In this one I'm joining customer records to location information so the output can be reused downstream. The name says exactly that. Consistent prefix, clear design, readable without opening the file.

Marts: the agreed-upon name

In a simple environment without too many marts, name them for the thing they represent. customers is the agreed-upon mart for all things customers. orders is the same for orders.

No prefix, because the mart is the table the rest of the business uses, and the name should read like the entity it is.

Reading the refs

The payoff shows up in the code. Open the customers mart and the refs tell you where every input comes from.

with customers as (

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

),

customer_locations as (

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

),

...

You can infer at a glance that one input is a staging model and the other is intermediate, before reading a single line of logic. That keeps the whole project organized and easy to maintain long term.

Schema file naming

The last piece is the YAML files at the top of each directory. These are the schema files, sometimes called properties files, where you declare the models in a directory along with their descriptions and tests.

Why the leading underscore

The files start with an underscore for one reason: it keeps them at the top of the directory in alphabetical order. Rather than being lost in the middle of a long list of models, they're always in the same place.

One file per directory, not per model

Each directory gets its own schema file listing every model in that directory, with tests and descriptions where you have them.

version: 2

models:
  - name: stg_jaffle_shop__customers
    description: One row per customer from the Jaffle Shop app.
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null

  - name: stg_jaffle_shop__orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null

If the file gets too long, you can split to one file per table. I'd advise against it unless it's absolutely necessary. The better fix is subdirectories, each with its own schema file, instead of a YAML file for every model.

Sources and models are different files

Staging directories also need a sources file, which declares the raw tables dbt reads from. That's a different concept than a models file, so it gets a different name.

_jaffle_shop__sources.yml
_jaffle_shop__models.yml

version: 2

sources:
  - name: jaffle_shop
    database: raw
    tables:
      - name: customers
      - name: orders
      - name: locations

The two underscores separate the concepts. One glance tells you which file is the schema for the sources and which is the schema for the models.

Docs blocks

If you want to offload long descriptions into a docs block instead of writing them inline in YAML, the same pattern applies. Underscore, directory name, two underscores, docs.

_marts__docs.md

{% docs customers %}
The agreed-upon customer table for reporting. One row per customer,
joined to their current location and lifetime order metrics.
{% enddocs %}

The point of all this

The whole point is to make the project easy on your brain. dbt is open source and fully customizable, which means a project has every opportunity to sprawl and get out of control.

Conventions like these are how you stay in control of the project, instead of the other way around. The dbt docs have more recommendations in the same spirit if you want to go deeper.

Related reading

• Why Bother With Naming Conventions?
• The Power of Naming in Data Projects (especially w/ dbt)
• What are "intermediate" models in dbt?
• 5 Tips for a Successful dbt Project

Final takeaway

Every dbt project I've inherited that felt out of control had the same root cause: nobody decided how to name things, so everybody did. Pick the three layers, the prefixes and the underscore files on day one, and the project stays readable no matter how large it gets.