Table Types, Constraints & Data Handling
Snowflake · Tables & Data Handling
Theory
Snowflake has three main table types that differ in lifetime and data protection. Permanent tables, the default, keep data until dropped and get Time Travel (1 day by default, up to 90 days on Enterprise and above) plus a 7-day Fail-safe. Transient tables also persist until dropped but have no Fail-safe and at most 1 day of Time Travel, which lowers storage cost. Temporary tables exist only in the session that created them, are invisible to other sessions and are dropped when the session ends.
For constraints, Snowflake accepts PRIMARY KEY, UNIQUE and FOREIGN KEY definitions but only enforces NOT NULL on standard tables; the others are metadata that tools and the optimizer can use.
Why we use it
Not all data needs the same protection. Raw staging data that can be reloaded does not need 7 extra days of Fail-safe storage, and scratch results do not need to outlive the session, so choosing the right type saves storage cost and clutter.
When to use it
Use permanent tables for business data you cannot recreate. Use transient tables for staging, ETL intermediate and easily reloaded data. Use temporary tables for session-only scratch work. Do not rely on PRIMARY KEY or UNIQUE to reject duplicates.
Permanent Tables
The default table type — full protection with Time Travel plus a 7-day Fail-safe recovery window on top, at the highest storage cost of the three types.
Transient Tables
Skip the extra Fail-safe recovery window, lowering storage cost; best for staging or intermediate ETL data you can comfortably afford to lose or reload.
Temporary Tables
Exist only for the current session and disappear automatically when it ends — ideal for scratch work during a single script or ad hoc analysis, with no lingering storage cost.
How it works
- Need a new table
- Must the data be recoverable after major mistakes? Yes: Create a permanent table. No: Consider transient or temporary.
- Is it needed only in this session? Yes: Create a TEMPORARY table. No: Create a TRANSIENT table.
- Add NOT NULL where values are required
- Handle duplicates in your load logic, not with UNIQUE
Example
CREATE OR REPLACE TABLE customers (
customer_id INT AUTOINCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
email VARCHAR(200) UNIQUE NOT NULL,
city VARCHAR(100),
signup_date DATE DEFAULT CURRENT_DATE(),
is_active BOOLEAN DEFAULT TRUE
);
CREATE OR REPLACE TRANSIENT TABLE staging_customers (
raw_data VARIANT, file_name VARCHAR(500), loaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);
CREATE OR REPLACE TEMPORARY TABLE temp_analysis (
metric VARCHAR(100), value FLOAT, calc_date DATE DEFAULT CURRENT_DATE()
);Key points
- Permanent tables get full Time Travel and 7-day Fail-safe
- Transient tables skip Fail-safe to reduce storage cost
- Temporary tables exist only for the session and cannot be seen by others
- Only NOT NULL is enforced; PK, UNIQUE and FK are informational
- Tables created in a transient schema or database are transient