1. Model the project as a journey from source-conformed to business-conformed data

Rule: Your dbt DAG should intentionally move from raw, source-shaped tables to clean business concepts. The core structure should be staging → intermediate → marts, not a flat collection of SQL files.

Why it matters: If the project does not encode this progression, every downstream BI dashboard, metric, and LLM-generated query has to guess whether a model is raw-ish, cleaned, reusable, or consumer-ready. That creates inconsistent joins, duplicated logic, and ambiguous definitions. dbt structure should be deliberately framed as the movement from many narrow source-conformed models toward fewer, wider business-conformed models.

Positive example:

models/
  staging/
    stripe/
      stg_stripe__payments.sql
  intermediate/
    finance/
      int_payments_pivoted_to_orders.sql
  marts/
    finance/
      orders.sql

Negative example:

models/
  orders_cleaned.sql
  orders_final.sql
  orders_dashboard.sql
  stripe_logic.sql

Warning: The folder structure is not cosmetic. It becomes a selection interface, a governance boundary, and a mental model for contributors.

2. Group staging models by source system, not by business team

Rule: In models/staging, subdirectories should represent source systems such as stripe, salesforce, jaffle_shop, or google_analytics.

Why it matters: Staging is where source data is standardized. Grouping staging by business function creates duplicate “atoms” before the project has established a single source of truth. Avoid marketing or finance folders in staging because that invites competing definitions of basic objects like orders or customers.

Positive example:

models/staging/stripe/stg_stripe__payments.sql
models/staging/jaffle_shop/stg_jaffle_shop__orders.sql

Negative example:

models/staging/finance/orders.sql
models/staging/marketing/orders.sql

Warning: This is one of the fastest ways to break semantic consistency. Once two teams stage “orders” differently, every metric built on top becomes politically and technically fragile.

3. Keep staging models boring: rename, recast, clean, categorize — but do not join or aggregate

Rule: A staging model should normally select from one source table and perform only reusable transformations: renaming, type casting, simple calculations, and basic categorizations.

Why it matters: Staging models are the reusable atomic layer. If you join or aggregate here, you silently change grain too early and remove optionality from downstream models. The recommended pattern is to select from one source, rename fields, recast types, and then build all later models on top of those standardized models.

Positive example:

select
    idas payment_id,
    orderidas order_id,
    amount/100.0as amount,
    created::timestampas created_at
from {{ source('stripe','payment') }}

Negative example:

select
    orders.customer_id,
    sum(payments.amount)as lifetime_value
from {{ source('stripe','payment') }}as payments
join {{ source('app','orders') }}as orders
on payments.order_id= orders.id
groupby1

Warning: Aggregation in staging is a grain violation. Joins in staging are usually a fanout risk. Use a base sublayer only when staging genuinely requires a source-level cleanup pattern, such as delete tables or unioning identical source structures.

4. Use source() only at the raw boundary and ref() everywhere else

Rule: Raw data should be declared as dbt sources and referenced through source(). Models should reference other models only through ref().

Why it matters: ref() lets dbt infer dependency order and keeps development and production environments isolated correctly. Direct database references bypass lineage, break environment portability, and make model dependencies invisible. Use ref() instead of direct relation references and limit raw references to one place.

Positive example:

from {{ref('stg_stripe__payments') }}

Negative example:

from analytics_prod.staging.stg_stripe__payments

Warning: Hard-coded relations are especially dangerous in CI, dev schemas, dbt Mesh, and downstream package consumption because the DAG no longer reflects the actual dependency.

5. Break complex transformations into intermediate models when they change grain, repeat logic, or hide fanout

Rule: Intermediate models should isolate meaningful transformation steps, especially joins, pivots, fanouts, aggregations, and reusable CTEs.

Why it matters: Complex models should be broken apart when CTEs are duplicated or when a CTE changes the grain of the data. Grain-changing logic deserves its own model because it should be testable and inspectable before it gets mixed into a mart.

Positive example:

int_payments_pivoted_to_orders.sql

This name tells you the input concept, the operation, and the output grain.

Negative example:

int_finance_logic.sql

This hides the transformation and gives no clue whether the model aggregates, joins, deduplicates, or fans out rows.

Warning: LLM-generated analytics queries are very sensitive to ambiguous grain. A model named customer_metrics could mean one row per customer, customer-day, customer-month, or customer-product unless the dbt layer makes grain explicit.

6. Name intermediate models with verbs, not vague nouns

Rule: Intermediate models should describe the transformation they perform: pivoted, aggregated_to, joined, fanned_out_by, deduplicated, ranked, sessionized.

Why it matters: Intermediate models should be named around verbs because this layer exists to perform purpose-built transformations on the way to marts. A good intermediate model name tells reviewers what changed without making them read the SQL first.

Positive examples:

int_payments_pivoted_to_orders.sql
int_orders_aggregated_to_customers.sql
int_events_sessionized.sql

Negative examples:

int_orders.sql
int_model_2.sql
finance_intermediate.sql

Warning: If an intermediate model cannot be named with a clear verb, it probably has too many responsibilities.

7. Build marts as business entities with one clear grain

Rule: A mart should represent a business entity or concept, such as orders, customers, payments, or accounts, with one row representing one instance of that entity.

Why it matters: Marts should be treated as business-defined entities; time-based rollups like orders_per_day are usually metrics, not pure marts. Avoid departmental duplicates like finance_orders and marketing_orders unless they are genuinely separate business concepts.

Positive example:

models/marts/finance/orders.sql
models/marts/marketing/customers.sql

Negative example:

finance_orders.sql
marketing_orders.sql
sales_orders.sql

Warning: Department-prefixed marts often encode political ownership rather than semantic difference. If finance revenue and product revenue differ, name the concepts precisely, such as tax_revenue versus recognized_revenue.

8. Denormalize marts only when you are not relying on the dbt Semantic Layer

Rule: Without the dbt Semantic Layer, wide denormalized marts are useful because BI users can query fewer joins. With the Semantic Layer, keep marts more normalized so MetricFlow has flexible dimensions and joins.

Why it matters: dbt projects often build highly denormalized datasets, but this limits the dimensionality available to MetricFlow. A more normalized approach is recommended when using the Semantic Layer.

Positive without Semantic Layer:

customers
  customer_id
  first_order_date
  lifetime_value
  number_of_orders

Positive with Semantic Layer:

customers
orders
payments

Then define semantic models and metrics on top.

Negative with Semantic Layer:

customer_metrics_obt

A single big rollup may be convenient for one dashboard but weak for reusable metrics.

Warning: This is a real architectural fork. Denormalized marts optimize human SQL and dashboard speed. Normalized semantic models optimize metric reuse and query generation flexibility.

9. Choose materializations by access pattern, not habit

Rule: Materialization should follow how the model is used:

staging      → view
intermediate → ephemeral or restricted view
marts        → table or incremental

Why it matters: Staging models should default to views because they are rarely queried directly and should stay fresh; intermediate models to ephemeral because they are implementation details; and marts to tables or incremental models because they are directly consumed and need performance.

Positive example:

models:
  jaffle_shop:
    staging:
      +materialized: view
    intermediate:
      +materialized: ephemeral
    marts:
      +materialized: table

Negative example:

models:
  jaffle_shop:
    staging:
      +materialized: table
    marts:
      +materialized: view

Warning: Ephemeral models keep the warehouse clean but make debugging harder because they are compiled into downstream SQL. For complex intermediate logic, restricted views are often the better operational choice.

10. Configure defaults at the folder level and override only exceptions

Rule: Put default materializations, schemas, and other common configs in dbt_project.yml by directory. Use model-level config only for exceptions.

Why it matters: Project-level configuration is preferred because configs cascade, with more specific scopes overriding broader ones. This keeps the project DRY and avoids copy-pasted config blocks.

Positive example:

models:
  jaffle_shop:
    staging:
      +materialized: view
    intermediate:
      +materialized: ephemeral
    marts:
      +materialized: table
      finance:
        +schema: finance

Negative example:

{{ config(materialized='table') }}

copied into every mart model.

Warning: Overusing tags to compensate for weak folder structure creates maintenance debt. Folders should be the primary grouping and selection mechanism; tags should represent exceptions.

11. Treat names as semantic contracts

Rule: Naming conventions should encode source, layer, entity, grain, and field meaning.

Recommended conventions:

stg_[source]__[entity]s.sql
int_[entity]s_[verb]s.sql
marts: [business_entity].sql
primary key: [object]_id
boolean: is_ / has_
timestamp: [event]_at
date: [event]_date

Also recommended: plural model names, snake_case, no abbreviations, no reserved words, and consistent key names across models.

Positive example:

customer_id,
is_completed_payment,
created_at,
created_date

Negative example:

id,
completed,
created,
cust_id

Warning: Bad names are not just a readability issue. They degrade BI self-service and LLM-based analytics because query generators infer meaning from names, joins, and grain.

12. Use YAML per folder, not one giant project file

Rule: Keep model and source YAML close to the models they describe. Use _[directory]__models.yml and, for staging folders, _[directory]__sources.yml.

Why it matters: A single monolithic YAML file centralizes config but makes specific tests, descriptions, and source definitions hard to find. One YAML file per model is searchable but creates too much file churn. Folder-level YAML is the best default balance.

Positive example:

models/staging/stripe/_stripe__sources.yml
models/staging/stripe/_stripe__models.yml
models/marts/finance/_finance__models.yml

Negative example:

models/schema.yml

containing every model, source, test, and description.

Warning: YAML structure affects test discoverability. If contributors cannot quickly find where a model is documented and tested, they will skip both.

13. Put tests where the modeling risk is, especially around keys and grain changes

Rule: Every important model should have a primary key, and transformations that change grain should be tested aggressively.

Why it matters: Each model should have a primary key, and CTEs that change grain should be broken out because those transformations are useful to test. Singular tests are valuable when testing interactions across specific models.

Positive examples:

models:
  - name: orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null

A fanout model should also test expected row counts or uniqueness at the new grain.

Negative example:

select*
from orders
join order_itemsusing (order_id)

with no test proving whether the output is one row per order or one row per order item.

Warning: Most metric bugs are not caused by obviously wrong SQL. They come from unnoticed grain changes, duplicate joins, and null-handling assumptions.

14. Use seeds only for small static lookup data, not ingestion

Rule: Seeds are for small CSV-backed lookup tables that do not exist in source systems. They are not a replacement for an EL tool.

Why it matters: Seeds are intended for lookup tables, not for loading source data into the warehouse.

Positive example:

seeds/utm_campaign_mappings.csv
seeds/zip_code_to_state.csv

Negative example:

seeds/orders.csv
seeds/payments.csv

used as recurring source ingestion.

Warning: Using seeds as ingestion hides lineage and operational freshness. dbt should transform data already in the warehouse, not act as your data loader.

15. Use Mesh only when ownership boundaries are real

Rule: Split dbt projects by durable domain ownership, governance needs, or scale — not merely by use case like “BI versus ML.”

Why it matters: dbt Mesh is useful for managing complexity across multiple interconnected projects, but interfaces should be defined based on teams, jobs, lineage, and selectors before splitting. Use groups and access controls first, with the most private access that preserves functionality.

Positive example:

customer_domain_project
finance_domain_project
foundational_sources_project

with public/protected interfaces.

Negative example:

bi_project
ml_project
executive_dashboard_project

all rebuilding the same customer and revenue concepts differently.

Warning: Mesh introduces intentional friction. That is good for governed interfaces, but wasteful if your team is just trying to organize a small project.

16. Use model contracts, access, and versions for stable cross-team interfaces

Rule: Once other teams or projects depend on a model, treat that model as an interface: define ownership, restrict access, use contracts, and version breaking changes.

Why it matters: Model contracts enforce structure and types, model versions manage breaking changes and migration windows, and access modifiers control whether models are private, protected, or public.

Positive example:

models:
  - name: orders
    config:
      access: public
      contract:
        enforced: true

Negative example:

models:
  - name: int_orders_joined_to_payments
    config:
      access: public

exposing implementation logic as a cross-team dependency.

Warning: Public models should be boring, stable, and documented. Do not publish intermediate implementation details just because another team asked for them once.

17. Keep semantic models and metrics in an intentional file structure

Rule: For the dbt Semantic Layer, either co-locate semantic YAML with each mart or create a dedicated models/semantic_models/ folder. For existing large projects, default to the dedicated folder.

Why it matters: Both options are valid; a dedicated subfolder is preferable when migrating existing projects because it makes it easier to see which marts have been codified into the Semantic Layer. Semantic models and metrics must live under model-paths, or the semantic manifest will be empty.

Positive example:

models/semantic_models/sem_orders.yml
models/semantic_models/sem_customers.yml

Negative example:

semantic_layer/orders.yml

outside model-paths.

Warning: This is not just organization. A misplaced semantic YAML file can mean your metrics are not recognized at all.

18. Use SQL style rules to protect review quality, not aesthetics

Rule: Adopt a style guide and automate it with SQLFluff or a formatter. The point is not prettiness; it is reducing review noise and making semantic mistakes visible.

Why it matters: Codify SQL style, use SQLFluff, avoid abbreviations, use explicit aliases, prefix columns when joining, and make join types explicit.

Positive example:

select
    orders.order_id,
    customers.customer_id

from orders

leftjoin customers
on orders.customer_id= customers.customer_id

Negative example:

select
    o.id,
    c.id
from orders o
join customers c
on o.customer= c.id

Warning: Ambiguous aliases and implicit joins make code review weaker. They also make it harder to spot fanout, wrong join direction, or accidental inner joins.

19. Separate development and production environments

Rule: Developers should run against a development target; production jobs should run against a production target.

Why it matters: Use separate development and production environments through dbt targets, plus version control and pull requests before merging to the production branch.

Positive example:

local development → dev target
scheduled job     → prod target

Negative example:

analyst laptop writes directly to production schemas

Warning: Environment discipline is part of semantic reliability. A trusted metric is not trusted if anyone can overwrite its production relation while experimenting.

20. Document deviations from the default conventions

Rule: Consistency matters more than the exact convention, but deviations must be explicit and documented.

Why it matters: The source repeatedly frames its conventions as strong defaults, not universal law. The important part is that teams understand why a structure exists and when it is safe to deviate.

Positive example:

We materialize selected intermediate models as views because they are used for
debugging complex grain-changing joins. They live in a restricted schema and
are not available to end users.

Negative example:

Some intermediate models are tables because the first developer preferred that.

Warning: Undocumented exceptions become fake patterns. New contributors will copy them, and the project will drift into inconsistency.

21. Design the mart first, then refactor upstream

Rule: Do not build dbt models strictly from left to right through the DAG. Start with the business output you need, prove the shape, then refactor logic back into staging and intermediate layers.

Why this matters: dbt project structure follows DAG order, but development usually should not. The recommended workflow is to mock the target output first, write the SQL needed to produce it, identify dependencies, then stage missing source tables and extract reusable logic upstream.

Good pattern:

1. Mock the target dashboard table or metric in a spreadsheet.
2. Write ugly-but-correct SQL to prove the output.
3. Identify source tables and grain.
4. Create missing staging models.
5. Move repeated or grain-changing logic into intermediate models.
6. Leave the mart clean and entity-grained.

Bad pattern:

1. Stage every source table.
2. Build many intermediate models speculatively.
3. Hope the final mart satisfies the stakeholder.

Warning: Premature DAG design creates beautiful unused models. The mart should justify the upstream abstractions.

22. Treat CTEs as miniature models

Rule: Inside a model, CTEs should tell the same story that the DAG tells across models: import, transform, join, aggregate, output.

Why this matters: Descriptive CTE names matter. A CTE should usually perform one logical unit of work, especially when the transformation affects grain or join behavior.

Good example:

with

ordersas (

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

),

payments_pivoted_to_order_grainas (

select
        order_id,
        sum(amount)as total_amount

from {{ref('stg_stripe__payments') }}

groupby1

),

orders_joined_to_paymentsas (

select
        orders.order_id,
        orders.customer_id,
        payments_pivoted_to_order_grain.total_amount

from orders

leftjoin payments_pivoted_to_order_grain
on orders.order_id= payments_pivoted_to_order_grain.order_id

)

select*from orders_joined_to_payments

Bad example:

with xas (...),
yas (...),
finalas (...)
select*from final

Warning: If a CTE name cannot explain what changed, the model probably hides business logic.

23. Pull duplicated CTEs into intermediate models

Rule: If the same CTE appears in multiple models, it should usually become an intermediate model.

Why this matters: Repeated CTEs are semantic drift waiting to happen. One analyst changes the logic in one mart but not another, and suddenly “completed payments” or “active customers” means two different things.

Good pattern:

int_payments_filtered_to_completed.sql

used by:

orders.sql
customers.sql
revenue.sql

Bad pattern:

where statusin ('success','completed')

copy-pasted into five marts with small variations.

Warning: Copy-pasted CTEs are worse than copy-pasted SQL style violations. They duplicate definitions.

24. Aggregate before joining when possible

Rule: When joining fact-like tables, aggregate to the intended join grain before the join.

Why this matters: Aggregate on the smallest useful dataset before joining. This improves performance and reduces fanout risk.

Good example:

payments_aggregated_to_order_grainas (

select
        order_id,
        sum(amount)as total_amount

from payments

groupby1

)

select
    orders.order_id,
    payments_aggregated_to_order_grain.total_amount

from orders

leftjoin payments_aggregated_to_order_grain
on orders.order_id= payments_aggregated_to_order_grain.order_id

Bad example:

select
    orders.order_id,
    sum(payments.amount)as total_amount

from orders

leftjoin payments
on orders.order_id= payments.order_id

leftjoin order_items
on orders.order_id= order_items.order_id

groupby1

Warning: Joining two one-to-many relationships before aggregation is the classic fanout trap. It produces inflated metrics that look plausible.

25. Always make join direction obvious

Rule: Prefer left-to-right join logic. Start from the grain-defining table, then join supporting data onto it.

Why this matters: Avoid right join and use explicit join types. The table in the from clause should usually define the output grain.

Good example:

from orders

leftjoin customers
on orders.customer_id= customers.customer_id

This says: “one row per order, with customer attributes added.”

Bad example:

from customers

rightjoin orders
on customers.customer_id= orders.customer_id

The same result may be possible, but the model is harder to reason about.

Warning: LLM query generation and human reviewers both infer grain from the from table. Make that inference easy.

26. Prefix columns whenever a model joins tables

Rule: If a model joins two or more relations, every selected column should be prefixed with its source relation or CTE name.

Why this matters: Prefix columns in joined models. This prevents ambiguous references and makes lineage visible inside the SQL.

Good example:

select
    orders.order_id,
    orders.customer_id,
    customers.customer_name

from orders

leftjoin customers
on orders.customer_id= customers.customer_id

Bad example:

select
    order_id,
    customer_id,
    customer_name

after joining several CTEs.

Warning: Unprefixed columns make it harder to detect accidental column shadowing, especially when source systems reuse generic names like id, status, created_at, or type.

27. Avoid table aliases unless the alias is genuinely clearer

Rule: Do not use cryptic aliases like o, c, p, or fct.

Why this matters: Aliases reduce readability and should be avoided. In dbt, clarity usually beats short SQL.

Good example:

from orders

leftjoin customers
on orders.customer_id= customers.customer_id

Bad example:

from orders o

leftjoin customers c
on o.customer_id= c.customer_id

Trade-off: Short aliases may be acceptable in very long SQL expressions, but they should be meaningful. current_orders is better than co; customers is better than c.

28. Use union all by default

Rule: Use union all unless you explicitly need deduplication.

Why this matters: Prefer union all over union. union performs duplicate removal, which adds compute and can hide upstream data-quality issues.

Good example:

select*from shopify_us_orders

unionall

select*from shopify_eu_orders

Bad example:

select*from shopify_us_orders

union

select*from shopify_eu_orders

Warning: If duplicate rows appear, fix or test the duplicate condition explicitly. Do not let union silently erase evidence.

29. Use import CTEs to limit scan size

Rule: Top-level import CTEs should not blindly select * from large upstream models once the model is mature. Select only the columns and rows needed.

Why this matters: Place import CTEs at the top of the file and limit scanned data where possible.

Good example:

ordersas (

select
        order_id,
        customer_id,
        order_date,
        order_total

from {{ref('orders') }}

where order_date>='2024-01-01'

)

Bad example:

ordersas (

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

)

in a heavy mart that needs only four columns.

Trade-off: select * is acceptable during early development or in simple staging models. In expensive downstream transformations, it becomes wasteful.

30. Use the final select * from final_cte pattern while developing

Rule: End each model with a simple select * from the final output CTE.

Why this matters: This pattern makes debugging easier. You can temporarily change the last line to inspect an earlier CTE without rewriting the model.

Good example:

select*from orders_joined_to_payments

Debugging move:

select*from payments_aggregated_to_order_grain

Bad example:

select
    order_id,
    customer_id,
    total_amount
from (
    ...
)

Warning: Deeply nested SQL makes dbt models harder to audit. Prefer named CTEs that expose each transformation step.

31. Materialize intermediate models as views when debugging complexity

Rule: Ephemeral intermediate models are a good default, but complex or suspicious intermediate logic should be materialized as a restricted view during development or permanently if debugging value outweighs clutter.

Why this matters: Ephemerals keep the warehouse clean but make troubleshooting harder because they are interpolated into downstream SQL.

Good use of ephemeral:

Small reusable transformation
No need to inspect output
Low complexity

Good use of restricted view:

Changes grain
Contains window functions
Performs deduplication
Feeds several marts
Often breaks during development

Warning: A tidy warehouse is not worth opaque logic. If a model is critical to metric correctness, being able to inspect it may matter more than hiding it.

32. Temporarily build a failing model chain as tables to locate warehouse errors

Rule: When an error appears in a downstream mart but likely originates upstream, temporarily materialize the dependency chain as tables.

Why this matters: Troubleshoot by building specific model chains as tables so the warehouse throws the error where it actually occurs.

Good debugging move:

stg_orders       → table temporarily
int_order_items  → table temporarily
orders           → table temporarily

Bad debugging move:

Only inspect the final mart SQL and assume the error originates there.

Warning: View stacks and ephemerals can make database errors misleading. The visible failure point is not always the logical failure point.

33. Keep warehouse UX clean

Rule: The warehouse output is part of the user experience. Do not expose implementation models in schemas used by analysts, BI tools, or LLM agents.

Why this matters: The warehouse is one of the interfaces to the knowledge graph encoded in dbt. Intermediate models should not be exposed in the main production schema.

Good pattern:

analytics.finance.orders
analytics.marketing.customers
analytics_intermediate.finance.int_payments_pivoted_to_orders

Bad pattern:

analytics.orders
analytics.customers
analytics.int_payments_pivoted_to_orders
analytics.stg_stripe__payments
analytics.tmp_joined_orders_v2

Warning: BI users and LLM agents do not reliably know which tables are safe. If you expose staging and intermediate models in the same schema as marts, people will query the wrong thing.

34. Use analyses for audit and migration SQL, not production logic

Rule: Store version-controlled, Jinja-enabled audit queries in analyses, especially during migrations or reconciliations.

Why this matters: Use analyses for queries that should be version-controlled but not built into the warehouse. Audit-helper style workflows are a typical fit.

Good use:

analyses/compare_legacy_orders_to_dbt_orders.sql
analyses/audit_revenue_migration.sql

Bad use:

analyses/customer_lifetime_value.sql

where the query is actually needed by dashboards.

Warning: If an analysis becomes a recurring dependency, promote it to a model and test it.

35. Document macros once they become shared infrastructure

Rule: Any macro used by multiple models should have documented purpose and arguments.

Why this matters: Create _macros.yml and document macros when they are ready for use.

Good example:

macros:
  - name: cents_to_dollars
    description: Converts integer cents into decimal currency.
    arguments:
      - name: column_name
        type: column
        description: Column containing the amount in cents.

Bad example:

{{ cents_to_dollars('amount') }}

with no explanation of rounding, null behavior, currency assumptions, or type returned.

Warning: Macros hide logic. Hidden logic without documentation becomes a semantic black box.

36. Prefer packages for common tests and utilities before writing custom logic

Rule: Before writing a singular test or custom macro, check whether a mature dbt package already covers the use case.

Why this matters: Packages such as dbt-utils, dbt-expectations, dbt-codegen, and dbt-audit-helper cover common project needs.

Good pattern:

Use dbt_utils.unique_combination_of_columns
instead of writing a one-off uniqueness test for composite grain.

Bad pattern:

Every team writes its own slightly different accepted-values, recency, or relationship test.

Warning: Custom tests are useful, but they create maintenance obligations. Use them when the business rule is genuinely specific.

37. Use Codegen after you understand staging manually

Rule: Learn to write staging models by hand, then automate boilerplate staging and source YAML with Codegen.

Why this matters: Codegen is recommended for every project, but only after developers understand the staging pattern.

Good pattern:

First few source tables: write manually.
After pattern is clear: generate staging boilerplate.
Then customize exceptions.

Bad pattern:

Generate 200 staging models no one understands.

Warning: Codegen accelerates conventions. It does not design them for you.

38. Use snapshots only for true Type 2 history

Rule: Use snapshots when source data is destructively updated and you need historical versions.

Why this matters: Snapshots are the tool for creating Type 2 slowly changing dimension records from Type 1 source data.

Good use:

Track customer tier changes over time.
Track account owner changes over time.
Track subscription status history when the source overwrites status.

Bad use:

Snapshot every table just in case.

Warning: Snapshots add storage, complexity, and interpretation burden. If the source already provides historical events, model those events instead.

39. Use groups and access modifiers to encode ownership

Rule: As a project grows, define groups and set model access intentionally: private for implementation details, protected for cross-team reuse inside a project boundary, public for stable interfaces.

Why this matters: Groups, private models, and explicit cross-team collaboration matter most in dbt Mesh or multi-team projects.

Good pattern:

models:
  - name: int_payments_pivoted_to_orders
    config:
      group: finance
      access: private

  - name: orders
    config:
      group: finance
      access: public

Bad pattern:

Everything is public because someone might need it someday.

Warning: Public access is a promise. Do not make implementation details public unless you are ready to support them.

40. Version models only for breaking interface changes

Rule: Use model versions when a model’s structure, meaning, or contract changes in a way that would break consumers.

Why this matters: Versioned models and deprecation dates are part of managing model evolution.

Good use of versioning:

orders_v1: existing revenue logic
orders_v2: revised revenue recognition logic

Bad use of versioning:

orders_v2 because the SQL was refactored internally but output is unchanged

Warning: Versioning is not a substitute for clean migration planning. Add deprecation dates and communicate which version consumers should move to.

41. Do not split projects by consumption mode

Rule: Avoid splitting dbt projects into “BI project,” “ML project,” and “executive reporting project” if they reuse the same core concepts.

Why this matters: Avoid splitting by ML versus reporting use cases because it undermines the single source of truth.

Good split:

finance-owned project
customer-domain project
platform/foundational project

Bad split:

dashboard_project
ml_features_project
ad_hoc_analysis_project

Warning: ML features and BI metrics should often come from the same governed marts or semantic definitions. Otherwise the business will get two versions of “customer,” “revenue,” and “active user.”

42. Keep rollups out of marts when they are really metrics

Rule: Do not create marts like orders_per_day, revenue_by_month, or customers_by_region unless they are deliberate aggregate tables. Prefer entity-grained marts plus metrics.

Why this matters: Pure marts should not usually include time-based rollups; grouped outputs move past marts into metrics.

Good pattern:

orders            → one row per order
customers         → one row per customer
metric: revenue   → aggregated by requested dimensions

Bad pattern:

orders_per_day
orders_per_week
orders_by_region
orders_by_channel

Warning: Pre-aggregated marts multiply definitions. They also restrict future slicing because the grain has already been collapsed.

43. Make null handling explicit in marts

Rule: When joining optional downstream data into marts, use explicit coalesce only where the business meaning is clear.

Why this matters: Typical mart examples coalesce missing payment amounts and order counts to zero. That is correct when “no related rows” means zero, not unknown.

Good example:

coalesce(order_payments.total_amount,0)as total_amount

when no payment rows means no successful payments.

Risky example:

coalesce(customer_orders.lifetime_value,0)as lifetime_value

if missing orders could mean ingestion failure, privacy filtering, or an unresolved join key.

Warning: Nulls are semantic information. Replacing null with zero can make data quality problems invisible.

44. Use business terminology at the semantic boundary

Rule: Rename source terms into business terms as early as staging when the mapping is stable.

Why this matters: Use business terminology rather than source terminology, for example customer_id instead of user_id if the business calls them customers.

Good example:

idas customer_id

Bad example:

user_id

in some models and:

customer_id

in others for the same entity.

Warning: Inconsistent terminology breaks metric discoverability. It also confuses LLM-based querying because the same concept appears under multiple names.

45. Optimize for boring consistency, not cleverness

Rule: Prefer repetitive, predictable patterns over clever SQL structures.

Why this matters: Consistency is a functional requirement. dbt projects are collaborative semantic systems, not personal SQL notebooks.

Good pattern:

source CTE
renamed/transformed CTE
final select

used consistently across staging models.

Bad pattern:

Every model has a different style, CTE order, aliasing convention, and config location.

Warning: Cleverness compounds. A clever staging model becomes a confusing intermediate model, then an unreliable mart, then a disputed metric.