Snowflake to Redshift Migration: 3 Easy Steps

Cloud-based data storage and warehousing solutions have now become the preferred choice for most modern businesses. This is primarily because leveraging a Cloud-based solution makes it easier for businesses to ensure that their Data Warehouses grow along with their unique data requirements and scale up or down on-demand or automatically to accommodate all peak-workload periods.

Cloud-based Data Warehouses enable businesses to efficiently settle all Data Availability and Security concerns as they support seamless Database Replication across multiple geographical locations, in addition to numerous backup and data recovery options. Two of the most popular Cloud-based Data Warehousing solutions are Amazon Redshift and Snowflake.

Both Data Warehouses offer their users a wide variety of features. Amazon Redshift offers its users exceptional features such as robust security and compliance across all its tiers along with discounts on long-term plans whereas Snowflake gives users the ability to scale Compute or Storage Nodes separately based on their requirements, thereby reducing costs significantly but offers security and compliance based on their tiers. Hence, based on the use case, businesses sometimes feel the need to migrate from one Data Warehouse to another.

This article will provide you with an in-depth understanding of how you can set up Snowflake to Redshift Migration seamlessly.

Introduction to Snowflake

Snowflake is one of the most popular enterprise-grade Cloud-based Data Warehouses that brings simplicity to its users without reducing the features being offered in any way. It is capable of automatically scaling resources up and down based on data requirements to ensure that users get the right balance of performance and cost.

Snowflake is now widely used by a large number of businesses primarily because it separates Compute Nodes from Storage Nodes. This is considered to be one of the most significant advantages of using Snowflake since all other databases, or Data Warehouses offer Compute and Storage Nodes together as a single bundle. Earlier, businesses had to acquire resources based on the largest workload and incur its cost. But with Snowflake, they can choose to pay only for the resources they use.

More information on Snowflake can be found here.

Understanding the Key Features of Snowflake

The key features of Snowflake are as follows:

• Advanced Scalability: Snowflake houses functionalities that allow users to practically create an unlimited number of Virtual Warehouses with each one running its workload against the data in its database.
• Robust Security: Snowflake ensures that all data stored in its Data Warehouses is secure by implementing a wide variety of industry-leading and powerful security features, such as Multi-factor Authentication, Automatic 256-bit AES Encryption, etc. Snowflake is also compliant with numerous enterprise-grade data security standards such as PCI DSS, HIPAA, SOC 1, and SOC 2.
• Automated Performance Tuning: Snowflake offers its users an Automatic Query Performance Optimization mechanism backed by a robust Dynamic Query Optimization Engine in their Cloud Services Layer.

Introduction to Amazon Redshift

Amazon Redshift is a fully-managed petabyte-scale Cloud-based Data Warehouse that was developed by Amazon. It was designed for the storage and analysis of petabyte-scale data. Amazon Redshift is built on a Column-oriented Architecture and designed to connect with numerous SQL-based clients, Business Intelligence, and Data Visualization tools and make data available to users in real-time. Based on PostgreSQL 8, Amazon Redshift delivers significantly enhanced performance and more efficient querying as compared to all other Data Warehouses. This helps teams make sound business analyses and decisions. More than 15,000 businesses now use Amazon Redshift globally, including large Enterprises such as Pfizer, McDonald’s, Facebook, etc.

More information on Amazon Redshift can be found here.

Understanding the Key Features of Amazon Redshift

The key features of Amazon Redshift are as follows:

• Massively Parallel Processing (MPP): Massively Parallel Processing is a distributed design approach in which the divide and conquer strategy is applied by several processors to large data jobs. A large processing job is broken down into smaller jobs which are then distributed among a cluster of Compute Nodes. These Nodes perform their computations parallelly rather than sequentially. As a result, there is a considerable reduction in the amount of time Redshift requires to complete a single, massive job.
• Fault Tolerance: Data Accessibility and Reliability are of paramount importance for any user of a database or a Data Warehouse. Amazon Redshift monitors its Clusters and Nodes around the clock. When any Node or Cluster fails, Amazon Redshift automatically replicates all data to healthy Nodes or Clusters.
• Redshift ML: Amazon Redshift houses a functionality called Redshift ML that gives data analysts and database developers the ability to create, train, and deploy Amazon SageMaker models using SQL seamlessly.
• Column-Oriented Design: Amazon Redshift is a Column-oriented Data Warehouse. This makes it a simple and cost-effective solution for businesses to analyze all their data using their existing Business Intelligence tools. Amazon Redshift achieves optimum query performance and efficient storage by leveraging Massively Parallel Processing (MPP), Columnar Data Storage, along with efficient and targeted Data Compression Encoding schemes.

Steps to Set up Snowflake to Redshift Migration

Users can set up Snowflake to Redshift Migration by implementing the following steps:

• Snowflake to Redshift Migration Step 1: Installing Required Packages
• Snowflake to Redshift Migration Step 2: Extracting Data from Snowflake
• Snowflake to Redshift Migration Step 3: Loading Data into Amazon Redshift

Snowflake to Redshift Migration Step 1: Installing Required Packages

The following Python packages are required for this process:

• Pandas: Pandas for Python can be installed by implementing the following command:

pip install pandas

• Snowflake Connector: This package is required to export all data from the Snowflake Data Warehouse as a Pandas Dataframe for Snowflake to Redshift Migration. It can be installed by running the following command:

pip install snowflake-connector-python==<version>

<version> in the above command has to be replaced with the version of Python installed on your local system.

• Pandas_redshift: This package is required to store the data extracted from Snowflake as a Pandas Dataframe into Amazon Redshift and fully perform the Snowflake to Redshift Migration. It can be installed by running the following command:

pip install pandas-redshift

More information on these packages can be found on the Pandas, Snowflake Connector, and Pandas_Redshift documentation.

Snowflake to Redshift Migration Step 2: Extracting Data from Snowflake

In order to set up Snowflake to Redshift Migration, the data first has to be extracted from Snowflake. This can be done using Snowflake’s Python connector. The following code can be used to extract data from Snowflake using Python:

import snowflake.connector
import pandas as pd

ctx = snowflake.connector.connect(
          host=host,
          user=user,
          password=password,
          account=account,
          warehouse=warehouse,
          database=database,
          schema=schema,
          protocol='https',
          port=port)

# Create a cursor object.
cur = ctx.cursor()

# Execute a statement that will generate a result set.
sql = "select * from t"
cur.execute(sql)

# Fetch the result set from the cursor and deliver it as the Pandas DataFrame.
df = cur.fetch_pandas_all()

You will have to make the following changes to your Snowflake to Redshift Migration code based on your unique requirements:

• The first function call to connect Python to Snowflake i.e. snowflake.connector.connect() should have the required parameters of your Snowflake Data Warehouse.
• The SQL query being executed should extract the data that you wish to load into Redshift. Information on queries supported by Snowflake can be found here.
• You can choose to execute multiple queries and create multiple Pandas Dataframes to suit your data and business needs.

Snowflake to Redshift Migration Step 3: Loading Data into Amazon Redshift

Once the required data has been extracted from Snowflake and stored in Pandas Dataframes, you will now need to load it into Amazon Redshift to complete your Snowflake to Redshift Migration. You can load your Pandas Dataframes into Amazon Redshift by running the following Python script:

import pandas_redshift as pr
pr.connect_to_redshift(dbname = <dbname>,
                        host = <host>,
                        port = <port>,
                        user = <user>,
                        password = <password>)
# Connect to S3
pr.connect_to_s3(aws_access_key_id = <aws_access_key_id>,
                aws_secret_access_key = <aws_secret_access_key>,
                bucket = <bucket>,
                subdirectory = <subdirectory>
                # As of release 1.1.1 you are able to specify an aws_session_token (if necessary):
                # aws_session_token = <aws_session_token>
                )

# Write the DataFrame to S3 and then to redshift
pr.pandas_to_redshift(data_frame = data,
                        redshift_table_name = <redshift_table_name>, append=False)

You will have to make the following changes to your Snowflake to Redshift Migration code that you execute based on your unique requirements:

• The first function call to connect Python to Amazon Redshift i.e. pr.connect_to_redshift() should have the required parameters of your Amazon Redshift Data Warehouse.
• The second function call to connect Python to Amazon S3 i.e. pr.connect_to_s3() should have the required parameters associated with your Amazon S3 account and buckets. This call is required since this Python package first loads data into Amazon S3 and that data is then loaded into Amazon Redshift from Amazon S3.
• The third function call to load your Pandas Dataframe to Amazon Redshift i.e. pr.pandas_to_redshift() should have the required information about the Pandas Dataframe you wish to import into Amazon Redshift along with the name of the table in which it should be stored. If the append parameter is set as False, any existing table with the same name in Amazon Redshift will be dropped and a new one will be created to store the Pandas Dataframe, and if it is set as True, it will append the Pandas Dataframe to the end of an existing table.
• If multiple Pandas Dataframes were created to extract the necessary data from Snowflake, you will have to make multiple calls to store that data in Amazon Redshift.

Conclusion

This article provided you with a step-by-step guide on how you can set up Snowflake to Redshift Migration seamlessly using Python. However, this process can be challenging for someone who does not have enough technical knowledge and expertise in Python, Snowflake, and Amazon Web Services (AWS) environment.

Visit our Website to Explore Hevo

Most database migration tasks including Snowflake to Redshift Migration, if done manually, require immense engineering bandwidth and resources for the development and maintenance of Data Pipelines. Hence, businesses can instead use existing automated No-code platforms like Hevo.

Hevo helps you directly transfer data from a source of your choice to a Data Warehouse such as Snowflake, Amazon Redshift, etc., Business Intelligence tools, or desired destination in a fully automated and secure manner without having to write the code. It will make your life easier and make data migration hassle-free. It is User-Friendly, Reliable, and Secure.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.