dbt Development Workflow¶

This guide covers the standard workflow for developing, testing, and deploying dbt models in the xo-data platform.

Prerequisites¶

dbt project initialized in apps/dbt_projects/dbt_xo_models/
Virtual environment with dbt-snowflake: apps/.dbt-venv/
Snowflake credentials in environment variables or profiles.yml
BRONZE tables populated by xo-foundry pipelines

Local Development Workflow¶

1. Activate dbt Environment¶

cd apps/dbt_projects/dbt_xo_models
source ../../.dbt-venv/bin/activate  # Or wherever your venv is

2. Verify Connection¶

dbt debug
# Expected output: "All checks passed!"

3. Create New Model¶

# Create model file
touch models/silver/client/model_name.sql

# Basic model template
cat > models/silver/client/model_name.sql << 'SQL'
{{
    config(
        materialized='incremental',
        unique_key='RECORD_KEY',
        incremental_strategy='append',
        tags=['client', 'entity']
    )
}}

WITH bronze_source AS (
    SELECT * FROM {{ source('bronze_client', 'TABLE_NAME') }}
),

{% if is_incremental() %}
new_records AS (
    SELECT bronze.*
    FROM bronze_source AS bronze
    LEFT JOIN {{ this }} AS silver
        ON bronze.RECORD_KEY = silver.RECORD_KEY
    WHERE silver.RECORD_KEY IS NULL
)
{% else %}
new_records AS (
    SELECT * FROM bronze_source
)
{% endif %}

SELECT
    -- Add your columns here
    *,
    CURRENT_TIMESTAMP() AS DBT_LOADED_AT,
    '{{ invocation_id }}' AS DBT_INVOCATION_ID
FROM new_records
SQL

4. Compile and Check SQL¶

# Compile model to see generated SQL
dbt compile --select model_name

# Check compiled SQL
cat target/compiled/dbt_xo_models/models/silver/client/model_name.sql

5. Run Model¶

# Run single model
dbt run --select model_name

# Run with full-refresh (ignore incremental logic)
dbt run --select model_name --full-refresh

# Run all models for a client
dbt run --select tag:warbyparker

6. Test Model¶

# Run tests for specific model
dbt test --select model_name

# Run all tests
dbt test

7. Generate Documentation¶

# Generate docs
dbt docs generate

# Serve docs locally (opens browser)
dbt docs serve

Testing Checklist¶

Before committing a new model:

dbt compile --select model_name succeeds
dbt run --select model_name succeeds
dbt test --select model_name passes
Check row counts match expectations
Verify incremental logic: run twice, second run should insert 0 rows (if no new data)
Review compiled SQL for correctness

Common dbt Commands¶

Development¶

# Compile without running
dbt compile --select model_name

# Run specific model
dbt run --select model_name

# Run model and downstream dependencies
dbt run --select model_name+

# Run model and upstream dependencies
dbt run --select +model_name

# Run specific tag
dbt run --select tag:warbyparker

# Run full refresh
dbt run --full-refresh

# Run specific folder
dbt run --select models/silver/warbyparker

Testing¶

# Test specific model
dbt test --select model_name

# Test specific column
dbt test --select model_name,column:RECORD_KEY

# Run only unique tests
dbt test --select test_type:unique

Debugging¶

# Show compiled SQL for a model
dbt compile --select model_name
cat target/compiled/dbt_xo_models/models/path/to/model.sql

# List all models
dbt ls

# List models with specific tag
dbt ls --select tag:warbyparker

# Show model dependencies
dbt ls --select model_name+ --output json

Integrating with Airflow¶

Update DAG Configuration¶

Enable dbt in pipeline YAML:

# packages/xo-foundry/configs/client-pipeline.yaml
globals:
  dbt:
    enabled: true
    project_dir: /usr/local/airflow/dbt/dbt_xo_models
    target: dev  # Overridden by ENVIRONMENT variable

Regenerate DAG¶

uv run --package xo-foundry python -m xo_foundry.cli.generate_dags generate-dag \
  --config /path/to/config.yaml \
  --output /path/to/dags/

Deploy to Astronomer¶

# Copy dbt project to Airflow dags folder
cp -r apps/dbt_projects/dbt_xo_models apps/airflow/xo-pipelines/dags/

# Start local Airflow
cd apps/airflow/xo-pipelines
astro dev start

# Deploy to production
astro deploy <deployment-id>

Troubleshooting¶

"Profile not found"¶

Problem: dbt can't find Snowflake credentials

Solution:

# Check profiles.yml location
ls ~/.dbt/profiles.yml

# Or create project-level profiles.yml
cp ~/.dbt/profiles.yml apps/dbt_projects/dbt_xo_models/profiles.yml

"Compilation Error"¶

Problem: Jinja2 syntax error in model

Solution:

# Check compiled SQL
dbt compile --select model_name --debug

# Common issues:
# - Missing {% endif %} for {% if %}
# - Typo in {{ ref('model') }}
# - Missing comma in config()

"Database Error: Object does not exist"¶

Problem: Source table not found

Solution:

# Verify source exists
dbt run-operation query --args '{sql: "SHOW TABLES IN WBP_DB_DEV.BRONZE"}'

# Check sources.yml has correct database/schema
cat models/sources.yml

"No unique_key for incremental model"¶

Problem: Incremental model doesn't specify unique_key

Solution:

{{
    config(
        materialized='incremental',
        unique_key='RECORD_KEY',  # Add this line
        incremental_strategy='append'
    )
}}

Best Practices¶

Model Organization¶

models/
├── silver/           # Historical enriched tables
│   ├── warbyparker/
│   │   ├── _warbyparker__sources.yml
│   │   ├── _warbyparker__models.yml
│   │   └── silver_warbyparker_*.sql
│   └── condenast/
└── gold/            # Business metrics
    └── metrics/

Naming Conventions¶

Models: {layer}_{domain}_{entity}.sql
Example: silver_warbyparker_contact_timestamps.sql
Sources: Prefix with bronze_{domain}
Tags: Use domain and entity tags for selection

Configuration¶

Always specify:

{{
    config(
        materialized='incremental',      # or 'table', 'view'
        unique_key='RECORD_KEY',         # for incremental
        incremental_strategy='append',   # or 'merge', 'delete+insert'
        on_schema_change='sync_all_columns',  # handle schema changes
        tags=['domain', 'entity']        # for selective runs
    )
}}

Testing¶

Minimum tests for every model:

columns:
  - name: RECORD_KEY
    tests:
      - unique
      - not_null
  - name: DBT_LOADED_AT
    tests:
      - not_null

Environment Variables¶

Set these in your shell or .env:

export SNOWFLAKE_ACCOUNT=your_account
export SNOWFLAKE_USER=your_user
export SNOWFLAKE_PASSWORD=your_password
export SNOWFLAKE_ROLE=SYSADMIN
export SNOWFLAKE_WAREHOUSE=XO_DEV_WH
export ENVIRONMENT=dev  # or 'prod'

References¶

dbt Documentation: https://docs.getdbt.com/
Snowflake dbt Adapter: https://docs.getdbt.com/reference/warehouse-setups/snowflake-setup
Astronomer Cosmos: https://astronomer.github.io/astronomer-cosmos/
xo-data dbt Integration: .claude/ongoing/projects/dbt-integration.md