---

name: dbt-migration description: Complete workflow for migrating legacy database DDL (views, tables, stored procedures) to dbt projects on Snowflake. This skill orchestrates the full migration lifecycle including discovery, planning, placeholder model creation, view conversion, stored procedure transformation, end-to-end testing, and deployment. Use this skill when planning or executing database migrations to dbt, delegating platform-specific syntax translation to source-specific skills.

Database to dbt Migration Workflow

Purpose

Guide AI agents through the complete migration lifecycle from legacy database systems (SQL Server, Oracle, Teradata, etc.) to production-quality dbt projects on Snowflake. This skill defines a structured, repeatable process while delegating platform-specific syntax translation to dedicated source-specific skills.

When to Use This Skill

Activate this skill when users ask about:

• Planning a database migration to dbt
• Organizing legacy scripts for migration
• Creating placeholder models with correct datatypes
• Converting views and stored procedures to dbt models
• Testing migration results against source systems
• Deploying migrated dbt projects to production
• Understanding the overall migration workflow

For platform-specific syntax translation, delegate to:

• dbt-migration-ms-sql-server - SQL Server / Azure Synapse T-SQL
• dbt-migration-oracle - Oracle PL/SQL
• dbt-migration-teradata - Teradata SQL and BTEQ
• dbt-migration-bigquery - Google BigQuery
• dbt-migration-redshift - Amazon Redshift
• dbt-migration-postgres - PostgreSQL / Greenplum / Netezza
• dbt-migration-db2 - IBM DB2
• dbt-migration-hive - Hive / Spark / Databricks
• dbt-migration-vertica - Vertica
• dbt-migration-sybase - Sybase IQ

---

Migration Workflow Overview

The migration process follows seven sequential phases. Each phase has entry criteria, deliverables, and validation gates that must pass before advancing.

Copyflowchart LR
    P1[1-Discovery] --> P2[2-Planning] --> P3[3-Placeholders] --> P4[4-Views]
    P4 --> P5[5-Table Logic] --> P6[6-Testing] --> P7[7-Deployment]

Phase Activities

• Phase 1 - Discovery
  • Inventory source objects
  • Map dependencies
  • Document volumes
  • Assess complexity
• Phase 2 - Planning
  • Create folder structure
  • Map to medallion layers
  • Define naming rules
• Phase 3 - Placeholders
  • Create NULL cast models
  • Generate _models.yml
  • Run compile test
  • Document schema
• Phase 4 - Views
  • Translate syntax
  • Apply CTE patterns
  • Add dbt tests
• Phase 5 - Table Logic
  • Analyze stored procedures
  • Convert to declarative SQL
  • Implement incremental patterns
• Phase 6 - Testing
  • Validate row counts
  • Compare checksums
  • Test business rules
  • Create mock data
• Phase 7 - Deployment
  • Deploy to dev
  • Run full validation
  • Document cutover plan
  • Deploy to production
  • Enable monitoring

Phase Metadata for Agent Execution

Skill Delegation Decision Tree

Copyflowchart TD
    START[Migration Task] --> Q1{Task Type}

    Q1 -->|Syntax| PLATFORM[Identify Platform]
    Q1 -->|Structure| ARCH[dbt-architecture]
    Q1 -->|Materialization| MAT[dbt-materializations]
    Q1 -->|Testing| TEST[dbt-testing]
    Q1 -->|Deploy| DEPLOY[dbt-commands]

    PLATFORM --> P_CHECK{Platform}
    P_CHECK -->|SQL Server| MSSQL[dbt-migration-ms-sql-server]
    P_CHECK -->|Oracle| ORA[dbt-migration-oracle]
    P_CHECK -->|Teradata| TERA[dbt-migration-teradata]
    P_CHECK -->|BigQuery| BQ[dbt-migration-bigquery]
    P_CHECK -->|Redshift| RS[dbt-migration-redshift]
    P_CHECK -->|PostgreSQL| PG[dbt-migration-postgres]
    P_CHECK -->|DB2| DB2[dbt-migration-db2]
    P_CHECK -->|Hive/Spark| HIVE[dbt-migration-hive]
    P_CHECK -->|Vertica| VERT[dbt-migration-vertica]
    P_CHECK -->|Sybase| SYB[dbt-migration-sybase]

Key Deliverables Per Phase

---

Phase 1: Discovery and Assessment

Create a complete inventory of source database objects and understand dependencies, volumes, and complexity to inform migration planning.

Phase 1 Activities

1. Inventory source objects: Query system catalogs for tables, views, procedures, functions
2. Document dependencies: Map object dependencies to determine migration order
3. Assess complexity: Categorize objects as Low/Medium/High/Custom complexity
4. Create migration tracker: Document objects in spreadsheet or issue tracker

Complexity Assessment

Phase 1 Skill References

Phase 1 Checklist

• All tables, views, procedures inventoried
• Row counts documented
• Object dependencies mapped
• Complexity assessment complete
• Migration tracker created
• Refresh frequencies identified

---

Phase 2: Planning and Organization

Organize legacy scripts, map objects to the dbt medallion architecture, and establish naming conventions before any conversion begins.

Phase 2 Activities

1. Organize legacy scripts: Create folder structure (tables/, views/, stored_procedures/, functions/)
2. Map to medallion layers: Assign objects to Bronze/Silver/Gold with appropriate prefixes
3. Define naming conventions: Follow dbt-architecture skill patterns
4. Create dependency graph: Visualize migration order
5. Establish validation criteria: Define success metrics per object

Layer Mapping Reference

Phase 2 Skill References

Phase 2 Checklist

• Legacy scripts organized in folders
• All objects mapped to medallion layers
• Naming conventions documented
• Dependency graph created
• Migration order established
• Validation criteria defined

---

Phase 3: Create Placeholder Models

Create empty dbt models with correct column names, data types, and schema documentation before adding any transformation logic. This establishes the contract for downstream consumers.

Phase 3 Activities

1. Generate placeholder models: Create SQL files with null::datatype as column_name pattern and where false
2. Map datatypes: Use platform-specific skill for datatype conversion to Snowflake types
3. Create schema documentation: Generate _models.yml with column descriptions and tests
4. Validate compilation: Run dbt compile --select tag:placeholder
5. Track status: Add placeholder tag to config for tracking

Placeholder Model Pattern

Copy{{ config(materialized='ephemeral', tags=['placeholder', 'bronze']) }}

select
    null::integer as column_id,
    null::varchar(100) as column_name,
    -- ... additional columns with explicit types
where false

Phase 3 Skill References

Phase 3 Checklist

• Placeholder model created for each target table
• All columns have explicit datatype casts
• Column names follow naming conventions
• _models.yml created with descriptions and tests
• All placeholder models compile successfully
• Placeholder tag applied for tracking

---

Phase 4: Convert Views

Convert source database views to dbt models, starting with simple views before tackling complex ones. Views are typically easier than stored procedures as they contain declarative SQL.

Phase 4 Activities

1. Prioritize by complexity: Simple views (no joins) → Join views → Aggregate views → Complex views
2. Apply syntax translation: Delegate to platform-specific skill (see Related Skills)
3. Structure with CTEs: Use standard CTE pattern from dbt-modeling skill
4. Add tests: Define tests in _models.yml using dbt-testing skill patterns
5. Replace placeholder logic: Update placeholder SELECT with converted logic

Phase 4 Skill References

Phase 4 Checklist

• Views prioritized by complexity
• Platform-specific syntax translated (delegate to source skills)
• CTE pattern applied consistently
• dbt tests added for each view
• Converted views compile successfully
• Inline comments document syntax changes

---

Phase 5: Convert Table Logic from Stored Procedures

Transform procedural stored procedure logic into declarative dbt models, selecting appropriate materializations for different ETL patterns.

Phase 5 Activities

1. Analyze ETL patterns: Identify Full Refresh, SCD Type 1/2, Append, Delete+Insert patterns
2. Map to materializations: Use pattern-to-materialization mapping from dbt-materializations skill
3. Break complex procedures: Split single procedures into multiple intermediate/final models
4. Convert procedural constructs: Replace cursors, temp tables, variables with declarative SQL
5. Document decisions: Add header comments explaining conversion approach

Pattern Mapping Reference

Procedural to Declarative Conversion

Phase 5 Skill References

Phase 5 Checklist

• All stored procedures analyzed for patterns
• ETL patterns mapped to dbt materializations
• Complex procedures broken into multiple models
• Procedural logic converted to declarative SQL
• Conversion decisions documented in model headers
• All converted models compile successfully

---

Phase 6: End-to-End Testing and Validation

Verify that migrated dbt models produce identical results to source system, using multiple validation techniques to ensure data integrity.

Phase 6 Activities

1. Row count validation: Compare total counts between source and target
2. Column checksum validation: Compare row-level hashes to identify differences
3. Business rule validation: Verify calculated fields match source logic
4. Aggregate validation: Compare summary metrics (sums, counts, averages)
5. Mock data testing: Create seed fixtures for complex transformation testing
6. Incremental validation: Test both full-refresh and incremental runs
7. Document results: Create validation report for each migrated object

Validation Techniques

Phase 6 Skill References

Phase 6 Checklist

• Row count validation queries created
• Checksum comparison implemented
• Business rule tests written
• Aggregate metrics compared
• Incremental models tested (full refresh + incremental)
• All validation queries pass
• Discrepancies documented and resolved
• Validation report completed

---

Phase 7: Deployment and Cutover

Deploy validated dbt models to production with a clear cutover plan and monitoring strategy.

Phase 7 Activities

1. Deploy to Development: Run dbt build --target dev and validate
2. Deploy to Test/UAT: Run full validation suite with --store-failures
3. Create cutover plan: Document pre-cutover, cutover, post-cutover, and rollback steps
4. Deploy to Production: Execute deployment with production data
5. Configure scheduled runs: Set up Snowflake tasks or dbt Cloud scheduling
6. Monitor post-deployment: Track run duration, row counts, test failures, performance

Cutover Plan Template

Phase 7 Skill References

Phase 7 Checklist

• Development deployment successful
• Test/UAT deployment successful
• Cutover plan documented
• Rollback procedure documented
• Stakeholder sign-off obtained
• Production deployment successful
• Scheduled runs configured
• Monitoring set up
• Migration marked complete

---

Related Skills

Workflow Skills

• dbt-architecture: Project structure, medallion layers, naming conventions
• dbt-modeling: CTE patterns, SQL structure, layer-specific templates
• dbt-testing: Data quality tests, dbt_constraints, singular tests
• dbt-materializations: Incremental strategies, snapshots, Python models
• dbt-performance: Clustering keys, warehouse sizing, query optimization
• dbt-commands: dbt CLI operations, model selection syntax
• dbt-core: Installation, configuration, package management
• snowflake-cli: Snowflake operations, deployment commands

Platform-Specific Translation Skills

For syntax translation, delegate to the appropriate source-specific skill:

---

Quick Reference: Phase Summary

---

Validation Hook Integration

Validation hooks automatically enforce quality standards when models are written or edited.

Quality Gates

Before advancing to each phase, ensure:

1. All models compile: dbt compile
2. Validation hooks pass: Check Claude output for errors
3. Tests pass: dbt test
4. Documentation complete: dbt docs generate

Validation by Phase

Hook configuration and detailed validation rules are defined in .claude/settings.local.json.