dbt Architect cheat sheet

Layer rule: staging models = 1:1 with source tables, rename/cast only; intermediate models hold reusable joins; marts expose business entities. Marts should never select directly from raw sources.

Materialization decision rule: views for staging and low-cost marts; tables for frequently queried marts; incremental for large fact tables with a reliable filter column; ephemeral only for CTEs reused inside a single downstream model chain.

Incremental strategies: append (immutable event data), merge (mutable rows, needs unique_key), delete+insert (no reliable update timestamp), insert_overwrite (partition-replace on BigQuery/Spark).

is_incremental() returns true only when the target relation already exists and the model is not in full-refresh mode; the filter must be inside this block or the first build fails.

Naming convention: , e.g. stg_stripe__payments, int_orders__joined, fct_orders, dim_customers. Prefixes fct_ and dim_ signal grain to consumers.

Grain declaration: every fct_ model states one row per X in its description; dim_ models carry a surrogate or natural key that is unique and not null, tested with unique and not_null.

Ref rule: use ref() and source() everywhere; hardcoded schema.table references break lineage, the DAG, and environment isolation. Model references must be acyclic by construction.

Project split: one dbt project per team or per data domain when CI time exceeds tolerance or release cadence differs; use a shared package for conformed staging and macros. Cross-project refs require the package dependency.

Model contracts (contract: enforced: true) lock column names and data types; adding a column requires a contract change and a full refresh of dependent models on some adapters.

Environment isolation: one target schema per environment (dev_, ci_, prod), generated with a custom schema macro such as generate_schema_name that appends the target schema instead of prefixing it.

Target selection: profiles.yml holds connection blocks; the target key is chosen by --target or the DBT_TARGET env var. Credentials come from environment variables or a secrets manager, never committed.

Dev/prod parity: same dbt version, same adapter version, same warehouse edition. Divergent adapter versions are the most common cause of 'works in dev, fails in prod'.

Warehouse sizing: separate warehouses for loading, transformation, and BI. Transformation warehouse size is set by the largest model's spill, not by average query cost; auto-suspend 60s, auto-resume on.

Storage layout: cluster or partition large facts on the column most filters use (usually date). Partition pruning fails when the filter wraps the column in a function such as cast().

Source freshness: loaded_at_field plus warn_after/error_after per source table; run dbt source freshness before the transformation job so stale ingestion blocks the run.

Package pinning: pin dbt_utils and other packages to a version range in packages.yml and commit packages.lock.yml; unpinned packages make builds non-reproducible.

Adapter capability check: confirm the adapter supports the chosen incremental strategy, contracts, and model access before designing around them; not all adapters implement insert_overwrite or contracts.

CI job shape: run on every pull request against a PR-schema target, execute dbt build --select state:modified+ --defer --state , then dbt test on the modified selection. --defer resolves unbuilt refs to the prod versions.

Slim CI requires a production manifest artifact; without --state pointing at a downloaded manifest, state:modified degrades to running everything.

dbt build interleaves tests with models, so a failing test blocks downstream models in the same run; dbt run followed by dbt test does not.

Job graph: separate scheduled jobs for sources (freshness), staging, marts, and exposures; use --select tag: or path: selectors rather than one monolithic job so a failure is isolated.

Retry semantics: dbt retry re-runs only the nodes that failed or were skipped in the previous invocation, reading run_results.json; it does not re-run successful nodes.

Deployment promotion: build and test in CI schema, then promote the same commit to prod. Never edit prod models by hand; the git commit is the source of truth.

Run artifacts: manifest.json (DAG and compiled config), run_results.json (status and timing per node), catalog.json (produced by dbt docs generate) feed dashboards, alerts, and state comparison.

Selectors: --select accepts union (space), intersection (comma), and exclusion (--exclude). graph operators are +model (upstream), model+ (downstream), and 2+model (two levels up).

Test tiers: schema tests (unique, not_null, accepted_values, relationships) for structural guarantees; singular tests in tests/ for business rules; dbt_utils or dbt_expectations for range and row-count checks.

Severity: warn keeps the run green but records the failure; error fails the node and blocks downstream. Reserve error for guarantees that must hold for the mart to be correct.

Model access (dbt 1.5+): private (same group only), protected (same project), public (cross-project). Groups plus access enforce domain boundaries at compile time.

PII handling: tag columns and models with meta.pii, apply masking in the warehouse or a post-hook, and exclude tagged models from broad selectors used by non-privileged roles.

Docs: every model gets a description; every column in a mart gets a description or an explicit exclusion. dbt docs generate builds the catalog and lineage graph from the manifest.

Exposures declare downstream consumers (dashboards, ML models) so lineage shows impact and dbt can warn when an upstream model changes.

Contracts plus tests are complementary: contracts fix the shape (names, types), tests fix the content (uniqueness, referential integrity, ranges). Neither replaces the other.

Freshness SLAs belong on sources, not on models; a stale source is an ingestion problem and should page the ingestion owner, not the analytics engineer.

Cost drivers ranked: full scans of large tables, repeated rebuilds of unchanged models, oversized always-on warehouses, and cross-region data transfer. Fix scan volume before tuning warehouse size.

Incremental lookback: set the filter to max(loaded_at) minus a late-arriving-data buffer (e.g. 3 days) rather than a fixed date; too-small buffers drop late rows silently.

Query pruning: filter on the partition column in the model's WHERE clause, avoid select *, and push filters into CTEs so the optimizer can prune before joins.

Join order and grain: join at the finest grain first, then aggregate; joining a fact to a dimension that fans out multiplies rows and inflates every downstream aggregate.

Materialized views or dynamic tables suit high-frequency BI queries over slowly changing data; they trade storage and refresh cost for query latency.

Microbatch incremental (dbt 1.9+) processes time ranges as independent batches, giving automatic retries and bounded memory per batch for very large event tables.

Threads: concurrency is bounded by warehouse queue depth; raising threads past the warehouse's concurrent-query limit increases queue time, not throughput.

State-based selection (state:modified, source_status:fresh) is the main lever for cutting CI and daily job cost; rebuilding unchanged models is pure waste.

Model splitting: split a model when a single run spills to disk or exceeds the job's time budget; keep it whole when splitting adds scans without reducing data volume.

Materialization choice follows query frequency and data volume: view for rarely queried staging, table for hot marts, incremental for large append-heavy facts.

The is_incremental() guard wraps both the filter and any merge logic; placing the filter outside it breaks the initial build.

Every fct_ model declares one row per event or transaction; every dim_ model declares a unique key validated by unique and not_null tests.

Use ref() and source() exclusively; direct schema.table references sever lineage and break per-environment schema isolation.

Split into separate dbt projects only when CI duration or release cadence demands it; share conformed logic through packages.

Enforced contracts lock column names and types, so schema changes become explicit review events rather than silent drift.

One schema per environment, generated by a custom generate_schema_name macro; dev, ci, and prod never share a schema.

Credentials come from environment variables or a secrets manager; profiles.yml is never committed with passwords.

Keep dbt core and adapter versions identical across dev, CI, and prod; adapter drift is a leading cause of environment-only failures.

Separate transformation, loading, and BI warehouses; size transformation by the worst spill, not the average query.

Partition or cluster large facts on the dominant filter column; wrapping that column in a function disables pruning.

Pin packages and commit packages.lock.yml so builds are reproducible across environments.

Slim CI runs dbt build --select state:modified+ --defer --state against a PR schema, then tests the modified selection.

--defer needs a production manifest; without it, state:modified cannot resolve unbuilt upstream refs and the selector widens.

dbt build interleaves tests with models, so a failing test blocks downstream nodes in the same run; separate run and test commands do not.

dbt retry re-executes only failed or skipped nodes from run_results.json, saving the cost of a full re-run.

Promote the tested commit to prod; never patch production models outside version control.

Use graph operators (+, n+, @) and tags or paths to scope jobs so a failure is isolated to one domain.

Schema tests cover uniqueness, nulls, accepted values, and referential integrity; singular tests encode business rules that generic tests cannot express.

Severity warn keeps the run green and logs the failure; severity error fails the node and blocks downstream models.

Model access levels private, protected, and public enforce domain boundaries at compile time when combined with groups.

Tag PII columns with meta and mask them in the warehouse or via post-hooks; exclude those tags from broad selectors.

Exposures record dashboards and ML consumers so lineage shows downstream impact of a model change.

Source freshness thresholds belong on sources and should alert the ingestion owner, not the analytics engineer.

Reduce scanned bytes before resizing warehouses: partition pruning, column pruning, and filter pushdown deliver larger savings than compute changes.

Incremental filters use max(loaded_at) minus a late-arrival buffer; buffers that are too small silently drop late rows.

Join at the finest grain and aggregate afterward; fan-out joins multiply rows and inflate every downstream aggregate.

Microbatch incremental processes bounded time ranges with per-batch retries, controlling memory on very large event tables.

Threads beyond the warehouse's concurrent-query limit add queue time rather than throughput.

State-based selection avoids rebuilding unchanged models, the largest avoidable cost in scheduled and CI jobs.

Staging models map 1:1 to source tables and only rename, cast, and lightly clean; business logic belongs in intermediate or mart models.

Incremental strategy by data shape: append for immutable events, merge for mutable rows with a unique_key, delete+insert without a reliable timestamp, insert_overwrite for partition replacement.

Ephemeral models are inlined as CTEs and leave no object in the warehouse; use them only for logic reused within one downstream chain.

Model names encode layer and grain (stg_, int_, fct_, dim_) so consumers can infer row grain from the name alone.

A cycle in ref() dependencies fails at compile time; break it by extracting shared logic into an intermediate model or macro.

Contracts are opt-in per model and require every column to declare a data type; untyped columns fail compilation once enforced.

Custom schema macro pattern: return target.schema ~ '_' ~ custom_schema_name so dev and CI schemas are namespaced under the target rather than replacing it.

The target is selected by --target or DBT_TARGET; CI overrides it per pull request to isolate concurrent builds.

Set auto-suspend to 60 seconds and auto-resume on for transformation warehouses; idle compute is the most common silent cost.

Run dbt source freshness before transformation jobs so stale ingestion halts the pipeline instead of propagating bad data.

Verify adapter support for contracts, model access, and each incremental strategy before committing to a design.

Commit packages.lock.yml alongside packages.yml; the lock file pins transitive dependency versions.

CI pipeline order: dbt deps, dbt build --select state:modified+ --defer --state prod-manifest, then upload artifacts for review.

Download the production manifest at the start of CI; a stale or missing manifest makes state comparison unreliable.

Split scheduled jobs by layer or tag so one failing domain does not block unrelated marts.

dbt retry reads the previous run_results.json and re-runs only failed or skipped nodes; it never re-runs successes.

The git commit is the deployment unit: build in CI, then run the identical commit in prod with no manual edits.

Artifacts (manifest.json, run_results.json, catalog.json) drive alerting, docs, and state comparison; retain them per run.

Relationships tests validate referential integrity between a child foreign key and a parent primary key; they are the main defense against orphaned facts.

accepted_values tests catch unexpected enum drift in status and category columns before it reaches dashboards.

Contracts fix shape (names and types); tests fix content (uniqueness, ranges, referential integrity). Use both.

Groups plus access modifiers enforce which projects may ref a model, turning governance into a compile-time error.

dbt docs generate builds catalog.json from the warehouse, adding row counts and column statistics to the lineage graph.

Document every mart column or explicitly exclude it; undocumented columns are the most common audit finding.

Order of optimization: cut scanned rows, then cut rebuilt models, then right-size compute. Reversing the order wastes effort.

Avoid select * in marts; column pruning reduces scanned bytes on columnar warehouses and stabilizes contracts.

Fan-out joins multiply fact rows; validate grain with a unique test on the join key after aggregation.

Materialized views and dynamic tables trade refresh cost for query latency and suit high-frequency BI over slowly changing data.

Raise threads only up to the warehouse's concurrent-query limit; beyond it, queries queue and elapsed time grows.

Split a model when a single run spills to disk or exceeds its time budget; keep it whole when splitting adds scans without cutting data volume.

Layering rule: sources feed staging, staging feeds intermediate, intermediate feeds marts. Marts never read raw sources directly.

Choose incremental only when the source has a reliable filter column (loaded_at, updated_at, or a partition key); otherwise use table.

The unique_key in an incremental model must be unique in the incoming batch, or merge produces duplicate rows.

full-refresh rebuilds the whole table and is required after changing an incremental model's schema or unique_key.

Use dbt_utils.generate_surrogate_key for deterministic hash keys across adapters instead of adapter-specific functions.

Place reusable business logic in macros or intermediate models, not duplicated across marts.

Schema naming: prod uses the clean schema name; dev and CI append the target so a developer never overwrites production tables.

Store warehouse credentials in a secrets manager and inject them at runtime; rotate keys without touching project code.

Match dbt and adapter versions across environments; version drift causes compile and SQL-dialect failures that appear only in prod.

Cluster large facts on the most-filtered column; clustering improves pruning when the leading column appears in filters.

Source freshness thresholds (warn_after, error_after) are per table and should reflect the ingestion SLA, not the transformation schedule.

Pin package versions and commit the lock file so every environment resolves identical dependency versions.

--defer makes unbuilt upstream refs resolve to the state manifest's relations, so CI builds only changed models against prod data.

state:modified compares node configs and SQL against the state manifest; source_status:fresh selects models whose sources are stale.

dbt build runs models and tests in DAG order, so a failed test blocks downstream models within the same invocation.

dbt retry re-runs only failed or skipped nodes from the last run_results.json, avoiding a full job re-run.

Isolate jobs by tag or path so a failure in one domain does not block unrelated marts.

Never hand-edit production models; the tested git commit is the only artifact promoted to prod.

Unique and not_null tests on every primary key are the baseline guarantee for any mart consumed by BI.

Severity error blocks downstream models; severity warn records the failure and lets the run continue. Choose per test, not per project.

Model access modifiers (private, protected, public) with groups enforce cross-domain dependency rules at compile time.

Tag PII with meta and mask it in the warehouse or via post-hooks; exclude PII-tagged models from broad selectors.

Exposures link dashboards and ML models into lineage so upstream changes surface their downstream impact.

Freshness SLAs live on sources and should page the ingestion owner; model-level freshness checks duplicate that signal.

Scanned bytes dominate cost on columnar warehouses; partition pruning and column pruning beat warehouse resizing.

Incremental lookback uses max(loaded_at) minus a late-arrival buffer; buffers that are too small drop late rows without error.

Microbatch incremental (dbt 1.9+) bounds memory per time batch and retries failed batches independently.

Join at the finest grain, then aggregate; joining before aggregation multiplies rows and inflates every downstream metric.

Threads above the warehouse concurrent-query limit add queue time; throughput is capped by the warehouse, not dbt.

State-based selection (state:modified, source_status:fresh) is the primary lever for cutting CI and daily job cost.

Staging models are 1:1 with sources and do only rename, cast, and light cleaning; all business logic lives downstream.

Materialization rule: view for staging, table for hot marts, incremental for large facts with a reliable filter column, ephemeral only for single-chain reuse.

is_incremental() is true only when the target exists and full-refresh is off; the filter must sit inside that block.

Every fct_ model states its grain in one sentence; every dim_ model has a unique, not-null key under test.

Use ref() and source() exclusively; hardcoded table names break lineage and environment isolation.

Split projects only when CI duration or release cadence requires it; share conformed logic through packages.

One schema per environment via generate_schema_name; dev, CI, and prod never share a schema.

Credentials come from environment variables or a secrets manager; profiles.yml is never committed with secrets.

Keep dbt core and adapter versions identical across environments; drift causes environment-only failures.

Separate transformation, loading, and BI warehouses; size transformation by worst-case spill, not average query.

Partition or cluster large facts on the dominant filter column; wrapping it in a function disables pruning.

Pin packages and commit packages.lock.yml for reproducible builds.

Slim CI: dbt build --select state:modified+ --defer --state against a PR schema, then test the modified selection.

--defer requires a production manifest; without it, state:modified cannot resolve unbuilt upstream refs.

dbt build interleaves tests with models so a failing test blocks downstream nodes; separate run and test do not.

dbt retry re-executes only failed or skipped nodes from run_results.json, avoiding a full re-run.

Promote the tested commit to prod; never patch production models outside version control.

Scope jobs with graph operators, tags, or paths so a failure stays isolated to one domain.

Schema tests cover uniqueness, nulls, enums, and referential integrity; singular tests encode business rules generic tests cannot express.

Severity warn keeps the run green and logs the failure; severity error fails the node and blocks downstream models.

Model access levels private, protected, and public with groups enforce domain boundaries at compile time.

Tag PII columns with meta and mask them in the warehouse or via post-hooks; exclude those tags from broad selectors.

Exposures record dashboards and ML consumers so lineage shows downstream impact of a model change.

Source freshness thresholds belong on sources and alert the ingestion owner, not the analytics engineer.

Reduce scanned bytes before resizing warehouses; pruning and filter pushdown save more than compute changes.

Incremental filters use max(loaded_at) minus a late-arrival buffer; too-small buffers silently drop late rows.

Join at the finest grain and aggregate afterward; fan-out joins multiply rows and inflate downstream aggregates.

Microbatch incremental processes bounded time ranges with per-batch retries, controlling memory on very large tables.

Threads beyond the warehouse concurrent-query limit add queue time rather than throughput.

State-based selection avoids rebuilding unchanged models, the largest avoidable cost in scheduled and CI jobs.

Incremental strategy by data shape: append for immutable events, merge for mutable rows with a unique_key, delete+insert without a reliable timestamp, insert_overwrite for partition replacement.

Ephemeral models are inlined as CTEs and leave no warehouse object; use them only for logic reused within one downstream chain.

Model names encode layer and grain (stg_, int_, fct_, dim_) so consumers infer row grain from the name alone.

A cycle in ref() dependencies fails at compile time; extract shared logic into an intermediate model or macro to break it.

Contracts are opt-in per model and require every column to declare a type; untyped columns fail compilation once enforced.

Staging models map 1:1 to source tables and only rename, cast, and lightly clean; business logic belongs in intermediate or mart models.

Custom schema macro pattern: return target.schema ~ '_' ~ custom_schema_name so dev and CI schemas are namespaced under the target.

The target is chosen by --target or DBT_TARGET; CI overrides it per pull request to isolate concurrent builds.

Set auto-suspend to 60 seconds and auto-resume on for transformation warehouses; idle compute is the most common silent cost.

Run dbt source freshness before transformation jobs so stale ingestion halts the pipeline instead of propagating bad data.

Verify adapter support for contracts, model access, and each incremental strategy before committing to a design.

Commit packages.lock.yml alongside packages.yml; the lock file pins transitive dependency versions.

CI pipeline order: dbt deps, dbt build --select state:modified+ --defer --state prod-manifest, then upload artifacts for review.

Download the production manifest at the start of CI; a stale or missing manifest makes state comparison unreliable.

Split scheduled jobs by layer or tag so one failing domain does not block unrelated marts.

dbt retry reads the previous run_results.json and re-runs only failed or skipped nodes; it never re-runs successes.

The git commit is the deployment unit: build in CI, then run the identical commit in prod with no manual edits.

Artifacts (manifest.json, run_results.json, catalog.json) drive alerting, docs, and state comparison; retain them per run.

Relationships tests validate referential integrity between a child foreign key and a parent primary key, defending against orphaned facts.

accepted_values tests catch unexpected enum drift in status and category columns before it reaches dashboards.

Contracts fix shape (names and types); tests fix content (uniqueness, ranges, referential integrity). Use both.

Groups plus access modifiers enforce which projects may ref a model, turning governance into a compile-time error.

dbt docs generate builds catalog.json from the warehouse, adding row counts and column statistics to the lineage graph.

Document every mart column or explicitly exclude it; undocumented columns are the most common audit finding.

Order of optimization: cut scanned rows, then cut rebuilt models, then right-size compute. Reversing the order wastes effort.

Avoid select * in marts; column pruning reduces scanned bytes on columnar warehouses and stabilizes contracts.

Fan-out joins multiply fact rows; validate grain with a unique test on the join key after aggregation.

Materialized views and dynamic tables trade refresh cost for query latency and suit high-frequency BI over slowly changing data.

Raise threads only up to the warehouse concurrent-query limit; beyond it, queries queue and elapsed time grows.

Split a model when a single run spills to disk or exceeds its time budget; keep it whole when splitting adds scans without cutting data volume.

Layering rule: sources feed staging, staging feeds intermediate, intermediate feeds marts. Marts never read raw sources directly.

Choose incremental only when the source has a reliable filter column (loaded_at, updated_at, or a partition key); otherwise use table.

The unique_key in an incremental model must be unique in the incoming batch, or merge produces duplicate rows.

full-refresh rebuilds the whole table and is required after changing an incremental model's schema or unique_key.

Use dbt_utils.generate_surrogate_key for deterministic hash keys across adapters instead of adapter-specific functions.

Place reusable business logic in macros or intermediate models, not duplicated across marts.

Schema naming: prod uses the clean schema name; dev and CI append the target so a developer never overwrites production tables.

Store warehouse credentials in a secrets manager and inject them at runtime; rotate keys without touching project code.

Match dbt and adapter versions across environments; version drift causes compile and SQL-dialect failures that appear only in prod.

Cluster large facts on the most-filtered column; clustering improves pruning when the leading column appears in filters.

Source freshness thresholds (warn_after, error_after) are per table and should reflect the ingestion SLA, not the transformation schedule.

Pin package versions and commit the lock file so every environment resolves identical dependency versions.

--defer makes unbuilt upstream refs resolve to the state manifest's relations, so CI builds only changed models against prod data.

state:modified compares node configs and SQL against the state manifest; source_status:fresh selects models whose sources are stale.

dbt build runs models and tests in DAG order, so a failed test blocks downstream models within the same invocation.

dbt retry re-runs only failed or skipped nodes from the last run_results.json, avoiding a full job re-run.

Isolate jobs by tag or path so a failure in one domain does not block unrelated marts.

Never hand-edit production models; the tested git commit is the only artifact promoted to prod.

Unique and not_null tests on every primary key are the baseline guarantee for any mart consumed by BI.

Severity error blocks downstream models; severity warn records the failure and lets the run continue. Choose per test, not per project.

Model access modifiers (private, protected, public) with groups enforce cross-domain dependency rules at compile time.

Tag PII with meta and mask it in the warehouse or via post-hooks; exclude PII-tagged models from broad selectors.

Exposures link dashboards and ML models into lineage so upstream changes surface their downstream impact.

Freshness SLAs live on sources and should page the ingestion owner; model-level freshness checks duplicate that signal.

Scanned bytes dominate cost on columnar warehouses; partition pruning and column pruning beat warehouse resizing.

Incremental lookback uses max(loaded_at) minus a late-arrival buffer; buffers that are too small drop late rows without error.

Microbatch incremental (dbt 1.9+) bounds memory per time batch and retries failed batches independently.

Join at the finest grain, then aggregate; joining before aggregation multiplies rows and inflates every downstream metric.

Threads above the warehouse concurrent-query limit add queue time; throughput is capped by the warehouse, not dbt.

State-based selection (state:modified, source_status:fresh) is the primary lever for cutting CI and daily job cost.