dbt Migration

Don't convert everything at once. Build and validate layer by layer.

Workflow

1. Analyze Legacy SQL

cat <legacy_sql_file>

Identify all tables referenced in the query.

2. Check What Already Exists

# Search for existing models/sources that reference the table
grep -r "<table_name>" models/ --include="*.sql" --include="*.yml"
find models/ -name "*.sql" | xargs grep -l "<table_name>"

For each table referenced in the legacy SQL:

1. •Check if an existing model already references this table
2. •Check if a source definition exists
3. •If neither exists, ask user: "Table X not found - should I create it as a source?"

Only proceed to intermediate/mart layers after all dependencies exist.

3. Create Missing Sources

# models/staging/sources.yml
version: 2

sources:
  - name: raw_database
    schema: raw_schema
    tables:
      - name: orders
        description: Raw orders from source system
      - name: customers
        description: Raw customer records

4. Build Staging Layer

One staging model per source table. Follow existing project naming conventions.

Build before proceeding:

dbt build --select <staging_model>

5. Build Intermediate Layer (if needed)

Extract complex joins/logic into intermediate models.

Build incrementally:

dbt build --select <intermediate_model>

6. Build Mart Layer

Final business-facing model with aggregations.

7. Validate Migration

# Build entire lineage
dbt build --select +<final_model>
dbt show --select <final_model>

Migration Checklist

• • All source tables identified and documented
• • Sources.yml created with descriptions
• • Staging models: 1:1 with sources, renamed columns
• • Intermediate models: business logic extracted
• • Mart models: final aggregations
• • Each layer compiles successfully
• • Each layer builds successfully
• • Row counts match original (manual validation)
• • Tests added for key constraints

Common Migration Patterns

• •Nested subqueries → Separate models (staging → intermediate → mart)
• •Temp tables → Ephemeral materialization {{ config(materialized='ephemeral') }}
• •Hardcoded values → Variables {{ var("name") }}

Anti-Patterns

• •Converting entire legacy query to single dbt model
• •Skipping the staging layer
• •Not validating each layer before proceeding
• •Keeping hardcoded values instead of using variables
• •Not documenting business logic during migration