TL;DR
A Snowflake temporary table only exists for the session that created it. No other session can see it, not even one opened by the same logged-in user. The moment that session ends, the table and everything in it is automatically dropped. While it exists, a temporary table still costs storage, even though people often assume “temporary” means free. A temporary table can share a name with a real permanent table, and Snowflake will quietly query the temporary one first for the rest of that session. Creating a temporary table is a DDL statement, so it automatically commits any open transaction, which can catch developers off guard when a rollback doesn’t undo what they expected. Temporary tables also cap Time Travel at zero or one day and carry no Fail-safe recovery period at all.
Key Takeaways A Snowflake temporary table exists only for the session that created it and is not visible to any other session, even from the same logged-in user. Connection pooling can silently break temp-table workflows, because a logical application request is not guaranteed to reuse the same underlying Snowflake session. A temporary table can share a name with a permanent table, and Snowflake resolves queries to the temporary object first for the lifetime of that session, even when the name is fully qualified. CREATE TEMPORARY TABLE is DDL, so it implicitly commits any currently open transaction before it runs, a common cause of a ROLLBACK not undoing the work a developer expected. Temporary tables still bill for storage while they exist, cap Time Travel at 0-1 day AND purge at session end (whichever comes first), and carry no Fail-safe recovery period. Snowflake’s May 2026 release added procedure-scoped temporary tables for Snowflake Scripting, cleaning up automatically at the end of a single procedure execution instead of the whole session. The Sentence Snowflake’s Own Docs Use to Describe a Temp Table Snowflake’s own documentation puts it in one blunt sentence: “A temporary table and all its contents are dropped at the end of the session.” Eleven words, and they carry the entire risk profile of the object.
Most teams read “temporary” and assume “cheap, forgiving, and safe to lean on.” Snowflake’s own engineers describe it differently: a temporary table is a session-scoped object that still bills for storage, still participates in transaction rules, and can quietly shadow a permanent table with the same name for as long as the session lives.
That gap between assumption and mechanics is where most production incidents involving temporary tables actually start. This guide covers what happens after CREATE TEMPORARY TABLE runs, not just the syntax that creates it.
What Makes a Table “Temporary” in Snowflake? Snowflake supports three core table persistence types: permanent, transient, and temporary. If you need the full comparison of storage cost, Time Travel days, and Fail-safe across all three, that decision framework already lives in Kanerika’s Snowflake CREATE TABLE guide . This piece assumes you already know a temporary table trades Fail-safe and long retention for a narrower, session-bound lifecycle.
What it does not assume you already know is what that lifecycle actually does to real applications, stored procedures, and connection-pooled services. That is the rest of this guide.
Watch on YouTube
Snowflake vs Redshift: Choosing the Right Data Warehouse Platform
Before the session-scope mechanics below, a quick platform-level grounding in how Snowflake’s architecture compares to a traditional cloud warehouse.
Snowflake Temporary Table Syntax The baseline statement takes the same arguments as a standard CREATE TABLE, with the TEMPORARY keyword added.
CREATE TEMPORARY TABLE tmp_orders (
order_id NUMBER,
customer_id NUMBER,
order_total NUMBER(12,2)
);TEMP works as shorthand for TEMPORARY in every form below.
CREATE TEMPORARY TABLE AS SELECT (CTAS) In production code, the CTAS form shows up far more often than a bare column-definition statement, because most temporary tables exist to hold the output of a query, not an empty structure waiting for inserts.
CREATE TEMPORARY TABLE tmp_high_value_orders AS
SELECT *
FROM orders
WHERE order_total > 10000;CREATE TEMPORARY TABLE LIKE To copy a table’s structure without copying its rows, use LIKE:
CREATE TEMPORARY TABLE tmp_orders_shell LIKE orders;LOCAL, GLOBAL, and VOLATILE: Compatibility Syntax, Not Different Scopes Engineers coming from Oracle, Teradata, SQL Server, or Redshift often expect these keywords to change table scope. In Snowflake, they don’t.
CREATE LOCAL TEMPORARY TABLE tmp_a (...);
CREATE GLOBAL TEMPORARY TABLE tmp_b (...);
CREATE VOLATILE TABLE tmp_c (...);Snowflake documents LOCAL TEMPORARY, GLOBAL TEMPORARY, and VOLATILE as synonyms provided purely for compatibility with other databases. Every one of them behaves identically to a table created with the plain TEMPORARY keyword.
GLOBAL TEMPORARY does not mean globally visible. The table is still scoped to a single session.LOCAL TEMPORARY does not add restrictions. It is the same object as TEMPORARY.VOLATILE is a Teradata-style synonym with no separate volatility rules in Snowflake.If a migration script assumes GLOBAL TEMPORARY unlocks cross-session access the way it might on another platform, that assumption is wrong on Snowflake, and it’s worth flagging in code review before it ships.
How Session Scope Actually Works “Session-scoped” is the phrase every guide uses. Few translate it into what actually happens when a real application, not a single worksheet tab, creates and queries a temporary table.
A temporary table belongs to a session, not a user The same person, connected twice, does not share temp tables between those two connections. Snowflake confirms directly that a temporary table is not visible to other sessions or users, including a second session opened by the same login.
-- Session A
CREATE TEMPORARY TABLE tmp_customer_ids AS
SELECT customer_id FROM customers WHERE region = 'EMEA';
-- Session B (same user, different connection)
SELECT * FROM tmp_customer_ids;
-- Object 'TMP_CUSTOMER_IDS' does not exist or not authorized.That error is not a permissions bug. It is the temp table doing exactly what it’s designed to do.
What happens on disconnect and reconnect A dropped connection ends the underlying session. The temporary table disappears with it. The next connection, even from the same client a second later, opens a new session and starts with nothing.
Connection closes (network blip, timeout, deploy restart). Snowflake session ends. Every temporary table tied to that session is purged. The client reconnects and opens a different session. Any code that assumed the temp table would still be there fails. Why connection pooling breaks temp-table workflows This is the production failure mode that generic definitions never mention, and it’s the one most worth understanding before writing application code against Snowflake.
A connection pool hands out physical connections, and each physical connection maps to a Snowflake session underneath. A logical application request is not the same thing as a Snowflake session.
Request A checks out a pooled connection tied to session 101 and creates tmp_orders. The connection returns to the pool when request A finishes. Request B checks out a connection, but the pool hands it session 284, not session 101. Request B queries tmp_orders and gets “object does not exist,” even though the code looks correct. The opposite failure exists too. If a pooled session stays alive far longer than the application logic expects, an old temporary table or a stale name collision can affect a request that has no idea that session has history.
The operating rule: never make application correctness depend on receiving the same Snowflake session across two logical requests unless the pool explicitly guarantees session affinity, and few do by default.
Diagnosing disappearing temp tables CURRENT_SESSION() returns the session ID behind the active connection. Logging it alongside every temp-table create and query turns a mysterious “object does not exist” report into a two-minute diagnosis: was the query actually running against a different session than the one that created the table?
SELECT CURRENT_SESSION();The Name-Shadowing Problem Most Guides Skip This is the section worth reading twice if your team names temporary tables after the objects they stage data for.
A temporary table can share a name with a permanent one Snowflake explicitly permits a temporary and a non-temporary table with the identical name in the same database and schema.
CREATE TABLE prod.public.orders (...); -- permanent
CREATE TEMPORARY TABLE prod.public.orders (...); -- temporary, same nameWhich one does a query actually hit? Inside the session that created it, the temporary table takes precedence. Snowflake describes the permanent object as effectively hidden by the session’s temporary object of the same name, for the lifetime of that session.
This holds even when the query fully qualifies the table name. A common assumption is that SELECT * FROM prod.public.orders must resolve to the permanent table because the reference is fully qualified. It doesn’t automatically. If a temporary table with that exact database, schema, and name exists in the session, Snowflake’s temp-table precedence rule still applies.
Why this makes DROP TABLE dangerous CREATE TEMPORARY TABLE orders (...);
DROP TABLE orders;That DROP TABLE removes the temporary object, not the permanent one, for as long as the temp table exists in that session. The real production risk shows up when an engineer believes they are modifying the permanent table, but their session has quietly been resolving every statement against the shadow object instead.
Snowflake calls out naming conflicts as particularly important with CREATE OR REPLACE, since a replace operation involves an implicit drop and recreate. A careless CREATE OR REPLACE TABLE inside a session that already has a temp table of the same name is exactly where this bites.
A naming convention that removes the risk entirely The fix is operational, not technical. Give every temporary object a prefix that a permanent table would never carry:
tmp_invoice_deduptmp_customer_deltatmp_reconciliation_keysNone of those names will ever collide with a real production table, and code review catches an accidental permanent-table name in a CREATE TEMPORARY TABLE statement immediately.
Kanerika Service
Snowflake Consulting and Implementation
Kanerika is a Snowflake Select Tier Partner that designs table strategy, naming standards, and session-safe pipeline patterns into Snowflake environments from day one.
Explore Snowflake Services Temporary Tables Inside Snowflake Transactions Session scope and transaction scope are not the same boundary, and conflating them causes a specific, repeatable bug.
A session can contain many transactions A temporary table’s lifetime is tied to the session that created it, not to any single transaction inside that session. It survives a COMMIT. It also survives a ROLLBACK, because the table object itself was never rolled back, only the DML that ran against it inside that transaction boundary.
CREATE TEMPORARY TABLE is DDL, and DDL commits This is the single most under-covered mechanic in existing guides on this topic. Snowflake states plainly that DDL statements, including CREATE TEMPORARY TABLE, implicitly commit any currently active transaction before they execute.
BEGIN;
UPDATE accounts SET status = 'reviewing' WHERE account_id = 500;
CREATE TEMPORARY TABLE tmp_accounts (...);
ROLLBACK;A developer who wrote this expecting the ROLLBACK to undo the UPDATE will be wrong. The CREATE TEMPORARY TABLE statement already committed that update the moment it ran, because DDL closes out the open transaction first. The ROLLBACK at the end has nothing left from that UPDATE to undo.
BEGIN
↓
UPDATE accounts ...
↓
CREATE TEMP TABLE (DDL)
↓
IMPLICIT COMMIT ← the UPDATE is now permanent
↓
ROLLBACK has nothing to undo from before this lineSnowflake is explicit that you cannot create, use, and drop a temporary or transient table inside a single atomic transaction, precisely because the create and drop are both DDL boundaries that force a commit.
The pattern that avoids the trap CREATE TEMPORARY TABLE tmp_work (...); -- create first, outside any transaction
BEGIN;
INSERT INTO tmp_work SELECT ...;
UPDATE tmp_work SET ...;
MERGE INTO target USING tmp_work ON ...;
COMMIT;
DROP TABLE tmp_work; -- drop after, also outside the transactionCreate the temporary table before opening a transaction, run the DML for that transaction, commit, and drop the table afterward as a separate statement. That order keeps the DDL commit points from silently absorbing DML you meant to keep reversible.
Procedure-Scoped Temporary Tables: New for Snowflake Scripting Snowflake shipped a narrower alternative to session-scoped temp tables in its version 10.18 release, for procedures written in Snowflake Scripting . Most existing guides on this keyword predate the feature entirely. For the broader mechanics of writing and calling procedures, see Kanerika’s Snowflake stored procedures guide .
CREATE OR REPLACE PROCEDURE build_report()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
CREATE OR REPLACE PROCEDURE SCOPED TEMP TABLE inner_scratch (
id NUMBER,
flag BOOLEAN
);
INSERT INTO inner_scratch SELECT id, TRUE FROM source_table;
-- inner_scratch is cleared automatically when this execution ends
RETURN 'done';
END;
$$;A procedure-scoped temp table exists only for a single execution of the stored procedure that created it, not for the rest of the calling session.
Table 1: Session-Scoped vs. Procedure-Scoped Temporary Tables Behavior Session temp table Procedure-scoped temp table Scope Entire Snowflake session One procedure execution Survives procedure completion Yes, while the session stays open No Cleanup Explicit DROP or session close Automatic when the call ends Best fit Data shared across statements in a session Internal procedure scratch work Collision risk across parallel calls Higher, if naming isn’t disciplined Lower, each execution is isolated
Two procedures running concurrently, each using an identically named procedure-scoped temp table for internal scratch work, don’t collide with each other the way two sessions sharing a loose naming convention might. That isolation is the main reason to reach for the newer syntax instead of a plain session-scoped table when the object is purely internal to one procedure call.
Where Temporary Tables Actually Earn Their Keep The generic “staging and testing” answer is technically true and operationally useless. Here is what that looks like in a real pipeline.
ETL and ELT staging inside one execution CREATE TEMPORARY TABLE tmp_customer_delta AS
SELECT *
FROM raw_customer
WHERE ingest_batch_id = :batch_id;Stage only the slice of data the current run needs, validate it, then merge the validated rows into the target table. The staging table never needs to outlive the run that created it, which makes it a genuine fit for session scope rather than a transient table someone has to remember to clean up later.
Land the current batch into a temporary staging table. Validate and deduplicate against business rules. Split accepted rows from rejected rows. MERGE the accepted rows into the permanent target.Let the temp table disappear with the session, or drop it explicitly once the merge succeeds. When a temporary table is the wrong choice for staging Reach for a transient table instead when any of these are true:
A different job or session needs to inspect the staged data. A retry after failure happens in a fresh session, not the one that staged the data. Debugging a failed run requires the intermediate rows to still exist afterward. Multiple workers or services need to read the same staged dataset. Testing and prototyping without touching production tables Before a new transformation, migration script, or business rule ships against a permanent table, a temporary table gives a safe place to try it first. Copy a representative slice of production data with CREATE TEMPORARY TABLE ... AS SELECT ... LIMIT, run the candidate logic against it, and inspect the output before touching anything permanent. For validating against a full point-in-time copy instead of a sampled slice, Kanerika’s dedicated guide to cloning tables in Snowflake covers that heavier-weight alternative.
Because the object disappears automatically at the end of the session, there’s no cleanup step to forget and no risk of a scratch table quietly becoming a permanent fixture that nobody remembers creating. That property is exactly what makes it a poor fit for anything meant to outlive the debugging session, and exactly what makes it a good fit for the debugging session itself.
Watch on YouTube
An Honest Snowflake vs Fabric Review
A practitioner-level look at where Snowflake’s platform choices help or hurt real workloads, useful context before the cost and CTE tradeoffs below.
Breaking a large SQL workflow into checkpoints CREATE TEMPORARY TABLE tmp_active_accounts AS
SELECT account_id, status, last_activity_date
FROM accounts
WHERE status = 'active';Materializing an expensive intermediate result once, then reusing it across several downstream statements, turns one dense query into a set of testable steps: filtering, deduplication, window calculations, reconciliation, and aggregation each become inspectable on their own. Engineers can check row counts and spot anomalies at each checkpoint instead of debugging one monolithic statement after the fact.
The counterpoint matters just as much: turning every query stage into its own temp table multiplies DDL statements, metadata objects, writes, and storage. Reserve checkpoints for genuinely expensive or genuinely reused intermediate results, not every filter step.
Temporary Table vs. CTE: A Performance Decision, Not a Style Preference A CTE’s scope ends with the SQL statement that defines it. A temporary table can be read by many statements across the same session. That difference should drive the decision, not habit.
A temp table does not automatically make a query faster Materializing a result into a temporary table costs three things: computing the source result, writing it to storage, and reading it back out again. For a transformation that’s cheap to compute and only used once, that extra write-then-read cycle can lose to simply leaving the logic in a CTE.
When materialization pays off An expensive multi-join result used exactly once: usually stays in a single query, no temp table needed. An expensive multi-join result reused across six downstream statements: materialization is worth testing. There’s no universal break-even row count or cost figure here, and treating one as gospel is how teams end up materializing intermediate results that were cheaper left alone. Test both paths against the actual data volume and reuse pattern.
A temporary table is not a substitute for result caching Snowflake already caches query results automatically for repeated identical queries. A temporary table shouldn’t be framed as a generic performance cache; it solves a specific reuse-across-statements or workflow-separation problem that result caching doesn’t address.
Talk to Kanerika
Auditing Your Snowflake Table Strategy?
Kanerika reviews staging patterns, connection pooling assumptions, and temp/transient table usage against how your pipelines actually run, not a generic checklist.
Schedule a Demo → What Temporary Tables Actually Cost Performance and cost claims around temporary tables are where the current search results for this topic get the most inconsistent, and it’s worth correcting the record with what Snowflake’s own documentation states. General warehouse and storage cost control is its own broader subject, covered in Kanerika’s Snowflake cost optimization guide ; this section stays scoped to what’s specific to temporary tables.
Temporary does not mean free storage Some existing guides describe temporary tables as carrying no additional storage cost. Snowflake’s documentation says the opposite: for as long as a temporary table exists, the data it stores contributes to the account’s overall storage charges. “Temporary” describes the table’s lifecycle, not its billing status.
Why a temp table can cost more than a “few hours” implies A session that stays connected for two days keeps its temporary tables alive for two days, not for some assumed short window. Snowflake specifically recommends explicitly dropping large temporary tables in any session kept open longer than 24 hours, precisely to prevent storage charges that nobody expected.
Compute cost is separate from storage cost Creating a temporary table still runs the warehouse compute needed to produce whatever result populates it. Querying it again afterward is a second read, not a free lookup. A TEMPORARY keyword doesn’t grant a query cheaper or separate compute; warehouse sizing decisions apply to temp-table workloads exactly as they do to permanent ones.
Metadata overhead at scale This is the operational cost most guides never mention, and Snowflake’s own documentation is direct about it: workloads that generate high volumes of temporary tables are very likely to see degraded performance when querying the Information Schema’s COLUMNS or TABLES views.
The pattern that causes it in practice: an application connection pool, many persistent connections, several temporary tables created per request, and no explicit cleanup between requests. Multiply that by normal request volume and the temp-table count in the account climbs fast.
Snowflake’s recommended fix matches the naming-convention discipline already covered above:
Explicitly DROP TABLE temp objects once their last consumer finishes, rather than waiting on session cleanup. Close idle sessions instead of leaving connections open indefinitely. Avoid a one-temp-table-per-trivial-step pattern in ETL code. Monitor unusually long-running sessions for accumulated temp-table debt. Temporary Tables and Snowflake Time Travel Most comparisons reduce this to a single number in a table row. The real behavior is two independent clocks, and understanding both is what actually prevents a failed recovery. Kanerika’s dedicated Time Travel guide covers retention windows and recovery mechanics for standard and transient tables in full; this section stays narrow to what changes for a temporary table specifically.
Clock one: the retention ceiling Temporary tables support a maximum Time Travel retention of one day. That number is a ceiling, not a guarantee.
Clock two: the session Snowflake documents the real constraint directly: a temporary table is purged once its session ends, so the effective retention period is 24 hours or the remainder of the session, whichever is shorter. A temp table created five minutes before its session closes gets roughly five minutes of practical Time Travel, regardless of the one-day ceiling on paper.
Retention clock: up to 24 hours (configured, maximum)
Session clock: ends whenever the session ends
Effective window: whichever clock runs out firstWhat happens after the session ends The table and its data are purged. They are not recoverable through Time Travel just because the configured retention window technically hadn’t elapsed; the session ending removes that option regardless.
No Fail-safe, and no UNDROP recovery plan Temporary tables have no Fail-safe period at all, which means there is no Snowflake-managed recovery window after the applicable retention or session lifecycle ends. The practical business rule: data that lives only in a temporary table must be reproducible from source. Even while Time Travel semantics technically apply during the active session, the object’s short and session-bound lifecycle makes it unsuitable as a dependable recovery mechanism. Don’t design a recovery plan around UNDROP on a temporary table.
One edge case worth knowing An active Time Travel query against a temporary or transient table can delay that table’s purge until the query finishes. It’s a narrow scenario, but worth knowing if a “why hasn’t this been cleaned up yet” question comes up during an audit.
Checklist
Snowflake Performance Optimization Checklist
A practical checklist for tightening Snowflake performance and cost across warehouses, storage, and table strategy, including temporary and transient table hygiene.
Get the Checklist → Temporary vs. Transient: The One Question That Decides It Kanerika’s CREATE TABLE guide already covers the full Standard vs. Transient vs. Temporary comparison in depth. Narrowed to just this decision, one question does most of the work:
Must a different session see this data?
Table 2: Temporary vs. Transient Decision Framework Condition Choose Temporary Choose Transient Data needs to survive the creating session No Yes Another session or process must read it No Yes A retry happens in a new session later No Yes Ops teams need to inspect it after a failure No Yes Data is fully reproducible from source Yes Either works Fail-safe recovery matters at all Never (none available) Never (also none)
If the workflow must survive a reconnect, a worker handoff, a retry in a later session, or a second consumer, that alone rules out a temporary table. Everything else about cost and performance is secondary to that one structural question.
Common Temporary Table Problems and How to Fix Them Most temporary-table incidents trace back to one of a handful of session-scope mistakes repeating across teams. The list below pairs each symptom with the mechanism behind it and the concrete fix, so the on-call engineer can match what they’re seeing to a cause instead of guessing.
“Object does not exist” right after a reconnect. The client opened a new session; the old temp table died with the previous one. Fix the client to recreate any temp objects it depends on immediately after establishing a new connection, rather than assuming they persisted.The table exists in one worksheet tab but not another. Each worksheet tab in Snowsight opens its own session. Recreate the object in the tab where the work is actually happening.A query silently hits the wrong data. A temporary table is shadowing a permanent table with the same name in that session. Rename the temp object using a tmp_ prefix and re-run; if results change, shadowing was the cause.A ROLLBACK didn’t undo the expected work. A DDL statement, including CREATE TEMPORARY TABLE, implicitly committed the transaction before the rollback ran. Restructure the script so temp-table creation happens before BEGIN, not inside the transaction block.A temp table vanished mid-workflow in an application. Likely causes: the connection closed, a connection pool handed out a different physical session, or a session timeout policy ended the session. Log CURRENT_SESSION() at creation and at the point of failure to confirm which one.Storage is climbing without an obvious cause. Check for large temporary tables inside long-lived sessions that were never explicitly dropped. A monitoring query against active sessions and their age is usually enough to find the culprit.Information Schema queries have gotten slower. Investigate temp-table volume and idle sessions; Snowflake documents this exact pattern against the COLUMNS and TABLES views specifically.A GLOBAL TEMPORARY table isn’t visible from another connection. GLOBAL TEMPORARY is compatibility syntax only; it never creates cross-session scope in Snowflake. Switch to a transient table if cross-session visibility is actually the requirement.Snowflake Temporary Table Best Practices None of these require new tooling. They’re operating discipline that turns everything covered above into habits a team follows automatically, instead of lessons learned the hard way after an incident.
Prefix every temporary object (tmp_ or an equivalent internal standard) so it can never collide with a permanent table name. Never make application correctness depend on pooled-session continuity unless the pool explicitly guarantees it. Create temporary tables before opening an explicit transaction, to avoid an accidental implicit commit mid-transaction. Drop large temporary tables as soon as their last consumer finishes, rather than waiting on session cleanup. Keep every temporary dataset fully reconstructable; never store state there that can’t be rebuilt from source. Materialize only results you’ll actually reuse; don’t replace every CTE with a table out of habit. Use procedure-scoped temp tables for scratch data that should disappear with a single procedure call, not the whole session. Switch to a transient table the moment a workflow needs to cross a session boundary. Building Reliable Snowflake Table Strategies: How Kanerika Helps Most of the incidents in this guide, an object silently shadowed, a rollback that didn’t roll back, storage that crept up in a forgotten session, don’t show up in a proof of concept. They show up months into production, once real request volume and real connection pooling are in play.
As a Snowflake Select Tier Partner, Kanerika builds table strategy into the platform design from the start rather than treating it as an afterthought once something breaks. That means naming conventions that make shadowing structurally hard to hit, transaction boundaries that account for DDL’s implicit commit behavior, and staging patterns that pick the right persistence type, temporary, transient, or permanent, based on how data actually flows through a pipeline, not on habit.
On one recent distributed-operations engagement, Kanerika’s Snowflake migration work cut manual reconciliation effort by 60% for a client running analytics across multiple regional systems, largely by replacing ad hoc staging patterns with governed, session-aware pipeline design. The full case study covers the architecture in more detail.
Teams that want a second set of eyes on an existing Snowflake footprint, staging patterns, connection pooling assumptions, or storage creep from long-lived sessions, can start with a working session against their actual pipelines rather than a generic audit checklist.
Case Study
60% Less Manual Reconciliation via Snowflake Migration
A distributed-operations client cut manual reconciliation effort by 60% after Kanerika replaced ad hoc staging patterns with governed, session-aware Snowflake pipeline design.
Read the Case Study → That review usually surfaces the same handful of issues: a naming convention that never accounted for shadowing, a connection pool configured without session-affinity guarantees, and at least one long-lived session quietly accumulating storage nobody is tracking. None of them are hard to fix once they’re visible. The cost is almost always in not knowing they exist until a report runs slow or a rollback doesn’t behave the way it should.
Wrapping Up A Snowflake temporary table is a session-scoped object, not a free or forgiving one. It bills for storage while it exists, participates in transaction rules the same way permanent tables do, and can hide a permanent table of the same name for as long as its session lasts.
Treat session ownership, not storage cost, as the first question when choosing between temporary and transient. If the workflow needs to survive a reconnect, a retry, or a second consumer, a temporary table is the wrong tool regardless of how attractive its lower cost profile looks on paper.
Frequently Asked Questions
How long does a temporary table last in Snowflake? A Snowflake temporary table exists until it is explicitly dropped or until the session that created it ends, whichever happens first. There is no separate expiration timer beyond the session’s own lifetime, so a short-lived session produces a short-lived table even if no one drops it manually.
Does a Snowflake temporary table cost money? Yes. Snowflake’s documentation states that the data stored in a temporary table contributes to the account’s overall storage charges for as long as the table exists. Storage cost is separate from compute cost, and a large temporary table left in a long-lived session can accumulate meaningful storage charges even though the object never appears in the permanent schema.
Can two sessions access the same Snowflake temporary table? No. A temporary table is visible only to the session that created it, not to other sessions from the same user and not to other users. A second connection querying the same table name gets an object-not-found error, because that session never created a temp table of its own.
Can a Snowflake temporary table have the same name as a permanent table? Yes, and this is one of the more dangerous behaviors to overlook. Snowflake permits a temporary and a permanent table with identical names in the same database and schema, and the temporary object takes precedence for every query and operation inside that session, even when the table name is fully qualified.
Is GLOBAL TEMPORARY actually global in Snowflake? No. GLOBAL TEMPORARY, LOCAL TEMPORARY, and VOLATILE are all compatibility synonyms for the plain TEMPORARY keyword, included so scripts migrating from other databases still run. None of them changes the object’s scope; every variant is still limited to the session that created it.
Does CREATE TEMPORARY TABLE commit an open transaction? Yes. CREATE TEMPORARY TABLE is a DDL statement, and DDL statements implicitly commit any currently active transaction before they run. A team that expects a later ROLLBACK to undo work performed before a temp-table creation will find that work already committed.
Do Snowflake temporary tables support Time Travel? Yes, but with real limits. Retention is capped at a maximum of one day, and the table is purged when its session ends regardless of that configured retention, so the effective window is 24 hours or the remainder of the session, whichever is shorter. Temporary tables also carry no Fail-safe period, so there is no recovery option once that window closes.
Should I use a temporary table or a CTE? Use a temporary table when an intermediate result needs to be read by multiple statements across a session; use a CTE when the result is only needed inside a single statement. Materializing into a temporary table adds a write-then-read cost, so it does not automatically make a query faster, and is worth testing against the actual reuse pattern before assuming it helps.
Should ETL staging tables be temporary or transient? Temporary works when the entire staging workflow, from load through validation to merge, completes inside one session. Switch to transient the moment staged data must survive a reconnect, a retry in a new session, or inspection by a separate process after a failure.
What is a procedure-scoped temporary table in Snowflake? Introduced in Snowflake’s May 2026 release for Snowflake Scripting, a procedure-scoped temporary table is created with CREATE OR REPLACE PROCEDURE SCOPED TEMP TABLE inside a stored procedure body. It exists only for that single procedure execution and is cleaned up automatically when the call finishes, rather than persisting for the rest of the calling session.
