05 Mar 20 · moyeenbasha · #Snowflake ·   Bookmark   ×

How to migrate from Teradata to Snowflake?

Hi all, We have one requirement where we have to migrate the existing Teradata to Snowflake, below is the full requirement

•Conversion of SAS jobs and Unix shell scripts to python along with conversion of all Teradata SQL to Snowflake SQL. Leverage Python and PySparkto contain functionality of Teradata BTEQ as much as possible

•Conversion of all Teradata views to Snowflake compatible views

•Conversion of Teradata Stored Procedures to SnowSQL/ Snowflake SP

•Convert all Teradata tables to Snowflake Tables. Assuming 300 tables and 1000 secondary indexes.

•Setup Snowflake environment with all DDL and security structures, ready for end user usage.

I am thinking to this way:

step1: Migrate the Metadata from Teradata to Snowflake

step2: Migrate the data using Spark with Python

Need your help :

In step1 is there any way where we can convert  DDL(Metadata) to snowflake easily

In step2 we have 2 type of loading(History,Incremental)

I believe Incremental load can be handled using Python/SPARK, how to load huge History loading.

Please let me know if am aligned with my requirement, if not help me with the suitable approach.

I will be grateful for any help you can provide.

moyeenbasha

posted on 05 Mar 20

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds




Thank you for asking a great question. Here is one of the tried and tested approach:

Step-1 : Establish the Security, VPC Service controls, and finalize the strategy for handling PHI / PII / PCI on the cloud

Start with setting up the infrastructure first and secure it. Have detailed debates on how you are going to handle sensitive data if you have any. You can choose to NOT load the sensitive data, or encrypt it, or Hash it. Once you establish the security and infrastructure go to step 2

Step-2: Migrating the structures from Teradata to Snowflake

You will need to migrate all your Table DDLs, View DDLs and Stored procedures, Macros and BTEQ scripts from Teradata to Snowflake. BTEQ is Teradata native utility and you can easily copy paste only the SQL in BTEQ to a plain text file and schedule it to run via SnowSQL. 

TeradataSnowflake
TableTable
ViewView
Stored ProcedureRewrite in Javascript / Python
MacroRewrite in Javascript / Python as Stored Proc
BTEQPaste only the SQL commands to a plain text file and run it via SnowSQL
FASTLOAD, MLOAD, TPUMP, TPTCOPY INTO Command
FASTEXPORT

COPY INTO Command

A lot of this process needs good understanding and expertise in both the systems, which may be challenging for beginners. There are automated online code converters to convert from Teradata to Snowflake. You can give it a try and augment it with engineers to complete the migration process. And also note, This is the best time to get rid of your obsolete objects

Step-3 Migrating History data from Teradata to Snowflake

The best way to do this is,

  • Do a BULK Unload from Teradata using FastExport or TPT
  • Compress the files
  • FTP the files to the cloud storage
  • Use COPY command to insert into the snowflake table

You can automate this process by building a unix based tool. The tool may require few rounds to trial and error to get all the datatypes to work

Step-4 Loading Incremental data to Snowflake

You should be able to use your existing ETL tool to load to Snowflake or even replace your ETL tool with some programming construct. The steps would remain the same,

  • Create a file with all the incremental data 
  • FTP the file to Cloud storage
  • Load into Stage table
  • Do Merge from Stage to Target and perform CDC

Step-5 Test everything

Cloud Migration is a serious stunt. The prime objective is to avoid / reduce end user disruption to any extend possible. As the success of the project depends on the end user adoption. So test everything, Your data, your schedules. Run it for few weeks before you hand it over to the end users. Be empathetic and allow ample time for the users to understand and adopt the new platform

Bonus Answer

Since you also asked about SAS, I would advise not to mix SAS with your cloud migration project. You should be able to migrate your SAS programs to python but thats a whole new topic in itself

And finally

Do not get overwhelmed ! I will be honest and not paint a Rosy picture, Cloud migration is hard, You will solve challenges in every step, You will overcome resistance, You will emerge successful because every goddamn thing will work at the end. But remember to take one step at a time :) 

Good Luck


Community Software by Hittly
Copied