dbt-migration-redshift_skill

Overview

This skill converts Amazon Redshift DDL (views, tables, stored procedures) and Redshift SQL into production-ready dbt models compatible with Snowflake. It preserves business logic while applying dbt best practices: explicit typing, materializations, schema.yml tests, and Snowflake SQL idioms. The output includes dbt model SQL, schema.yml documentation and tests, a config block, inline conversion comments, and an explanation of key changes.

How this skill works

Provide the Redshift DDL or SQL objects to convert. The skill parses DDL, maps Redshift types and functions to Snowflake equivalents, removes Redshift-only constructs (DISTKEY/SORTKEY, COPY/UNLOAD, system catalog calls) and rewrites procedural logic into declarative dbt models and CTEs. It produces one or more dbt models with explicit casts and aliases, a schema.yml with tests and descriptions, and a config block with recommended materialization and clustering guidance.

When to use it

• Converting Redshift views or tables into Snowflake-backed dbt models
• Migrating Redshift PL/pgSQL procedures into modular dbt models or Snowflake Scripting
• Generating schema.yml files with tests and column documentation
• Translating Redshift-specific SQL (COPY, DISTKEY, SORTKEY, system catalogs) to Snowflake idioms
• Designing incremental or clustered materializations for large datasets

Best practices

• Prefer views for read-only logic and tables/incremental models for heavy transforms or large datasets
• Always cast columns with explicit precision/scale using ::TYPE and provide explicit aliases
• Break complex procedures into modular models with clear ref() dependencies
• Add tests (unique, not_null, relationships) and descriptive column/table docs in schema.yml
• Use Snowflake functions (CURRENT_TIMESTAMP(), NVL/COALESCE, APPROX_COUNT_DISTINCT) and recommend clustering keys instead of DIST/SORT

Example use cases

• Convert a Redshift view that joins hospital encounters and claims into a dbt view with preserved logic and explicit types
• Refactor a Redshift stored procedure that aggregates daily metrics into incremental dbt models and an orchestration model
• Replace Redshift COPY/UNLOAD pipelines with Snowflake COPY INTO and create staging models in dbt
• Generate schema.yml for migrated tables including unique/not_null tests and relationships to upstream models
• Translate Redshift system catalog queries to INFORMATION_SCHEMA or Snowflake table functions for monitoring