dbt lab: project layout that scales

A dbt project (dbt is the tool that turns raw tables into clean ones using SQL files) needs a folder layout that a new teammate can understand without asking anyone. Layout is not about looks. It is how a team finds out what one row means, who owns a table, and what is safe to change next.

The first project feels fine with six models in one folder. Six months later you have orders_final_v3.sql, a summary table that selects from another summary table that selects from a staging model named after a person, and a chat thread titled “which orders is the real orders.” This post is a lab on project shape that still makes sense when the third analyst joins.

This is the first post in the dbt project lab series. It sets folders, naming, and layering before you lean hard on tests and builds, and the next post writes the first models and tests end to end. If you need the concept layer first, read the site’s overview of dbt in the pipelines series, and keep SQL, Data quality, and Data pipelines nearby. The full map is on Learn.

What “scales” means here

Scaling does not mean supporting a giant company on day one. It means five practical things, and each one saves someone an hour.

• A new teammate can guess where a model lives from its job.
• You can change a source column without digging through twenty files named “final.”
• The final tables that people query stay thin and shaped like the business, while raw quirks stay near the source.
• Tests and documentation sit next to the models they protect.
• Cleanups are boring instead of heroic.

If your layout depends on knowledge that lives only in people’s heads, such as “oh, we always put finance things in intermediate for historical reasons,” it will not scale past the people who remember the story.

Rule of thumb: Give each model one job and one grain sentence. The grain is what a single row stands for, such as “one row per order.” If you need “and also” more than once, split the model or rename it until the job is honest.

The three layers and what each is allowed to do

Staging: source-shaped, lightly cleaned

Staging models sit closest to the source systems, and one staging model per source table is a strong default. A staging model may do four things.

• Rename columns to the team’s standard.
• Change data types, for example turning text into numbers or dates.
• Trim strings, parse dates, and make empty values consistent.
• Add basic flags that still come from that one source, for example is_deleted based on a source status.

Some jobs do not belong in staging if you want to stay sane. Do not join several sources to invent a new business entity, do not do heavy KPI math, and do not “fix the dashboard while we are here.” Staging should feel almost boring, and that boredom is the point, because when the source changes you want exactly one obvious file to touch.

Intermediate: private building blocks

Intermediate models reshape data so it can be reused. They handle joins that multiply or collapse rows, bridge tables, and step-by-step enrichment that is not yet a finished table for the business. People outside the project should rarely query them, so name them in a way that keeps them off dashboards by accident.

Use an intermediate model when two final tables would otherwise repeat the same complex join. Do not use the layer as a parking lot for half-finished ideas. If a model has no downstream ref (the dbt command that points one model at another), ask whether it should exist at all.

Marts: business-shaped, consumer-ready

Marts are the final tables that analysts, business intelligence (BI) tools, and tools that push data back into business apps should touch. Their grain is a business entity or a clear fact, such as customers, orders, order lines, or a monthly subscription snapshot. Names can lean toward the business (fct_orders, dim_customers) instead of the names of source systems.

Marts may join staging and intermediate models, but they should not redo source renames that belong in staging. If you find yourself casting the same timestamp in three marts, push that work down a layer.

A folder tree you can copy

Exact trees vary by team. This lab tree is a proven starting shape.

models/
  staging/
    jaffle_shop/
      _jaffle_shop__sources.yml
      stg_jaffle_shop__customers.sql
      stg_jaffle_shop__orders.sql
      stg_jaffle_shop__payments.sql
  intermediate/
    int_orders_pivoted_to_payments.sql
  marts/
    core/
      dim_customers.sql
      fct_orders.sql
      _core__models.yml
  utilities/
    all_dates.sql

Four notes explain why the tree looks this way.

• Subfolders for each source system under staging keep messy boundaries clear when you add a second customer system later.
• A double underscore in names (stg_jaffle_shop__orders) is a common convention that spells out layer prefix, source, and table.
• Settings files in YAML, a plain-text format for structured settings, sit beside the models. That way each group is documented and tested without one giant project-wide file.
• Mart subfolders by domain (core, finance, marketing) beat one flat dump of marts after the first year.

Naming conventions that reduce meetings

Avoid final, v2, new, use_this, and people’s names in model names. Versions belong in your git history, not in file names. If you must retire a mart, plan a rename with a warning period, and do not leave behind a permanent _old twin that everyone still uses.

The sources file is part of the layout

Declare your sources explicitly. Freshness checks and source-level tests hang off that declaration, so it earns its place in the layout. Here is a minimal shape.

version: 2

sources:
  - name: jaffle_shop
    database: raw
    schema: jaffle_shop
    tables:
      - name: orders
        columns:
          - name: id
            tests:
              - not_null
              - unique
      - name: customers
      - name: payments

Staging models should call source('jaffle_shop', 'orders') instead of hardcoding database.schema.table strings in every file. Hardcoded locations make it painful to promote a project from a test setup to production.

Materializations by layer (starter defaults)

A materialization is how dbt stores a model: as a view (a saved query) or as a real table. Defaults differ by warehouse cost and team taste, so treat the following as a sensible starting point for the lab.

• Staging models are views, which are cheap and always current with the upstream tables.
• Intermediate models are views, or ephemeral (never stored, just folded into the next query) when thin. Make them tables if they are reused heavily and expensive to compute.
• Marts are tables, or incremental tables (only new rows get added) when volume demands it.

Put these layer defaults in dbt_project.yml so individual models stay quiet unless they need an exception. Exceptions should be rare, and each one should carry a comment.

models:
  my_project:
    staging:
      +materialized: view
    intermediate:
      +materialized: view
    marts:
      +materialized: table

Worked example: orders lab layout decisions

The business goal is one trusted fct_orders and one trusted dim_customers for a small shop. The sources are customers, orders, and payments.

Grain sentences first

Dependency sketch

Sources feed staging only, intermediate reads staging, and marts read both staging and intermediate. No mart reads a raw source, and no staging model reads a mart. Because nothing loops back, the lineage stays readable in dbt docs and in code review.

What we refuse to do in version one

• No “god model” that joins every table for convenience.
• No dashboard-facing view of staging tables.
• No finance mart until the core grains are stable.
• No shared macro that hides a business rule behind a cute name until we need it twice.

Settings files, docs, and tests belong in the layout too

Keep each YAML settings file next to the models it describes. A _core__models.yml beside the marts is easier to work with than a 2,000-line file at the root of the project. In it, document three things.

• A model description that opens with the grain sentence.
• Column descriptions for the keys and metrics that consumers will see.
• Tests for uniqueness and not-null on primary keys, which the next post in the series covers in more depth.

A docs site does not replace a conversation, but it beats digging through old files to work out what happened. If your company also uses a data catalog, treat dbt docs as the source of truth for transforms and keep metric names aligned with Metrics that matter.

Macros, seeds, and snapshots: park them deliberately

• The macros/ folder is for repeated SQL patterns, and you should not turn a one-off into a macro.
• The seeds/ folder is for small reference CSV files such as country codes or status maps, and it is not for facts with a million rows.
• The snapshots/ folder is for when you need type-2 history (a saved record of how a value changed over time) and the source does not provide it. Snapshots are powerful and easy to misuse, so start without them unless tracking slowly changing dimensions is a real requirement.

A cluttered macros/ folder becomes a second language that only some people speak. Prefer readable SQL in the models until the repetition has hurt you twice.

Environments and folders (dev versus prod)

Layout inside models/ is not the same thing as your plan for environments, but the two interact. Use targets (dev and prod) and developer schemas so experiments never overwrite production tables. Custom schemas per layer can also help governance, for example staging in stg and marts in marts. Pick one pattern, write it in the project readme, and stick to it long enough for it to become habit.

Common mistakes

• Keeping one folder for everything. That is fast on day one and hostile on day ninety.
• Letting marts select from another team’s staging models without agreement, which creates hidden coupling.
• Putting business logic in staging and again in marts, so the two definitions drift apart.
• Exposing intermediate models to BI tools, so that temporary join tables quietly become permanent interfaces.
• Adding versions and adjectives to names. orders_final_new_correct is a cry for help.
• Skipping grain sentences, which leaves you with two “customer” models that follow different uniqueness rules.
• Keeping one giant YAML file at the top of the repo, which causes merge conflicts and a fear of editing docs.

How to practice this week

• On day 1, draw your current model graph on paper. Circle anything that jumps layers the wrong way, such as a mart reading staging or staging reading a mart.
• On day 2, write grain sentences for your ten most used models, and fix any that you cannot finish in one sentence.
• On day 3, propose a target folder tree in a short written proposal. Do not move files yet, and collect objections first.
• On day 4, move one source’s staging models into the new pattern, update the refs, and run just that part of the project.
• On day 5, add or fix sources.yml for that system, and document the materialization defaults in dbt_project.yml.

Quick recap

• A layout scales when people can predict where logic lives.
• Staging cleans sources, intermediate builds private blocks, and marts serve the business grain.
• Names carry layer and intent, while git carries history, so keep versions out of file names.
• Sources, YAML files, and materialization defaults are part of the architecture.
• Aim for one job, one grain sentence, and one obvious folder.

The next post in the series builds the first models and tests on this layout: staging SQL, refs into a mart, unique and not-null tests, and a small build you can explain in review.

Series notes

This is Part 1 of the dbt project lab. Related: SQL, Data quality, Data pipelines.

Sources

• dbt Docs: How we structure our dbt projects
• dbt Docs: Sources
• dbt Docs: Materializations
• dbt Docs: dbt_project.yml
• dbt Docs: How we structure our dbt projects: Staging

More definitions in Key Terms →

Merch on Etsy

Open store →

Git Version Control Shirt View on Etsy → Standard Deviation T-Shirt View on Etsy → You're On Mute Coffee Mug View on Etsy → Standard Deviation White Ceramic Mug View on Etsy →