Introduction to Teradata Volatile Tables and Snowflake Temporary Tables
Teradata and Snowflake offer temporary tables. This post will compare and contrast the types of tables available on each platform.
We will examine distinct table varieties found only in Teradata and Snowflake and exhibit their ideal utilization.
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 essential difference in how naming conflicts are resolved if the same table name is used for both a permanent and 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 unavailable. This can lead to unpleasant surprises when you want to undo changes.
Name conflicts with Teradata Volatile Tables are solved differently. An error occurs if the database or the session user is not listed and there is a permanent table and a volatile table with the same name.
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.
We can restore permanent tables for a time after the time travel period (depending on 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).