Course contents

SnowPro Core COF-C03 Study GuidePart 6: Database objects and platformTable Types

Table Types

Table Types

Definition

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.

TypeTime TravelFail-safeCan be cloned intoCreate views on itLifetime
Permanent0–90 days7 daysAny typeYesUntil dropped
Transient0–1 dayNoneTransient, temporaryYesUntil dropped
Temporary0–1 dayNoneTransient, temporaryYesCurrent session only
What stays recoverable after a change or a DROP, per table type, not to scale. Permanent: Time Travel, 1 day by default and up to 90 days on Enterprise, then 7 days of Fail-safe. Transient: Time Travel of 0 or 1 day, then nothing. Temporary: the same 0 or 1 day, no Fail-safe, and the table disappears when the session ends.TABLE TYPEAFTER A CHANGE OR DROP → (not to scale)CREATE TABLEPermanentthe defaultTime Travel1 day default, up to 90 (Enterprise)Fail-safe7 days, Snowflake SupportCREATE TRANSIENTTransientno Fail-safeTime Travel0 or 1 dayno Fail-safe: the data is goneCREATE TEMPORARYTemporarythis session onlyTime Travel0 or 1 dayno Fail-safe · dropped when the session ends
TABLE TYPEAFTER 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
Figure 6.3 The table type decides how long a mistake stays recoverable, and you can't change it after creation. Each bar shows what remains after a change or a DROP: blue is Time Travel you use yourself, amber is Fail-safe that only Snowflake Support can use. Transient and temporary tables also cost less because they have no Fail-safe storage.

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

?Does anything outside this session need the table?
No
Temporary

Scratch space for one script. Invisible to every other session, self-destructs at session end, no Fail-safe cost.

Yes
One more question ↓

It must persist, so: how much does losing it hurt?

?Could you rebuild it from upstream if it vanished?
Easily
Transient

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.

Painfully or not at all
Permanent

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:

StatementWhat it doesThe scenario it answers
INSERTAppends new rowsPlain loading; never touches existing rows
UPDATE / DELETEModifies or removes rows matching a WHERETargeted changes; each one rewrites whole micro-partitions
MERGEUpsert 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 OVERWRITETruncates the table and inserts, atomically, in one statement"Fully replace the contents without a window where the table is empty"
TRUNCATERemoves all rows and the table's load metadata; keeps the table and its grantsEmpty it fast; unlike DROP, nothing to UNDROP because the object survives
CREATE OR REPLACESwaps the entire object for a new oneStructure 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 tgt
USING staging_customers src
  ON tgt.customer_id = src.customer_id
WHEN MATCHED THEN UPDATE SET tgt.email = src.email, tgt.tier = src.tier
WHEN NOT MATCHED THEN INSERT (customer_id, email, tier)
  VALUES (src.customer_id, src.email, src.tier);
Exam trap · MERGE vs INSERT OVERWRITE

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.