dbt Project Standard Template (Analytics Engineering)

dbt Project Standard: [Project name]

Analytics engineering conventions for one dbt project or a group of related projects: layers and folder layout, naming, sources and freshness, model design and materialisation, tests (generic, singular and unit tests), model contracts and versions, groups and access, documentation, macros and packages, environments and targets, CI with state comparison and deferral, the code review checklist and cost guardrails. Written for dbt Core 1.11 and 1.12 (stable lines in October 2026, 1.12 marked Latest) and kept neutral where dbt Core 2.0 (announced as an alpha in June 2026, with 2.0.x releases tagged since September 2026) or another engine differs; check docs.getdbt.com for the support status of the release you run. Replace every [bracketed] value and every row marked Example.

Document control

Approvals

Revision history

Contents

• 1. Scope and principles
• 2. Layers and folder layout
• 3. Naming conventions
• 4. Sources and freshness
• 5. Model design and materialisation
• 6. Testing
• 7. Contracts, versions, groups and access
• 8. Documentation
• 9. Macros and packages
• 10. Environments and targets
• 11. CI and deployment
• 12. Code review checklist
• 13. Cost guardrails
• 14. Exceptions and compliance
• 15. Glossary and references

1. Scope and principles

This standard applies to [all dbt projects in the corvid-analytics organisation] and to every person who opens a pull request against them. It applies to models, seeds, snapshots, sources, tests, macros, semantic definitions and exposures. Orchestration, ingestion and storage design are out of scope and live in the documents named in the guidance above.

Rules are written as "must" (blocking in review) or "should" (expected, an exception needs a short reason in the pull request). Section 14 says how to record a standing exception.

2. Layers and folder layout

The project uses three model layers. Each layer may read only from the layer before it, except that marts may also read other marts of the same group.

Rules:

• DBT-001 A staging model must select from exactly one source table and must not join.
• DBT-002 Only staging models may call source().
• DBT-003 Intermediate models must not be exposed to BI tools or other projects; set their access to private or protected (section 7).
• DBT-004 A mart must declare its grain in its description, for example "one row per order line".
• DBT-005 Seeds hold small, static reference data maintained by people (country codes, channel mappings). Seeds must not hold data exported from a system.

Folder layout (Example):

corvid_analytics/
  dbt_project.yml
  packages.yml
  models/
    staging/
      shop_db/            # one folder per source system
        _shop_db__sources.yml
        _shop_db__models.yml
        stg_shop_db__orders.sql
        stg_shop_db__order_lines.sql
      payments_api/
        stg_payments_api__payments.sql
    intermediate/
      orders/
        int_orders__lines_joined_to_payments.sql
    marts/
      orders/             # one folder per domain or group
        _orders__models.yml
        fct_orders.sql
        dim_customers.sql
      checkout/
        fct_checkout_sessions.sql
    semantic/
      orders_semantic.yml
  snapshots/
  seeds/
  macros/
  tests/                  # singular tests
  analyses/

The semantic/ folder holds semantic definitions governed by Semantic Model Specification (data/semantic-model-specification). Analyses are for ad hoc SQL that is versioned but never materialised.

3. Naming conventions

Column rules:

• DBT-010 Use snake case for every model and column name.
• DBT-011 Name primary keys <entity>_id and keep the same name for the foreign key in every other model.
• DBT-012 Prefix booleans with is_ or has_.
• DBT-013 Suffix timestamps with _at and store them in UTC; suffix dates with _date. State the time zone in the description when a local date is required by the business.
• DBT-014 Put the unit in the name of every measure whose unit is not obvious: amount_eur, duration_seconds.
• DBT-015 Do not use abbreviations that are not in the glossary in section 15 or in the Data Dictionary (architecture/data-dictionary).
• DBT-016 Do not use reserved words of any supported engine as column names.

4. Sources and freshness

• DBT-020 Declare every raw table in a sources file next to its staging models, with a description, an owner in meta (placed under config, which current releases document and which supports inheritance) and the loader that writes it.
• DBT-021 Every source table that is loaded incrementally must have a loaded-at field and a warn and error freshness threshold.
• DBT-022 Run the source freshness check before each production build and publish the result to the alert channel of the source owner.
• DBT-023 A source that has a Data Contract (data/data-contract) must reference the contract ID in meta, and its tests must cover the contract's quality rules.

Sources file sketch (Example):

sources:
  - name: shop_db
    description: Operational order database, replicated by CDC.
    config:
      meta:
        owner: "@orders-platform"
        contract_id: DC-ORD-001
      loaded_at_field: _loaded_at
      freshness:
        warn_after: {count: 2, period: hour}
        error_after: {count: 6, period: hour}
    tables:
      - name: orders
        columns:
          - name: order_id
            data_tests: [not_null, unique]

Property keys move between dbt releases: freshness moved under config in 1.9, loaded_at_field in 1.10, and column-level meta in 1.10 (backported to 1.9). Top-level meta is still accepted for backward compatibility but does not inherit, so write new properties under config. Check the property reference for your release before copying a sketch.

5. Model design and materialisation

Model file rules:

• DBT-030 Start each model with import CTEs, one per ref or source, then logical CTEs, then a final select.
• DBT-031 Select explicit columns in marts; select * is allowed only in import CTEs.
• DBT-032 Use ref and source for every relation; hard-coded schema or table names are forbidden.
• DBT-033 Keep one business rule per CTE and name the CTE after the rule.
• DBT-034 Format SQL with the project's linter and formatter configuration; CI fails on lint errors.

• DBT-035 Every incremental model must handle late-arriving records with a look-back window of at least [3 days] (Example) and must say in its description how deletes in the source are propagated.
• DBT-036 Schedule a full refresh of each incremental model at least [monthly] (Example) or document why it is unsafe.
• DBT-037 Python models need approval from the data platform lead and must state why SQL is not sufficient.

6. Testing

The project uses four kinds of tests. Data tests run against built data; unit tests run against small fixed inputs before data is built (available from dbt 1.8).

Minimum tests per layer:

• DBT-040 A test that fails in production with severity error stops downstream models in the same run and raises an alert for the model owner, using the severity scale in Incident Severity Matrix (ops/incident-severity-matrix) when consumers are affected.
• DBT-041 Warning-level tests must have an owner and a review date; a warning ignored for [30 days] (Example) is either fixed, promoted to error or deleted.
• DBT-042 Unit tests must not run in production builds; exclude them by resource type in production jobs.
• DBT-043 Store failing rows for marts tests in a dedicated audit schema with a retention of [14 days] (Example).
• DBT-044 Write a unit test for every bug fix to a calculation, so the bug cannot return.

Organisation-wide thresholds (for example acceptable null rates per data classification) come from Data Quality Standard (data/data-quality-standard); this section only says how they are implemented in dbt.

7. Contracts, versions, groups and access

A model contract guarantees the column names and data types of a model's output and fails the build when they differ. Model versions let a breaking change ship as a new version while consumers migrate. Groups assign models to an owning team, and access levels limit who may ref a model.

Rules:

• DBT-050 Every model belongs to a group, and every group has an owner with a contact in the group definition.
• DBT-051 Every public model must have an enforced contract, a description, a declared grain and tests on its primary key.
• DBT-052 A breaking change to a public model (removing or renaming a column, changing a type, changing the grain) must ship as a new model version. Keep the previous version for at least [90 days] (Example) and set its deprecation date.
• DBT-053 Announce a new version and the deprecation date to every consumer listed in exposures and in the related data contract before merge.
• DBT-054 Non-breaking additions (a new nullable column) do not need a new version but must be listed in the changelog of the data product.
• DBT-055 Declare exposures for each dashboard, application or data product that reads a public model, with the owner and URL.

Contracts are supported on some materialisations only: tables, views (column names and types, without constraints) and incremental models whose schema change policy is append_new_columns or fail. They are not supported on materialised views, ephemeral models, Python models, custom materialisations unless the author added support, or on seeds, snapshots and sources. Check the contract page for your release and adapter, and record unsupported cases in section 14.

Warehouse access is separate from dbt access: the access level controls which groups and projects may ref a model, while BI tools and other readers are governed by the grants and masking policies set per target (section 10).

8. Documentation

• DBT-060 Every model and every column of a mart has a description. Descriptions say what the value means for the business, its unit, and any rule that changes it.
• DBT-061 Reuse descriptions of shared columns through doc blocks so customer_id means the same thing everywhere.
• DBT-062 Record in config.meta for each mart: owner, data classification per Data Classification Policy (data/data-classification-policy), contains personal data (yes or no), and the related data product ID.
• DBT-063 Columns that hold personal data carry a tag that drives masking policies and the retention rules in Data Retention and Deletion Standard (data/data-retention-deletion-standard).
• DBT-064 Generate and publish project documentation from the production build only, so it describes what consumers actually read.
• DBT-065 Keep business glossary terms in the Data Dictionary (architecture/data-dictionary) and link to them; do not maintain a second glossary inside the project.

9. Macros and packages

• DBT-070 Write a macro only when the same logic appears in three or more models or when engine-specific SQL must be isolated.
• DBT-071 Each macro has a description, documented arguments and at least one model or unit test that exercises it.
• DBT-072 Prefer the cross-database macros shipped with dbt and the adapter over custom engine-specific SQL.
• DBT-073 Pin every package to an exact version or a narrow range in packages.yml, commit the lock file, and upgrade through a pull request that runs the full CI.
• DBT-074 Approve new packages through the organisation's dependency review: licence, maintenance activity, and whether the package executes SQL with elevated grants.
• DBT-075 Override generate_schema_name only in one place, and document the resulting schema names per target in section 10.

10. Environments and targets

• DBT-080 Credentials are never stored in profiles.yml in the repository; read them from environment variables or the secret store.
• DBT-081 Personal development builds must limit data volume with a target-aware filter macro.
• DBT-082 Masking and row access policies apply in every target that holds production personal data, including dev.
• DBT-083 Drop CI schemas automatically when the pull request is closed and drop idle development schemas after [14 days] (Example).

11. CI and deployment

The pull request job uses "slim CI": it compares the project with the manifest of the latest production run (state comparison), builds only modified models and their children, and defers unmodified parents to the production relations so they are not rebuilt.

• DBT-090 The production job publishes its manifest to a fixed location after every successful run so CI can compare state.
• DBT-091 If the production manifest is missing, state comparison cannot run: CI builds and tests the whole project (or the paths changed in the pull request), never skipping tests.
• DBT-092 Merges to the main branch follow Branching Strategy (dev/branching-strategy); deployments run through the pipeline described in CI/CD Pipeline Design (dev/ci-cd-pipeline-design).
• DBT-093 Upgrades of the dbt engine or adapter run the full project in staging and compare row counts and key aggregates of every public model before production.
• DBT-094 Reruns after a failure use the retry capability for the failed nodes rather than a manual partial build.

12. Code review checklist

Use Pull Request (dev/pull-request) for the description template.

13. Cost guardrails

• DBT-100 A model that becomes one of the top 10 by compute for two months in a row needs an optimisation ticket or a recorded exception.
• DBT-101 Delete models with no downstream nodes, no exposures and no queries in [90 days] (Example) after notifying the group owner.

14. Exceptions and compliance

Record standing exceptions here. Each exception names the rule, the reason, the compensating control and an expiry date.

Measure compliance each quarter from the project manifest: share of models with descriptions, share of public models with contracts, models without primary key tests, and open exceptions past their expiry. Report the figures to the data governance forum described in Data Governance Operating Model (data/data-governance-operating-model).

15. Glossary and references

References:

• dbt Core releases and changelog: https://github.com/dbt-labs/dbt-core (1.11.16 and 1.12.6 released 2026-10-14, 1.12.6 marked Latest; dbt Core 2.0, a Rust implementation, was announced as an alpha on 2026-06-01 and 2.0.x releases have been tagged since September 2026; check docs.getdbt.com for its current support status before adopting it).
• dbt documentation: best practice guide on project structure, model contracts, model versions, model access and groups, unit tests, source freshness, state selection and deferral: https://docs.getdbt.com
• Related templates: Data Platform Architecture (architecture/data-platform-architecture), Data Pipeline Specification (data/data-pipeline-spec), Data Contract (data/data-contract), Data Quality Standard (data/data-quality-standard), Data Product Specification (data/data-product-specification), Semantic Model Specification (data/semantic-model-specification), Metric Definition (data/metric-definition), Coding Standards (dev/coding-standards).

Standard and licence note

Structure aligned with the project structure, testing, governance and CI concepts described in the dbt documentation (docs.getdbt.com) and with dbt Core (Apache License 2.0). Only concept, command and property names are used; no text, examples or diagrams from the dbt documentation are reproduced, and the Corvid Commerce example is fictional. This template is original text written for this collection. dbt, dbt Core and dbt Labs are trademarks of dbt Labs, Inc.; this template is not affiliated with or endorsed by dbt Labs. Check the current documentation for your dbt release and adapter before use, as property names and defaults change between releases. Template licence: MIT.