A dbt project with twenty models is pleasant to work in regardless of how it is organised. At a hundred and fifty, structure is the difference between a change taking an hour and taking a day, and between a build failure you can diagnose and one you cannot. The decisions that matter are all made in the first month.
01Three layers, and no shortcuts between them
Staging models map one to one with source tables and do nothing but rename, cast and lightly clean. Intermediate models do the joins and logic. Marts are what people query. The rule that makes it work is that staging is the only layer allowed to reference a source, and nothing skips a layer.
The temptation is to reference a source directly from a mart because it is one table and the staging model feels like ceremony. Do that a few times and the dependency graph stops being a graph you can reason about, and a source schema change breaks things in places nobody can predict.
| Layer | May reference | Does | Never does |
|---|---|---|---|
| Staging | Sources only | Rename, cast, basic cleaning | Joins or business logic |
| Intermediate | Staging, intermediate | Joins, aggregation, logic | Reference sources |
| Marts | Intermediate, staging | Final shape for consumption | Reference sources |
02Naming that tells you what a thing is
Prefix by layer and name by grain: stg_stripe__charges, int_orders_joined_to_customers, fct_orders, dim_customers. It looks bureaucratic and it means anyone can tell from a filename what layer a model sits in, what it is built from and whether it is a fact or a dimension.
Include the source system in staging names. Two systems will eventually both have a customers table, and stg_customers versus stg_salesforce__customers is the difference between an obvious model and a five-minute investigation every time someone new arrives.
03Tests that catch real problems
Unique and not_null on every primary key, and relationships tests on every foreign key, cover the failures that silently corrupt downstream numbers - duplicated rows inflating totals, orphaned references dropping them. Those two categories account for most incorrect dashboards.
Beyond that, add tests that encode business rules: order totals are non-negative, statuses fall within a known set, a completed order always has a completion timestamp. Those catch upstream changes that are schema-valid and semantically wrong, which is the class of problem that survives longest before anyone notices.
| Test | Where | Catches |
|---|---|---|
| unique + not_null | Every primary key | Duplicates and missing rows |
| relationships | Every foreign key | Orphaned references |
| accepted_values | Status and enum columns | New values appearing upstream |
| freshness | Every source | Pipelines that stopped silently |
| Custom business rules | Marts | Semantically wrong but valid data |
04Incremental where it matters, not everywhere
Incremental models add real complexity: a unique key, a merge strategy, and reasoning about late-arriving data. That complexity is worth it on the handful of large fact tables where full rebuilds cost time and money, and it is pure overhead on a dimension table with fifty thousand rows.
Default to full refresh and convert only when a model's build time or cost is genuinely a problem. Teams that make everything incremental up front spend a lot of time debugging merge behaviour on tables that would rebuild in four seconds.
05Document the grain, above everything else
The single most valuable line of documentation on any model is its grain: one row per order, one row per customer per day. Most misuse of a model comes from someone assuming a different grain and producing a number that is wrong by a factor they never notice.
Descriptions on columns whose meaning is not obvious from the name come second. Documenting that customer_id refers to a customer is noise; documenting that revenue is net of refunds but gross of tax prevents a genuine reporting error.
Topics
Priya Iyer
Staff Engineer · SyncTrix
Writes about the engineering decisions behind production systems - architecture, delivery and the trade-offs that only show up at scale.
Building something like this?
SyncTrix engineers AI, SaaS, platform and cloud systems for enterprises and high-growth teams. Tell us what you're shipping and we'll scope it with you.
Talk to an engineer