What are Tables in Snowflake Really?
Tables in Snowflake are not just “storage.”
They are strategies for how you manage your data based on cost, recoverability, query patterns, and lifecycle.
And in Snowflake, you have many kinds:
Table Type | Special Purpose
Permanent Table | Long-term production data
Temporary Table | Scratch, session-only data
Transient Table | Semi-temporary data (no Fail-Safe)
External Table | Querying external cloud files without loading
Dynamic Table | Auto-refresh materialized views
2.1 Permanent Table
Full durability.
Fail-Safe included (7 days backup after drop — you pay storage).
Time Travel enabled (by default 1 day, up to 90 days if paid).
Ideal for important business data.
When to use:
Customer databases, financial transactions, regulatory data.
Cost impact:
Higher, because Snowflake stores extra metadata + Fail-safe copies.
2.2 Transient Table
Semi-durable, but cheaper.
NO Fail-Safe.
Time Travel supported, but no disaster backup.
When to use:
ETL staging tables
Pre-aggregated reporting tables you can recreate
Cost impact:
Lower than Permanent tables.
Hidden Pro Tip:
Use TRANSIENT for huge intermediate tables that are costly to recreate if lost, but you don’t want Fail-safe charges.
2.3 Temporary Table
Exists only during the session (or until the session expires).
No metadata saving after session closes.
Super fast and lightweight.
When to use:
Scratch calculations inside stored procedures.
Temporary joins or filter results.
Hidden Pro Tip:
In massive complex ETLs, use TEMPORARY tables inside a transaction so they auto-disappear and keep warehouse clean.
2.4 External Table
You don’t load the data into Snowflake.
You query files directly sitting in S3, Azure Blob, GCS.
Real scenario:
You have millions of Parquet/JSON/CSV files sitting on S3.
You don’t want to pay storage cost again in Snowflake.
You create an External Table instead.
