dbt Project Structure & Conventions

The reasoning

`models/staging/` gets one subfolder per source system and one model per source table, named `stg_shopify__orders`. These are views, they do renaming, casting, light cleaning and column selection, and they never join. The value is that everything downstream sees one consistently-named version of each table, and there is exactly one place to change when the vendor renames a column.

`models/intermediate/` holds the pieces that are not the final answer but are used by more than one thing: a join that reassembles an entity from three staged tables, a deduplication step, a fan-out resolved before it can do damage. Name them `int_<entity>_<verb>`, like `int_orders_joined_to_payments`. Nobody outside dbt should query these, and if only one mart uses one, it probably belongs inside that mart.

`models/marts/` is organised by business domain — `finance/`, `marketing/` — not by source, because that is how consumers think and because a mart usually spans sources. These are tables, they are the contract with the business, and they carry the tests and documentation that matter. `fct_` for event-grain facts, `dim_` for entities.

Alongside those: `models/staging/<source>/_sources.yml` declaring the sources next to the models that use them, `snapshots/` for SCD2, `seeds/` for version-controlled reference data, `macros/`, and `tests/` for singular tests. The convention I would defend hardest is one staging model per source table — it is what stops the same cleaning logic existing in four places with three different opinions.

The reasoning

The strongest case is reuse. Two marts that both need orders joined to payments should not each write that join — they will drift, and one day the two dashboards will disagree by 0.4% and somebody will spend a day on it. One intermediate model, two refs, one definition.

The second case is isolating a hazard. A join that fans out, a source with duplicates that need a deterministic dedupe, a currency conversion that has to happen before any aggregation — putting these in their own model means you can test them at the point they happen and downstream models can assume the grain. A mart that silently double-counts because the fan-out was buried in a CTE is the failure this prevents.

The third is readability. A mart that is three hundred lines with eight CTEs is a model nobody will review properly. Lifting two of those CTEs into named models makes the DAG document the pipeline and gives each step a place for tests and a description.

Where the reviewer is right: a single-use intermediate model that just renames things, or one that exists because the layer was in the template. Every model is a build cost, an object in the warehouse and a thing to maintain, so the layer should earn its place per model rather than by policy. Ephemeral is the middle ground — the logic gets a name and a place in the DAG without a relation being built.

The reasoning

dbt has a flat namespace: two models called `orders` in different folders is an error, and `{{ ref('orders') }}` carries no folder information. So the name has to say what the thing is. Reading `{{ ref('stg_shopify__orders') }}` in a mart tells you the layer, the source system and the table without opening anything.

The double underscore between source and table is a real convention rather than a flourish. Sources and tables both contain single underscores, so `stg_shopify__order_items` unambiguously splits into system and table where `stg_shopify_order_items` does not.

The prefix also does work in the warehouse and in the logs. Every model becomes a relation, and a schema where names sort into `dim_`, `fct_`, `int_` and `stg_` groups is navigable in a BI tool's table picker. A run log of eighty lines is scannable when the prefix tells you which layer failed.

The one to be strict about is `fct_` and `dim_`, because those encode grain. A `fct_` model is one row per event at a stated grain; a `dim_` model is one row per entity. Getting that wrong in the name is how a dimension quietly becomes a fact table with duplicates in it. And whatever you choose, `dbt_project_evaluator` or a simple singular test can enforce the convention so it does not rot as people join.

The reasoning

Every model in `models/` can be dropped and rebuilt from its inputs. A snapshot cannot: it records what a row looked like at the times you happened to run it, and that history is not recoverable from the current state of the source. That difference is why it has its own directory, its own command, and why `dbt run` does not touch it.

The consequence people care about is that `--full-refresh` on a snapshot destroys history irreversibly. Some teams put a guard in CI for exactly this, and `dbt build --exclude resource_type:snapshot` in any job that might full-refresh is a reasonable habit.

On sources versus staging: I point snapshots at the source. The snapshot's job is to record what arrived, and if it reads a staging model then a change to that model's logic — a new filter, a different cast — silently changes what future snapshot rows look like, while the old ones keep the old shape. You end up with history that is a mixture of two definitions and no way to tell which row is which.

The counter-argument is real and worth acknowledging: snapshotting raw means snapshotting vendor column names and types, and every consumer then has to clean them. The usual resolution is to snapshot the source and put a staging model *on top of* the snapshot, so cleaning happens after the recording rather than before it.

The reasoning

A mart wired straight to a source skips the staging layer, so that source's cleaning and renaming exists in one model and nowhere else — and the next consumer will write it again, differently. Every source should be touched by exactly one staging model.

A rejoined DAG — thirty models refing one model — is not automatically wrong, but it tells you that model is load-bearing and probably under-tested. It is also the thing that will make every change to it a project-wide event, so it deserves a contract and the strictest tests you have.

Staging models refing staging models means the layer has grown logic it was not supposed to have. Marts refing marts several levels deep means the mart layer has become an undocumented intermediate layer, and the bottom of that chain is being rebuilt as a table for no reason.

Leaf models with no children and no exposure are the quiet one: nothing in dbt consumes them, so either something outside dbt does — in which case declare an exposure so the graph knows — or nothing does, and you are paying to build a table nobody reads. `dbt_project_evaluator` checks most of these automatically, which is worth mentioning because it turns a code review conversation into a CI job.

The reasoning

Read the plus as pointing at the arrows you want to follow. `+fct_revenue` adds everything upstream, which is what you want when you need to build a model in a fresh environment. `fct_revenue+` adds everything downstream, which is what you want when you have changed a model and need to know the blast radius. `+fct_revenue+` is both.

A number bounds the hops: `1+fct_revenue` is the model and its direct parents only, `2+` two levels. This matters on a project where `+model` on a leaf mart means the entire project — a bounded selector rebuilds enough context without rebuilding everything.

`@fct_revenue` is the one people cannot explain and the one CI needs. It selects the model, everything downstream of it, *and* all the ancestors of those downstream models. The reason is that if you have changed a model, you need to rebuild its children — and to build a child you need all of that child's parents, which may include models untouched by your change and absent from your environment.

Two related forms worth knowing: `dbt ls` takes exactly the same selectors and prints what would be selected without running anything, which is how you check a CI selector before committing it; and `--resource-type model` narrows the output, because a bare selector also returns tests, seeds and snapshots attached to those nodes.

The reasoning

The selector methods are the vocabulary: `tag:` for tags set in config, `path:` for a directory, `config.materialized:` and other config values, `source:` for everything from a source, `resource_type:` for models versus tests versus snapshots, `exposure:` for everything feeding one, and `package:` for models from a package.

Union and intersection are what let you compose them. `tag:finance,config.materialized:table` selects finance models that are also tables. `tag:daily path:models/staging` selects everything that is either tagged daily or lives in staging. Getting these the wrong way round on a scheduled job means either running far more than intended or running almost nothing, and both are quiet failures.

The graph operators combine with all of it: `tag:finance+` is every finance model and everything downstream of them, which is the shape of most real schedule definitions.

For anything you would have to explain in a comment, use a YAML selector instead. `selectors.yml` lets you name a selection — `nightly_finance` — with the full boolean structure written out readably, and then `dbt build --selector nightly_finance`. It is version-controlled, it is reviewable, and it stops the same 90-character string being pasted into four different scheduler configs.

The reasoning

dbt resolves `--select` first to get a set, then removes everything matched by `--exclude`. Both take the full selector syntax, so exclusion follows exactly the same rules — including the graph operators, which is the fix: `--exclude int_orders_enriched+` removes the model and everything downstream of it.

The trap is that the default behaviour looks reasonable and produces a bad state. The mart builds successfully, from yesterday's version of its parent, and reports success. Nothing fails, nothing warns, and the numbers are a mixture of today's staging and yesterday's intermediate.

Precedence is worth stating plainly: exclude wins. A node matched by both is out, regardless of how specifically the select matched it. There is no way to re-include something an exclude removed within the same invocation.

Where exclude genuinely earns its place: skipping an expensive model in CI (`--exclude tag:expensive`), skipping snapshots from anything that might full-refresh (`--exclude resource_type:snapshot`), and excluding a known-broken model while you fix it — in which case you almost always want the trailing plus, because building its children is exactly what you do not want.

The reasoning

The reason not to use folders is that a folder is one dimension and you will eventually want three. Layer, business domain, refresh cadence, criticality, PII-containing — the moment two of these disagree about where a model should live, the folder structure starts lying, and moving a model to fix it changes every path-based selector you have.

Tags are set in config, they compose, they can be applied at the folder level in `dbt_project.yml` and added to in the model, and they merge rather than override — so `+tags: ['finance']` on the folder plus `tags=['hourly']` in the model gives you both.

The refinement I would offer for scheduling specifically: tag the *entry points*, not every model, and use graph operators. `dbt build --select tag:hourly+` starting from a handful of tagged sources or staging models is far more maintainable than tagging forty models and keeping the tags in step as the graph changes.

And once there is more than one schedule, put the selections in `selectors.yml` with names, so the scheduler config references `--selector hourly` rather than embedding graph logic in a cron job that nobody reviews. That is the actual answer to 'something else'.

The reasoning

dbt does not care — YAML anywhere under your model paths is picked up, and the file name is irrelevant. So this is purely a maintainability decision, and the deciding factor is merge conflicts. A shared `schema.yml` in a folder that three people are actively working in conflicts constantly, and YAML conflicts are resolved badly because they look mergeable when they are not.

Per-model files — `fct_revenue.yml` next to `fct_revenue.sql` — also mean the documentation and tests are where you look for them. Someone opening the model finds its tests in the file beside it rather than searching a folder-level file for the right block.

The exception is sources. Those genuinely belong grouped, one `_shopify__sources.yml` per source system, because a source declaration covers many tables and the freshness config is usually shared. The leading underscore sorts them to the top of the directory listing, which is a small thing that helps.

Whatever you pick, be consistent, because inconsistency is what actually costs time: a project where half the tests are in per-model files and half are in a folder file means you have to check both every time. Enforce it in review, or with a lint rule if the team is big enough for that to be worth it.

The reasoning

Tests assert things about the rows that are there. If the loader failed and yesterday's rows are still sitting in the raw table, every `not_null` and `unique` test passes, every model builds, and the run is green. Freshness is the only check that asks how old the newest row is.

Configuration lives on the source: `loaded_at_field` names the column to measure — ideally the loader's ingestion timestamp rather than a business timestamp, because a business `updated_at` can legitimately be old — and `warn_after` / `error_after` give thresholds as a count and a period. Both can be set per table or inherited from the source.

Where it goes in the pipeline is the part interviewers push on. Running it *before* the build, as a gate, means a stale source fails fast and nothing is built on old data. Running it after tells you why the numbers look wrong but only once you have already published them. Fail-fast is right for critical sources; a warning is right for ones that are legitimately intermittent.

Two details worth having. `filter` on the freshness config lets you scope the max() to a partition on a huge table, so the check does not scan everything. And where a source has no reliable timestamp, `dbt source freshness` cannot help — the fallback is a metadata-based check, which some adapters support natively, or a volume test that alerts when today's row count is far from the usual.

The reasoning

`config: contract: {enforced: true}` plus a full column list with `data_type` on each turns the YAML from documentation into an assertion. Before writing anything, dbt compares the model's actual columns and types against the declaration and fails the build on any mismatch — a missing column, an extra one, a type that changed.

The timing is the whole value. Without a contract, dropping a column is a green run and a broken dashboard tomorrow morning. With one, it is a failed pull request with a message naming the column, and the person who needs to know finds out while they are still holding the context.

Contracts also carry constraints — `not_null`, `primary_key`, `check` — which are pushed down to the warehouse where the warehouse enforces them, and are otherwise validated by dbt. Enforcement varies a lot by adapter, so check what yours actually applies rather than assuming; on several warehouses a primary key constraint is informational only.

The cost is rigidity, and that is the point. Adding a column to a contracted model means updating the YAML in the same commit. That is exactly the friction you want on a table other teams depend on, and exactly the friction you do not want on a staging model nobody outside dbt reads — so contract the boundary, not the whole project.

The reasoning

dbt lets one model have multiple versions defined in YAML under `versions:`, each with its own contract and its own optional SQL file. `{{ ref('dim_customers', v=2) }}` selects a version explicitly; a bare ref gets whichever version is marked `latest_version`. Both versions are real relations, suffixed `_v1` and `_v2`, so consumers reading the warehouse directly can move at their own pace.

The migration is then a normal deprecation: build v2 alongside v1, tell the consumers, set `deprecation_date` on v1 so dbt warns anyone still referencing it, wait for the window, delete v1 and its YAML.

`access` belongs in the same answer. Marking a model `access: private` means only models in the same group can ref it, and `protected` restricts cross-project refs. That is how you get the freedom to refactor: if only the boundary models are public, everything behind them is yours to change without a version dance.

The honest caveat: versioning has a real cost — two relations, two builds, two sets of tests — so it is for genuine published interfaces, not for every mart. Most breaking changes are better handled by a conversation and a single coordinated commit; versions are for when the consumers are on a different team, a different repo, or a different release cycle.

The reasoning

An exposure is a YAML declaration of something outside dbt that depends on your models: a dashboard, a report, an ML application, a reverse-ETL sync. It names the models it uses via `depends_on: [ref('fct_revenue')]`, and carries a type, an owner with an email, a URL and a description.

The immediate payoff is impact analysis in both directions. `+exposure:revenue_dashboard` gives you everything that must be built for that dashboard to be correct, which is a legitimate scheduling selector. And when you are about to change `fct_revenue`, the docs site and the graph tell you three dashboards and a named owner are downstream — so the conversation happens before the change rather than after.

The second payoff is `dbt build --select +exposure:*`, which is a defensible definition of "what actually matters": the models that feed something a human looks at. Everything else in your project is either intermediate or a candidate for deletion, and that is a genuinely useful thing to be able to compute.

The honest limitation is that exposures are hand-maintained, so they rot. The mitigation is to generate them where you can — some BI tools expose their lineage through an API, and there are packages that build exposures from Looker or Tableau metadata — and to treat adding one as part of the definition of done for shipping a dashboard.

The reasoning

The reason it drifts is that each consumer can express it, so each consumer does. Any solution has to remove the ability, not just the inclination — which is why "we agreed on a definition" always loses to the next person who needs it sliced differently.

The strongest available mechanism is a semantic layer metric: define the measure and its dimensions once in YAML, and have BI query the metric rather than the table. Every consumer gets the same SQL because the same service generates it, and a change is a reviewed commit rather than an edit in a dashboard.

Without that, the practical version is a single mart at the finest grain anyone needs, with the metric as a column, and a team rule that BI tools may filter and sum but may not define. The rule holds only if the mart genuinely serves everyone's grain — the moment someone needs a cut you did not build, they will recompute it, and you are back where you started.

The failure to name explicitly is pre-aggregating too early. A mart of `active_customers_by_month` cannot answer `by month and region`, so a second mart appears with its own definition. Model the grain, not the report.

The reasoning

`+schema:` does not set the schema, it sets the *custom schema*, which is passed to the `generate_schema_name` macro along with the target. The built-in implementation returns `<target_schema>_<custom_schema>` when a custom schema is given, and the target schema when it is not.

The reason is developer isolation. If `+schema: marts` meant literally `marts`, then every developer's local run and every CI run would write to the same production schema, and two people building the same model at the same time would fight. Concatenation means my dev target `dbt_vijay` gives me `dbt_vijay_marts` and yours gives you yours, automatically, with no per-developer configuration.

The common override keeps that behaviour in dev and drops the prefix in production: if the target is prod, return the custom schema as-is; otherwise fall back to the default concatenation. Define your own `generate_schema_name` in `macros/` and dbt uses yours instead of the built-in — dbt resolves macros from your project before the global namespace, which is the general mechanism for overriding anything dbt ships.

Two cautions. This is a footgun if you get it wrong: an override that always returns the custom schema means every developer writes to production. And there is a matching `generate_database_name` for the same trick at the database level, plus `generate_alias_name` for the relation name — all three are the same pattern.

The reasoning

Be honest about the audience. The docs site's real value is the lineage graph and column-level detail, and the people who need those are engineers and analysts doing impact analysis — "what feeds this", "what breaks if I change it". For that it is very good, and `dbt docs serve` during a refactor is a legitimate workflow.

Business users will not go to a second site. `persist_docs: {relation: true, columns: true}` writes your model and column descriptions into the warehouse as comments, and most BI tools surface those in their field picker. That puts the description at the moment of use, which is the only place it gets read.

What makes docs rot is treating them as a separate task. The things that actually work: doc blocks so a description exists once rather than eleven times; a CI check that new models have a description and a test, which turns it into part of the definition of done; and descriptions written for the reader who is confused rather than for coverage — "one row per order line" and "excludes cancelled orders" beat "the orders fact table".

The one piece I would fight for regardless of the site is model-level grain statements. "One row per X" on every model is a sentence that prevents an entire class of bug, and it is the first thing I look for when reviewing somebody else's model.

The reasoning

First, check the symptom is what it looks like. "Every pull request runs the whole CI" is a slim CI problem, not a project size problem: `state:modified+` with a deferred production manifest cuts a 900-model CI to the handful a change touches, and that alone fixes most of the pain. Ownership confusion is fixed by folders with code owners and `access: private` on models that are not meant to be shared.

The real reasons to split are organisational: teams that need to deploy on different schedules, a team whose models must not be blocked by another team's failing test, or a genuine security boundary where one team must not read another's raw data. Those are not solvable inside one project.

dbt Mesh is the mechanism. Each project publishes models marked `access: public` under a group with an owner, and downstream projects ref them across the boundary with `{{ ref('finance', 'fct_revenue') }}`, resolved through the upstream project's manifest. Contracts and versions become load-bearing here rather than optional, because the consumer is now a different repository on a different release cycle.

The cost is honest and worth stating: you no longer have one graph. Orchestration across projects is your problem, a change that spans two projects is two pull requests, and debugging lineage means looking in two places. The failure mode is splitting along a technical line — staging in one project, marts in another — which gives you all of the coordination cost and none of the independence.

The reasoning

Grain first, because it is the source of the worst bugs. Does the model state its grain in a description, and do the joins preserve it? A join to a table that has more than one row per key silently multiplies your measures, and no test you add later will tell you which number is right. I want a `unique` test on the declared key, and I want to see it fail if I break the join.

Then blast radius. `dbt ls --select <changed_model>+` in the pull request description, or better, CI that runs it. Changing a staging model that forty marts depend on is a different review from changing a leaf. If the model is contracted or has consumers via exposures, that is a conversation, not a merge.

Then cost and materialization. Is a new mart a table when it should be a view, or an incremental model that will silently drift? Is a staging model doing something expensive that four downstream models will each trigger? Has anyone checked what this adds to the nightly run — a model that takes twenty minutes is a real decision.

Then the conventions: right layer, right prefix, no hardcoded relation names, no `select *` from a source, a description that says what the rows are, and no business logic that has quietly appeared in staging. Most of these can be enforced by `dbt_project_evaluator` or a linter in CI, and pushing them there is better than spending human review on them — it leaves the review for grain, blast radius and cost, which are the parts a machine cannot check.

The reasoning

The convention is that sources are touched by exactly one staging model each, and everything else goes through it. The cost of breaking it is not this pull request — it is the second consumer, who finds no staging model, adds their own `source()` call, and casts the timestamp slightly differently. Now two marts disagree and neither is obviously wrong.

The objection is that a staging model for one column is overhead. It is small overhead: a view over a source, costing nothing to build, existing so the vendor's column names appear in exactly one file. When the vendor renames that column, you change one model rather than grepping.

There is a lineage argument too. Impact analysis is built on the graph, and `dbt ls --select source:shopify+` is only meaningful if the models downstream of a source are a layer you can reason about. A mart hanging directly off a source is an edge that skips the structure your selectors assume.

The one exception I would accept is a genuinely one-off, throwaway analysis in `analyses/`, which is not part of the build. In `models/`, if it is worth building nightly it is worth staging first.