Oracle to Snowflake Translation with SnowConvert

This document summarizes the key transformations performed by SnowConvert when migrating Oracle SQL to Snowflake. It covers data type mapping, function translations, and other SQL construct adjustments, providing examples to illustrate the process. This summary is intended as a high-level overview; always refer to the official SnowConvert documentation for the most comprehensive and up-to-date information.

Data Type Mapping:

SnowConvert handles the mapping of Oracle data types to their Snowflake equivalents. While many types have direct counterparts, some require conversion or special handling.

  • Numeric Types: Oracle’s NUMBER maps to Snowflake’s NUMBER. Precision and scale are generally preserved, but it’s crucial to verify after conversion. FLOAT and BINARY_FLOAT/BINARY_DOUBLE might require adjustments depending on the specific precision and how they are used.

    • Example: NUMBER(10,2) in Oracle becomes NUMBER(10,2) in Snowflake.

  • String Types: VARCHAR2, NVARCHAR2, and CHAR map to Snowflake’s VARCHAR and CHAR. Character set considerations are important. CLOB and NCLOB map to Snowflake’s VARCHAR (with size limitations) or TEXT. Large CLOBs might require alternative handling due to size restrictions in Snowflake’s VARCHAR.

    • Example: VARCHAR2(255) becomes VARCHAR(255). CLOB might become VARCHAR(16777216) or require a different large object strategy.

  • Date/Time Types: DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE map to corresponding Snowflake types. Time zone handling is a key consideration during migration. Snowflake offers TIMESTAMP_NTZ (no time zone) and TIMESTAMP_TZ (with time zone).

    • Example: TIMESTAMP WITH TIME ZONE in Oracle might become TIMESTAMP_TZ in Snowflake.

  • LOBs: Oracle BLOB and BFILE (Binary Large Objects) require special attention. Snowflake uses VARBINARY for binary data. Large BLOBs might necessitate a different storage strategy. BFILE (external file LOBs) might require redesign as Snowflake doesn’t directly support them in the same way.

    • Example: BLOB might become VARBINARY. BFILE would require a different approach, potentially involving staging the file in cloud storage and then loading the data into Snowflake.

  • Other Types: Other data types like RAW, ROWID, and user-defined types require specific mapping strategies. Refer to the SnowConvert documentation for details.

SQL Function and Construct Translation:

SnowConvert handles the translation of numerous SQL functions and constructs. Many have direct equivalents, while others require conversion or emulation.

  • String Functions: Functions like SUBSTR, UPPER, LOWER, TRIM, CONCAT are generally translated directly. However, some functions might have slightly different names or argument orders.

    • Example: SUBSTR(string, start, length) in Oracle is similar to SUBSTRING(string, start, length) in Snowflake.

  • Numeric Functions: Functions like ABS, ROUND, MOD, CEIL, FLOOR are usually translated directly.

    • Example: ROUND(number, decimals) is the same in both Oracle and Snowflake.

  • Date/Time Functions: Functions like SYSDATE, SYSTIMESTAMP, ADD_MONTHS, EXTRACT have Snowflake equivalents. However, time zone handling might differ.

    • Example: SYSDATE in Oracle becomes CURRENT_DATE() in Snowflake. ADD_MONTHS(date, number) is the same in both.

  • Aggregate Functions: SUM, AVG, COUNT, MIN, MAX are typically translated directly.

  • Analytic Functions (Window Functions): Oracle’s analytic functions (e.g., ROW_NUMBER, RANK, LAG, LEAD) are generally supported in Snowflake, but syntax or behavior might have subtle differences.

    • Example: ROW_NUMBER() OVER (ORDER BY column) is similar in both, but always verify for edge cases.

  • Conditional Logic: CASE expressions are generally translated directly.

    • Example: CASE WHEN condition THEN result ELSE result END is the same in both.

  • Joins: Inner, outer, and cross joins are usually translated without issues.

  • DDL Statements: CREATE TABLE, ALTER TABLE, DROP TABLE are generally translated. However, constraints, indexes, and other table properties require careful review and mapping. Oracle-specific storage clauses might need to be adapted.

    • Example: An Oracle CREATE TABLE statement with specific tablespace or storage parameters might require adjustments in Snowflake.

  • DML Statements: SELECT, INSERT, UPDATE, and DELETE statements are generally translated.

  • Stored Procedures and Functions (PL/SQL): Oracle’s PL/SQL code needs to be converted to Snowflake’s stored procedure language (Snowflake Scripting). This is a complex process and SnowConvert can assist, but manual intervention is often required.

  • Triggers: Oracle triggers require re-implementation in Snowflake using Snowflake’s task and stream features.

  • Packages: Oracle packages do not have a direct equivalent in Snowflake. The functionality needs to be re-architected, often using stored procedures and functions.

  • Sequences: Oracle sequences can be mapped to Snowflake sequences.

  • Views: Oracle views are usually translated directly.

Example of a More Complex Conversion:

Let’s say you have an Oracle query like this:

SELECT employee_id,
       ename,
       hire_date,
       SALARY,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn
FROM employees
WHERE hire_date > ADD_MONTHS(SYSDATE, -12);
Copy

SnowConvert would likely translate this to Snowflake SQL that looks very similar:

SELECT employee_id,
       ename,
       hire_date,
       SALARY,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn
FROM employees
WHERE hire_date > ADD_MONTHS(CURRENT_DATE(), -12); -- SYSDATE becomes CURRENT_DATE()
Copy

In this case, the core logic and syntax are preserved. However, it’s important to note the following:

SYSDATE was converted to CURRENT_DATE(). Any Oracle-specific data type nuances in the employees table would have been handled according to the data type mapping rules. If there were any Oracle-specific functions within the query that didn’t have direct Snowflake equivalents, SnowConvert would have attempted to translate them or flag them for manual review (using EWIs or FDMs). Key Considerations

  • Testing is Crucial: Always thoroughly test the converted code to ensure functional equivalence. Pay close attention to data type behavior, edge cases in functions, and performance differences.

  • Time Zone Handling: Time zone conversions require meticulous planning and testing.

  • PL/SQL Conversion Complexity: Converting PL/SQL requires significant effort. SnowConvert can automate parts of this, but manual review and adjustments are generally necessary.

  • Review EWIs and FDMs: Carefully examine any EWIs (Error, Warning, Information) and FDMs (Functional Difference Messages) generated by SnowConvert. These highlight areas that need attention.

  • Performance Tuning: Snowflake’s performance characteristics are different from Oracle’s. You’ll likely need to tune queries and data structures after conversion.

By understanding these transformations and potential differences, you can better prepare for an Oracle to Snowflake migration with SnowConvert.

Language: English