Snowflake permanent vs transient vs temporary tables: choose by lifecycle and recovery.

Start with lifespan, sharing and recovery

Permanent is Snowflake's default table type. Transient and temporary tables reduce historical-data protection, but they do not make active table storage free. The recovery objective, reproducibility and required audience should drive the choice.

Permanent vs transient vs temporary tables

Retention is not only a table property. Account, database and schema settings can influence the effective value, and an account minimum-retention setting can raise it. Check the object's actual configuration instead of relying on a memorized default.

Permanent tables: default persistence and strongest recovery

A normal CREATE TABLE creates a permanent table unless another type is specified. It persists until explicitly dropped and can be used by authorized sessions. Permanent tables participate in Time Travel according to the effective retention configuration and have a non-configurable seven-day Fail-safe period after Time Travel data ages out.

CREATE TABLE analytics.orders (order_id NUMBER, order_ts TIMESTAMP_TZ, amount NUMBER(12,2)) DATA_RETENTION_TIME_IN_DAYS = 7;

Extended Time Travel beyond one day is available for eligible permanent objects on Enterprise Edition and higher, up to the documented limit. It increases historical storage because changed or deleted micro-partitions remain available longer. Use longer retention where recovery value justifies the cost.

Transient tables: persistent access without Fail-safe

A transient table remains available across sessions until explicitly dropped, like a permanent table. The essential difference is recovery: transient tables have at most one day of Time Travel and no Fail-safe. Once retention expires, neither the user nor Snowflake can recover the lost historical data.

CREATE TRANSIENT TABLE pipeline.orders_stage (payload VARIANT, loaded_at TIMESTAMP_TZ) DATA_RETENTION_TIME_IN_DAYS = 1;

Transient databases and schemas extend the same behavior: tables created within them are transient by definition. This can simplify staging design, but it also increases the impact of a mistaken drop if the source cannot recreate the data within the available Time Travel window.

Storage cost nuance

Transient tables still incur active storage and Time Travel storage charges. They avoid Fail-safe storage because they have no Fail-safe period. That saving is appropriate only when the recovery loss is acceptable. Cost optimization without a tested rebuild path is an availability risk.

Temporary tables: session-local data with hidden operational traps

A temporary table normally exists for the remainder of the session that creates it and is not visible to other users or sessions. When that session ends, Snowflake purges the table and its data is unrecoverable. Temporary tables can use 0 or 1 day of Time Travel, but the real retention cannot extend past session termination.

CREATE TEMPORARY TABLE order_totals AS SELECT customer_id, SUM(amount) AS total_amount FROM analytics.orders GROUP BY customer_id;

Temporary data still contributes to storage billing while it exists. Long-lived sessions containing large temporary tables can therefore create unexpected cost. Snowflake also warns that high volumes of temporary tables can degrade queries against Information Schema TABLES and COLUMNS views. Explicitly drop them and end inactive sessions.

The same-name shadowing problem

Snowflake permits a temporary table and a permanent or transient table to have the same name in the same schema. Within the temporary table's session, the temporary object takes precedence. A query that looks fully qualified can still operate on the temporary table, and DDL such as DROP, CREATE OR REPLACE or UNDROP can produce surprising results.

Worked scenario: choose table types for a nightly revenue pipeline

Situation: A pipeline ingests raw files, normalizes them, builds a revenue fact table and produces one-session quality-check aggregates.

The design should also define retention, ownership, rebuild procedures and cleanup. Table type is one part of lifecycle governance; it does not automatically schedule deletion, validate source availability or prove that a rebuild meets the recovery objective.

Real-world issue: the production table appears empty in one session

An analyst reports that PROD.MART.CUSTOMERS contains 12 rows, while other users see 18 million. Grants, warehouse and database are correct.

1. Check the current session. Run SHOW TABLES and inspect whether a temporary table named CUSTOMERS exists.
2. Check table kind and creation time. A notebook or stored workflow may have created a same-named temporary object earlier.
3. Test from a fresh session. If the expected permanent table appears, name shadowing—not lost production data—is the cause.
4. Drop or rename the temporary table. Use unambiguous scratch names and schemas to avoid recurrence.
5. Review DDL carefully. Ensure no mistaken DROP or UNDROP targeted the shadowing object.

This is an illustrative learning scenario, not a customer incident.

Common SnowPro Core mistakes

• Assuming transient means session-scoped.
• Assuming temporary tables do not consume storage.
• Giving transient tables a seven-day Fail-safe period.
• Expecting a temporary table to survive the session that created it.
• Ignoring same-name shadowing between temporary and permanent tables.
• Choosing transient for irreplaceable data only to reduce cost.
• Assuming table types can be converted freely after creation.
• Applying a 90-day retention answer to Standard Edition or to transient data.
• Forgetting that tables in a transient schema or database are transient.

Frequently asked questions

Official Snowflake references

Verify current behavior in Snowflake's documentation for temporary and transient tables, Time Travel, Fail-safe and CREATE TABLE. Confirm certification scope through the official SnowPro Core page. ITCertPath does not reproduce confidential exam questions.