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?

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.

Blog post image

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:

Job Support Program

Online Work Support for your on-job roles.

jobservice

Our work-support plans provide precise options as per your project tasks. Whether you are a newbie or an experienced professional seeking assistance in completing project tasks, we are here with the following plans to meet your custom needs:

  • Pay Per Hour
  • Pay Per Week
  • Monthly
Learn MoreGet Job Support
Course Schedule
NameDates
Snowflake TrainingDec 14 to Dec 29View Details
Snowflake TrainingDec 17 to Jan 01View Details
Snowflake TrainingDec 21 to Jan 05View Details
Snowflake TrainingDec 24 to Jan 08View Details
Last updated: 21 Sep 2023
About Author

 

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 .

read less
  1. Share:
Snowflake Articles