Home  >  Blog  >   Snowflake  > 

What is a Snowflake Table & Types of Tables

Rating: 5
  
 
10184

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?

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.

Temporary Tables

#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.

 

Transient Tables#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.

Permanent Tables

MindMajix Youtube Channel 

Comparison of Table Types

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

Comparison of Table Types

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:

Join our newsletter
inbox

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
NameDates
Snowflake TrainingMay 17 to Jun 01
Snowflake TrainingMay 21 to Jun 05
Snowflake TrainingMay 23 to Jun 07
Snowflake TrainingMay 28 to Jun 12
Last updated: 02 May 2022
About Author
Madhuri Yerukala

Madhuri is a Senior Content Creator at MindMajix. She has written about a range of different topics on various technologies, which include, Splunk, Tensorflow, Selenium, and CEH. She spends most of her time researching on technology, and startups. Connect with her via LinkedIn and Twitter .