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
-
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.
-
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.
- Created with
-
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.
- Created with
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
);