Snowflake Table Types – Permanent, Transient, Temporary & External
Snowflake has four table types with nearly identical DDL but very different storage and recovery behaviour: permanent, transient, temporary, and external. The transient drop scenario is the trap that catches most COF-C03 candidates on the exam.
| Term | Plain meaning |
|---|---|
| Time Travel | The window during which you can query, clone, or UNDROP historical data. 1 to 90 days depending on edition and table type. |
| Fail-safe | A fixed 7-day disaster-recovery copy that Snowflake holds after Time Travel ends. Only Snowflake Support can recover from it. You pay storage for it whether you use it or not. |
| Permanent table | The default. Full Continuous Data Protection. Time Travel plus Fail-safe. Lives until you drop it. |
| Transient table | Same scope as permanent, but no Fail-safe. Cheaper to store. Time Travel capped at 1 day. |
| Temporary table | Lives inside one session. Vanishes at session end. Invisible to every other session. No Fail-safe. |
| External table | Files stay in S3, Azure Blob, or GCS. Snowflake only holds the metadata. Read-only by default. |
| Session | One authenticated connection. Ends on logout, driver disconnect, or 4 hours of idle by default for programmatic clients. |
Today’s Concept
Micro-Concept 1: The Master Comparison Table
The matrix below answers most table-type questions on the exam. Read it row by row, and pay attention to the asymmetries between columns.
| Property | Permanent | Transient | Temporary | External |
|---|---|---|---|---|
| Default type? | Yes (default) | No (explicit) | No (explicit) | No (explicit) |
| Survives session end? | Yes | Yes | No (vanishes) | Yes |
| Visible to other sessions? | Yes | Yes | No | Yes |
| Time Travel | 0–1 day Standard; 0–90 days Enterprise+ | 0–1 day | 0–1 day (bounded by session) | No Time Travel |
| Fail-safe | 7 days (always) | No Fail-safe | No Fail-safe | No Fail-safe |
| Storage cost | Highest (active + TT + Fail-safe) | Medium (active + TT only) | Lowest (only while session lives) | None inside Snowflake (you pay cloud-storage bills) |
| Writable? | Yes | Yes | Yes | Read-only by default (Iceberg can write, see Day 12) |
| DDL keyword | CREATE TABLE | CREATE TRANSIENT TABLE | CREATE TEMPORARY TABLE | CREATE EXTERNAL TABLE |
Micro-Concept 2: Permanent, the Default
A plain CREATE TABLE with no qualifier produces a permanent table. You get the full Continuous Data Protection lifecycle: active storage flows into Time Travel when data changes, then into Fail-safe after Time Travel ends. You pay for all three tiers. Use permanent for anything where data loss would actually hurt the business (source-of-truth tables, curated layers, and anything downstream consumers rely on).
Retention nuances that the exam pokes at:
- Standard edition is capped at 1 day of Time Travel.
- Enterprise edition and above lets you set
DATA_RETENTION_TIME_IN_DAYSanywhere from 0 to 90. - Fail-safe is fixed at 7 days for permanent tables. There is no parameter to change it, shorten it, or skip it.
Micro-Concept 3: Transient, Cheaper Storage with No Fail-safe Layer
Transient tables behave like permanent tables in every way that matters for SQL access. They survive sessions. Any role with the right grants can read them. The difference sits one layer below: no Fail-safe.
Once the Time Travel window (1 day maximum) closes, the data is purged the instant the retention clock ticks past zero. There is no second layer. No support ticket gets it back.
Where transient earns its place:
- Staging and landing zones rebuilt from source on every load.
- Work tables and scratch space inside long-running pipelines.
- Large intermediate ETL tables where 7 days of Fail-safe on multi-TB data would be pure waste.
On a 5 TB nightly staging table, skipping 7 days of Fail-safe is a real number on the monthly storage line. On a recent client pipeline, every landing-layer table was transient for exactly this reason. The source of truth was the upstream system, and reloading was cheap.
One trap that costs people marks: a table’s type is fixed at creation. There is no ALTER TABLE ... SET TRANSIENT. To “convert” you clone or CTAS into a new table of the desired type and swap names. The lab walks through this.
Micro-Concept 4: Temporary, Session-Scoped
A temporary table lives only as long as the session that created it. The properties that show up on the exam:
- Auto-dropped when the session ends. Explicit
DROPworks too but is not required. - Invisible to every other session. Two users can each create
my_tempin the same schema at the same time with different columns. There is no collision, because neither sees the other’s table. - Can shadow a permanent table of the same name. Inside the session that created the temp, the temp wins for that name. Outside that session, the permanent table is still visible and unchanged. When the session ends, the permanent re-emerges. This is a favourite exam trap.
- Time Travel up to 1 day, but always bounded by the session itself. Whichever ends first wins.
Micro-Concept 5: External Tables, Metadata in Snowflake, Data Outside
An external table points Snowflake at files sitting in S3, Azure Blob, or GCS via a stage. The schema definition and partition metadata live in Snowflake. The actual data files never do.
- Read-only by default. Standard external tables reject DML. Iceberg tables, covered tomorrow, allow writes.
- No Time Travel. No Fail-safe. There is no Snowflake-managed history of data that Snowflake does not store.
- Used heavily in data lake patterns where the same files need to be queryable by Snowflake and by Spark, Athena, or other engines.
- Performance leans on a partition column defined against the file layout on object storage. Without it, every query scans all files.
Micro-Concept 6: The Drop + UNDROP Lifecycle by Type
This is the verbatim Q37 scenario from the SnowPro practice review, and it catches a lot of candidates. What happens at DROP, and where the recovery window ends, is different for every type:
| Type | Recoverable within Time Travel | Recoverable via Fail-safe | End state after retention |
|---|---|---|---|
| Permanent | Yes. UNDROP within Time Travel window (up to 90d Enterprise+) | Yes. Snowflake Support, 7 days | Up to 97 days protected, then gone |
| Transient | Yes. UNDROP within max 1 day | No | Gone after 1 day max. Support cannot recover. |
| Temporary | Within the session, up to retention setting | No | Gone at session end. Always. |
| External | UNDROP recreates the metadata if within retention; the data in S3/Azure/GCS was never inside Snowflake anyway | No | Metadata gone after retention; cloud data is untouched (you control that) |
Micro-Concept 7: Naming Conflicts
Naming rules across types are exam-favourite trivia, and the asymmetry catches people:
- A permanent and a transient table cannot share a name in the same schema. The second CREATE errors out.
- A temporary table CAN share a name with a permanent or transient table in the same schema. Inside the creating session the temp wins and the underlying table is hidden (not dropped). When the session ends, the original becomes visible again.
Cheat Sheet
| Concept | What to remember |
|---|---|
| Default type | Permanent. |
| Fail-safe | Permanent only. 7 days. Snowflake Support recovers. Transient, temporary, external = none. |
| Time Travel max | Permanent: 1 day Standard, 90 days Enterprise+. Transient + Temporary: 1 day max. External: no Time Travel. |
| Survives session | Permanent, transient, external = yes. Temporary = no. |
| Cross-session visibility | Temporary = no (invisible to other sessions). All others = yes. |
| Transient dropped | UNDROP within 1 day; after that, gone forever (no Fail-safe). |
| Temporary dropped or session ends | Gone forever. Not recoverable. |
| Type conversion | Not allowed. Use clone or CTAS, then swap names. |
| Name shadowing | Temp can shadow a permanent table within its session. Original re-emerges when session ends. |
| External writes | Read-only by default. (Iceberg = exception, Day 12.) |
| Storage cost ranking | Permanent > Transient > Temporary > External (Snowflake-internal) |
Three traps appear on this topic across most mock tests. (1) “Transient tables have a 7-day Fail-safe period”: FALSE. Zero days. That is the defining property of the transient type. (2) “Snowflake Support can recover a dropped transient table after Time Travel expires”: FALSE. No Fail-safe means Support has nothing to recover from. Once the Time Travel window closes (1 day maximum) the data is purged immediately. If “ask Snowflake Support” appears as an option on a transient-table question, it is the distractor. (3) “Temporary tables become visible across sessions when you grant SELECT”: FALSE. Session scope is absolute. No GRANT, no role escalation, no ACCOUNTADMIN trick changes it. When a question describes another session looking at a temp table, the answer is “object does not exist” every single time.
Hands-On Lab
lab_xs + day10_orders from Day 10. | Lab needs TWO worksheets for step 4.Create one table of each type and inspect them. The metadata in SHOW TABLES is where the three types declare themselves.
USE ROLE SYSADMIN;
USE WAREHOUSE lab_xs;
ALTER WAREHOUSE lab_xs RESUME IF SUSPENDED;
-- Permanent (default)
CREATE OR REPLACE TABLE perm_t (id INT, val STRING);
-- Transient
CREATE OR REPLACE TRANSIENT TABLE trans_t (id INT, val STRING);
-- Temporary (session-scoped)
CREATE OR REPLACE TEMPORARY TABLE temp_t (id INT, val STRING);
SHOW TABLES LIKE '%_t';
-- Inspect: kind column, retention_time column, is_temporary, is_transientkind column reads TABLE, TRANSIENT, or TEMPORARY. Retention defaults to 1 day on all three. The is_transient and is_temporary flags are mutually exclusive. A row never has both set true.Verify storage-tier differences via TABLE_STORAGE_METRICS. Load some rows first so there is something to measure.
INSERT INTO perm_t SELECT seq4(), 'val_' || seq4() FROM TABLE(GENERATOR(ROWCOUNT => 100000));
INSERT INTO trans_t SELECT seq4(), 'val_' || seq4() FROM TABLE(GENERATOR(ROWCOUNT => 100000));
INSERT INTO temp_t SELECT seq4(), 'val_' || seq4() FROM TABLE(GENERATOR(ROWCOUNT => 100000));
-- TABLE_STORAGE_METRICS shows ACTIVE / TIME_TRAVEL / FAILSAFE bytes per table
SELECT
TABLE_NAME, IS_TRANSIENT,
ACTIVE_BYTES, TIME_TRAVEL_BYTES, FAILSAFE_BYTES,
RETENTION_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
WHERE TABLE_NAME IN ('PERM_T','TRANS_T','TEMP_T')
ORDER BY TABLE_NAME;
-- (May lag a few minutes due to ACCOUNT_USAGE latency.)PERM_T can show non-zero FAILSAFE_BYTES. TRANS_T and TEMP_T read zero in that column today, and they will still read zero a year from now. That column is the storage-cost difference between the types, made visible.Confirm temp_t is visible inside its own session. Stay in this worksheet; the SELECT will work.
SELECT COUNT(*) FROM temp_t; -- works (same session)From the second worksheet, try to read temp_t. Seeing the error message makes session scope concrete in a way that reading about it does not.
-- In a NEW worksheet (new session):
SHOW TABLES LIKE 'temp_t'; -- empty result
SELECT * FROM temp_t; -- ERROR: object does not exist
-- But trans_t and perm_t are visible:
SELECT COUNT(*) FROM trans_t; -- works
SELECT COUNT(*) FROM perm_t; -- worksperm_t and trans_t fine but cannot even list temp_t. No grant fixes this. There is nothing to grant on. The temp table does not exist for any session other than the one that created it. This property is what makes temp tables safe for per-user scratch state inside stored procedures and notebooks.Test the transient drop and UNDROP window, the Q37 scenario in code. Back in worksheet 1.
DROP TABLE trans_t;
UNDROP TABLE trans_t; -- works within Time Travel window (≤ 1 day for transient)
SELECT COUNT(*) FROM trans_t;
-- 100,000 rows restored
-- After 1 day max, that UNDROP would fail with a not-found error.
-- And Snowflake Support cannot recover it — no Fail-safe.Prove the “type is fixed at creation” rule using clone and swap. The only way to “change” a table’s type is to create a new table of the desired type with the same data, then rename.
-- Direct ALTER to change type? Not a thing — there is no ALTER TABLE ... SET TRANSIENT.
-- Pattern: CLONE into the new type, then ALTER ... SWAP WITH to rename atomically.
CREATE TRANSIENT TABLE perm_t_new CLONE perm_t; -- new table, new type, zero-copy data
ALTER TABLE perm_t SWAP WITH perm_t_new; -- atomic name swap
DROP TABLE perm_t_new; -- old permanent table, now under the _new name
-- Verify the kind flipped:
SHOW TABLES LIKE 'perm_t';
-- kind column now reads TRANSIENT
CLONE plus SWAP WITH is the standard “convert” pattern. The clone is zero-copy: no data is moved, only metadata is created. The swap is metadata-only and atomic. A downstream query running against perm_t at the swap moment sees either the old version or the new version, with no gap in between. One thing to call out: the Fail-safe history of the original permanent table does not survive the conversion. The new transient has no Fail-safe by definition. Anything in the old permanent’s Fail-safe layer is lost once the dropped permanent ages out.Cleanup. Keep day10_orders. The lab dependency map needs it through Day 39.
DROP TABLE IF EXISTS perm_t;
DROP TABLE IF EXISTS perm_t_new; -- swapped-out original from step 5b, if you ran it
DROP TABLE IF EXISTS trans_t;
-- temp_t auto-drops on session end (or DROP if you want to be explicit)
DROP TABLE IF EXISTS temp_t;
ALTER WAREHOUSE lab_xs SUSPEND;
-- KEEP day10_orders — needed for Days 13, 26, 31, 32, 35, 39Snowflake Documentation
Official Snowflake docs that back up today’s content.
External References
Lifecycle and storage-metric details worth reading once.
Practice Questions
Options:
A. The table is no longer available for use.
B. The table can be undropped using Fail-safe.
C. The table can be recovered for 1 day only and after that it is no longer available.
D. The table can be recovered only with the assistance of Snowflake Support.
Why C: Transient tables get a maximum 1-day Time Travel window and no Fail-safe layer behind it. UNDROP works as long as the drop happened inside that window. After 1 day the data is purged and no further recovery path exists.
Why not A: Too absolute. The table is recoverable for up to a day after the drop. That is the whole point of the question.
Why not B: Fail-safe does not exist for transient tables. This is the single most-tested distinction in the table-types syllabus.
Why not D: Without Fail-safe there is nothing for Snowflake Support to recover from. Any answer option that routes a transient-table recovery through Support is the distractor.
Options:
A. The query returns the row count from the original creator’s temp table
B. The query errors because temporary tables are visible only to the session that created them
C. The query waits until the original session ends, then returns the row count
D. The query returns 0 rows but the table is visible
Why B: Session scope on temporary tables is absolute. No grant, no role escalation, no USE SECONDARY ROLES ALL trick makes a temp table visible in another session. The second session’s query errors with object-does-not-exist.
Why not A: There is no cross-session visibility at all. Even if the second user had ACCOUNTADMIN, the result would not change.
Why not C: Snowflake does not queue queries waiting for someone else’s session to end. The distractor sounds plausible but does not match how the platform works.
Why not D: The object is not visible. There is no row of metadata to return zero rows from. The query fails before execution.
Options:
A. Permanent only
B. Permanent and transient
C. Permanent, transient, and temporary
D. All four types including external
Why A: Fail-safe is exclusive to permanent tables, fixed at 7 days, and not configurable. Every other type is explicitly designed without it.
Why not B: Transient tables skipping Fail-safe is the defining feature of the type. That is exactly how they reduce storage cost.
Why not C: Temporary tables also have no Fail-safe. They cannot, because they do not survive the session that created them.
Why not D: External tables hold zero data inside Snowflake. There is no internal storage layer for Fail-safe to apply to.
Options:
A. Only the metadata lives in Snowflake; the actual data files remain in S3, Azure Blob, or GCS
B. Standard external tables are read-only by default
C. External tables support up to 90 days of Time Travel on Enterprise Edition
D. External tables are stored inside Snowflake’s micro-partition system
E. External tables have a 7-day Fail-safe period
Why A: The defining property of an external table is that the schema and partition metadata sit in Snowflake. The files stay in cloud object storage. That is what makes the data-lake pattern work.
Why B: Standard external tables reject DML. Iceberg tables, a different feature covered on Day 12, change this. The question asks about external tables in general.
Why not C: External tables have no Time Travel. The data is not in Snowflake, so Snowflake cannot version it.
Why not D: External tables do not live in Snowflake’s storage layer. That is precisely the architectural choice the type exists to enable.
Why not E: No Fail-safe, for the same reason as no Time Travel. Snowflake protects what Snowflake stores.
Options:
A. Permanent: strongest protection
B. Transient: same scope as permanent but no Fail-safe storage cost
C. Temporary: auto-cleanup is free
D. External: no Snowflake storage at all
Why B: The defining clue is “rebuilt every night from the upstream system.” The data is reproducible, so paying for Fail-safe to protect it is wasted spend. Transient gives the same multi-session SQL access as permanent while skipping the Fail-safe storage line entirely.
Why not A: Permanent on 5 TB pays for 7 days of Fail-safe storage on a table that is overwritten every 24 hours. The Fail-safe layer protects data that is already reproducible from source. That is pure waste.
Why not C: Staging tables in real pipelines need to be readable by orchestration tools, monitoring dashboards, and downstream loads. All of those run in different sessions. Temporary vanishes at the creator’s session end, which breaks every one of those consumers.
Why not D: External tables require the source files to already be sitting in cloud object storage and are read-only. The scenario here is data landing into Snowflake, not querying files in place.
Today you learned: Four table types, separated by scope and protection. Permanent is the default, with full Time Travel (up to 90 days on Enterprise+) and a fixed 7-day Fail-safe period. Transient has the same multi-session scope as permanent but caps Time Travel at 1 day and has no Fail-safe. Temporary is session-scoped, vanishes when the session ends, and is invisible to every other session. External holds nothing in Snowflake storage. The data sits in S3, Azure, or GCS, and the type is read-only by default with no Time Travel and no Fail-safe. Type is fixed at creation; clone or CTAS plus a name swap is the only way to “convert.”
Key takeaway: Match the table type to the recovery tolerance of the data. Reproducible data is transient. Per-session scratch is temporary. Source-of-truth and curated layers are permanent. Lake-style files queried in place are external.
Tomorrow (Day 12): Apache Iceberg & Dynamic Tables, two newer C03 table types. Iceberg is the open table format Snowflake reads and writes via a catalog, extending external-table semantics with full DML. Dynamic Tables are declarative pipelines that refresh on a TARGET_LAG instead of streams and tasks wired up by hand.
