Skip to content

Library · Data Practice OS

dbt project standards

Reference guide · Published · Source reviewed

The answer: one dbt project per domain, one layout everyone recognises, and portable SQL by default. Platform-specific behaviour goes in configs and macros, never scattered through models. This is how ADR-003: dbt as the transformation standard keeps the option of moving a domain between platforms.

When dbt is the right tool

When it isn't

  • Low-latency streaming (seconds). Use Lakeflow streaming tables or Snowflake dynamic tables with short lag, and keep dbt for what's downstream.
  • Heavy Python, ML training, unstructured document processing. Use notebooks or jobs on the platform. dbt Python models exist, but are best kept for light work.
  • Ingestion. dbt transforms data already in the platform. Landing data is Snowflake Openflow or Lakeflow (Connect, Declarative Pipelines, Jobs) territory.

Layout

dbt-registrations/
├── dbt_project.yml
├── models/
│   ├── staging/        # stg_  one per source table, rename + type only
│   ├── intermediate/   # int_  reusable logic, not exposed
│   ├── marts/          # fct_, dim_, agg_   the gold products
│   └── semantic/       # semantic models + metrics
├── snapshots/
├── seeds/              # small static reference data only (code lists)
├── macros/
├── tests/              # singular tests for business rules
└── contracts/          # ODCS contract YAML per published product

Conventions

  • Every model has a YAML entry with a description, owner (meta.owner), grain statement and column descriptions. CI fails if a mart has none (dbt-checkpoint or a custom check).
  • Every mart has: unique + not_null on its key, relationships to its dimensions, and an enforced model contract (Data contracts).
  • Sources declare freshness. loaded_at_field: _loaded_at, warn at 6h and error at 24h unless the contract says otherwise.
  • Style is linted. SQLFluff with the dbt templater, in pre-commit and CI. Lowercase keywords, explicit column lists in marts, CTEs over subqueries.
  • No hard-coded database names. Always ref() and source(). Environments come from targets.
  • Rule ids in meta during migrations. Models may carry meta.implements: [rule ids], and tests meta.verifies. Use folder-level +meta only when every model in the folder implements the rule (proposed, Tracing rules from legacy code to modern code).

Cross-platform portability

Most SQL is the same on both platforms. Where it differs, wrap it.

-- macros/cross_db/safe_parse_date.sql
{% macro safe_parse_date(col, fmt) %}
  {{ return(adapter.dispatch('safe_parse_date')(col, fmt)) }}
{% endmacro %}

{% macro snowflake__safe_parse_date(col, fmt) %} try_to_date({{ col }}, '{{ fmt }}') {% endmacro %}
{% macro databricks__safe_parse_date(col, fmt) %} try_to_date({{ col }}, '{{ fmt }}') {% endmacro %}

Prefer dbt's built-in cross-database macros (dbt.dateadd, dbt.datediff, dbt.concat, dbt.type_string) over writing your own.

Materialisations

Use Snowflake Databricks
Default mart table table (Delta)
Large append/merge facts incremental (merge, with unique_key) incremental (merge, or replace_where)
Declarative freshness dynamic_table (with target_lag) materialized_view / streaming_table
Interfaces and shares view (secure for shares) view
Cross-platform gold table_format='iceberg' (Horizon-managed), or catalog_name from catalogs.yml for an external catalog; works on table, incremental and dynamic table. Set external_volume to the organisation's volume: without it, dbt v2.0 and later writes to Snowflake-managed storage (dbt Iceberg on Snowflake, checked 6 Nov 2026) Delta with UniForm table properties 
# models/marts/_marts.yml (excerpt)
models:
  - name: fct_registration_event
    description: "One row per registration transaction (new, renewal, transfer, cancellation)."
    config:
      materialized: incremental
      unique_key: registration_event_id
      contract: { enforced: true }
      meta: { owner: registrations-data, classification: OFFICIAL_SENSITIVE }

Which dbt: v1, v2, OSS or the platform

dbt now ships in three flavours, and all of them run in GitHub Actions as the Paved road baseline:

  • dbt v1 (dbt Core): Apache 2.0, still fully supported.
  • dbt v2: the Rust engine previously called Fusion. Free to use, but proprietary under the dbt Product Licensing Agreement. New capabilities land here. Snowflake and Databricks are both supported adapters.
  • dbt OSS (dbt-oss): the Apache 2.0 distribution of v2, for when policy requires open source.

The commercial dbt platform adds a managed scheduler, IDE and hosted semantic layer APIs. Consider it if the semantic layer serves many tools (ADR-006: Define metrics once in a semantic layer). Pick v1 or dbt-oss if the organisation's licence policy rules out the proprietary v2 terms. See dbt licensing and Snowflake Iceberg support.

Pitfalls

  • One giant project for all of the organisation. Slow CI and constant merge conflicts. Use one project per domain, with cross-project ref (dbt Mesh) or published sources between them.
  • Business rules in Jinja. Logic hidden in macros is hard to review and impossible for text-to-SQL to see. Keep rules in SQL models with tests that name the rule (Extracting business rules).
  • Incremental without a backfill path. Every incremental model must run correctly with --full-refresh. Test it in CI on a sample.