Different SQL Table Definitions in Teradata & Snowflake

Looking back on that, I think some of you may have thought I was writing about a piece of furniture. You might be on the wrong website for that (though I, and all my fellow bloggers, reserve the right to get crafty in future blog posts). I’m really writing about the souped-up spreadsheet kind of table. Both are pictured to the right, and I would hope creating either would yield some measure of satisfaction.

Given that a table is one of the most fundamental objects that you can create in a database, there must be enough literature out there to tell us what a table actually is. (And yes, there is.) It's been harder to find a guide to how to compare different data warehouses in regards to how they create these tables. So who better than the people who spend their lives converting from one source to a specific target to develop a cross reference. How does a table definition in Snowflake compare to Teradata? This is a shockingly common question that we get in the early stages of a data warehouse migration project, now that Mobilize.Net has decided to take our talents to migrations from legacy data warehouses to Snowflake.

Thus, this blog post was born. We decided to put together a brief table of tables:

(... no comment from this author on the above image ...)

(... moving on ...)

This post will focus on Snowflake and Teradata. (We'll tackle Oracle to Snowflake in a future post.) Both use the CREATE TABLE command in SQL, but that doesn't mean that all CREATE TABLE statements are made equal. What are the differences between the tables they are creating? Like any good question, the answer to that question is... it depends. CREATE TABLE could mean multiple things based on what default settings are used or even what mode you're using. Let's take a look at some of the language used in creating a table. Specifically, we'll compare:

• the language used in the actual CREATE TABLE statement
• the differences in naming conventions
• the parameters used to complete the statement

Teradata to Snowflake

When we look at the CREATE TABLE statements, what syntax do we change in the actual statement itself to create unique table types? For example, in Teradata, you can specify if you want to create a multiset or a set table by inserting it before the word TABLE in the create table statement (CREATE SET TABLE or CREATE MULTISET TABLE). The language that you use is based on the outcome you desire, so it's important to understand what kind of table you want to create. Here is a quick summary of the most common type of tables that we've seen in conversions from Teradata to Snowflake:

• Permanent Multiset Table: The standard ANSI compliant table. The word "Permanent" is not specified in the CREATE TABLE statement.
• Permanent Set Table: The standard table, but no duplicate rows are permitted.The word "Permanent" is not specified in the CREATE TABLE statement.
• Multiset Global Temporary Table: A temporary multiset table that is made available to rest of those with access to the database. 
• Set Global Temporary Table: A temporary set table that is made available to rest of those with access to the database. 
• Multiset Volatile Table: A temporary multiset table that is only available to the current user in the current session.
• Set Volatile Table: A temporary set table that is only available to the current user in the current session.

Below is a flowchart that helps you decide what you want to do with each of the table types listed above. This also maps each of those table types with the equivalent output in Snowflake. Feel free to zoom in.

Hopefully, looking through the diagram above helps you make the decision on what create table syntax you might want to use to meet your needs in Teradata or Snowflake. If we look at this as a table (as opposed to a flowchart), we can highlight the conversions from Teradata to Snowflake (note that a table type used in Snowflake is listed at the bottom):

Hopefully, the above table of tables makes sense. All of these define the type of table you're attempting to create. 

A Few Notes

The above diagram and table only scratch the surface of the parameters that can be applied to a table in either Teradata or Snowflake. There are considerably more options to consider for each table (a few of which are mentioned in this blog post, and more may appear in future posts). Many of these parameters can create new table types. Here are a few more common ones in Teradata:

• Queue Table: A queue table manages how data is inserted into the table with (and this may shock you) a queue. The first column of the table will be a user-defined timestamp called the QITS (Query Insertion Timestamp), and this is used as the primary index. Queue tables are not supported in Snowflake, but the functionality is also not necessary due to Snowflake's architecture. 
• NoPI Table: A NoPI (and that's p-i on the end there) table is a table without a primary index in Teradata. The biggest advantage of this is an increase in speed for loading data, but there are other advantages as well. As with the aforementioned queue table, the functionality is not necessary due to Snowflake's architecture.
• Temporal Table: A temporal table stores data with respect to time. There are numerous sub-options and functions you can perform with a temporal table, and you can even create entirely temporal databases. With a temporal table, you can queue a tables history based on what it looked like at a given moment in time. Snowflake has it's own Time Travel function that serves a similar function. 
• Error Logging Table: This type of table is usually used in conjunction with a temporal table to log errors that occur during operations done on a particular table (such as INSERT or MERGE).
• Derived Table: Essentially, a derived table is a view. It is a table that is built (or... derived) from another table. A derived table would most likely convert to a view in Snowflake.

The above table types we've seen in small quantities. The syntax is usually formed from one of the prefixes in the table able, but the parameters are different. For example, consider the following queue table in Teradata vs. a NoPI table.

-- Queue
CREATE MULTISET TABLE queue_table, QUEUE
( qits_col TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  col_1 NUMERIC,
  col_2 VARCHAR(30)
) PRIMARY INDEX(qits_col);

-- NoPI
CREATE MULTISET TABLE no_pi
( col_1 NUMERIC NOT NULL,
  col_2 VARCHAR(30),
  col_3 VARCHAR(50)
) NO PRIMARY INDEX;

In the above example, both tables use the same CREATE MULTISET TABLE intro, but the parameters are different which change how we would classify each table. 

If you're looking for more information on changes in table types from one data warehouse platform to another, reach out to us. This post has only scratched the surface.