Database to dbt Model Conversion

Purpose

Transform database DDL (views, tables, stored procedures) from any source platform into production-quality dbt models compatible with Snowflake, 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 database views or tables to dbt models
• Migrating stored procedures to dbt
• Translating SQL syntax from one database to Snowflake
• Generating schema.yml files with tests and documentation
• Handling database-specific syntax conversions

Task Description

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

Input Requirements

I will provide you the SQL DDL to convert. Please ask for the source database platform if not obvious from the syntax.

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: [source_db].[schema].[object_name]
 Source Platform: [Teradata|Oracle|SQL Server|etc.]
 Purpose: [brief description]
 Conversion Notes: [key changes]
 Description: [SQL logic description] */

WITH source_data AS (
 SELECT
 COLUMN1 AS COLUMN1_ALIAS,
 COLUMN2 AS COLUMN2_ALIAS,
 COLUMN3 AS COLUMN3_ALIAS,
 COLUMN4 AS COLUMN4_ALIAS
 FROM {{ ref('upstream_model') }}
),
transformed_data AS (
 SELECT
 UPPER(COLUMN1) AS COLUMN_ALIAS1,
 LOWER(COLUMN2) AS COLUMN_ALIAS2,
 COLUMN3 || COLUMN4 AS COLUMN_ALIAS3
 FROM source_data
)
SELECT
 COLUMN_ALIAS1,
 COLUMN_ALIAS2,
 COLUMN_ALIAS3
FROM transformed_data

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

models:
 - name: model_name
 description: "Table description; converted from [Source Platform] [Original object name]"
 columns:
 - name: COLUMN_ALIAS1
 description: "Column description; Primary key"
 tests:
 - unique
 - not_null
 - name: COLUMN_ALIAS2
 description: "Column description; Foreign key to OTHER_TABLE"
 tests:
 - relationships:
 to: ref('OTHER_TABLE')
 field: OTHER_TABLE_KEY
 - name: COLUMN_ALIAS3
 description: "Column description"

# dbt_project.yml
models:
  my_project:
  ? [domain]
  ? [target_schema_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

Performance Optimization:

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

Dependencies:

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

Validation Checklist

• [] Source platform identified
• [] Every DDL statement has been accounted for in the dbt models
• [] SQL in models is compatible with Snowflake
• [] 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-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

Supported Source Databases

Translation References

Detailed syntax translation guides are available in the translation-references/ folder.

Copyright Notice: The translation reference documentation in this repository is derived from Snowflake SnowConvert Documentation and is © Copyright Snowflake Inc. All rights reserved. Used for reference purposes only.

Full Translation Reference Index

Full Translation Reference Index (Expanded)