web_docs/docs/dbt/quick-reference.md

metadata

sidebar_position: 1

dbt Quick Reference

🚀 Common Commands

Running Models

# Run all models
dbt run

# Run specific model
dbt run --select stg_bronze_contacts

# Run model and all downstream dependencies
dbt run --select stg_bronze_contacts+

# Run model and all upstream dependencies
dbt run --select +contact_ai

# Full refresh (rebuild from scratch)
dbt run --full-refresh

# Run specific folder
dbt run --select staging
dbt run --select marts

Testing

# Run all tests
dbt test

# Test specific model
dbt test --select contact_ai

# Test specific type
dbt test --select test_type:unique
dbt test --select test_type:not_null

Documentation

# Generate documentation
dbt docs generate

# Serve documentation site (opens in browser)
dbt docs serve

# Serve on specific port
dbt docs serve --port 8001

Debugging

# Test database connection
dbt debug

# Compile SQL without running
dbt compile

# Show SQL for specific model
dbt compile --select contact_ai
# Then check target/compiled/...

# Preview model results
dbt show --select stg_bronze_contacts --limit 10

Packages

# Install packages from packages.yml
dbt deps

# Update packages
dbt deps --upgrade

🎯 Useful Flags

# Run with specific target (dev/prod)
dbt run --target prod

# Exclude specific models
dbt run --exclude staging

# Run by tag
dbt run --select tag:daily

# Fail fast (stop on first error)
dbt run --fail-fast

# Show detailed logs
dbt run --debug

🔍 Selection Syntax

# Model name
dbt run --select my_model

# Model and downstream
dbt run --select my_model+

# Model and upstream  
dbt run --select +my_model

# Model and both directions
dbt run --select +my_model+

# All models in folder
dbt run --select staging

# All models in folder (recursive)
dbt run --select staging.*

# By tag
dbt run --select tag:daily

# By source
dbt run --select source:bronze+

# Multiple selections (OR)
dbt run --select model_a model_b

# Intersection (AND)
dbt run --select staging,tag:daily

📊 Model Configuration

-- In .sql file
{{
    config(
        materialized='incremental',
        unique_key='id',
        on_schema_change='sync_all_columns',
        tags=['daily'],
        enabled=true
    )
}}

# In .yml file
models:
  - name: my_model
    config:
      materialized: table
      tags: ['hourly']

🧪 Test Examples

# Schema tests in .yml
columns:
  - name: id
    tests:
      - unique
      - not_null
      - relationships:
          to: ref('other_table')
          field: id
      - accepted_values:
          values: ['a', 'b', 'c']

-- Custom test in tests/
-- Should return 0 rows (empty = pass)
SELECT * FROM {{ ref('my_model') }}
WHERE invalid_condition

🔄 Incremental Strategies

-- Append (default)
{{ config(materialized='incremental') }}

-- Delete + insert
{{
    config(
        materialized='incremental',
        unique_key='id',
        incremental_strategy='delete+insert'
    )
}}

-- Merge (upsert)
{{
    config(
        materialized='incremental',
        unique_key='id',
        incremental_strategy='merge'
    )
}}

📝 Jinja Snippets

-- Conditional logic
{% if is_incremental() %}
    WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

-- Loop
{% for column in ['a', 'b', 'c'] %}
    {{ column }},
{% endfor %}

-- Reference model
{{ ref('other_model') }}

-- Reference source
{{ source('schema', 'table') }}

-- Use variable
{{ var('my_variable') }}

-- Use macro
{{ my_macro(argument) }}

🛠️ Macros

-- Define macro in macros/
{% macro cents_to_dollars(column_name, scale=2) %}
    ({{ column_name }} / 100)::numeric(16, {{ scale }})
{% endmacro %}

-- Use in model
SELECT {{ cents_to_dollars('price_cents') }} as price_dollars

📋 Project Setup

# Initialize new project
dbt init my_project

# Install packages
dbt deps

# Test connection
dbt debug

# Run project
dbt build  # Run + test everything

🐛 Troubleshooting

# Clear compiled files
dbt clean

# Full logs
dbt run --debug

# Compile only (no run)
dbt compile --select my_model

# Check for syntax errors
dbt parse

🎨 Common Patterns

Safe column selection

SELECT
    {{ dbt_utils.star(from=ref('my_table'), except=['sensitive_column']) }}
FROM {{ ref('my_table') }}

Generate surrogate key

{{ dbt_utils.generate_surrogate_key(['column1', 'column2']) }} as id

Union tables

{{ dbt_utils.union_relations(
    relations=[ref('table1'), ref('table2')]
) }}

Pivot

{{ dbt_utils.pivot(
    column='status',
    values=['pending', 'approved', 'rejected'],
    agg='sum',
    then_value='amount'
) }}