Migrate Data from Oracle to Snowflake4.12.2020
Migrating data structures and mapping data types
Oracle to Snowflake Migration Series, Part 2
We were happy to see great responses to our first part of the series. In this upcoming part, we’re starting with our deep dive into the data migration itself. If you’d like us to review your stack and help you with your migration challenges, feel free to drop us a message. Read on how to migrate data from Oracle to Snowflake.
Snowflake Cloud Data Platform supports standard ANSI SQL as well as analytic extensions such as window functions. This means that most database objects and code will migrate from Oracle to Snowflake seamlessly.
In order to prepare for migration, you must choose all database objects that will be migrated. These may include tables, views, sequences and functions. To create all of these objects in Snowflake you will run DDL scripts, just like you would in Oracle. There are a few considerations to keep in mind, which will be explained in more detail in the following sections.
No Storage Related Options in Snowflake DDL
Snowflake takes care of everything related to the physical storage of data and as a user you don’t need to define indexes, partitions or storage clauses such as, for example, the tablespace name. You need only the basic DDL, such as CREATE TABLE, CREATE VIEW and so on.
Traditional relational databases, such as Oracle, use indexes for better performance. Snowflake with its MPP architecture performs parallel processing behind the scenes (there is no need to provide a hint for parallel processing like you would in Oracle) so no indexes are needed or supported. Because Snowflake doesn’t use indexes, you don’t have to include your indexes and partitions in the DDL scripts.
Snowflake manages all database objects behind the scenes without the use of data files, table partitions or sub-partitions. Storage grows and shrinks as you add or delete data without any intervention from users. This also means that Snowflake has no concept of tablespace, therefore you can execute your DDL statements that create tables without storage clauses.
Oracle enforces primary key and foreign key constraints. While Snowflake supports the syntax to define primary keys and foreign keys, they are not enforced within Snowflake. In case you have any load processes that rely on constraints to prevent duplicate entries and orphaned records, you will have to re-engineer them.
Migrating to Snowflake?
Dive in with Snowflake Innovation partner of the year in EMEA region
Prepare DDL Scripts for Migration
If you already have DDL scripts to create objects in Oracle, you can use the same scripts to create objects in Snowflake. You may have to change some data types to Snowflake optimized data types as explained in the following section.
If you don’t already have DDL scripts to create objects in Oracle, or the script that you have is outdated, you might use a data modeling tool such as, for example, Oracle SQL Developer Data Modeler or SqlDBM (why we really like the latter read here) or another data modeling tool, to reverse engineer the existing Oracle database and then create DDL scripts using ANSI SQL. Most of the generated scripts will run in Snowflake, although some modification may be required due to Oracle specifics that don’t translate into ANSI SQL.
If you don’t have DDL scripts and you also don’t have access to a data modeling tool, you can generate the DDL yourself by reading metadata in the Oracle data dictionary tables. Again, you only need the object definitions without any storage related information. In case you need to do any data type conversion, you can do this already while building the DDL scripts.
Mapping Data Types
Snowflake supports most basic SQL data types for use in columns, local variables, expressions, parameters and any other relevant locations. Data types are automatically coerced whenever necessary and possible.
A comprehensive list of data types supported by Snowflake is available in the Snowflake documentation. Most data types will convert straightforwardly from Oracle to Snowflake. However, there are some considerations.
Numeric Data Types
Snowflake supports all common numeric data types to store numeric values, including integer, fixed point and floating point. When converting numeric data types from Oracle, one important consideration is that Oracle supports data type NUMBER without scale and precision. In such cases, when a value is provided and precision is not specified, Oracle will store the value as given, including any decimal places.
In Snowflake, if scale and precision aren’t defined, then a precision of 0 is assumed. The consequence is that when converting data stored in a column of data type NUMBER without scale or precision in Oracle, the numbers will be stored as whole numbers without decimals in Snowflake. Some up-front data analysis is required to verify the precision and scale of data in Oracle and determine the corresponding data type to use in Snowflake. Ideally, you would update the DDL scripts that create objects in Snowflake accordingly.
Text Data Types
Snowflake supports the usual string and binary data types. All character string data types are stored as variable length strings. The frequently used VARCHAR2 data type in Oracle is also recognized in Snowflake and stored internally as VARCHAR so that DDL scripts can remain unchanged with respect to string data types.
Date and Time Data Types
The DATE data type in Snowflake contains only the date value without time while in Oracle, the DATE data type can include time as well. When migrating DATE values from Oracle to Snowflake it is recommended to use the TIMESTAMP_NTZ as the Snowflake data type to preserve the time component, otherwise you run the risk of losing the time values. Again, you would update the DDL scripts accordingly.
The date model format RR, which in Oracle lets you store 20th century dates in the 21st century by specifying only the last two digits of the year, is not supported in Snowflake. Instead, the YY model format behaves similarly in Snowflake: when a two-digit year is supplied, if the year is between 00 and 69, it will translate to between 2000 and 2069, otherwise it will use 19 as the first two digits of the year.
The above is a summary of examples of the most common data type conversions between Oracle and Snowflake. Stay with us as more knowledge sharing is coming soon!