IBM DB2 to dbt Model Conversion
Purpose
Transform IBM DB2 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 DB2 views or tables to dbt models
- Migrating DB2 stored procedures to dbt
- Translating DB2 SQL syntax to Snowflake
- Generating schema.yml files with tests and documentation
- Handling DB2-specific syntax conversions (Inline SQL PL, FETCH FIRST, CONTINUE/EXIT handlers)
Task Description
You are a database engineer working for a hospital system. You need to convert IBM DB2 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 DB2 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: IBM DB2
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::TIMESTAMP_NTZ 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 IBM DB2 [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
DB2 to Snowflake Syntax Conversion:
- Convert FETCH FIRST n ROWS ONLY to LIMIT n
- Replace compound statements with Snowflake Scripting
- Convert CONTINUE/EXIT handlers to exception handling
- Translate inline SQL PL to Snowflake Scripting
- Handle CURRENT DATE/TIMESTAMP differences
- Convert DB2 string functions
- Replace EXCEPT/INTERSECT if using non-ANSI syntax
Key Data Type Mappings
| DB2 |
Snowflake |
Notes |
| INTEGER/INT |
INTEGER |
|
| BIGINT |
BIGINT |
|
| SMALLINT |
SMALLINT |
|
| DECIMAL/NUMERIC |
DECIMAL |
|
| REAL/FLOAT |
FLOAT |
|
| DOUBLE |
DOUBLE |
|
| CHAR/VARCHAR |
Same |
|
| CLOB |
VARCHAR |
Max 16MB |
| BLOB |
BINARY |
Max 8MB |
| DATE |
DATE |
|
| TIME |
TIME |
|
| TIMESTAMP |
TIMESTAMP |
|
| XML |
VARIANT |
|
Key Syntax Conversions
-- FETCH FIRST -> LIMIT
SELECT * FROM table FETCH FIRST 10 ROWS ONLY -> SELECT * FROM table LIMIT 10
-- CURRENT DATE/TIMESTAMP (no parentheses in DB2)
CURRENT DATE -> CURRENT_DATE()
CURRENT TIMESTAMP -> CURRENT_TIMESTAMP()
-- CONTINUE/EXIT handlers -> Exception handling
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ... ->
EXCEPTION WHEN OTHER THEN ...
-- VALUES clause
VALUES (1, 'a'), (2, 'b') -> SELECT 1, 'a' UNION ALL SELECT 2, 'b'
Common Function Mappings
| DB2 |
Snowflake |
Notes |
COALESCE(a, b) |
COALESCE(a, b) |
Same |
IFNULL(a, b) |
IFNULL(a, b) |
Same |
VALUE(a, b) |
NVL(a, b) |
|
NULLIF(a, b) |
NULLIF(a, b) |
Same |
SUBSTR(str, pos, len) |
SUBSTR(str, pos, len) |
Same |
TRIM(str) |
TRIM(str) |
Same |
UPPER/LOWER |
Same |
|
LENGTH(str) |
LENGTH(str) |
Same |
LOCATE(search, str) |
POSITION(search IN str) |
|
DECIMAL(val, p, s) |
val::NUMBER(p, s) |
|
INTEGER(val) |
val::INTEGER |
|
VARCHAR(val) |
val::VARCHAR |
|
DATE(val) |
val::DATE |
|
CURRENT DATE |
CURRENT_DATE() |
Add parentheses |
CURRENT TIMESTAMP |
CURRENT_TIMESTAMP() |
Add parentheses |
DAYS(d) |
DATEDIFF('day', '0001-01-01', d) |
Days since epoch |
YEAR/MONTH/DAY(d) |
YEAR/MONTH/DAY(d) |
Same |
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
- [] DB2-specific syntax converted (FETCH FIRST, compound statements, handlers)
- [] 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 Database
| Database |
Key Considerations |
| IBM DB2 |
Inline SQL PL, FETCH FIRST, CONTINUE/EXIT handlers, compound statements |
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
| Folder |
Description |
| db2 |
CONTINUE handler |
| db2 |
CREATE FUNCTION |
| db2 |
CREATE PROCEDURE |
| db2 |
EXIT handler |
| db2 |
Subqueries |
1---2name: dbt-migration-db2-23description:4 Convert IBM DB2 DDL to dbt models compatible with Snowflake. This skill should be used when5 converting views, tables, or stored procedures from DB2 to dbt code, generating schema.yml files6 with tests and documentation, or migrating DB2 SQL to follow dbt best practices.7metadata:8 source: https://skillmd.com/skills/majiayu000/dbt-migration-db2-29---1011# IBM DB2 to dbt Model Conversion1213## Purpose1415Transform IBM DB2 DDL (views, tables, stored procedures) into production-quality dbt models16compatible with Snowflake, maintaining the same business logic and data transformation steps while17following dbt best practices.1819## When to Use This Skill2021Activate this skill when users ask about:2223- Converting DB2 views or tables to dbt models24- Migrating DB2 stored procedures to dbt25- Translating DB2 SQL syntax to Snowflake26- Generating schema.yml files with tests and documentation27- Handling DB2-specific syntax conversions (Inline SQL PL, FETCH FIRST, CONTINUE/EXIT handlers)2829---3031# Task Description3233You are a database engineer working for a hospital system. You need to convert IBM DB2 DDL to34equivalent dbt code compatible with Snowflake, maintaining the same business logic and data35transformation steps while following dbt best practices.3637# Input Requirements3839I will provide you the DB2 DDL to convert.4041# Audience4243The code will be executed by data engineers who are learning Snowflake and dbt.4445# Output Requirements4647Generate the following:48491. One or more dbt models with complete SQL for every column502. A corresponding schema.yml file with appropriate tests and documentation513. A config block with materialization strategy524. Explanation of key changes and architectural decisions535. Inline comments highlighting any syntax that was converted5455# Conversion Guidelines5657## General Principles5859- Replace procedural logic with declarative SQL where possible60- Break down complex procedures into multiple modular dbt models61- Implement appropriate incremental processing strategies62- Maintain data quality checks through dbt tests63- Use Snowflake SQL functions rather than macros whenever possible6465## Sample Response Format6667```sql68-- dbt model: models/[domain]/[target_schema_name]/model_name.sql69{{ config(materialized='view') }}7071/* Original Object: [database].[schema].[object_name]72 Source Platform: IBM DB273 Purpose: [brief description]74 Conversion Notes: [key changes]75 Description: [SQL logic description] */7677WITH source_data AS (78 SELECT79 customer_id::INTEGER AS customer_id,80 customer_name::VARCHAR(100) AS customer_name,81 account_balance::NUMBER(18,2) AS account_balance,82 created_date::TIMESTAMP_NTZ AS created_date83 FROM {{ ref('upstream_model') }}84),8586transformed_data AS (87 SELECT88 customer_id,89 UPPER(customer_name)::VARCHAR(100) AS customer_name_upper,90 account_balance,91 created_date,92 CURRENT_TIMESTAMP()::TIMESTAMP_NTZ AS loaded_at93 FROM source_data94)9596SELECT97 customer_id,98 customer_name_upper,99 account_balance,100 created_date,101 loaded_at102FROM transformed_data103```104105```yaml106# models/[domain]/[target_schema_name]/_models.yml107version: 2108109models:110 - name: model_name111 description: "Table description; converted from IBM DB2 [Original object name]"112 columns:113 - name: customer_id114 description: "Primary key - unique customer identifier"115 tests:116 - unique117 - not_null118 - name: customer_name_upper119 description: "Customer name in uppercase"120 - name: account_balance121 description: "Current account balance; Foreign key to OTHER_TABLE"122 tests:123 - relationships:124 to: ref('OTHER_TABLE')125 field: OTHER_TABLE_KEY126 - name: created_date127 description: "Date the customer record was created"128 - name: loaded_at129 description: "Timestamp when the record was loaded by dbt"130```131132```yaml133# dbt_project.yml (excerpt)134models:135 my_project:136 +materialized: view137 domain_name:138 +schema: target_schema_name139```140141## Specific Translation Rules142143### dbt Specific Requirements:144145- If the source is a view, use a view materialization in dbt146- Include appropriate dbt model configuration (materialization type)147- Add documentation blocks for a schema.yml148- Add descriptions for tables and columns149- Include relevant tests150- Define primary keys and relationships151- Assume that upstream objects are models152- Comprehensively provide all the columns in the output153- Break complex procedures into multiple models if needed154- Implement appropriate incremental strategies for large tables155- Use Snowflake SQL functions rather than macros whenever possible156- **Always cast columns with explicit precision/scale** using `::TYPE` syntax (e.g.,157 `column_name::VARCHAR(100)`, `amount::NUMBER(18,2)`) to ensure output matches expected data types158- **Always provide explicit column aliases** for clarity and documentation159160### Performance Optimization:161162- Suggest clustering keys if needed163- Recommend materialization strategy (view vs table)164- Identify potential performance improvements165166### DB2 to Snowflake Syntax Conversion:167168- Convert FETCH FIRST n ROWS ONLY to LIMIT n169- Replace compound statements with Snowflake Scripting170- Convert CONTINUE/EXIT handlers to exception handling171- Translate inline SQL PL to Snowflake Scripting172- Handle CURRENT DATE/TIMESTAMP differences173- Convert DB2 string functions174- Replace EXCEPT/INTERSECT if using non-ANSI syntax175176### Key Data Type Mappings177178| DB2 | Snowflake | Notes |179| --------------- | --------- | -------- |180| INTEGER/INT | INTEGER | |181| BIGINT | BIGINT | |182| SMALLINT | SMALLINT | |183| DECIMAL/NUMERIC | DECIMAL | |184| REAL/FLOAT | FLOAT | |185| DOUBLE | DOUBLE | |186| CHAR/VARCHAR | Same | |187| CLOB | VARCHAR | Max 16MB |188| BLOB | BINARY | Max 8MB |189| DATE | DATE | |190| TIME | TIME | |191| TIMESTAMP | TIMESTAMP | |192| XML | VARIANT | |193194### Key Syntax Conversions195196```sql197-- FETCH FIRST -> LIMIT198SELECT * FROM table FETCH FIRST 10 ROWS ONLY -> SELECT * FROM table LIMIT 10199200-- CURRENT DATE/TIMESTAMP (no parentheses in DB2)201CURRENT DATE -> CURRENT_DATE()202CURRENT TIMESTAMP -> CURRENT_TIMESTAMP()203204-- CONTINUE/EXIT handlers -> Exception handling205DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ... ->206EXCEPTION WHEN OTHER THEN ...207208-- VALUES clause209VALUES (1, 'a'), (2, 'b') -> SELECT 1, 'a' UNION ALL SELECT 2, 'b'210```211212### Common Function Mappings213214| DB2 | Snowflake | Notes |215| ----------------------- | ---------------------------------- | ---------------- |216| `COALESCE(a, b)` | `COALESCE(a, b)` | Same |217| `IFNULL(a, b)` | `IFNULL(a, b)` | Same |218| `VALUE(a, b)` | `NVL(a, b)` | |219| `NULLIF(a, b)` | `NULLIF(a, b)` | Same |220| `SUBSTR(str, pos, len)` | `SUBSTR(str, pos, len)` | Same |221| `TRIM(str)` | `TRIM(str)` | Same |222| `UPPER/LOWER` | Same | |223| `LENGTH(str)` | `LENGTH(str)` | Same |224| `LOCATE(search, str)` | `POSITION(search IN str)` | |225| `DECIMAL(val, p, s)` | `val::NUMBER(p, s)` | |226| `INTEGER(val)` | `val::INTEGER` | |227| `VARCHAR(val)` | `val::VARCHAR` | |228| `DATE(val)` | `val::DATE` | |229| `CURRENT DATE` | `CURRENT_DATE()` | Add parentheses |230| `CURRENT TIMESTAMP` | `CURRENT_TIMESTAMP()` | Add parentheses |231| `DAYS(d)` | `DATEDIFF('day', '0001-01-01', d)` | Days since epoch |232| `YEAR/MONTH/DAY(d)` | `YEAR/MONTH/DAY(d)` | Same |233234### Dependencies:235236- List any upstream dependencies237- Suggest model organization in dbt project238239---240241# Validation Checklist242243- [] Every DDL statement has been accounted for in the dbt models244- [] SQL in models is compatible with Snowflake245- [] DB2-specific syntax converted (FETCH FIRST, compound statements, handlers)246- [] All business logic preserved247- [] All columns included in output248- [] Data types correctly mapped249- [] Functions translated to Snowflake equivalents250- [] Materialization strategy selected251- [] Tests added252- [] SQL logic description complete253- [] Table descriptions added254- [] Column descriptions added255- [] Dependencies correctly mapped256- [] Incremental logic (if applicable) verified257- [] Inline comments added for converted syntax258259---260261## Related Skills262263- **dbt-modeling**: For CTE patterns and SQL structure guidance264- **dbt-testing**: For implementing comprehensive dbt tests265- **dbt-architecture**: For project organization and folder structure266- **dbt-materializations**: For choosing materialization strategies (view, table, incremental,267 snapshots)268- **dbt-performance**: For clustering keys, warehouse sizing, and query optimization269- **dbt-commands**: For running dbt commands and model selection syntax270- **dbt-core**: For dbt installation, configuration, and package management271- **snowflake-cli**: For executing SQL and managing Snowflake objects272273---274275## Supported Source Database276277<!-- prettier-ignore -->278| Database | Key Considerations |279|---|---|280| **IBM DB2** | Inline SQL PL, FETCH FIRST, CONTINUE/EXIT handlers, compound statements |281282## Translation References283284Detailed syntax translation guides are available in the `translation-references/` folder.285286> **Copyright Notice:** The translation reference documentation in this repository is derived from287> [Snowflake SnowConvert Documentation](https://docs.snowflake.com/en/migrations/snowconvert-docs)288> and is © Copyright Snowflake Inc. All rights reserved. Used for reference purposes only.289290### Reference Index291292<!-- prettier-ignore -->293| Folder | Description |294|---|---|295| db2 | [CONTINUE handler](translation-references/db2/db2-continue-handler.md) |296| db2 | [CREATE FUNCTION](translation-references/db2/db2-create-function.md) |297| db2 | [CREATE PROCEDURE](translation-references/db2/db2-create-procedure.md) |298| db2 | [EXIT handler](translation-references/db2/db2-exit-handler.md) |299| db2 | [Subqueries](translation-references/db2/subqueries.md) |