How to migrate from Teradata to Snowflake?

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. 

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

We have created a product "SnowPole" to automate some of these functionalities. Pls follow the demo videos from the link below. If interested, kindly reach out to snowpole@cannytechsolutions.com. We can discuss  on how to expedite your DW modernization process. 

https://youtu.be/71I6eRyn_zU

https://www.youtube.com/playlist?list=PLYiS7ShOTsqkVvdsZzw4Vfjajy2pgD_ZS