dbt Development Workflow¶

Last Updated: 2026-06-11

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/client/silver/model_name.sql

# Canonical Silver model template (merge strategy — ADR 020)
cat > models/client/silver/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.

3c. Add Tags to the `config()` Block¶

Every model needs tags so DAGs can select it. Tags follow the 4-dimension taxonomy in dbt-models.md §11. Domain (wbp, cnd, etc.) and layer (silver, gold) are inherited from dbt_project.yml — do not add them here.

Layer	Tags to add in `config()`
Silver	One source tag: `gladly`, `gsheets`, `maestro`, or `gdrive`
Gold `fct_`	`fct` + one metric tag: `contacts`, `qa`, `csat`, `fcr`, `durations`, `productivity`
Gold `agg_`	`agg`
Gold `rpt_`	`rpt` + one grain tag: `agent` or `account`
Gold `exp_`	`exp` + one grain tag: `agent` or `account`
Gold `int_`	No tags needed (ephemeral, not DAG-selectable)

-- Silver example
{{ config(materialized='incremental', unique_key='RECORD_KEY',
          incremental_strategy='merge', on_schema_change='sync_all_columns',
          tags=['gladly']) }}

-- fct_ example
{{ config(materialized='table', tags=['fct', 'contacts']) }}

-- rpt_ example
{{ config(materialized='view', tags=['rpt', 'agent']) }}

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/client/silver/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 (using domain tag)
dbt run --select tag:wbp

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 all models for a client (domain tag)
dbt run --select tag:wbp

# Run full refresh
dbt run --full-refresh

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

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:wbp
dbt ls --select "tag:wbp,tag:gladly+"

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

Integrating with Airflow¶

DAG Config Pattern¶

dbt DAGs use run_tags to select models. The Foundry factory composes the dbt selector automatically. Config files live in apps/airflow/xo-pipelines/dags/configs/.

# Example: apps/airflow/xo-pipelines/dags/configs/warbyparker-dbt-gladly.yaml
dag:
  domain: warbyparker
  pipeline_name: dbt_gladly
  pipeline_type: dbt_pipeline
  description: dbt Silver+Gold transformations for Gladly
  owner: data-engineering
  start_date: '2025-01-01'
  dagrun_timeout_minutes: 30
storage:
  snowflake:
    schema: SILVER
    connection_id: snowflake_dbt
    database: WBP_DB_DEV
    warehouse: XO_DEV_WH
  environments:
    prod:
      database: WBP_DB_PROD
      warehouse: XO_PROD_WH
transform:
  engine: dbt
  project_dir: /usr/local/airflow/dbt/xo_medallion
  connection_id: snowflake_dbt
  run_tags: ["wbp", "gladly"]      # Foundry composes: tag:wbp,tag:gladly+
  exclude_tags: ["exp"]            # Excludes exploratory views from the run

run_tags and select are mutually exclusive. Use select only for special cases that can't be expressed as a tag intersection (e.g., select: tag:core_ph_roster).

Regenerate DAG¶

After editing a config, regenerate the Python DAG file:

uv run xo-foundry generate-dag \
  --config apps/airflow/xo-pipelines/dags/configs/warbyparker-dbt-gladly.yaml \
  --output apps/airflow/xo-pipelines/dags/

# Regenerate all DAGs and verify no drift
uv run xo-foundry generate-all \
  --config-dir apps/airflow/xo-pipelines/dags/configs \
  --output apps/airflow/xo-pipelines/dags/ \
  --check

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 the client's source file has correct database/schema
cat models/warbyparker/_warbyparker__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¶

Domain-first structure — each client has its own folder:

models/
├── warbyparker/
│   ├── _warbyparker__sources.yml
│   ├── silver/
│   │   └── wbp_gladly_contact_timestamps.sql
│   ├── intermediate/
│   └── gold/
│       ├── fct_wbp_contacts.sql
│       ├── agg_wbp_contacts_channel_daily.sql
│       └── rpt_wbp_agent_performance_channel_daily.sql
├── condenast/
└── core/
    └── gold/          # dim_employee_*, rpt_master_*

Naming Conventions¶

Silver: {client}_{object}.sql — e.g., wbp_gladly_contact_timestamps.sql
Gold facts: fct_{client}_{object}.sql — e.g., fct_wbp_contacts.sql
Gold aggs: agg_{client}_{subject}_{grain}.sql — e.g., agg_wbp_contacts_channel_daily.sql
Gold reports: rpt_{client}_{purpose}.sql — e.g., rpt_wbp_agent_performance_channel_daily.sql
Client codes: wbp (Warby Parker), cnd (Condé Nast), jcr (J.Crew), core (shared)

See ADR 009 for full naming conventions.

Configuration¶

Tags depend on the model's layer — see Step 3c and dbt-models.md §11:

-- Silver model (source tag only — domain/layer inherited from dbt_project.yml)
{{
    config(
        materialized='incremental',
        unique_key='RECORD_KEY',
        incremental_strategy='merge',
        on_schema_change='sync_all_columns',
        tags=['gladly']
    )
}}

-- Gold fct_ model
{{ config(materialized='table', tags=['fct', 'contacts']) }}

-- Gold rpt_ model
{{ config(materialized='view', tags=['rpt', 'agent']) }}

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

dbt Development Workflow¶

Prerequisites¶

Local Development Workflow¶

1. Activate dbt Environment¶

2. Verify Connection¶

3. Create New Model¶

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

3c. Add Tags to the config() Block¶

4. Compile and Check SQL¶

5. Run Model¶

6. Test Model¶

7. Generate Documentation¶

Testing Checklist¶

Common dbt Commands¶

Development¶

Testing¶

Debugging¶

Integrating with Airflow¶

DAG Config Pattern¶

Regenerate DAG¶

Deploy to Astronomer¶

Troubleshooting¶

"Profile not found"¶

"Compilation Error"¶

"Database Error: Object does not exist"¶

"No unique_key for incremental model"¶

Best Practices¶

Model Organization¶

Naming Conventions¶

Configuration¶

Testing¶

Environment Variables¶

References¶

3c. Add Tags to the `config()` Block¶