dbt-migration-ms-sql-server_skill

Overview

This skill converts Microsoft SQL Server and Azure Synapse T-SQL DDL (views, tables, stored procedures) into production-quality dbt models that run on Snowflake. It preserves business logic while applying dbt best practices, generating model SQL, schema.yml documentation and tests, and a recommended materialization strategy. The outputs are ready for data engineers to include in a dbt project and to validate with dbt test and run.

How this skill works

Provide the T-SQL DDL or stored-procedure SQL and the skill parses objects, maps T-SQL types/functions to Snowflake equivalents, and emits one or more dbt model files with explicit casts and aliases. It also generates a schema.yml with column descriptions, tests (unique, not_null, relationships), and a config block specifying materialization and incremental logic when appropriate. Inline comments identify converted T-SQL idioms (IDENTITY, TOP, #temp tables, TRY...CATCH) and explain architectural choices.

When to use it

• Converting SQL Server/Azure Synapse views or tables to dbt models for Snowflake
• Translating stored procedures or procedural T-SQL into declarative dbt models/CTEs
• Generating schema.yml files with tests and column documentation
• Migrating T-SQL-specific syntax (IDENTITY, TOP, #temp tables, TRY...CATCH) to Snowflake patterns
• Designing incremental models and deciding materialization (view/table/incremental)

Best practices

• Break complex procedures into small, testable dbt models using CTEs and ref() for dependencies
• Always cast columns explicitly with ::TYPE and include column aliases for documentation clarity
• Prefer view materialization for logic-only conversions and table/incremental for large historical loads
• Add not_null and unique tests for primary keys and relationships tests for foreign keys
• Replace procedural flows with declarative SQL; use Snowflake functions instead of procedural constructs

Example use cases

• Convert a SQL Server view that used TOP and NOLOCK into a dbt view compatible with Snowflake, with LIMIT and removed NOLOCK comments
• Transform a T-SQL stored procedure that populated temp tables into multiple dbt models using transient or temporary staging models and a final incremental model
• Generate schema.yml for a migrated table including descriptions, unique/not_null tests and a relationships test to its upstream model
• Migrate IDENTITY columns into Snowflake AUTOINCREMENT or sequence definitions and update downstream logic to use the new key