Managing external, Iceberg, and hybrid tables alongside ordinary ones

Snowflake supports permanent, transient, and temporary tables, each with different Time Travel and Fail-safe retention that directly affect storage cost and recovery options. Understanding how to create, clone, swap, and load data into these table types—along with external tables for querying data in place—is essential for designing efficient data pipelines.

1 · Learn the must-know

• 1Permanent tables get up to 90 days of Time Travel (Enterprise+, 1 day default on Standard) plus a mandatory 7-day Fail-safe period after Time Travel expires, both adding storage cost.
• 2Transient tables support Time Travel of 0 or 1 day only and have no Fail-safe period, making them ideal for staging or intermediate ETL tables where cost matters more than long-term recovery.
• 3Temporary tables exist only for the life of the session that created them, are automatically dropped at session end, have no Fail-safe, and are not visible to other sessions or users.
• 4CREATE TABLE ... CLONE (zero-copy cloning) instantly duplicates a table's metadata and shares underlying micro-partitions until either the source or clone is modified (copy-on-write), and the clone inherits the table type unless explicitly overridden.
• 5ALTER TABLE ... SWAP WITH is a metadata-only operation that atomically exchanges the complete contents, structure, and privileges of two tables, commonly used to promote a newly loaded/transformed table into production with zero downtime.
• 6External tables store only metadata and reference files in an external stage (read-only, not physically stored in Snowflake), and their schema/partition metadata can be refreshed manually or automatically; use CTAS to materialize them into a native table when better performance is needed.

2 · Check your understanding