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 how you can set Teradata to Snowflake migration.

What is Snowflake?

Snowflake is a cloud-based data warehousing platform designed for modern data analytics and processing. Snowflake separates computing, storage, and services. Therefore, they may scale independently with an 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 essential pointers that are driving customers to a Teradata to Snowflake migration:

• Teradata charges you for both storage and computing 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 enticed more Teradata customers. Conversely, Teradata 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, ” allowing automatic and instant scaling in an impossible way with Teradata.
• Snowflakes’ technology meets the needs of today’s business users, such as the increasing requirement for unlimited concurrency and performance.

Challenges in Migrating Data from Teradata to Snowflake

1. SQL Dialect Differences: Different SQL syntax and data handling approaches between Teradata and Snowflake can require extensive code modifications.
2. Complex Code Migration: Migrating complex stored procedures, user-defined functions (UDFs), and intricate SQL queries can be time-consuming and error-prone. Manual rewriting may be necessary, increasing the risk of introducing errors and impacting performance.
3. Data Type Conversion: Ensuring accurate and lossless conversion of data types between Teradata and Snowflake is crucial for data integrity.
4. Data Distribution and Partitioning: Optimizing data distribution and partitioning strategies in Snowflake to achieve optimal query performance can be challenging.

Teradata to Snowflake Migration: 2 Easy Methods

Method 1: Teradata to Snowflake Migration with DDL Scripts

Data migration from Teradata to Snowflake isn’t easy and requires 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 must 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 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 develop 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. You’ll need to export the most recent version of DDL scripts from Teradata to Snowflake to do so. Before migrating the DDL scripts, you need to edit them to make them compatible with Snowflake DDL. You must remove code for extraneous features, such as primary indexes and other storage or distribution-related clauses, which are not required in Snowflake. You must also replace the data types in Teradata Scripts with the Snowflake-optimized data types. Below is a table that provides a map of the 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 ones after extracting the metadata needed from the Teradata Data Dictionary. However, as discussed, primary indexes and storage clauses are not required in Snowflake, hence you don’t need to extract metadata for them.

You only need basic DDL, such as CREATE TABLE, VIEW, and CREATE SEQUENCE. You must write an SQL extract script to generate new DDL scripts. 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 the scripts are ready, you can log into your Snowflake Account and execute them. 

Step 5: Load Data from Teradata to Snowflake

Your Snowflake Table is 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 must leverage Teradata Parallel Transporter (TPT) to extract the data for each table and convert it to one or more delimited flat files in text format. You can then upload these files into an AWS S3 staging bucket using the PUT command. After moving the data into S3 buckets, you can start loading it into your Snowflake Table 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 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 and 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.

Tools That Can Help with Teradata Migration

• AWS Schema Conversion Tool: Helps migrate databases from various sources, including Teradata, to AWS services like Amazon Redshift.
• Hevo’s Automated Pipeline: An official Snowflake Partner with an easy, reliable, and quick way to migrate your data from Teradata to Snowflake. Its auto-schema mapping feature reduces your burden of manual mapping and errors. 
• Datafold’s Migration Agent: Automates the SQL conversion process, minimizing manual effort and reducing the risk of errors.
• Snowflake’s SnowConvert: A native code-conversion tool installed locally and supports various operating systems (Windows, Mac, Linux).
• BryteFlow XL Ingest: A data replication tool that leverages intelligent partitioning and parallel synchronization for fast and efficient data loading.
• Matillion ETL: A cloud-based ETL platform with pre-built connectors and a user-friendly interface for data integration and transformation.

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 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 tedious and time-consuming, but a data integration tool like Hevo can perform this process without effort or time. 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.

FAQs