In the world of data management and analytics, Snowflake has emerged as a powerful cloud-based data platform that offers unparalleled flexibility, scalability, and efficiency.
One crucial aspect of Snowflake’s architecture is its table types, which allow users to tailor their data storage and recovery strategies based on specific needs. In this article, we will explore the different table types available in Snowflake and delve into the key considerations when choosing the right type for your data workloads.
Why do table types matter?
Cost
Snowflake charges its users based on their utilization of compute resources, storage resources, and data transfer resources. Choosing appropriate table types in your architecture can save drastically on storage costs and improve efficiency of compute resources.
Traceability and Recovery
Snowflake has powerful features such as Time Travel and Fail-Safe periods that enable traceability and recovery on your data. Identifying which data sets require this level of protection can give you the freedom to drop the extra cost on the unnecessary tables.
Time Travel
Time Travel enables accessing of historical data at any point within a defined period. This allows users to restore data objects like tables and schemas. As well as duplicating or backing up data from key points in the past. The Time Travel period varies based on table type and individual configuration, but the maximum is 90 days.
Fail-Safe
Fail-safe provides a (non-configurable) 7-day period during which historical data may be recoverable by Snowflake. This occurs immediately after the Time Travel period ends.
Table Types
Permanent
Permanent tables are the default table type in Snowflake. They require no special syntax to create.
CREATE TABLE person(id NUMBER, name VARCHAR(50))Their data is stored permanently and are automatically protected by the Time Travel and Fail-Safe features. This means that you will incur any costs associated with storing the table data as well as the historical data.
Time-Travel is configurable between 0 and 90 days for permanent tables. However the Fail-Safe period is always 7 days.
Long-lived tables, such as fact tables, should always be defined as permanent to ensure they are fully protected by Fail-safe.
Transient
Transient tables share some traits with permanent tables. They persist until explicitly dropped from the database. Which means, they also incur storage costs for the current and historical data. Where they differ lies in the recoverability features.
Time Travel is only available for 0 or 1 day, and the Fail-Safe period is not enabled at all. This makes Transient tables ideal for storing data that can be recovered from sources external to Snowflake.
Another common use case for Transient tables is in ETL (Extract, Transform, and Load) workloads. Since the data is stored for a day, it can help to debug and monitor your data ingestion process.
Transient tables are created with the “TRANSIENT” keyword on table creation.
CREATE TRANSIENT TABLE transientTable (id NUMBER, creation_date DATE);Temporary
Lastly, Temporary tables are the least persistent of all the table types. They exist only within the remainder of the session they were created. This means they are also not visible to other users in Snowflake. For the duration of the existence of the table its data will contribute to the total storage cost of the account.
Temporary tables can have a Time Travel retention period of 1 day; however, a temporary table is purged once the session (in which the table was created) ends and they have no Fail-Safe period.
Temporary tables are useful for storing data needed for a session-related specific task or operation, such as query optimization or managing staging data. They can also be used as intermediate stages in ETL pipelines.
Temporary tables are created with the “TEMPORARY” keyword on table creation.
CREATE TEMPORARY TABLE temp (id NUMBER, creation_date DATE);Wrap up
Understanding the various table types in Snowflake and knowing when to use them can significantly impact the efficiency, cost-effectiveness, and reliability of your data architecture.
If you are embarking on your data warehousing journey and could use some help or consulting, please send us a message!