Oracle to Snowflake Migration Case Study Presentation

Oracle to Snowflake Migration Series, part 1

Case study

As a case study, we will present one of our migrations from Oracle to Snowflake and share our findings. In addition to achieving benefits when migrating from Oracle to Snowflake, there were specific reasons why this organization was ready for a migration:

  • Their analytics system was deteriorating in performance due to increasing data volumes and numbers of users over the years since the solution was developed​
  • Maintenance of the system was increasing in complexity as a consequence of using an in-house ETL tool that relied heavily on Oracle performance tuning hints that didn’t perform well after migrating to Exadata
  • Substantial effort for DBA tasks such as performance tuning, query optimization, gathering statistics, backup activities and so on was required on a regular basis

When embarking on a migration journey, we understand that data migration is much more than simply copying the data. We do a full assessment of the existing data warehouse and clearly define what must be migrated. We review the as-is architecture and prepare a detailed list of:

  • Databases, including all data warehouse staging areas
  • Data models, including all database objects such as tables and views and schemas
  • ETL processes that populate and pull data in a variety of forms
  • Orchestration processes
  • Security schemas and authorization privileges

To start the migration process, we follow a series of standard steps that include:

  • Set up the environment​
  • Create data structures​ 
  • Perform initial data extract and load​ 
  • Convert code​
  • Optimize performance if needed

Each of the standard migration steps will be described in more detail in the following sections.

Get your copy of the Oracle to Snowflake Migration Ebook!

Written by Maja Ferle, Snowflake Data Superhero.

(by getting your copy you agree to our Privacy Policy)

Setting up the environment

A target Snowflake account must be set up in order to begin migration with security schemas in place. The good news is, you can start discovering Snowflake for free. You can play around with your data using Snowflake’s $400 worth of credits for free for 30 days.

During this trial, you will be able to take advantage of the majority of features by choosing the Enterprise or Business Critical edition. Next, choose your desired platform – either AWS, Azure or Google Cloud Platform (GCP). You can choose any one of them for the purpose of this tutorial.

Signing in will give you a unique URL containing your account number and the zone where Snowflake has been installed. The link takes you to the Snowflake web interface, where you can log in and access your Snowflake Data Cloud world.

For all your questions about using Snowflake, it’s best to refer to Snowflake documentation. You can learn more about Snowflake system roles and their purpose and Setting up Role-based Access Control on our blog.

Maja Ferle
Senior Consultant and Snowflake Data Superhero