16. Cheat sheet

Everything worth keeping within reach.

Commands

dbt debug                  # test connection + config. start here.
dbt deps                   # install packages from packages.yml
dbt seed                   # load CSVs from seeds/
dbt run                    # build models
dbt test                   # run tests
dbt snapshot               # capture SCD2 history
dbt build                  # seed + snapshot + run + test, in
                           # graph order. USE THIS.
dbt compile                # render SQL, execute nothing
dbt parse                  # parse only, write manifest
dbt retry                  # re-run failures from last invocation
dbt clean                  # delete target/ and dbt_packages/
dbt ls                     # list selected resources
dbt source freshness       # check source staleness
dbt docs generate          # manifest + catalog
dbt docs serve             # local docs site
dbt run-operation <macro>  # run a macro standalone
dbt show --select <model>  # preview a model's results

Common flags

--select / -s        what to build
--exclude            what to skip
--target / -t        which profile output
--full-refresh       rebuild incrementals from scratch
--threads N          override profile threads
--vars '{"k": "v"}'  override vars
--state DIR          directory with a previous manifest
--defer              resolve unbuilt refs from state
--favor-state        always prefer state for unselected nodes
--fail-fast          stop at first failure
--empty              build with zero rows (schema check)
--no-partial-parse   ignore the parse cache

Selection syntax

customers                    one model
+customers                   it and all ancestors
customers+                   it and all descendants
+customers+                  its whole lineage
2+customers                  two levels of ancestors
customers+1                  one level of descendants
@customers                   ancestors, it, descendants,
                             and their ancestors

"model_a model_b"            union (space)
"model_a,tag:finance"        intersection (comma)
--exclude tag:deprecated     subtraction

Methods

tag:finance
path:models/marts
file:customers.sql
package:dbt_utils
config.materialized:incremental
config.meta.contains_pii:true
test_type:unit | singular | generic
test_name:unique
source:jaffle_shop+
exposure:executive_dashboard
group:finance
access:public
version:latest
resource_type:model
state:new | modified | modified.body | modified.configs
result:error | fail | warn | success
source_status:fresher

The ones you will actually use

dbt build --select "state:modified+" --state ./prod \
          --defer --favor-state          # slim CI
dbt build --select tag:hourly            # by cadence
dbt build --select +customers            # a model and its inputs
dbt retry                                # after a failure
dbt build --exclude "test_type:unit"     # production
dbt test  --select "test_type:unit"      # CI

Project files

dbt_project.yml    the project. in git.
profiles.yml       the connection. NOT in git.
                   default ~/.dbt/profiles.yml
packages.yml       dependencies
package-lock.yml   pinned versions. COMMIT THIS.

# dbt_project.yml essentials
name: 'jaffle_shop'
profile: 'jaffle_shop'
require-dbt-version: [">=1.9.0", "<2.0.0"]

models:
  jaffle_shop:              # ← project name, always
    +materialized: view
    staging:
      +materialized: view
    marts:
      +materialized: table
      +schema: marts

Materializations

	Build	Storage	Read	Use for
view	instant	none	full query	staging, small
table	full	full copy	fast	marts
incremental	delta	full copy	fast	large, growing
ephemeral	none	none	inlined CTE	rarely
materialized_view	warehouse-managed	full copy	fast	live freshness

Incremental

{{ config(
    materialized     = 'incremental',
    unique_key       = 'order_id',
    incremental_strategy = 'merge',
    on_schema_change = 'append_new_columns',
    cluster_by       = ['order_date']
) }}

select * from {{ ref('stg_orders') }}

{% if is_incremental() %}
where updated_at > (
    select dateadd(day, -3, max(updated_at)) from {{ this }}
)
{% endif %}

Strategies

append            blind insert. immutable rows only.
merge             match on unique_key. THE DEFAULT.
delete+insert     for adapters without MERGE.
insert_overwrite  whole partitions. must be complete.
microbatch        1.9+. time-batched, retryable,
                  backfillable. event_time + batch_size.

on_schema_change

ignore              DANGEROUS. drops new columns silently.
fail                error and stop.
append_new_columns  THE SENSIBLE DEFAULT.
sync_all_columns    adds AND DROPS. data loss.

Rules

• Test unique on unique_key. Always.
• Use a lookback window — a strict high-water mark drops late data forever.
• Lookbacks require an idempotent strategy (merge, not append).
• Deletes never arrive. Absence does not propagate.
• You should be able to --full-refresh it.

Tests

models:
  - name: customers
    columns:
      - name: customer_id
        tests: [unique, not_null]
      - name: status
        tests:
          - accepted_values:
              values: ['active', 'churned']
      - name: manager_id
        tests:
          - relationships:
              to: ref('employees')
              field: employee_id
      - name: address
        tests:
          - not_null:
              config:
                severity: warn
                error_if: ">1000"
                store_failures: true

-- custom generic: tests/generic/test_positive.sql
{% test positive(model, column_name) %}
select * from {{ model }} where {{ column_name }} <= 0
{% endtest %}

# unit test
unit_tests:
  - name: test_order_size
    model: orders
    given:
      - input: ref('stg_orders')
        rows: [{order_id: 1, amount: 150}]
    expect:
      rows: [{order_id: 1, size: 'large'}]

A test is a query that should return zero rows.

Sources, seeds, snapshots

sources:
  - name: jaffle_shop
    database: raw
    schema: jaffle_shop
    loaded_at_field: _fivetran_synced
    freshness:
      warn_after:  {count: 12, period: hour}
      error_after: {count: 24, period: hour}
    tables:
      - name: raw_orders

snapshots:
  - name: orders_snapshot
    relation: source('jaffle_shop', 'raw_orders')
    config:
      unique_key: order_id
      strategy: timestamp          # or 'check'
      updated_at: updated_at
      invalidate_hard_deletes: true

Snapshots cannot be rebuilt. Never casually --full-refresh one.

Jinja context

ref('model')              another model
ref('project','model')    cross-project
source('src','table')     a source
this                      the current relation
config(...)               set config
var('name', default)      project variable
env_var('NAME', default)  environment variable — RETURNS A STRING
target.name               'dev' | 'prod' | 'ci'
target.schema/.database   the active target's
is_incremental()          incremental state
execute                   false during parse — GUARD run_query
run_query(sql)            execute at compile time
log(msg, info=True)       print during a run
adapter.dispatch(...)     cross-warehouse macro
modules.datetime / .re
builtins.ref              the pre-override version

Patterns

{# limit dev data — the highest-value pattern in dbt #}
{% if target.name != 'prod' %}
where event_date >= dateadd(day, -7, current_date)
{% endif %}

{# loop with trailing comma #}
{% for m in methods %}
  sum(...) as {{ m }}{% if not loop.last %},{% endif %}
{% endfor %}

{# macro #}
{% macro cents_to_dollars(col, dp=2) %}
  round({{ col }} / 100.0, {{ dp }})
{% endmacro %}

{# cross-adapter #}
{% macro my_fn(x) %}
  {{ return(adapter.dispatch('my_fn')(x)) }}
{% endmacro %}
{% macro default__my_fn(x) %}...{% endmacro %}
{% macro bigquery__my_fn(x) %}...{% endmacro %}

Config precedence

CLI flags  >  {{ config() }}  >  .yml  >  dbt_project.yml

scalars   replaced   (materialized, unique_key)
lists     CLOBBERED  (tags — restate inherited ones!)
dicts     merged     (meta)
hooks     APPENDED   (all run, broad → narrow)

Custom schema override

-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
    {%- if target.name == 'prod' and custom_schema_name is not none -%}
        {{ custom_schema_name | trim }}
    {%- elif custom_schema_name is none -%}
        {{ target.schema }}
    {%- else -%}
        {{ target.schema }}_{{ custom_schema_name | trim }}
    {%- endif -%}
{%- endmacro %}

Gate on target.name == 'prod', never on != 'dev'.

Artifacts

target/manifest.json         the project, resolved
target/run_results.json      what happened
target/catalog.json          real warehouse columns
target/sources.json          freshness results
target/partial_parse.msgpack the parse cache
target/compiled/             YOUR SQL, Jinja resolved
target/run/                  compiled SQL + DDL

Never commit these. Publish manifest.json from production to object storage for slim CI.

Packages worth having

packages:
  - package: dbt-labs/dbt_utils            # essential
  - package: calogica/dbt_expectations     # more tests
  - package: dbt-labs/codegen              # generate YAML
  - package: dbt-labs/audit_helper         # compare relations
  - package: dbt-labs/dbt_project_evaluator # audit the project

Troubleshooting

Symptom	First thing to try
Cannot connect	dbt debug
Macro change has no effect	rm target/partial_parse.msgpack
Model does the wrong thing	read target/compiled/.../model.sql
Wrong config applied	dbt ls --select model --output json
Landed in the wrong schema	custom schemas concatenate — ch11
Column missing after a run	on_schema_change was ignore
Duplicates appearing	unique_key is not unique — test it
Rows missing	strict high-water mark — add a lookback
Everything modified in CI	version mismatch or env_var in config
Nothing modified in CI	stale state manifest
CI used a stale relation	add --favor-state
Dashboard suddenly slow	view stacked on views — materialise
Tests pass, data is old	no source freshness check

The ten rules

1. ref() everywhere. A hard-coded name breaks ordering, environments, lineage and state selection at once.
2. unique + not_null on every primary key. The floor.
3. dbt source freshness before the build. Stale data passes every test.
4. dbt build, not run then test. Failures stop the branch.
5. Marts are tables. Views stack badly.
6. Incremental only when you have measured the pain — and always with a lookback and a uniqueness test.
7. Set on_schema_change explicitly.
8. Limit dev data with target.name. The largest cost saving available.
9. Snapshots are irreplaceable data. Schedule them, back them up, never casually refresh.
10. When the file stops looking like the SQL that runs, you have gone too far.

The guide

1. What dbt is, and why it exists
2. The project
3. Models and ref()
4. Materializations
5. Incremental models
6. Sources, seeds and snapshots
7. A dbt run traced
8. Jinja and macros
9. Tests and contracts
10. Documentation and lineage
11. Configuration and precedence
12. State, artifacts and slim CI
13. Tuning and operations
14. Failure modes
15. When to use dbt, and when not
16. Cheat sheet — you are here