What are the steps to be taken to migrate historical data load from Teradata to Snowflake? Imagine there is 200TB+ of historical data combined from all tables.
I am thinking of two approaches. But I don't have enough expertise and experience on how to execute them. So looking for someone to fill in the gaps and throw some suggestions
Approach 1- Using TPT/FEXP scripts
- I know that TPT/FEXP scripts can be written to generate files for a table. How can I create a single script that can generate files for all the tables in the database. (Because imagine creating 500 odd scripts for all the tables is impractical).
- Once you have this script ready, how is this executed in real-time? Do we create a shell script and schedule it through some Enterprise scheduler like Autosys/Tidal?
- Once these files are generated , how do you split them in Linux machine if each file is huge in size (because the recommended size is between 100-250MB for data loading in Snowflake)
- How to move these files to Azure Data Lake?
- Use COPY INTO / Snowpipe to load into Snowflake Tables.
Approach 2
- Using ADF copy activity to extract data from Teradata and create files in ADLS.
- Use COPY INTO/ Snowpipe to load into Snowflake Tables.
Which of these two is the best suggested approach ? In general, what are the challenges faced in each of these approaches.