See why hundreds of industry leaders trust Secoda to unlock their data's full potential.
See why hundreds of industry leaders trust Secoda to unlock their data's full potential.
Get the guideSnowflake supports several types of tables to cater to different data storage and management needs. This guide will walk you through the main table types in Snowflake, their characteristics, and use cases.
Snowflake offers a variety of table types to meet diverse data storage and management requirements. These include Permanent Tables, Temporary Tables, Transient Tables, External Tables, Hybrid Tables, and Iceberg Tables. Each table type has unique characteristics that make it suitable for specific use cases.
Permanent tables are the default and most common table type in Snowflake. They are designed for storing long-term data that requires high availability, data protection, and recovery mechanisms.
CREATE TABLE STORE (STORE_ID NUMBER, STORE_NAME STRING);
This code creates a permanent table named STORE with columns STORE_ID and STORE_NAME. Permanent tables support Time Travel up to 90 days and have a 7-day fail-safe period for data recovery.
Temporary tables are designed for storing transient, non-permanent data that is only needed for a specific session or short-term operation.
CREATE TEMPORARY TABLE temp_students (
student_id INT PRIMARY KEY,
name VARCHAR(100),
age INT,
major VARCHAR(100)
);
This code creates a temporary table named temp_students. Temporary tables only exist within the current session and are automatically dropped when the session ends.
Transient tables are a hybrid between permanent and temporary tables. They persist until explicitly dropped but do not have the same level of data protection as permanent tables.
CREATE OR REPLACE TRANSIENT TABLE T_TABLE (INTERGER_COLUMN NUMBER(38,0));
This code creates a transient table named T_TABLE. Transient tables have a limited Time Travel period of 0-1 day and no fail-safe period.
External tables are used to query data stored in external cloud storage (e.g., AWS S3, Azure Blob Storage) from within Snowflake.
External tables are read-only and do not support Time Travel or fail-safe capabilities. They are useful for accessing and querying data lakes without ingesting data into Snowflake.
Hybrid tables combine columnar key-value pair databases and caching. They are designed for transactional and operational workloads that require high throughput and low latency on small random reads and writes.
Iceberg tables store data and metadata files in external cloud storage, such as Amazon S3, Google Cloud Storage, or Azure Storage. The data is stored in Apache Iceberg table format.
Table Type Persistence Time Travel Fail-Safe Use Case Permanent Long-term Up to 90 days 7 days Core business data Temporary Session-based 0-1 day None Short-term operations Transient Until explicitly dropped 0-1 day None Intermediate storage External External storage None None Data lakes Hybrid Until explicitly dropped Varies Varies Transactional workloads Iceberg External storage Varies Varies Large datasets
While working with Snowflake tables, you may encounter some common challenges. Here are a few and their solutions:
In this guide, we explored the different types of tables in Snowflake, including their characteristics and use cases. Understanding these table types will help you make informed decisions based on your data storage and management needs.
Built for data teams, designed for everyone so you can get more from your data stack.
© 2024 Secoda, Inc. All rights reserved.
By using this website, you accept our Terms of Use and Privacy Policy.
