Making the Transition from Teradata to Snowflake

Teradata is a global innovator when it comes to Enterprise Data Warehouses (EDWs). Having been in the business of providing data storage systems for over forty years the software company has assisted a number of Fortune 100 companies with data solutions that are mature, scalable and secure.

Despite being held in high regard by the world’s leading research and advisory company, many companies are transitioning from this legacy system to other cloud-based computing systems such as Snowflake. The reason behind this? Teradata maintenance can cost up to $90,000 a year.  Moreover, exponential growth in data along with Teradata’s performance and capacity issues make a transition to a more efficient database imperative. Not to mention the advent of new data sources and workloads as well as the high licensing cost and Total Cost of Ownership (TCO).

Snowflake can really shine under such circumstances being a cloud based EDW that functions on AWS, Azure and Google Cloud. The storage system is unique in that it uses a virtual warehouse that run on isolated compute clusters for query processing thereby offsetting the burden on the other warehouses. Being programmed to function in this manner has made Snowflake much faster, more adaptable and manageable.

There are other advantages of migrating from Teradata to Snowflake. For instance, the average savings from cloud migration come to around 15% on all IT expenditure. It’s not just about cost saving alone, Snowflake can provide remote access to users so that your enterprise can remain in operation even during unforeseen circumstances. It is no wonder that cloud adoption by organizations is up by nearly 60% since 2018 with benefits such as better disaster recovery, flexibility and the ability to alleviate IT staff workloads.

Orchestrating the Transition

The migration journey comes in two phases:

• Defining the database objects i.e., SQL code conversion, tables, views etc.
• Shifting the data from Teradata to Snowflake

Even though there are multiple instruments and functions at your disposal intended to carry data from Teradata to Snowflake, this monotonous undertaking of transforming database objects is often left under scripted. 

1. Transforming Teradata Table DDL to Snowflake

You can begin by exporting the table definition into a text formatted file.

SELECT ‘SHOW TABLE ‘||TRIM(DATABASENAME)||’.’||TRIM(TABLENAME)||’;’ FROM DBC.TABLESV WHERE DATABASENAME = ‘YOUR_DATABASE_NAME’ AND TABLEKIND =’T’;

Carry out the above query in SQL Assistant and you will arrive at the show table commands for every table in your database.

With the show table commands now displayed, you can begin to execute them, finally exporting the table DDLs into a text file.

The next step upon arriving at the Teradata DDLSs is to download and install a free Chrome plugin called Roboquery. Copy the Teradata table DDLs once you have run the extension and select the convert button to convert the code to Snowflake.

2. Transforming Teradata Views to Snowflake

This stage begins by carrying out the same steps as listed in the previous stage except that now you extract the view definitions with the query:

SELECT ‘SHOW VIEW ‘||TRIM(DATABASENAME)||’.’||TRIM(TABLENAME)||’;’ FROM DBC.TABLESV WHERE DATABASENAME = ‘YOUR_DATABASE_NAME’ AND TABLEKIND =’V’;

Follow this by exporting the view definitions into a text file again and copying in the source view definitions in to Roboquery and selecting the convert button to have the resulting conversion of view SQL to Snowflake.

3. Transforming Teradata SQL to Snowflake

SQL Queries can be converted using the same steps. Roboquery can transform all datatypes, functions in addition to rewriting the structure to be more in alignment with the Snowflake data warehouse structure.

Minimalize Manual Intervention

Although you can manually convert all of the code to work with Snowflake it’s not something you would ideally like to spend time on doing. But with the help of modern tools, schema can be converted from Teradata to Snowflake almost instantaneously, formatting it to be optimized for Snowflake’s EDW.

Would you like to begin your transition from Teradata to Snowflake? DataSwitch would be happy to help. DataSwitch provides Intuitive, Predictive and Self-Serviceable Schema redesign from 3NF to Document Model, as well as fully automated data migration & transformation based on redesigned schema and no-touch code conversion from legacy data scripts to modern database APIs. You can count on DataSwitch for cost-effective, accelerated solutions for digital data transformation and modernization. Our no code and low code solutions along with enhanced automation, cloud data expertise and unique schema generation accelerates time to market. Get your enterprise’s cloud-driven data modernization journey running at light speed with business continuity ensured. Book a demo to know more.