dbt Analytics Engineering cheat sheet

dbt materializations: view (default) rebuilds a view each run; table drops and recreates; incremental appends/merges only new rows; ephemeral is a CTE inlined into downstream models and never written to the warehouse; materialized view is warehouse-managed.

Incremental models require is_incremental() guarding the filter block; the filter only applies on runs after the first build, so the first run loads the full source set.

Incremental strategies are adapter-specific: append (insert only), merge (default on Snowflake/BigQuery/Spark with unique_key), delete+insert (default on Postgres/Redshift), and insert_overwrite (partition replacement on BigQuery/Spark).

unique_key defines row identity for merge and delete+insert; without it, merge falls back to append behavior and duplicates can accumulate.

Refs build the DAG: ref('model') and source('src','table') resolve dependencies, enable lineage, and let dbt substitute the correct database/schema at compile time. Never hardcode schema-qualified names.

Model layering convention: staging models (stg_) rename, cast and deduplicate one source each; intermediate (int_) models hold reusable joins and logic; marts (fct_, dim_) are wide, business-facing and typically materialized as table or incremental.

dbt compiles Jinja to SQL before execution; {{ }} outputs expressions, {% %} controls flow. Macros in macros/ are reusable Jinja functions callable as {{ macro_name(args) }}.

Surrogate keys are commonly generated with dbt_utils.generate_surrogate_key([...]) which hashes the concatenated column list; it returns null if any input is null unless coalesced first.

Incremental models should be tested for uniqueness and non-nullness on the unique_key, since a bad key silently produces duplicate rows on merge.

Sources are declared in .yml under sources: with name, database, schema and tables; freshness is configured with loaded_at_field plus warn_after/error_after thresholds and checked with dbt source freshness.

Source freshness compares max(loaded_at_field) against current time; error_after triggers a non-zero exit, warn_after only warns. Filter out bad timestamps with a filter: expression on the source.

Generic tests: unique, not_null, accepted_values (values list), and relationships (to: ref('other'), field: id) for referential integrity. They are declared per column or per model in YAML.

Custom generic tests are defined in tests/ or macros/ as a macro returning SQL that selects failing rows; a test passes when the query returns zero rows.

Singular tests are standalone .sql files under tests/ containing a failing-row query; they have no parameters and are not reusable across models.

severity: warn downgrades a failing test to a warning; store_failures: true persists failing rows to a _dbt_test__audit table for debugging.

dbt build runs models and tests in DAG order, so a failing test blocks downstream models; dbt test alone runs tests after all models already exist.

Test selection: dbt test --select model_name tests that model's tests; dbt test --select source:src tests source-level tests; state:modified compares against a previous manifest for CI.

dbt_utils and dbt_expectations packages extend testing with expression_is_true, unique_combination_of_columns, recency and row-count checks; declare them in packages.yml and run dbt deps.

Descriptions live in schema .yml files at model, column, and source level; they surface in dbt docs and can be inherited via the docs: block with {% docs %} markdown files.

dbt docs generate builds catalog.json and manifest.json; dbt docs serve hosts the documentation site locally, including the DAG lineage graph and compiled SQL.

Lineage is derived from ref() and source() calls; hardcoded table names break lineage edges and hide dependencies from the DAG.

Exposures declare downstream consumers (dashboards, notebooks, ML models) in YAML with type, owner, depends_on and maturity, extending lineage past the warehouse boundary.

Groups and access modifiers (private/protected/public) restrict cross-project refs; contracts with enforced: true validate column names and data types at build time.

Model versions (versions:, latest_version:) allow backward-compatible schema evolution while consumers pin to a version via ref('model', v=1).

Meta fields attach arbitrary key-value governance data (owner, pii, sla) to models and columns; they are queryable from the manifest for automated policy checks.

dbt build artifacts (manifest.json, run_results.json, catalog.json) in target/ are the machine-readable source of truth for lineage and audit tooling; persist them in CI.

dbt_project.yml sets name, version, profile, model-paths, and per-folder configuration: models: project: folder: +materialized: table applies config hierarchically, with more specific paths overriding broader ones.

profiles.yml holds target connection settings and is kept outside the project (default ~/.dbt/) or supplied via env_var(); never commit credentials.

Targets (dev/prod) are selected with --target; dbt run --target prod uses that target's schema, threads and credentials. threads controls parallelism.

Commands: dbt run (models), dbt test, dbt build (models+tests+snapshots+seeds), dbt seed (CSV loads), dbt snapshot (SCD2), dbt compile, dbt deps, dbt source freshness.

Selectors: --select (s), --exclude, graph operators +model (parents), model+ (children), 2+model (two levels up), @model (parents and children of parents). Tag/ path/ config selectors also work.

Environments vary by target: use generate_schema_name macro or custom schema config to control whether custom schemas are prefixed with the target schema.

Slim CI runs dbt build --select state:modified+ --defer --state against production artifacts, building only changed models and their children.

Jobs in dbt Cloud (or an external orchestrator) define commands, schedules, and environment; production runs should use --fail-fast or build to stop on first error.

Snapshots implement SCD Type 2 with unique_key, strategy (timestamp or check), updated_at and a target_schema; dbt_valid_from/dbt_valid_to track history.