Migrating from Oracle 2 Snowflake: Part 1

In this blog, I would like to share my POC experience migrating from Oracle to Snowflake . I will share the current architecture & proposed architect at high level and will like to share step by step action we had taken to achieve our desired goal.

Note: This is more to give an idea on what challenges one can face during Oracle to Snowflake migration. All suggestion & expression are my own, and it had no relation with my employer(past/present).

Before we jump into real action, I would like to set the context so that we are on the same page in terms of scope of project.

In this POC we are migrating existing Perl Scripting & Oracle 19C database with Python, Azure & Snowflake. In the existing architecture, Perl is used as an ETL tool to ingest data from Source Systems and call a couple of business routines written in Oracle database to transform and finally insert into Oracle DWH. Then this data was consumed by existing SAS team for further processing. All business logic resides in Oracle database. Perl is more for file processing & procedure calling. Perl being one of the oldest scripting language lack support for modern application

As you can see, we are into a very legacy tool set and there were lots of challenges which we were not able to manage using our current architecture. Few of the challenge are as below:

  1. Scaling
  2. Platform Modernization to open future opportunity.
  3. Reduce Maintenance Overhead
  4. Reduce Cost
  5. Performance
  6. Cloud Perspective.
  7. Licensing burden

Let’s discuss a bit more on each challenge to identify what makes Snowflake different from Oracle and why to chosen Snowflake.

Scalability: Scalability is one of the major problem with most of the on-premises databases. Considering limited in-house resource, often on-premise data warehouse lacks scaling power which cloud data warehouse can easily provision. In case of Oracle, scaling can be only limited to hardware investment done by organization. Whereas, Snowflake works on Pay-as-you-use model which can give you power to scale seamlessly in few moments without any CAPEX.

Platform Modernization: As our platform was majority concerning old on-premises technology, we are lacking to open up new possibility to expand our application horizon and talk to new generation tool set. AI/ML out-of-the-box support from Snowflake is giving us the upper hand in the market.

Reduce Maintenance Overhead: Snowflake is market as cloud data warehouse as-a-service that manage all the administrative task which we have to do being Oracle DBA. HA & DR is readily available as snowflake automatically sync to multiple availability zone. In case of Oracle, we do have multiple HA & DR option, but we have to purchase hardware to set up same.

Reduce Cost: Since we are going away from traditional CAPEX model and moving toward OPEX model, we are saving cost on CAPEX. Also, Snowflake comes as-a-service, so the majority of admin jobs are out-of-the-box available, and we don’t need a team of highly paid admins. Savings on resource is also significant.

Performance: In Oracle we had to do lots of performance tuning, indexing & stats maintenance and many more to keep up with performance requirement. Snowflake reduces complexity with built-in performance tuning, so no manual query optimization is required.

Cloud Perspective: Being on cloud is new normal, it also opens up possibility of ingestion from multiple data source and multiple data format. Snowflake support ingesting data from multiple data source and support all major data format using Variant data type. In Oracle, these things are not out-of-the-box, and you have to write long PL SQL to handle same.

Licensing burden: Oracle is famous for his licensing cost(per person) and the majority of organization which was not getting opportunity earlier to come out of Oracle due to unavailability of Oracle like enterprise scale data warehouse are now opting for Snowflake. Snowflake don’t have any license cost as it work on Pay-For-What-You-Use only model.

There were few more feature which attract us toward Snowflake:

Reuse of existing skilled resource: The best part of moving to Snowflake is that you don’t need to spend on upskilling your in-house resources. Resource from Oracle background can easily grasp Snowflake skill set as Snowflake follow ANSI SQL standard and the majority of Oracle syntax are look-a-like except few Oracle proprietary syntax. So you can reutilize world most widely used programming skill and no further overhead is required while moving to Snowflake.

Ecosystem Support: Snowflake has huge support ecosystem of Data Integration, Security & Governance, Business Intelligence, ML & Data Science. This can help any future requirement for application growth.

Data Sharing & Data Marketplace: Snowflake has unique data sharing & data marketplace which can help you market your data or share or get any data. Secure Data Sharing enables sharing selected objects in a database in your account with other Snowflake accounts.

Time Travel: Snowflake Time Travel enables accessing historical data (i.e. data that has been changed or deleted) at any point within a defined period. It gives you to the power to get any data from past via SQL statement. No more DBA route is needed. Isn’t that cool.

Zero Copy Clone: Cloning also referred to as “zero-copy cloning” creates a copy of a database, schema or table. A snapshot of data present in the source object is taken when the clone is created and is made available to the cloned object. The cloned object is writable and is independent of the clone source. That is, changes made to either the source object or the clone object are not part of the other. Cloning a database will clone all the schemas and tables within that database. Cloning a schema will clone all the tables in that schema.

There was many more feature which was future ready and out-of-the-box available in Snowflake like Auto Clustering, Table Swap etc.

Our POC current architecture looks like something below:

Existing Architecture

Our proposed POC architecture is as below:

  1. Perl → Python
  2. Oracle DWH → Azure Blob + Snowpipe + Snowflake DWH

Above choice not only gives us freedom from licenses /person burden but also gives us modern cloud platform with future ready. Also helps us to overcome from all the challenges which we were facing in current architecture.

For further reading, please wait for upcoming blog on Migrating from Oracle 2 Snowflake: Part 2

Hope this blog help you to get insight on why one should migrate from Oracle to Snowflake. Feel free to ask a question in the comment section if you have any doubts regarding this. Give a clap if you like the blog. Stay connected to see many more such cool stuff. Thanks for your support.

You Can Find me:

Follow me on Medium: https://rajivgupta780184.medium.com/

Follow me on Twitter: https://twitter.com/RAJIVGUPTA780

Connect with me in LinkedIn: https://www.linkedin.com/in/rajiv-gupta-618b0228/

Subscribe to my YouTube Channel: https://www.youtube.com/channel/UC8Fwkdf2d6-hnNvcrzovktg

#Keep learning #Keep Sharing #EverydayLearning.

References:-

Data Architect by Profession | Snowflake Data Super Hero | Learner by nature | Blogger by Passion| Motivator by act