Snowflake Table Types

Snowflake provides various table types to meet different data storage and processing requirements. Each table type has specific characteristics regarding persistence, performance, cost, and Time Travel capabilities.

Overview of Table Types

┌───────────────────────────────────────────────────────────────────────┐
│                         SNOWFLAKE TABLE TYPES                         │
├───────────────────────────────────────────────────────────────────────┤
│                                                                       │
│  ┌──────────────────┐  ┌───────────────────┐  ┌───────────────────┐   │
│  │    PERMANENT     │  │    TEMPORARY      │  │    TRANSIENT      │   │
│  │     TABLES       │  │     TABLES        │  │     TABLES        │   │
│  │                  │  │                   │  │                   │   │
│  │   ✓ Fail-safe    │  │   ✗ Fail-safe     │  │   ✗ Fail-safe     │   │
│  │   ✓ Time Travel  │  │   ✓ Time Travel   │  │   ✓ Time Travel   │   │
│  │     (90 days)    │  │      (1 day)      │  │     (1 day)       │   │
│  │                  │  |  Current Session  │  │  Between Sessions │   │
│  └──────────────────┘  └───────────────────┘  └───────────────────┘   │
│                                                                       │
│  ┌──────────────────┐  ┌───────────────────┐  ┌───────────────────┐   │
│  │     EXTERNAL     │  │     DYNAMIC       │  │      HYBRID       │   │
│  │     TABLES       │  │     TABLES        │  │      TABLES       │   │
│  │                  │  │                   │  │                   │   │
│  │   Data Outside   │  │   Auto-refresh    │  │    OLTP + OLAP    │   │
│  │    Snowflake     │  │   Materialized    │  │    Unistore       │   │
│  └──────────────────┘  └───────────────────┘  └───────────────────┘   │
│                                                                       │
│  ┌──────────────────┐  ┌───────────────────┐  ┌───────────────────┐   │
│  │     ICEBERG      │  │      EVENT        │  │    DIRECTORY      │   │
│  │     TABLES       │  │      TABLES       │  │     TABLES        │   │
│  │                  │  │                   │  │                   │   │
│  │   Apache Format  │  │   Log/Telemetry   │  │   Stage Files     │   │
│  │    Open Table    │  │     Storage       │  │     Catalog       │   │
│  └──────────────────┘  └───────────────────┘  └───────────────────┘   │
└───────────────────────────────────────────────────────────────────────┘

1. Permanent Tables

Permanent tables are the default table type in Snowflake, designed for storing critical business data that requires maximum data protection.

Characteristics:

• Time Travel: Up to 90 days (Enterprise Edition and higher)
• Fail-safe: Additional 7 days of data recovery after Time Travel expires
• Data Protection: Highest level of data protection and recovery options
• Storage Costs: Higher due to Time Travel and Fail-safe storage
• Persistence: Data persists until explicitly dropped

Use Cases:

• Production data warehouses
• Critical business data
• Regulatory compliance data
• Financial records

Creation Syntax:

CREATE TABLE customer_data (
    customer_id INTEGER,
    customer_name VARCHAR,
    created_date DATE
);

2. Temporary Tables

Temporary tables exist only within a session and are automatically dropped when the session ends.

Characteristics:

• Scope: Session-specific (visible only within the session)
• Time Travel: Limited to 1 day (24 hours)
• Fail-safe: Not available
• Auto-cleanup: Automatically dropped at session end
• Storage Costs: Lower due to no Fail-safe period
• Naming: Can have the same name as permanent tables

Use Cases:

• ETL intermediate staging
• Session-specific calculations
• Temporary data transformations
• Development and testing

Creation Syntax:

CREATE TEMPORARY TABLE temp_staging (
    id INTEGER,
    data VARCHAR
);

3. Transient Tables

Transient tables are a middle ground between permanent and temporary tables, offering persistence without Fail-safe protection.

Characteristics:

• Time Travel: Limited to 1 day (0 or 1 day configurable)
• Fail-safe: Not available
• Persistence: Persists until explicitly dropped
• Storage Costs: Lower than permanent tables
• Cloning: Can be cloned like permanent tables

Use Cases:

• ETL staging tables that need to persist across sessions
• Non-critical derived data
• Development and testing environments
• Temporary reporting tables

Creation Syntax:

CREATE TRANSIENT TABLE staging_data (
    record_id INTEGER,
    processed_flag BOOLEAN
);

4. External Tables

External tables allow querying data stored outside of Snowflake, in external cloud storage locations.

Characteristics:

• Data Location: Data remains in external storage (S3, Azure Blob, GCS)
• Read-only: Cannot perform DML operations (INSERT, UPDATE, DELETE)
• Metadata: Only metadata is stored in Snowflake
• Time Travel: Not supported
• Performance: Slower than native tables
• File Formats: Supports various formats (Parquet, ORC, Avro, JSON, CSV)

Use Cases:

• Data lake integration
• Querying archived data
• Cross-platform data sharing
• Cost-effective storage for infrequently accessed data

Creation Syntax:

CREATE EXTERNAL TABLE ext_customer_data
WITH LOCATION = @my_external_stage/data/
FILE_FORMAT = (TYPE = PARQUET);

5. Dynamic Tables

Dynamic tables are continuously updated materialized views that automatically refresh as underlying data changes.

Characteristics:

• Automated Refresh: Automatically updates based on defined lag
• Declarative: Define the desired state, Snowflake handles updates
• Incremental Processing: Only processes changed data
• Target Lag: Configurable freshness requirements
• Cost: Consumes compute resources for refresh operations

Use Cases:

• Real-time analytics
• Data pipeline automation
• Continuous data transformation
• Materialized aggregations

Creation Syntax:

CREATE DYNAMIC TABLE sales_summary
TARGET_LAG = '1 minute'
WAREHOUSE = compute_wh
AS
SELECT 
    product_id,
    SUM(quantity) as total_quantity,
    SUM(amount) as total_amount
FROM sales_transactions
GROUP BY product_id;

6. Hybrid Tables

Hybrid tables (Unistore) combine OLTP and OLAP capabilities, supporting both transactional and analytical workloads.

Characteristics:

• Row-based Storage: Optimized for transactional operations
• ACID Compliance: Full transactional support
• Primary Keys: Enforced unique constraints
• Indexes: Support for secondary indexes
• Lock Management: Row-level locking for concurrent transactions
• Performance: Fast single-row operations

Use Cases:

• Application databases
• Operational data stores
• Real-time transactional systems
• Mixed OLTP/OLAP workloads

Creation Syntax:

CREATE HYBRID TABLE orders (
    order_id NUMBER(38,0) NOT NULL PRIMARY KEY,
    customer_id NUMBER(38,0),
    order_date TIMESTAMP_NTZ,
    status VARCHAR(20),
    INDEX idx_customer (customer_id),
    INDEX idx_date (order_date)
);

7. Iceberg Tables

Iceberg tables use Apache Iceberg table format, providing an open table format for large analytic datasets.

Characteristics:

• Open Format: Apache Iceberg compatibility
• External Storage: Data stored in customer-managed storage
• Interoperability: Works with multiple compute engines
• Schema Evolution: Supports schema changes without rewriting data
• Partitioning: Hidden partitioning support
• Time Travel: Version history through Iceberg snapshots

Use Cases:

• Multi-engine analytics
• Data lake architectures
• Vendor-neutral storage
• Cross-platform data sharing

Creation Syntax:

CREATE ICEBERG TABLE customer_iceberg (
    customer_id INTEGER,
    customer_name STRING,
    registration_date DATE
)
CATALOG = 'SNOWFLAKE'
EXTERNAL_VOLUME = 'my_external_volume'
BASE_LOCATION = 'customer_data/';

8. Event Tables

Event tables are specialized tables for storing and analyzing log and telemetry data efficiently.

Characteristics:

• Time-series Optimized: Designed for time-based data
• High Ingestion Rate: Optimized for streaming data
• Automatic Clustering: Time-based partitioning
• Retention Policies: Automatic data expiration
• Schema Flexibility: Semi-structured data support

Use Cases:

• Application logs
• IoT telemetry data
• Security event monitoring
• Clickstream analytics

9. Directory Tables

Directory tables provide a catalog view of files in a stage, allowing SQL queries over file metadata.

Characteristics:

• File Metadata: Query file properties without loading data
• Automatic Refresh: Synchronized with stage contents
• No Data Storage: Only metadata is stored
• Read-only: Cannot modify through directory table

Use Cases:

• File inventory management
• Data discovery
• File processing workflows
• Audit and compliance tracking

Table Type Comparison Matrix

┌──────────────┬────────────┬─────────────┬───────────┬───────────┬───────────┐
│ Feature      │  Permanent │  Temporary  │ Transient │ External  │  Dynamic  │
├──────────────┼────────────┼─────────────┼───────────┼───────────┼───────────┤
│ Time Travel  │  0-90 days │   0-1 day   │  0-1 day  │    No     │    Yes    │
├──────────────┼────────────┼─────────────┼───────────┼───────────┼───────────┤
│ Fail-safe    │    7 days  │     No      │    No     │    No     │    No     │
├──────────────┼────────────┼─────────────┼───────────┼───────────┼───────────┤
│ Persistence  │   Until    │   Session   │   Until   │  External │   Until   │
│              │  dropped   │     only    │  dropped  │   files   │  dropped  │
├──────────────┼────────────┼─────────────┼───────────┼───────────┼───────────┤
│ DML Support  │    Yes     │     Yes     │    Yes    │    No     │    No*    │
├──────────────┼────────────┼─────────────┼───────────┼───────────┼───────────┤
│ Clone        │    Yes     │      No     │    Yes    │    No     │    Yes    │
├──────────────┼────────────┼─────────────┼───────────┼───────────┼───────────┤
│ Storage Cost │    High    │     Low     │  Medium   │   Low     │   Medium  │
├──────────────┼────────────┼─────────────┼───────────┼───────────┼───────────┤
│ Use Case     │ Production │     ETL     │  Staging  │ Data Lake │ Analytics │
└──────────────┴────────────┴─────────────┴───────────┴───────────┴───────────┘
* Dynamic tables are read-only from user perspective, auto-updated by system

Best Practices for Table Selection

1. Use Permanent Tables for:
   • Core business data requiring maximum protect

     ---

     © Kevin Thomas Luzbetak, MSc