How to Perform Teradata to Snowflake Migration in Minutes?

Integration from Teradata to Snowflake resolves the challenge of modernizing data infrastructure from legacy on-premises Teradata systems to cloud-based Snowflake. Indeed, Teradata is expensive to maintain; it scales poorly and is slower to adopt cloud-native analytics. So, Snowflake’s integration answers this by giving a cloud data platform that is scalable, flexible, and cost-effective for fast processing, real-time analytics, and seamless integration of disparate data sources to give modern data capabilities into perfect insights and operational efficiencies.

In this blog, we will dive deep into the methods by which you can integrate Teradata with Snowflake.

What is Snowflake?

Snowflake is a cloud-based data warehousing platform designed for modern data analytics and processing. Snowflake separates compute, storage, and services. Therefore, they may scale independently with a SQL data warehouse for querying and analyzing structured and semi-structured data stored in Amazon S3 or Azure Blob Storage.

Advantages of Snowflake

• Scalability: Using Snowflake, you can automatically scale the compute and storage resources to manage varying workloads without any human intervention.
• Supports Concurrency: Snowflake delivers high performance when dealing with multiple users supporting mixed workloads without performance degradation.
• Efficient Performance: You can achieve optimized query performance through the unique architecture of Snowflake, with particular techniques applied in columnar storage, query optimization, and caching.

What is Teradata?

Produced by Teradata Corp, Teradata is a popular on-premise Relational Database Management System (RDBMS). It is best suited for organizations handling and analyzing huge amounts of data. It is highly scalable and is ideal for large Data Warehousing operations. Teradata made the transition to the Cloud a little late, but they are innovating constantly to retain their large customer base.

The Teradata Database system is based on Massively Parallel Processing (MPP) architecture. The MPP architecture divides the workload evenly across the system by splitting tasks among its processes and running them in parallel.

Benefits of Teradata to Snowflake Migration

Below are some of the important pointers that are driving customers to a Teradata to Snowflake migration:

• Teradata charges you for both, storage and also compute power. Snowflake, on the other hand, charges about $1 a day per terabyte of storage. Migrating to Snowflake saves you time and other resources.
• Snowflake’s intuitive features like Compressing Data, Statistic Collection, Workload Management, and Disaster Recovery have been enticing more and more Teradata customers. Teradata, on the other hand, is more dba-driven, lacks advanced features, and requires much more maintenance and time.
• Snowflake has made migrating to the Cloud very easy. You can simply connect third-party products and services to Snowflake through a plugin instead of building a third-party API integration.
• Snowflake’s shared architecture separates “compute” from “storage” which allows automatic and instant scaling in a way not possible with Teradata.
• Snowflakes’ technology meets the needs of today’s business users, such as the increasing requirement for unlimited concurrency and performance.

Technical Challenges

• Transferring large volumes of data from Teradata to Snowflake while maintaining data integrity and consistency can be complex and time-consuming for you.
• There are a lot of differences in data types, formats, and schema structures between Teradata and Snowflake, which may require extensive data transformation.
• Teradata works on a massively parallel processing (MPP) architecture in which data is first broken down and then distributed over as many nodes as there are for processing in parallel. This method very tightly integrates data storage with computational resources, which is completely in contrast to Snowflake’s architecture.
• Teradata uses its variant of SQL, known as Teradata SQL. It includes unique syntax and functions that are specific to its architecture and can cause compatibility issues with Snowflake.

Teradata to Snowflake Migration: 2 Easy Methods

This article delves into 2 of the best ways for Teradata to Snowflake migration. Below are the 2 methods shown. But before that, let us discuss the technical challenges you may face.

Method 1: Teradata to Snowflake Migration with DDL Scripts

Data migration from Teradata to Snowflake isn’t easy and requires you to follow a systematic process. You can follow these steps manually to migrate from Teradata to Snowflake.

Step 1: Moving your Data Model

To start with Teradata to Snowflake migration, you’ll need to move your Database Objects from Teradata to Snowflake. Depending on the scope of your initial migration, Database Objects include the Databases, Tables, Views, and Sequences in your existing Teradata Data Warehouse that you wish to move over to the Snowflake Data Warehouse.

There are several options for moving your data model from Teradata to Snowflake.

In addition to that, the user must set up a Snowflake Account before proceeding with Teradata to Snowflake migration.

Step 2: Setup Snowflake Account

At a minimum, the user who owns the Teradata Database must create a Snowflake Account to meet the needs. You can move over to their homepage to set up your account. Use the Snowflake UI to further create Accounts, Users, Databases, Warehouses, etc., on Snowflake.

Step 3: Migrating Existing DDL Scripts 

After creating Snowflake Accounts and Databases, you can start creating the Database Structure. To do so, you’ll need to export the most recent version of DDL scripts from Teradata to Snowflake. Before migrating the DDL scripts, you need to edit them to make them compatible with Snowflake DDL. You need to remove code for extraneous features such as primary indexes and other storage or distribution-related clauses, that are not needed in Snowflake. You also need to replace the data types used in Teradata Scripts with the Snowflake-optimized data types. Below is a table that provides a mapping between Teradata and Snowflake data types.

Teradata Data Type	Snowflake Data Type
BYTEINT	BYTEINT
SMALLINT	SMALLINT
INTEGER	INTEGER
BIGINT	BIGINT
DECIMAL	DECIMAL
FLOAT	FLOAT
NUMERIC	NUMERIC
CHAR	CHAR
VARCHAR	VARCHAR
LONG VARCHAR	VARCHAR
CHAR VARYING(n)	CHAR VARYING(n)
REAL	REAL
DATE	DATE
TIME	TIME
TIMESTAMP	TIMESTAMP
BLOB	BINARY
CLOB	VARCHAR
BYTE	BINARY
VARBYTE	VARBINARY
GRAPHIC	VARBINARY
JSON	VARIANT
ARRAY	ARRAY

Step 4: Creating New DDL Scripts

If you don’t have current DDL scripts for your Data Warehouse, you can generate new DDL scripts after extracting the metadata needed from the Teradata Data Dictionary. However, as discussed, primary indexes and storage clauses are not needed in Snowflake, hence you don’t need to extract metadata for them.

You only need basic DDL, such as CREATE TABLE, CREATE VIEW, and CREATE SEQUENCE. To generate new DDL scripts, you will need to write a SQL extract script. As discussed above, you need to convert the data types in your Teradata design to the Snowflake-optimized data types. However, you can directly code the data type conversions into the script instead of doing any manual search and replace process.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    BirthDate DATE,)

I have created a Table called Customers with CustomerID, FirstName, and LastName as some of its columns.

Once you have the scripts ready, you can log into your Snowflake Account and execute them. 

Step 5: Load Data from Teradata to Snowflake

Your Snowflake Table is now ready to handle the historical data already loaded in your Teradata system. If you’re running Teradata on-premises, it is advisable to route the data to a Cloud Storage like AWS S3 before loading it into Snowflake. To do so, you will need to leverage Teradata Parallel Transporter (TPT) to extract the data for each table to one or more delimited flat files in text format. You can then upload these files into an AWS S3 staging bucket by using the PUT command. After moving the data into S3 buckets, you can start loading it into your Snowflake Table by using the COPY command.

Command to load the data into AWS S3 bucket using PUT:

PUT file:///Users/myuser/data.csv @my_stage/data/;

Command to load the data into Snowflake using the COPY Command:

COPY INTO my_table FROM @snowflake_stage/path/to/files FILE_FORMAT = (FORMAT_NAME = my_format);

Step 6: Connect Data Sources to Snowflake

After replicating all historical data from Teradata into Snowflake, you can directly connect Snowflake to the Data Sources that are written into Teradata. Make use of Snowflake’s JDBC drivers to load data programmatically into Snowflake from various sources. Many of the mainstream tools have native connectors to Snowflake.

Step 7: Decommission Teradata

Finally, Teradata to Snowflake migration is accomplished! You can now turn off all Teradata load processes and revoke access to Teradata.

Method 2: Move Data to Snowflake with Hevo

Hevo helps you directly transfer data from any source to Snowflake, Business Intelligence tools, Data Warehouses, or a destination of your choice in a completely hassle-free & automated manner. Hevo completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form.

Step 1: Configure any Source

You can select and configure any source of your choice from the 150+ sources Hevo provides.

• Configure Source: Connect Hevo Data with a source of your choice by providing a unique name for your Pipeline, along with details about your Data Source.

Step 2: Configure Destination

You can easily configure Snowflake as your destination and provide the credentials to your Snowflake account for Hevo to access it.

• Integrate Data: Establish a connection to Snowflake by providing information about your Snowflake Account and its credentials such as Database Name, Username, and Password.

Conclusion

Snowflake is a major player in the Cloud Data Warehousing industry and understanding how to migrate into it has become important. This article introduced you to Teradata and Snowflake and provided you with a step-by-step guide on Teradata to Snowflake migration through DDL scripts. Migrating to Snowflake with DDL scripts requires a lot of time and resources. 

Migrating to Snowflake is a tedious and time taking process but a Data Integration tool like Hevo can perform this process with no effort and no time.

FAQs to migrate data from Teradata to Snowflake

Give Hevo Data a try and sign up for a 14-day free trial today. Hevo offers plans & pricing for different use cases and business needs!

Share your experience of working with Teradata to Snowflake migration in the comments section below.