Types of Tables in Snowflake

Tables, as database constructs, are designed to organize data into rows and columns logically. In Snowflake, all data resides within these database tables. In addition to the conventional database tables, Snowflake offers distinct table types tailored for data that doesn’t require long-term retention.

Snowflake provides support for three primary table types:

1. Permanent Table
2. Transient Table
3. Temporary Table

Permanent Table

Permanent tables represent the standard, traditional database tables. In Snowflake, they serve as the default table type, requiring no extra syntax during creation to establish their permanence.

Data residing within permanent tables occupies storage space and contributes to the storage fees associated with your Snowflake account. Moreover, permanent tables offer additional functionalities, including Time-Travel and Fail-Safe features, enhancing data availability and recovery capabilities.

To create a permanent table in Snowflake, use the following SQL query:

create table employee (id number, name varchar(50));

Transient Table

In Snowflake, Transient tables share similarities with permanent tables, but they distinguish themselves by lacking a Fail-safe period and offering only a very limited Time-Travel window. They find their ideal use in scenarios where the data within the table is non-critical and can be re-created through external means if necessary.

Much like permanent tables, Transient Tables contribute to your overall storage costs within your account. However, due to their absence of Fail-safe functionality, there are no associated Fail-safe expenses — meaning, there are no costs related to preserving data for Fail-safe disaster recovery.

To create a Transient table in Snowflake, simply include the “transient” keyword within the table creation syntax.

create transient table employee (id number, name varchar(50));

Temporary Table

Temporary Tables in Snowflake have a lifespan restricted to the session in which they are initiated and are accessible only throughout that particular session. They remain concealed from other users or sessions, and upon the session’s conclusion, all data housed within the table is permanently purged and cannot be recovered.

Similar to Transient tables, temporary tables lack a Fail-safe retention period and feature an extremely limited Time-Travel duration. They prove most advantageous for non-permanent, transient data that retains relevance solely within the session of their creation.

It’s important to note that while Temporary tables are automatically discarded at the end of the session, Snowflake recommends a proactive approach of explicitly dropping these tables when they are no longer needed, especially when dealing with sizable temporary datasets. To create a Temporary table in Snowflake, you must specify the “temporary” attribute within the create table syntax.

create temporary table employee (id number, name varchar(50));

Choosing Right Table Type

Choosing the right table type in Snowflake depends on the nature of your data and its intended use.

Permanent tables are ideal for storing critical data meant for long-term use, offering the benefits of Time-Travel and Fail-Safe features for data availability and recovery.

Transient tables are suitable when data is non-critical and can be recovered externally.

Temporary tables, on the other hand, are designed for short-term use within a single session and should be employed for non-permanent, transitory data.

To make an informed choice, consider your data’s permanence, recovery requirements, and the scope of its usage, ensuring optimal data management within Snowflake.