Teradata to Snowflake Migration Guide

So, you are convinced that Snowflake is for real, lets dive right into how to migrate from Teradata to Snowflake.

1. Understand the differences between Teradata and Snowflake

The first step is to understand the architecture as well as syntax differences between Snowflake and Teradata. Few of the Teradata native functions may not be available in Snowflake. Check out these articles first,

• Differences between Snowflake and Teradata
  
• Converting Teradata data types to Snowflake data types
  

CASE SENSITIVITY

Teradata is case insensitive by default. Snowflake is case sensitive (e.g. ‘Glass’, ‘GLASS’, and ‘glass’ are 3 different values), during the migration, give attention to checking for comparison issues in queries. One simple workaround is to use UPPER on both sides of a comparison (e.g. WHERE UPPER(COLUMNNAME)=UPPER(COLUMNNAME))

ENFORCEMENT OF PRIMARY KEYS AND FOREIGN KEYS

Teradata enforces Primary Keys and Foreign Key constraints. While Snowflake doesn't enforce PKs or FKs. This means you’ll need to re-engineer your ETL load processes that depend on constraints to prevent duplicate entries and orphaned records from being entered into the data warehouse.

QUALIFY

Teradata supports the QUALIFY syntax, whereas Snowflake doesn't support the Qualify function, But here's a workaround:

• An equivalent of "QUALIFY" command in Snowflake
  

DATE VS. TO_DATE()

Teradata has the capability to put DATE in front of a string in order to treat it as a date value (e.g. DATE ‘2018-12-31’). In Snowflake, the syntax is TO_DATE() (e.g. TO_DATE(‘2018-12-31’)). It isn’t necessary to use DATE or TO_DATE() in many situations since both Teradata and Snowflake can interpret the data values stored in a string. 

DATE SUBTRACTION

Teradata will subtract one date from another date (e.g. SELECT ‘2018-12-31’ – ‘2018-12-01’). In Snowflake, to do this same type of date comparison, the DATEDIFF function is required (e.g. SELECT DATEDIFF(day, ‘2018-12-01’, ‘2018-12-31’)).

LIKE ANY

Teradata supports the LIKE ANY syntax as shown below:

SELECT * FROM PRODUCT
WHERE DESC LIKE ANY (‘%yog%’, ‘%milk%’);

Similar logic is implemented in Snowflake with the syntax as shown below:

SELECT * FROM PRODUCT
WHERE (DESC LIKE ‘%yog%’ OR DESC LIKE ‘%milk%’);

TERADATA PERIOD DATA TYPE

Teradata implemented the PERIOD data type to store two dates or timestamps in a single column as a date range. Snowflake doesn’t support the PERIOD data type, so when migrating from Teradata to Snowflake, PERIOD columns will need to be split into two separate date or timestamp columns or changed to a VARCHAR. The load process for those columns will need to account for this change.

UPDATING DATA THROUGH A VIEW

Teradata allows inserts, updates, and deletes to be executed against a view, which will then update the underlying table. In Snowflake, inserts, updates and deletes must be executed against a table and can’t be executed against a view. Again, load processes may need to be re-engineered to account for this.

UPDATE SYNTAX

Teradata allows the FROM in an UPDATE statement to come before the SET statement. In Snowflake, the UPDATE syntax requires that the FROM comes after the SET statement.

DELETE ALL SYNTAX

Teradata supports adding ALL to the end of a DELETE statement. In Snowflake, adding ALL to the end of a DELETE statement isn’t supported and needs to be removed.

TERADATA-SPECIFIC SYNTAX

Teradata has SQL syntax for creating tables (DDL) that isn’t used in Snowflake:

• SET/MULTISET
  
• FALLBACK
  
• PRIMARY INDEX
  
• PARTITION BY
  
• COMPRESS
  
• FORMAT
  
• INDEXES
  

TERADATA HAS SQL SYNTAX WITH VIEWS (DDL) THAT ISN’T USED IN SNOWFLAKE:

• LOCKING ROW FOR ACCESS
  
• SEL (must be spelled out as SELECT)
  
• DEL (must be spelled out as DELETE)
  

Also, REPLACE VIEW syntax in Teradata should be updated to CREATE OR REPLACE VIEW in Snowflake.

2. Plan the migration

• First, Document the existing setup, Collect the list of all the objects, Users, Security roles, permissions, Backup and recovery processes.
  
• Create a migration strategy, classify the objects that will be migrated as-is, the processes that will be re-engineered, and the final architecture diagram
  
• Assemble a migration team and create a migration plan, Deadlines, and the budget
  

3. Execute the migration

Execute the migration plan, test the objects and data before and after the migration to make sure everything is good. Run Teradata and Snowflake processes in parallel for a limited time. If everything looks good, repoint your tools to use Snowflake instead of Teradata and cutover to Snowflake

victor

posted on 17 Jan 19