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.
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.
Snowflake offers three types of tables, namely - Transient, Temporary, & Permanent.
The permanent table is the default type in Snowflake.
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.
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.
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.
The following table summarizes the differences between the three data types with regard to Time travel and fail-safe.
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:
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:
Name | Dates | |
---|---|---|
Snowflake Training | Dec 14 to Dec 29 | View Details |
Snowflake Training | Dec 17 to Jan 01 | View Details |
Snowflake Training | Dec 21 to Jan 05 | View Details |
Snowflake Training | Dec 24 to Jan 08 | View Details |
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 .