A temporary table exists only for the session that created it and disappears when the session ends. A transient table stays until you drop it, like a normal table, but it gives up some data protection in return for lower storage cost.
The differences
CREATE TEMPORARY TABLE tmp_orders AS SELECT ...; -- gone when the session ends CREATE TRANSIENT TABLE stg_orders (...); -- stays, less protection CREATE TABLE core_orders (...); -- permanent (default)
How they compare:
- Permanent: full Time Travel (1 day by default, up to 90 on Enterprise) plus 7 days Fail-safe.
- Transient: Time Travel of 0 or 1 day, and no Fail-safe.
- Temporary: same as transient (0 or 1 day, no Fail-safe), and only visible in the session.
Without Fail-safe, a changed or deleted micro-partition is released sooner, so you pay for less storage. For tables that are rewritten every day, the saving can be real, since a permanent table keeps old versions for the whole retention plus 7 days.
What to use them for
- Staging tables where data is loaded, transformed and then replaced each run.
- Intermediate tables in a pipeline that can be rebuilt from source.
- Scratch work in a script. A temporary table also means no leftovers if the job fails.
Keep permanent tables for the data that you cannot easily recreate, such as the final core and mart layers or anything loaded once from a source that does not keep history.
Where this shows up in tooling
dbt can create transient tables, and for dbt-snowflake it does so by default for tables it builds, which is why people sometimes find they have no Fail-safe on models. You can change that with the transient: false config for important models.
Warning
If a transient table is dropped by mistake after the day of Time Travel, nothing can bring it back, not even Snowflake support. Decide per table whether it can be rebuilt, and say that in your answer.