February 8

0 comments


Both Teradata and Snowflake offer temporary tables. In this blog post, I'll show you which types of tables Teradata and Snowflake provide, what similarities and differences they have.

We will also look at table types that are only available in Teradata or only in Snowflake. I will show you what these can best be used for.

What is the equivalent of Teradata Volatile Tables in Snowflake?

Snowflake has the so-called Temporary Tables. These are roughly equivalent to the volatile tables in Teradata.

In both Teradata and Snowflake, the lifetime of these tables is the session. The tables are automatically dropped as soon as a session ends.


What are the differences between the Teradata Volatile Table and the Snowflake Temporary Table?

There is an important difference in how naming conflicts are resolved if the same table name is used for both a permanent table and a temporary table.

In Snowflake the temporary table always has priority. If a permanent table is created when a temporary table with the same name already exists in the same schema, the temporary table is always addressed.

If you create a temporary table with the same name as an existing permanent table, the permanent table is hidden by the temporary table.

This can lead to unexpected problems. All changes are made on the Snowflake Temporary Table and Time Travel functionality is not available. This can lead to unpleasant surprises when you want to undo changes.

Name conflicts with Teradata Volatile Tables are solved differently. If the database or the session user is not listed and there is a permanent table as well as a volatile table with the same name, an error occurs.

Snowflake Temporary Tables offer a history of one day (time travel feature). Teradata doesn't support time traveling.

What is the syntax of Teradata Volatile Tables or Snowflake Temporary Tables?


Snowflake Syntax:
CREATE TEMPORARY TABLE Customer
(
CustomerId NUMBER,
OPENDATE DATE
);

Teradata Syntax:
CREATE VOLATILE TABLE Customer
(
CustomerId INTEGER,
OPENDATE DATE
) PRIMARY INDEX (CustomerId) ON COMMIT PRESERVE ROWS;

ON COMMIT PRESERVE ROWS must be specified so that the inserted rows are not immediately deleted

Which table type is only available in Teradata?

The content of a GLOBAL TEMPORARY TABLE is also only available during the session. The difference to the VOLATILE Table is that the Table Definition (DDL) is stored in the Data Dictionary.

As soon as a GLOBAL TEMPORARY table is accessed in a DML statement, it is materialized in the session. Global Temporary Tables also survive a system restart (volatile tables are lost).

Teradata does not use spool space for global temporary tables as it does for volatile tables, but temp space.

As with volatile tables, ON COMMIT PRESERVE ROWS must be specified so that the inserted rows are not immediately deleted.

Syntax:
CREATE GLOBAL TEMPORARY TABLE Customer
(
CustomerId INTEGER,
OPENDATE DATE
) PRIMARY INDEX (CustomerId) ON COMMIT PRESERVE ROWS;

Which table type is only available in Snowflake?

The main difference is that there is no fail-safe period with permanent tables.

Permanent tables can be restored for a time after the time travel period (depending on the edition 1-90 days) is over

This is not possible for transient tables.

Since transient tables do not provide fail-safe protection, there are only storage costs as long as the table exists (for permanent tables, you have to pay as long as the tables are protected by fail-safe).

Syntax:
CREATE TRANSIENT TABLE Customer
(
CustomerId NUMBER,
OPENDATE DATE
);

Transient Tables offer a history of one day (time travel feature).

DWH Pro Admin


Teradata and Data Warehouse Specialist with more than 20 years of experience

You might also like

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Never miss a good story!

 Subscribe to our newsletter to keep up with the latest trends!

>