Teradata vs Snowflake: A Comparison of Volatile and Temporary Tables

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.

Snowflake temporary tables can shadow permanent tables in the same schema and support Time Travel subject to 0/1-day retention and session lifetime. They have no Fail-safe. Do not assume unavailable Time Travel or recovery after session end. Teradata same-name resolution should be checked with qualified names; that behavior was not tested here.

Use ON COMMIT PRESERVE ROWS for temporary-table data that must survive a transaction commit. ON COMMIT DELETE ROWS deletes at the transaction boundary, not immediately after every insert in every mode. GTT definitions are persistent and data instances are per session; restart/recovery behavior was not tested here. The volatile-table syntax example is CREATE VOLATILE TABLE Customer (CustomerId INTEGER, OpenDate DATE) PRIMARY INDEX (CustomerId) ON COMMIT PRESERVE ROWS;

Use ON COMMIT PRESERVE ROWS for temporary-table data that must survive a transaction commit. ON COMMIT DELETE ROWS deletes at the transaction boundary, not immediately after every insert in every mode. GTT definitions are persistent and data instances are per session; restart/recovery behavior was not tested here. The volatile-table syntax example is CREATE VOLATILE TABLE Customer (CustomerId INTEGER, OpenDate DATE) PRIMARY INDEX (CustomerId) ON COMMIT PRESERVE ROWS;

Snowflake transient tables have no Fail-safe; permanent tables can have Fail-safe after Time Travel. Transient Time Travel is configurable at 0 or 1 day. Storage history can remain after DROP, so costs do not necessarily stop at table deletion.