refactoring-dbt-models

dbt Refactoring

Find ALL downstream dependencies before changing. Refactor in small steps. Verify output after each change.

Workflow

1. Analyze Current Model

cat models/<path>/<model_name>.sql

Identify refactoring opportunities:

• CTEs longer than 50 lines → extract to intermediate model
• Logic repeated across models → extract to macro
• Multiple joins in sequence → split into steps
• Complex WHERE clauses → extract to staging filter

2. Find All Downstream Dependencies

CRITICAL: Never refactor without knowing impact.

# Get full dependency tree (model and all its children)
dbt ls --select model_name+ --output list

# Find all models referencing this one
grep -r "ref('model_name')" models/ --include="*.sql"

Report to user: "Found X downstream models: [list]. These will be affected by changes."

3. Check What Columns Downstream Models Use

BEFORE changing any columns, check what downstream models reference:

# For each downstream model, check what columns it uses
cat models/<path>/<downstream_model>.sql | grep -E "model_name\.\w+|alias\.\w+"

If downstream models reference specific columns, you MUST ensure those columns remain available after refactoring.

4. Plan Refactoring Strategy

Opportunity	Strategy
Long CTE	Extract to intermediate model
Repeated logic	Create macro in macros/
Complex join	Split into intermediate models
Multiple concerns	Separate into focused models

5. Execute Refactoring

Pattern: Extract CTE to Model

Before:

-- orders.sql (200 lines)
with customer_metrics as (
    -- 50 lines of complex logic
),
order_enriched as (
    select ...
    from orders
    join customer_metrics on ...
)
select * from order_enriched

After:

-- customer_metrics.sql (new file)
select
    customer_id,
    -- complex logic here
from {{ ref('customers') }}

-- orders.sql (simplified)
with order_enriched as (
    select ...
    from {{ ref('raw_orders') }} orders
    join {{ ref('customer_metrics') }} cm on ...
)
select * from order_enriched