Oracle to Snowflake Migration, Part 13.12.2020
Introduction and motivation
Oracle to Snowflake migration series, Part 1
Oracle is among the leading database management systems for running online transaction processing and data warehousing systems. However, with increasing volume, velocity, and variety of data that is at our disposal for modern analytics, Oracle offers limited scalability to support increasing workloads.
Snowflake’s Data Cloud on the other hand scales in a way that’s simply not possible to do with Oracle. By separating compute from storage, Snowflake automatically and instantly scales with its multi-cluster, shared architecture. It provides access to a virtually unlimited number of concurrent users and applications while ensuring that performance is not diminished. As compared to traditional solutions such as Oracle, Snowflake enables data to be stored in a common location at a fraction of the cost.
Despite the widespread use of Oracle and the availability of Oracle talent, many business applications are moving away from Oracle and onto Snowflake not only for the superior scalability but also for other benefits that distinguish Snowflake from traditional Oracle environments.
For this reason, we’re starting our Oracle to Snowflake migration series, where we’ll deep dive into the data migration strategy itself, present how to migrate metadata, ETL scripts, stored procedures and functions and what to look out for during the process. To top it up, we’ll show you some really staggering comparisons from our recent projects.
We’ll start off with the benefits when comparing Snowflake vs. Oracle, which are answering the first question of why to migrate and are explained in more detail in the following section.
Migrating to Snowflake?
Dive in with Snowflake Innovation partner of the year in EMEA region
No traditional administration
Snowflake is based on an as-a-service delivery model in the cloud that manages itself without the need for traditional database administration roles. Snowflake reduces complexity with built-in performance tuning, so no manual query optimization is required.
Workload management is unnecessary in a Snowflake environment due to its multi-cluster architecture, which allows you to create separate compute clusters for varying workloads to avoid resource contention. This is all accomplished without manual intervention from database administrators.
Already in the cloud
Many new data sources are already in the cloud which enables efficient analytics and reduces investment in new infrastructure to store and process such data sources. Data can be accessed from all these sources with direct, governed and secure data sharing in real-time with minimal data copying and moving operations.
Pay only what you use
Snowflake cost is based on a pay-as-you-go model based on the amount of data and workload that is processed which simply means that you pay for only what you use. This significantly differs from Oracle, where capacity planning for on-premises is required and you always incur the risk of over or under configuring your system. This is especially critical in situations when multiple servers are required, such as with high-availability configurations that often require overhead capacity. Even if you transition to the Oracle cloud service, you still have a similar capacity planning risk because compute and storage are fixed per instance. If you need more capacity within an Oracle cloud service, you must buy in predefined increments. Snowflake’s elastic storage and compute architecture eliminates this risk.
The Oracle database has several disaster recovery scenarios, many of which require the purchase of additional hardware, software licenses and network infrastructure. With Snowflake, this is not necessary because by design, Snowflake is automatically synced across multiple availability zones. No manual administration work is required on your part to establish this.
Separate development and test environments
With Oracle, if you need separate development and test environments, you need additional servers, which means more infrastructure and administration effort. To set up a new environment, administrators must perform the cumbersome task of exporting data structures and data from one environment and then importing into another environment.
In Snowflake, this is not required as you can simply create another database in your account and configure it for any purpose, such as development or test. In addition, with Snowflake’s clone feature, you can instantly populate the new database with a complete copy of the data at no additional cost.
Snowflake is ANSI SQL compliant and thus supports the tools millions of business users already know how to use today. This should allow for a straightforward transition from Oracle.
In summary, there are many compelling reasons to migrate from Oracle to Snowflake. Once the decision to migrate has been made, the actual migration exercise begins.
We will explore various aspects of migrating from Oracle to Snowflake in subsequent parts of this series.