Quick guide to Snowflake Table Types Comparison

1. Permanent Table

• Description: Standard, persistent storage for long-term data.
• Usage: Ideal for primary business data needing frequent access and backups.
• Durability: Full durability with time-travel and fail-safe.
• Cost: Higher cost due to full data protection.
• Create Table: CREATE TABLE my_permanent_table (id INT, name STRING);

2. Temporary Table

• Description: Exists only for the duration of a session.
• Usage: Useful for short-term data processing or transformations.
• Durability: Dropped at session end, no time-travel.
• Cost: Lower cost as no long-term storage.
• Create Table: CREATE TEMPORARY TABLE my_temp_table (id INT, name STRING);

3. Transient Table

• Description: Semi-persistent with no fail-safe protection.
• Usage: Suitable for intermediate or staging data where recovery isn’t critical.
• Durability: Retains data for session, limited time-travel.
• Cost: Lower storage cost due to limited durability.
• Create Table: CREATE TRANSIENT TABLE my_transient_table (id INT, name STRING);

4. External Table

• Description: References external storage (e.g., S3, Azure Blob).
• Usage: Accesses large, static data sets without loading into Snowflake.
• Durability: No data storage in Snowflake.
• Cost: Minimal storage cost; external data access fees.
• Create Table: CREATE EXTERNAL TABLE my_external_table (id INT, name STRING) LOCATION=@my_s3_stage FILE_FORMAT=(TYPE=PARQUET);

5. Materialized View

• Description: Stores results of a query with automatic updates.
• Usage: Optimizes performance for frequent, complex queries, often used in analytics.
• Durability: Updated with source data changes.
• Cost: Potentially higher storage cost for fast access.
• Create View: CREATE MATERIALIZED VIEW my_materialized_view AS SELECT * FROM my_table;

6. Iceberg Table

• Description: Manages data as snapshots using a high-performance table format designed for big data (e.g., Parquet).
• Usage: Ideal for large-scale analytics on data lakes and hybrid data warehousing setups.
• Durability: Versioned snapshots for time-travel.
• Cost: Efficient for large, unstructured datasets.
• Create Table: CREATE TABLE my_iceberg_table USING iceberg LOCATION 's3://path/to/data' PARTITIONED BY (date);