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/xo-medallion/
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/xo_medallion
source ../../.dbt-venv/bin/activate  # Or wherever your venv is

2. Verify Connection¶

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

3. Create New Model¶

Which materialization strategy?

Pattern	Use when
`incremental_strategy='merge'`	Default for all new Silver models — handles batch_replace corrections via RECORD_HASH check (ADR 020)
`materialized='table'`	Source data changes retroactively every run (e.g. Google Sheets)
`--full-refresh` flag	Bulk rebuild after a major Bronze incident or schema migration
`incremental_strategy='append'`	Legacy only — do not use for new models

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

# Canonical Silver model template (merge strategy — ADR 020)
cat > models/silver/client/model_name.sql << 'SQL'
{{
    config(
        materialized='incremental',
        unique_key='RECORD_KEY',
        incremental_strategy='merge',
        on_schema_change='sync_all_columns',
        tags=['client', 'entity']
    )
}}

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

{% if is_incremental() %}
-- Insert new records; update records corrected via Bronze batch_replace backfills
upsert_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               -- new records → INSERT
       OR bronze.RECORD_HASH != silver.RECORD_HASH  -- corrected records → UPDATE
)
{% else %}
upsert_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 upsert_records
SQL

3b. Determine Column Types (Silver Models Only)¶

Bronze tables store all API data as VARCHAR. Silver is where type conversion happens. Before writing column selects, inspect the Bronze source to know which columns need casting:

# Describe the Bronze table schema
snow sql -q "DESCRIBE TABLE WBP_DB_DEV.BRONZE.GLADLY_WORK_SESSIONS"

# Sample source data to verify format
snow sql -q "SELECT * FROM WBP_DB_DEV.BRONZE.GLADLY_WORK_SESSIONS LIMIT 5"

Use Snowflake's safe cast functions — they return NULL on failure instead of erroring:

Target Type	Function	Example
Timestamp	`TRY_TO_TIMESTAMP_NTZ(col) AS col`	`TRY_TO_TIMESTAMP_NTZ(CONTACT_STARTED_AT) AS CONTACT_STARTED_AT`
Number	`TRY_TO_NUMBER(col) AS col`	`TRY_TO_NUMBER(HANDLE_TIME_IN_SEC) AS HANDLE_TIME_IN_SEC`
Boolean	`IFF(UPPER(col) = 'TRUE', TRUE, FALSE) AS col`	`IFF(UPPER(IS_ACTIVE) = 'TRUE', TRUE, FALSE) AS IS_ACTIVE`
IDs / text	(keep as VARCHAR)	`CONTACT_ID,`

Add a comment on each cast line: -- VARCHAR → TIMESTAMP_NTZ

See ADR 013 for the full rationale.

4. Compile and Check SQL¶

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

# Check compiled SQL
cat target/compiled/xo_medallion/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
(Silver) Cast columns produce expected types: run DESCRIBE TABLE WBP_DB_DEV.SILVER.{TABLE} after dbt run and confirm typed columns
(Silver) No unexpected NULLs from TRY_* casts: verify NULL counts match source nulls, not conversion failures

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/xo_medallion/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/xo_medallion
    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/xo_medallion 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/xo-medallion/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