Table Types
Every table is one of three types, chosen at creation and never changed afterward without recreating the table: permanent (default), transient, or temporary. Part 5 covered the Time Travel and Fail-safe trade-offs; this section is the full comparison.
| Type | Time Travel | Fail-safe | Can be cloned into | Create views on it | Lifetime |
|---|---|---|---|---|---|
| Permanent | 0–90 days | 7 days | Any type | Yes | Until dropped |
| Transient | 0–1 day | None | Transient, temporary | Yes | Until dropped |
| Temporary | 0–1 day | None | Transient, temporary | Yes | Current session only |
| TABLE TYPE | AFTER A CHANGE OR DROP → (not to scale) | |
|---|---|---|
| CREATE TABLE Permanent the default | Time Travel 1 day default, up to 90 (Enterprise) | Fail-safe 7 days, Snowflake Support |
| CREATE TRANSIENT Transient no Fail-safe | Time Travel 0 or 1 day | no Fail-safe: the data is gone |
| CREATE TEMPORARY Temporary this session only | Time Travel 0 or 1 day | no Fail-safe · dropped when the session ends |
Temporary tables are invisible outside the session that created them, even to other sessions authenticated as the same user, and drop automatically when the session ends. They're the natural choice for scratch work inside a single script or transformation run. A temporary table can share its name with a permanent table in the same schema; inside its session, the temporary one wins. A permanent table can be cloned into any of the three types, but a transient or temporary table can't be cloned up into a permanent one at all.
Picking the type in three questions
Scratch space for one script. Invisible to every other session, self-destructs at session end, no Fail-safe cost.
It must persist, so: how much does losing it hurt?
Staging layers, ETL intermediates, high-churn scratch that persists. Skips the 7-day Fail-safe surcharge, which makes it the answer whenever the goal is to minimize the cost of reconstructible data.
The default for a reason: up to 90 days of Time Travel on Enterprise plus Fail-safe behind it. Source-of-truth data lives here.
Writing to tables: the DML lineup
You need to pick the right write statement for a scenario, and two of them, MERGE and INSERT OVERWRITE, are easy to confuse:
| Statement | What it does | The scenario it answers |
|---|---|---|
INSERT | Appends new rows | Plain loading; never touches existing rows |
UPDATE / DELETE | Modifies or removes rows matching a WHERE | Targeted changes; each one rewrites whole micro-partitions |
MERGE | Upsert in one statement: match a source to a target, update the matches, insert the rest | "Apply a staging table of changed customers to the target": the daily-ETL workhorse |
INSERT OVERWRITE | Truncates the table and inserts, atomically, in one statement | "Fully replace the contents without a window where the table is empty" |
TRUNCATE | Removes all rows and the table's load metadata; keeps the table and its grants | Empty it fast; unlike DROP, nothing to UNDROP because the object survives |
CREATE OR REPLACE | Swaps the entire object for a new one | Structure changed too, not just rows; grants on the old object are lost unless you add COPY GRANTS, any stream on it goes stale, and the old version is reachable only via Time Travel |
MERGE deserves the extra minute because it's the one candidates who haven't written pipelines get wrong. UPSERT INTO and REPLACE INTO are other databases' keywords; Snowflake's answer is always this shape:
MERGE INTO customers tgtUSING staging_customers src ON tgt.customer_ id = src. customer_ id WHEN MATCHED THEN UPDATE SET tgt.email = src.email, tgt.tier = src.tierWHEN NOT MATCHED THEN INSERT (customer_ id, email, tier) VALUES (src. customer_ id, src.email, src.tier);
Both "bring the target in line with a source," which is exactly why they're easy to mix up. MERGE keeps every target row the source doesn't mention; INSERT OVERWRITE discards them, because it truncates first. A scenario that says "existing customers not in today's file must be preserved" has just ruled out INSERT OVERWRITE for you.

