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:

• One or more dbt models with complete SQL for every column

• A corresponding schema.yml file with appropriate tests and documentation

• A config block with materialization strategy

• Explanation of key changes and architectural decisions

• 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:

Snowflake Object dbt Equivalent Materialization

VIEW dbt model view

TABLE (static) dbt model table

TABLE (append) dbt model incremental (append)

TABLE (merge) dbt model incremental (merge)

DYNAMIC TABLE dbt model incremental or table

MATERIALIZED VIEW dbt model table with scheduling

STORED PROCEDURE dbt model(s) Break into CTEs/models

STREAM + TASK dbt model incremental with is_incremental()

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