Brief Overview on Converting Teradata SQL and Stored Procedures to Snowflake

by Brandon Carver, on Apr 28, 2020 6:39:45 AM

spock_imageLet’s talk about SQL and sProcs. And no, we are not talking sequels about Spock (though that does sound compelling, that has already been done), but something just as exciting: translating SQL and Stored Procedures (sProcs). Two weeks ago, Mobilize.Net and Snowflake announced a partnership to provide script translation tools for converting from a legacy, on-prem warehouse to Snowflake. We’ve been translating code since the days of Windows 95, but what’s there to translate with SQL? Isn’t SQL the same everywhere you go? And what does this have to do with Spock? (Again, it’s sProcs.)

You would think SQL was the same everywhere, but alas no. That would be way too logical. SQL has ANSI standards that most data warehouses adhere to, but everyone’s architecture is a bit different. That “bit” of difference creates huge problems when translating from one architecture to the next. When transforming from a legacy on-prem warehouse to the cloud, both systems are going to have “things” that appear in one that don’t appear in the other. For example, most Teradata SQL programmers I know always start creating a set table the same way:


That’s quite the mouthful for creating a table with no duplicate values (no one ever accused SQL of lacking verbosity). What’s actually happening here? Why can’t I just write ```CREATE TABLE DATABASE.TABLE (``` and let that be the end of it? It’s that architecture and those extensions of the ANSI SQL. The ```NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO``` statements are essentially telling Teradata how to store the table or different attributes about the table (i.e. not to use large quantities of space to create backups and save copies in order to recover the table). If that verbosity wasn't enough, the table types aren't always directly translatable from one to next. Snowflake does not support Teradata's SET TABLE functionality directly. Instead, you are forced to create a MULTISET TABLE. This is not a huge problem given that MULTISET is the default CREATE TABLE in both Teradata (when in ANSI transaction mode) and Snowflake. These differences in architecture create some obstacles that would otherwise not exist if ANSI SQL was followed by all. 

How would we create the above table in Snowflake? Different architecture, different commands. In fact, the same table created in Snowflake would look like:


That’s it. Snowflake optimizes the architecture and has an ever present recovery system in place that doesn’t need you to turn it on or off every time you create a table.

If you're looking for a deeper dive into the differences between CREATE TABLE statements in Teradata and in Snowflake, check out this page focused exclusively on that topic.

But what about those sProcs? Same story: different architecture. Stored Procedures written for a legacy system like Teradata are generally (not always) written in SQL. Here’s an example of a simple Stored Procedure:

FOR fUsgClass AS cUsgClass CURSOR FOR
          ,TRIM(COL1) AS COL1ALIAS
     FROM cust_knowledge_db.Some_Other_Table
     SET lvSeqNo = lvSeqNo + 1;
     SET UPDATE1 = fUsgClass.COL1ALIAS + 1;
     SET UPDATE2 = fUsgClass.COL3 + 123;

We have some simple commands like FOR and DO in this procedure along with a CURSOR statement. (This is a Stored Procedure, Teradata-style.) We’re using those to update the values in our columns. It’s relatively simple as far as sProcs go, and depending on what you are trying to do, quite useful. However, will this procedure work in Snowflake? You guessed it. Nope. How will we ever overcome this tragedy?

Snowflake uses JavaScript as its scripting language for stored procedures. If you tried to import this directly from Teradata into Snowflake, it would fail. You need to rewrite it to something that looks like this:

     var sql_stmt = `SELECT

               ,TRIM(COL1) AS COL1ALIAS
          FROM cust_knowledge_db.PUBLIC.Some_Other_Table`;
     var stmt = snowflake.createStatement({
          sqlText : sql_stmt
     var res = stmt.execute();
     while ( ) {
          var fUsgClass = {
               COL0 : res.getColumnValue(1),
               COL1ALIAS : res.getColumnValue(2),
COL3 : res.getColumnValue(4)
          lvSeqNo = lvSeqNo + 1;
          UPDATE1 = fUsgClass.COL1ALIAS + 1;
          UPDATE2 = fUsgClass.COL3 + 123;


The same action is accomplished, and our sProc works just as well as it always has. Except that it’s in JavaScript now. Does this mean that we have to know SQL and JavaScript in order to write Stored Procedures? These extensions to the ANSI SQL are already costing time, money, and preventing a move to the cloud. Do we double down and get another competent Full Stack Developer to add to the team to deal with these problems related to JavaScript? I wonder how much time and money that might cost.

Luckily, Mobilize.Net is here to turn one set of SQL into another set of SQL, and all of your most precious and complex sProcs into JavaScript. We'll write some more about sProcs in the future, but for now: live long and prosper.

Topics:MVCSnowflakesql conversionTeradata


Subscribe to Mobilize.Net Blog

Download Free Trial