Vertica to dbt Model Conversion

Purpose

Transform Vertica DDL (views, tables, stored procedures) 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 Vertica views or tables to dbt models
• Migrating Vertica stored procedures to dbt
• Translating Vertica SQL syntax to Snowflake
• Generating schema.yml files with tests and documentation
• Handling Vertica-specific syntax (projections, flex tables, ANY/ALL predicates)

Task Description

You are a database engineer working for a hospital system. You need to convert Vertica 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 Vertica 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

sql
1-- dbt model: models/[domain]/[target_schema_name]/model_name.sql
2{{ config(materialized='view') }}
3
4/* Original Object: [schema].[object_name]
5   Source Platform: Vertica
6   Purpose: [brief description]
7   Conversion Notes: [key changes]
8   Description: [SQL logic description] */
9
10WITH source_data AS (
11    SELECT
12        customer_id::INTEGER AS customer_id,
13        customer_name::VARCHAR(100) AS customer_name,
14        account_balance::NUMBER(18,2) AS account_balance,
15        -- TIMESTAMPTZ converted to TIMESTAMP_TZ
16        created_date::TIMESTAMP_TZ AS created_date
17    FROM {{ ref('upstream_model') }}
18),
19
20transformed_data AS (
21    SELECT
22        customer_id,
23        UPPER(customer_name)::VARCHAR(100) AS customer_name_upper,
24        account_balance,
25        created_date,
26        CURRENT_TIMESTAMP()::TIMESTAMP_NTZ AS loaded_at
27    FROM source_data
28)
29
30SELECT
31    customer_id,
32    customer_name_upper,
33    account_balance,
34    created_date,
35    loaded_at
36FROM transformed_data

yaml
1## models/[domain]/[target_schema_name]/_models.yml
2version: 2
3
4models:
5  - name: model_name
6    description: "Table description; converted from Vertica [Original object name]"
7    columns:
8      - name: customer_id
9        description: "Primary key - unique customer identifier"
10        tests:
11          - unique
12          - not_null
13      - name: customer_name_upper
14        description: "Customer name in uppercase"
15      - name: account_balance
16        description: "Current account balance; Foreign key to OTHER_TABLE"
17        tests:
18          - relationships:
19              to: ref('OTHER_TABLE')
20              field: OTHER_TABLE_KEY
21      - name: created_date
22        description: "Date the customer record was created"
23      - name: loaded_at
24        description: "Timestamp when the record was loaded by dbt"

yaml
1## dbt_project.yml (excerpt)
2models:
3  my_project:
4    +materialized: view
5    domain_name:
6      +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

Vertica to Snowflake Syntax Conversion

• Remove projection specifications
• Handle flex table conversions
• Convert case sensitivity with quoted identifiers
• Replace ANY/ALL array predicates with Snowflake equivalents
• Convert Vertica-specific functions
• Handle COPY syntax differences
• Remove SEGMENTED BY clauses

Key Data Type Mappings

Key Syntax Conversions

sql
1-- Projections -> Remove (Snowflake auto-manages)
2CREATE PROJECTION proj AS SELECT ... -> (remove entirely)
3
4-- SEGMENTED BY -> CLUSTER BY
5CREATE TABLE t (...) SEGMENTED BY HASH(id) ->
6CREATE TABLE t (...) CLUSTER BY (id)
7
8-- Flex tables -> VARIANT columns
9CREATE FLEX TABLE t() -> CREATE TABLE t (data VARIANT)
10
11-- Case sensitivity (Vertica folds to lowercase)
12SELECT Col -> SELECT "Col"  -- if case matters
13
14-- ANY/ALL array predicates
15col = ANY(ARRAY[1,2,3]) -> col IN (1,2,3)
16col <> ALL(ARRAY[1,2,3]) -> col NOT IN (1,2,3)

Common Function Mappings

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
• [] Vertica-specific syntax converted (projections removed, case sensitivity handled)
• [] 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

Supported Source Database

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.

Reference Index

• Create Table
• Create View
• Data Types
• Identifier Between Vertica And Snowflake
• Operators
• Predicates
• Overview (README)
• Subqueries