dbt Core projects (dbt_project.yml) on Snowflake, Databricks, BigQuery, Redshift and PostgreSQL: staging, intermediate and mart models, sources, seeds, snapshots, macros, data tests, unit tests and model contracts.
dbt Core projects (dbt_project.yml) on Snowflake, Databricks, BigQuery, Redshift and PostgreSQL: staging, intermediate and mart models, sources, seeds, snapshots, macros, data tests, unit tests and model contracts.
Used for: dbt.
Principles
Every model reads its inputs through ref() and source(); no hard-coded database.schema.table names, so environments, CI schemas and lineage work.
Layer the project: staging models (stg_) clean one source each 1:1, intermediate models (int_) hold reusable logic, marts (fct_, dim_) are what people query; marts never read sources directly.
Every model has a primary key with unique and not_null data tests, and every non-trivial transformation (case logic, window functions, date maths) has a dbt unit test with given and expected rows.
Public models (marts others depend on) have an enforced model contract (contract: {enforced: true}) with column data types, and a version when the contract changes in a breaking way.
Never write a credential into profiles.yml or dbt_project.yml: profiles read secrets with env_var('DBT_ENV_SECRET_...'), and CI gets them from its secret store.
Make large fact models incremental with a unique_key and an is_incremental() filter on a reliable timestamp, and choose the strategy (merge, delete+insert, insert_overwrite, microbatch) the warehouse handles best.
Data tests and unit tests run against the warehouse in CI (dbt build on the changed models and their children); without a connection they are reported as not run, never as passed.
Configure in the right place: folder-level defaults (materialization, schema, tags) in dbt_project.yml, exceptions in the model's config block, documentation and tests in YAML next to the models.
Keep SQL readable and linted: CTEs at the top (import CTEs, logical CTEs, a final select), lower-case keywords or the project's style as sqlfluff enforces it.
Pin packages in packages.yml to exact versions or narrow ranges and commit package-lock.yml.
Conventions
Accessibility
Concurrency
Set threads for the warehouse's concurrency; models without dependencies between them run in parallel, so a model must never depend on another without ref().
Do not let two jobs build the same incremental model at once (one schedule per environment, a lock in the orchestrator).
Dependencies
Pin dbt packages in packages.yml (exact version or a narrow range) and commit package-lock.yml; upgrade dbt-core and the adapter together in a dedicated change.
Keep require-dbt-version in dbt_project.yml in line with the version CI uses.
Documentation
Every model and every column people query has a description in YAML (doc blocks for long text), and dbt docs generate is part of the release.
Each source documents its owner, loader and freshness expectation; exposures document the dashboards and applications that use the marts.
Comment non-obvious SQL (why a filter exists, a known source quirk) in the model.
Errors
Fail early on bad data with data tests at the right severity (error for keys and contracts, warn with thresholds for soft expectations) and store_failures for investigation.
Guard macros with argument checks and exceptions.raise_compiler_error('...') that say what to change.
Handle late and duplicate data explicitly in incremental models (a lookback window, qualify row_number() = 1 deduplication) rather than trusting the source.
Logging
Use the run results and artifacts (run_results.json, manifest.json, sources.json) for observability: ship them to the data-observability or monitoring tool and alert on failed tests and stale sources.
Use {{ log('...', info=True) }} sparingly in macros and never log row data or secrets; tag models (tags: ['finance']) so runs and alerts can be scoped.
Add query tags or comments (query_tag on Snowflake, query-comment in dbt_project.yml) so warehouse cost and query history can be traced back to the model and run.
Naming
Prefix models by layer: stg___ (double underscore between source and entity), int_, fct and dim_; plural entity names (stg_stripe__payments).
Name columns in snake_case: primary keys id, booleans is/has_, timestamps _at (UTC), dates _date, amounts with their unit or currency (amount_usd, amount_cents).
Name YAML files after their folder (_stripe__models.yml, _stripe__sources.yml) and unit and data tests after the behaviour they check.
Performance
Materialize by cost: views for light staging, tables for marts, incremental for large facts, ephemeral only for small reusable CTEs.
Cluster and partition marts for their queries: cluster_by on Snowflake (large tables only), partition_by and cluster_by on BigQuery, liquid_clustered_by or zorder on Databricks, sort and dist keys on Redshift.
Filter early and select only the columns needed; avoid cross joins and window functions over whole tables in staging; push deduplication to one place.
Size the warehouse per model where the adapter allows it (snowflake_warehouse config) instead of running everything on a large one.
Security
Credentials only through env_var() in profiles.yml (prefix secrets with DBT_ENV_SECRET_ so dbt scrubs them from logs); never in dbt_project.yml, vars, seeds or models; CI uses a dedicated service user with key-pair or OAuth authentication.
Least privilege: the dbt user owns only the schemas it builds, reads only the sources it needs, and grants access to marts through the grants config (grants: {select: ['reporter']}), never broad role grants in hooks.
Validate and allow-list values that reach SQL through vars or macro arguments; never build SQL from untrusted text in run_query or execute blocks (injection), and quote identifiers with adapter.quote.
Treat seeds and snapshots as data with personal information where they hold it: mask or exclude sensitive columns in staging and apply the warehouse's masking policies to marts.
Pin packages to versions and keep package-lock.yml committed; audit the Python dependencies (dbt-core, the adapter) for vulnerabilities.
Structure
models/staging, models/intermediate and models/marts as above; tests/ for singular data tests, macros/, seeds/ for small static lookups only, snapshots/ for slowly changing sources, analyses/ for ad-hoc SQL.
Set materializations and schemas per folder in dbt_project.yml (staging: view, marts: table or incremental) and override in a model only with a reason.
Each model file holds one model; import CTEs (select from ref/source) first, then logical CTEs, then select * from final.
Keep a committed profiles.yml (if any) free of credentials, reading everything with env_var(), with a ci target; keep personal profiles in ~/.dbt/profiles.yml.
Testing
Unit tests (dbt 1.8+ unit_tests) cover the logic of each non-trivial model with given input rows and expected output; they need no production data but do need a warehouse connection to run.
Data tests: unique and not_null on every primary key, relationships for foreign keys, accepted_values for enumerations, and expression or package tests (dbt_utils, dbt_expectations) for business rules; integration of the whole DAG is checked by dbt build in CI.
Run slim CI on pull requests: dbt build --select state:modified+ --defer --state in an isolated CI schema, then drop it.
Name unit tests test_ and singular tests assert.
Done means tested: new or changed behaviour ships with data tests or unit tests in the same change; offline, the tests are compiled and listed and reported as not run, because they need the warehouse.
Review checklist
Does every new model read through ref() or source(), with no hard-coded database or schema?
Is the model in the right layer and folder, named by the convention (stg_, int_, fct_, dim_)?
Does every new or changed model have a primary key with unique and not_null tests?
Does non-trivial logic have a dbt unit test with given and expected rows?
Do public models have an enforced contract with data types, and is a breaking change versioned?
Are incremental models correct for late and duplicate data (unique_key, lookback, strategy)?
Is the materialization right for the model's size and use, with clustering or partitioning where the warehouse benefits?
Are credentials only read through env_var(), with nothing secret committed?
Are new models and columns documented in YAML?
Does the project parse (dbt parse) and is sqlfluff clean?
Are packages pinned and package-lock.yml committed?
Is it clear in the pull request which tests ran against the warehouse and which did not?
Quality checks (run when your repository has the tool configured)
