What is a Snowflake Table & Types of Tables

Snowflake has emerged as one of the most reliable platforms for cloud data warehousing. It offers cloud-based data storage or data-warehouse-as-a-service and analytics service more flexible than traditional offerings. According to Forrester’s ‘Total Economic Impact Study,’ Snowflake customers can expect an ROI of 612% and total benefits of over $21 million over three years. 

This blog post describes what the Snowflake table is, the types of tables in Snowflake, and compares table types.

Table of Content - Snowflake Table

What is a Table in Snowflake? What are the Types of Tables in Snowflake? Comparison of Table Types

What is a table in Snowflake?

A snowflake schema is a logical grouping of tables in a multidimensional database during computing such that the entity-relationship plan relates a snowflake shape. Snowflake is a method of normalizing the table’s dimension in a star schema. The principle behind Snowflaking is the normalization of the dimension tables by eliminating the low cardinality attributes.

All data in Snowflake is stored in database tables and logically structured as collections of rows and columns. To get the maximum benefit of Snowflake tables, it’s better to understand the physical structure behind the logical structure, especially on large tables.

Snowflake consists of schemas, which are logical groupings of database objects, such as views and tables. Snowflake does not place any limit on the number of databases, schemas, or things.

To learn more concepts on Snowflake, then check out our MindMajix's Snowflake Training.

What are the types of tables in Snowflake?

 

What are the types of tables in Snowflake?

Snowflake offers three types of tables, namely - Transient, Temporary, & Permanent.

The permanent table is the default type in Snowflake.

#1. Temporary Tables:

Snowflake supports creating temporary tables to store transient, non-permanent data.

Temporary tables exist only within the session. They are created and persist only for the session remainder. They are not visible to other sessions or users and don’t support standard features like cloning.

Therefore the data stored in the system is cleaned entirely and is not recoverable either by the user-created table or Snowflake.

To create a temporary table, specify the TEMPORARY keyword in CREATE TABLE.

 

#2. Transient Tables:

Snowflake supports creating Transient tables that continue until dropped explicitly and are available to all the users with the relevant privileges.

To maintain transitory data beyond each session, transient tables are designed. Temporary tables are similar to permanent tables with the vital difference in their absence of a Fail-safe period.

To create a temporary table:

After creation, transient tables cannot be converted to another table type.

 

#3. Permanent Tables:

Permanent tables have a Fail-safe period similar to transient tables and provide additional security of data recovery and protection.

Presently, the permanent table cannot be modified to Transient Table using ALTER TABLE command. Property of TRANSIENT is set at the creation of a table and cannot be customized.

In the same way, it isn’t possible to change directly a transient table to a permanent table.

In order to convert an existing transient table to a permanent table (or vice versa) through protecting data and other characteristics such as granted privileges and column defaults, you can create a new table and use the COPY GRANTS clause, then copy the data.

Let’s understand with a few examples.

Example 1) Create a transient database to acquire all create schema/tables as transient by default.

Example 2) Create a permanent database with Transient schema to acquire all create tables as transient by default.

 

Comparison of Table Types

The following table summarizes the differences between the three data types with regard to Time travel and fail-safe.

Preparing for Snowflake Interview? Here’s Top Snowflake Interview Questions and Answers

Conclusion:

In this article, you have learned about the Snowflake table, the types of tables, and their comparison. We hope you got a clear idea of  Snowflake tables. To know more read our Snowflake Tutorial.

Thanks for reading this article so far. If you find this blog useful then please share it with your friends and colleagues. If you have any questions or feedback then please drop it in the comment section below.

Related Articles:

• Star Schema & Snowflake Schema in QlikView
• Snowflake vs Databricks
• Snowflake vs Redshift
• Snowflake Architecture
• Snowflake Data Warehouse