Dbt Migration Snowflake

Snowflake to dbt Model Conversion

Purpose

Transform Snowflake DDL (views, tables, stored procedures) into production-quality dbt models, maintaining the same business logic and data transformation steps while following dbt best practices.

When to Use This Skill

Activate this skill when users ask about:

• Converting Snowflake views or tables to dbt models
• Migrating Snowflake stored procedures to dbt
• Generating schema.yml files with tests and documentation
• Modernizing existing Snowflake SQL to follow dbt best practices

Task Description

You are a database engineer working for a hospital system. You need to convert Snowflake DDL to equivalent dbt code, maintaining the same business logic and data transformation steps while following dbt best practices.

Input Requirements

I will provide you the Snowflake DDL to convert.

Audience

The code will be executed by data engineers who are learning Snowflake and dbt.

Output Requirements

Generate the following:

1. One or more dbt models with complete SQL for every column
2. A corresponding schema.yml file with appropriate tests and documentation
3. A config block with materialization strategy
4. Explanation of key changes and architectural decisions
5. Inline comments highlighting any syntax that was converted

Conversion Guidelines

General Principles

• Replace procedural logic with declarative SQL where possible
• Break down complex procedures into multiple modular dbt models
• Implement appropriate incremental processing strategies
• Maintain data quality checks through dbt tests
• Use Snowflake SQL functions rather than macros whenever possible

Sample Response Format

-- dbt model: models/[domain]/[target_schema_name]/model_name.sql
{{ config(materialized='view') }}

/* Original Object: [database].[schema].[object_name]
   Source Platform: Snowflake
   Purpose: [brief description]
   Conversion Notes: [key changes]
   Description: [SQL logic description] */

WITH source_data AS (
    SELECT
        customer_id::INTEGER AS customer_id,
        customer_name::VARCHAR(100) AS customer_name,
        account_balance::NUMBER(18,2) AS account_balance,
        created_date::DATE AS created_date
    FROM {{ ref('upstream_model') }}
),

transformed_data AS (
    SELECT
        customer_id,
        UPPER(customer_name)::VARCHAR(100) AS customer_name_upper,
        account_balance,
        created_date,
        CURRENT_TIMESTAMP()::TIMESTAMP_NTZ AS loaded_at
    FROM source_data
)

SELECT
    customer_id,
    customer_name_upper,
    account_balance,
    created_date,
    loaded_at
FROM transformed_data

## models/[domain]/[target_schema_name]/_models.yml
version: 2

models:
  - name: model_name
    description: "Table description; converted from Snowflake [Original object name]"
    columns:
      - name: customer_id
        description: "Primary key - unique customer identifier"
        tests:
          - unique
          - not_null
      - name: customer_name_upper
        description: "Customer name in uppercase"
      - name: account_balance
        description: "Current account balance; Foreign key to OTHER_TABLE"
        tests:
          - relationships:
              to: ref('OTHER_TABLE')
              field: OTHER_TABLE_KEY
      - name: created_date
        description: "Date the customer record was created"
      - name: loaded_at
        description: "Timestamp when the record was loaded by dbt"

## dbt_project.yml (excerpt)
models:
  my_project:
    +materialized: view
    domain_name:
      +schema: target_schema_name

Specific Translation Rules

dbt Specific Requirements

• If the source is a view, use a view materialization in dbt
• Include appropriate dbt model configuration (materialization type)
• Add documentation blocks for a schema.yml
• Add descriptions for tables and columns
• Include relevant tests
• Define primary keys and relationships
• Assume that upstream objects are models
• Comprehensively provide all the columns in the output
• Break complex procedures into multiple models if needed
• Implement appropriate incremental strategies for large tables
• Use Snowflake SQL functions rather than macros whenever possible
• Always cast columns with explicit precision/scale using ::TYPE syntax (e.g., column_name::VARCHAR(100), amount::NUMBER(18,2)) to ensure output matches expected data types
• Always provide explicit column aliases for clarity and documentation

Performance Optimization

• Suggest clustering keys if needed
• Recommend materialization strategy (view vs table)
• Identify potential performance improvements

Snowflake to dbt Conversion Patterns

Since the source is Snowflake, focus on converting to dbt best practices:

Key Conversion Examples

-- Snowflake VIEW → dbt view model
CREATE VIEW schema.my_view AS SELECT ... →
{{ config(materialized='view') }}
SELECT ...

-- Snowflake TABLE with CTAS → dbt table model
CREATE TABLE schema.my_table AS SELECT ... →
{{ config(materialized='table') }}
SELECT ...

-- Snowflake MERGE pattern → dbt incremental
MERGE INTO target USING source ON ... →
{{ config(
    materialized='incremental',
    unique_key='id',
    merge_update_columns=['col1', 'col2']
) }}
SELECT ... FROM {{ ref('source_model') }}
{% if is_incremental() %}
WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

-- Snowflake STREAM/TASK → dbt incremental
CREATE STREAM my_stream ON TABLE source;
CREATE TASK my_task ... INSERT INTO target SELECT * FROM my_stream →
{{ config(materialized='incremental', unique_key='id') }}
SELECT * FROM {{ ref('source') }}
{% if is_incremental() %}
WHERE _metadata_timestamp > (SELECT MAX(_metadata_timestamp) FROM {{ this }})
{% endif %}

-- Stored procedure logic → CTE pattern
BEGIN ... multiple statements ... END →
WITH step1 AS (...), step2 AS (...), step3 AS (...)
SELECT * FROM step3

Snowflake-Specific Features in dbt

-- Clustering keys
{{ config(
    materialized='table',
    cluster_by=['date_col', 'category']
) }}

-- Transient tables (no Time Travel/Fail-safe)
{{ config(
    materialized='table',
    transient=true
) }}

-- Copy grants
{{ config(copy_grants=true) }}

-- Query tags
{{ config(query_tag='dbt_model_name') }}

Data Type Handling

Snowflake data types map directly - no conversion needed.

Dependencies

• List any upstream dependencies
• Suggest model organization in dbt project

Validation Checklist

• [] Every DDL statement has been accounted for in the dbt models
• [] SQL in models is compatible with Snowflake (already native)
• [] All business logic preserved
• [] All columns included in output
• [] Data types correctly mapped
• [] Functions translated to Snowflake equivalents
• [] Materialization strategy selected
• [] Tests added
• [] SQL logic description complete
• [] Table descriptions added
• [] Column descriptions added
• [] Dependencies correctly mapped
• [] Incremental logic (if applicable) verified
• [] Inline comments added for converted syntax

Related Skills

• $dbt-migration - For the complete migration workflow (discovery, planning, placeholder models, testing, deployment)
• $dbt-modeling - For CTE patterns and SQL structure guidance
• $dbt-testing - For implementing comprehensive dbt tests
• $dbt-architecture - For project organization and folder structure
• $dbt-materializations - For choosing materialization strategies (view, table, incremental, snapshots)
• $dbt-performance - For clustering keys, warehouse sizing, and query optimization
• $dbt-commands - For running dbt commands and model selection syntax
• $dbt-core - For dbt installation, configuration, and package management
• $snowflake-cli - For executing SQL and managing Snowflake objects