How to accelerate cloud migration from Oracle to Snowflake

Shedding legacy technology to move to the cloud helps organizations gain momentum. Think: a cohesive business model where silo units can collaborate and participate in accelerating business drivers, as well as best-in-class secured access to data.

What does it take to move to the cloud?

There is no better time than now to enjoy the benefits of cloud platforms and business agility. TCO, scalability, agility, security and availability are all benefits of cloud computing. But to realize them, organizations need thorough planning. This involves strategic decision-making by leveraging Gartner’s principle of 5 R’s cloud migration strategy—rehost, refactor, revise, rebuild and replace.

No matter your business model, choosing the right cloud platform and cloud technologies is critical. You’ll need to make sure that the one you choose can effectively break silos, integrate systems, and consolidate and analyze data to provide a bird’s-eye view for leadership. That’s why it’s no surprise that Snowflake is gaining quite a bit of popularity through its unique architecture, which is built and designed for the cloud from the ground up.

Why you should consider the Snowflake Data Cloud

The Snowflake Data Cloud is a global network where thousands of organizations mobilize data with near-unlimited scale, currency and performance. There are multiple reasons to migrate to the Snowflake Data Cloud, including improvements to:

• Productivity
• Scalability
• Performance
• Elasticity
• Concurrency
• TCO

As a fully managed platform, Snowflake follows the ANSI-SQL standard, a traditional lexical framework that makes user adoption easy, and features a near-zero maintenance configuration platform and fail-safe architecture. Additionally, Snowflake features:

• Decoupled compute and storage architecture to scale independently and with flexibility.
• Low-cost compute and storage services offers pay-per-use billing per second after a minute.
• Data encryption services to keep tighter control over data.
• The allowance of concurrency and workloads on the same object through auto-scaling.
• Performance-intensive solutions due to on-demand scale in/out and scale up/down features.

How to migrate to Snowflake

Luckily, for traditional databases migrating to Snowflake, a multi-stage process has already been well-defined. And as a Snowflake Elite Partner, TEKsystems is well-qualified to help rapidly move you through the multiple steps involved in each stage:

Assess

• Scan source database objects
• Identify dependencies among objects
• Evaluate migrating objects based on dependencies

Migrate

• Retrieve data structure definition of objects
• Convert retrieved object definitions to Snowflake supported scripts
• Validate and execute scripts on target Snowflake platform
• Load data to target objects

Validate

• Verify consistency of data
• Evaluate data quality
• Validate against source

Cloud data migration is a strategic move, involving a considerable amount of time and effort. This exercise requires an individual team with subject matter experts, skilled functional professionals, database architects, database administrators and data engineers. Currently, there isn't an out-of-the-box tool to migrate the data or move the process from legacy on-premise data warehouse to Snowflake. That's where we can help.

TEKsystems AMP^GS Cloud Migration Toolkit is an innovative solution that helps our customers migrate their on-premise data warehouse to Snowflake.

About TEKsystems AMP^GSTM Cloud Migration Toolkit

TEKsystems AMP^GS Cloud Migration Toolkit is a TEKsystems Global Services proprietary accelerator that facilitates the migration of data from on-premise source database to cloud data platforms. TEKsystems AMP^GS Cloud Migration Toolkit comprises three steps of cloud migration that correlates to Snowflake’s migration process:

1. Assess: Discovery and assessment
   The assess step connects to the on-premise source database and provides a lineage and dependency matrix of the object in the source system. This is a critical step while evaluating migration entities, as it organizes and classifies objects for systematic migration. This greatly helps in understanding the dependencies between various database objects and its lineage through powerful visualization capabilities—ultimately allowing us to drill from the parent object to the lowest level leaf node. For example, users can select the database package and see the dependent objects, such as procedures, functions, views and tables.
2. Migrate: Data and process migration
   The migrate step is an end-to-end migration utility with the capability of moving your data and processes (code), which includes converting database objects such as procedures, functions and packages to the relevant target cloud data platform. It’s a feature-rich data preparation tool with integrated metadata management. TEKsystems AMP^GS Cloud Migration Toolkit can support multiple source databases like Oracle, Netezza, Teradata and SQL Server. Target databases include AWS Redshift and Snowflake, although it currently only supports the Oracle source. Through this data and process migration, we extract the source data and load it to the target database while creating object structures in supported target database formats through the self-service user interface. Along with migrating the data from source to target environments, TEKsystems AMP^GS Cloud Migration Toolkit also converts technical debt (e.g., procedure, functions and packages) created over many years in legacy source database environments to the supported target environment. Automating multistep migration processes saves a significant amount of time and cost for enterprises.
3. Validate: Test automation framework
   The final step includes a test automation framework within the toolkit, which helps ensure the consistency and quality of the migrated data. It’s packed with predefined data validation test scripts to help ensure holistic business use cases. The test automation framework is also integrated with a majority of the same source databases above.

Migrating from Oracle to Snowflake using TEKsystems AMP^GS Cloud Migration Toolkit

Visualize our TEKsystems accelerator with a use case example of migrating from an Oracle database to Snowflake. Although the goal of TEKsystems AMP^GS Cloud Migration Toolkit is to automate the migration of your existing Oracle database objects into Snowflake, there are known differences between the source and target environments that will require manual conversion/migration effort.

First, TEKsystems AMP^GS Cloud Migration Toolkit identifies the objects and its dependency during the assess step, followed by the migrate step. This retrieves data description language (DDL) object definitions from the Oracle database, converting those DDL definitions into Snowflake-compatible scripts and executing the scripts to create object structures in Snowflake to migrate the data. All of these steps are seamlessly carried out by TEKsystems AMP^GS Cloud Migration Toolkit.

The benefits of leveraging a cloud migration accelerator

One of the key features of TEKsystems AMP^GS Cloud Migration Toolkit includes it's platform-agnostic design, meaning it can be deployed on any Snowflake-supported cloud platform, like Amazon Web Services (AWS), Microsoft Azure and Google Cloud Platform (GCP). In fact, it can also be deployed on an on-premise server. Furthermore, it can convert Oracle PL/SQL objects to Snowflake-supported database objects, as well. TEKsystems AMP^GS Cloud Migration Toolkit analyzes and identifies PL/SQL program units, converts it to Snowflake-supported user-defined functions leveraging JavaScript. Additional key features are:

• Ability to process multiple database objects in parallel
• Ability to maintain object catalog to keep inventory of migrated objects
• Platform-agnostic design
• Auto extract and capacity to generate DDL scripts
• Synchronous object migration
• Support of initial, incremental mode movement
• Ability to maintain atomicity, consistency, isolation and durability (ACID) properties of migrating objects

TEKsystems AMP^GS Cloud Migration Toolkit expedites the overall migration process by automating multiple steps, keeping a catalog of migrating objects and generating comprehensive audit reports of the migration, including time of migration and execution time. Plus, the test automation framework within TEKsystems AMP^GS Cloud Migration Toolkit is a self-service application that ensures the right visibility of migrated datasets. Users only need to define source and target connections, and the test automation framework takes care of the data validation through automated scripts. There are discrete test cases covered to help ensure data integrity, like a row-count check, which measures the count of rows between the source and target database. Included is an additional MD5 check to audit data quality by analyzing the data composition on the source side and matching it with the same dataset on the target side.

Typically, it’s difficult capturing migration glitches that happen due to NULL columns/fields, but our framework’s NULL field validation script helps ensure NULL entities are represented as NULL on the target side instead of a blank space or a random character. Similarly, our data integrity tests capture the predefined referential check or uniqueness of a key field at both source and target instance. Apart from predefined data validation test cases, users are also allowed to define custom scenarios for validation. The framework covers a multitude of validation scenarios for users and has datacentric dashboards to provide a holistic view of migration activity.

Manual data/code migration is a multistep process involving various team members with diverse skill sets and immense effort. TEKsystems AMP^GS Cloud Migration Toolkit is a more precise recipe to facilitate data and code migration from a source to Snowflake. Automating processes and saving time—our accelerator helps maximize your investments and optimize for greater results. TEKsystems AMP^GS Cloud Migration Toolkit saves more time compared to manual migration efforts, and Snowflake’s cloud data platform itself has exceptional data compression ability through a unique data compression algorithm. Together, TEKsystems AMP^GS Cloud Migration Toolkit coupled with the power of Snowflake provides a better TCO in terms of cost and performance compared to traditional IT platforms.

Devang Pandya is a managing director for TEKsystems Global Services and is responsible for the global delivery of data analytics and insights.

Shishir Shrivastava is a senior manager for TEKsystems Global Services, leading the architecture of TEKsystems AMP^GS Cloud Migration Toolkit.