migrating-sql-to-dbt

# dbt Migration

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

## Workflow

### 1. Analyze Legacy SQL

```bash
cat <legacy_sql_file>
```

Identify all tables referenced in the query.

### 2. Check What Already Exists

```bash
# 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

```yaml
# 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:**
```bash
dbt build --select <staging_model>
```

### 5. Build Intermediate Layer (if needed)

Extract complex joins/logic into intermediate models.

**Build incrementally:**
```bash
dbt build --select <intermediate_model>
```

### 6. Build Mart Layer

Final business-facing model with aggregations.

### 7. Validate Migration

```bash
# 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