Skip to main content

2.3 Tables & Dynamic Tables

Tables are the fundamental objects that physically hold your data in micro-partitions. Snowflake supports several variations of tables optimized for different use cases.

Permanent, Transient, and Temporary Tables​

  1. Permanent Tables (Default)

    • The standard table type.
    • Offers up to 90 days of Time Travel and 7 days of Fail-safe.
    • Ideal for production data.
  2. Transient Tables

    • Created with CREATE TRANSIENT TABLE.
    • Offers only 1 day of Time Travel and zero Fail-safe.
    • Ideal for staging data or intermediate ETL tables where storage costs should be minimized, and the data can be easily regenerated if lost.
  3. Temporary Tables

    • Created with CREATE TEMPORARY TABLE.
    • Only exists for the duration of the user's session. Once the session ends, the table is automatically dropped!
    • Highly useful for complex scripting and intermediate analytical calculations.

External Tables​

External Tables allow you to query data that lives outside of Snowflake's internal storage (e.g., querying Parquet files sitting directly in an AWS S3 bucket).

  • The data is not loaded into Snowflake.
  • Snowflake simply stores the file-level metadata and acts as a query engine.
  • External tables are read-only!

Table Swapping & Cloning​

Similar to databases, tables can be instantly cloned. Additionally, Snowflake allows you to instantly swap the contents of two tables, which is a powerful technique for blue/green deployment pipelines!

-- Swap the production table with the newly loaded staging table
ALTER TABLE PROD_TABLE SWAP WITH STAGING_TABLE;

Sequences and Default Values​

You can generate auto-incrementing surrogate keys for your tables using Sequences.

-- Create a sequence
CREATE SEQUENCE customer_seq START = 1 INCREMENT = 1;

-- Use it as a default value
CREATE TABLE CUSTOMERS (
customer_id NUMBER DEFAULT customer_seq.nextval,
name VARCHAR
);