Creating tables (permanent, temporary, transient) in Snowflake - Visual Walkthrough

Process Flow - Creating tables (permanent, temporary, transient)

Start

↓

Choose Table Type

↓

Permanent

↓

Define Schema & Options

↓

Execute CREATE TABLE

↓

Table Created with chosen type

↓

End

You start by choosing the table type, define its structure, then run the create command to make the table with that type.

Execution Sample

Snowflake

CREATE TABLE perm_table (id INT, name STRING);
CREATE TEMPORARY TABLE temp_table (id INT, name STRING);
CREATE TRANSIENT TABLE trans_table (id INT, name STRING);

Creates three tables: permanent, temporary, and transient with same columns.

Process Table

Step	Command	Table Type	Action	Result
1	CREATE TABLE perm_table (id INT, name STRING);	Permanent	Create permanent table	perm_table created, stored permanently
2	CREATE TEMPORARY TABLE temp_table (id INT, name STRING);	Temporary	Create temporary table	temp_table created, exists only in session
3	CREATE TRANSIENT TABLE trans_table (id INT, name STRING);	Transient	Create transient table	trans_table created, no fail-safe retention
4	Session ends	Temporary	Temporary tables dropped	temp_table removed automatically
5	Data retention check	Permanent	Permanent and transient tables remain	perm_table and trans_table still exist

💡 Temporary table removed after session ends; permanent and transient tables persist.

Status Tracker

Table Name	Before Creation	After Creation	After Session End
perm_table	does not exist	exists permanently	still exists
temp_table	does not exist	exists temporarily	does not exist
trans_table	does not exist	exists transiently	still exists

Key Moments - 3 Insights

Why does the temporary table disappear after the session ends?

Because temporary tables are designed to exist only during the session, as shown in execution_table row 4 where the session ends and the temporary table is dropped automatically.

What is the difference between permanent and transient tables in terms of data retention?

Permanent tables have full data retention and fail-safe, while transient tables do not have fail-safe but still persist after session ends, as seen in execution_table rows 3 and 5.

Can you access a temporary table from another session?

No, temporary tables are session-scoped and cannot be accessed outside the session they were created in, as shown by their removal after session ends in execution_table row 4.

Visual Quiz - 3 Questions

Look at the execution table, what happens to temp_table after the session ends?

A It remains available permanently

B It is automatically dropped

C It becomes a permanent table

D It is converted to a transient table

💡 Hint

Check execution_table row 4 where session ends and temp_table is removed.

According to variable_tracker, which table exists after the session ends?

A perm_table and trans_table

B temp_table only

C perm_table only

D trans_table only

💡 Hint

Look at variable_tracker columns 'After Session End' for all tables.

If you want a table that persists but does not have fail-safe, which type should you create?

A Permanent table

B Temporary table

C Transient table

D External table

💡 Hint

Refer to execution_table rows 3 and 5 about transient table properties.

Concept Snapshot

CREATE TABLE creates a permanent table stored permanently.
CREATE TEMPORARY TABLE creates a session-only table dropped after session ends.
CREATE TRANSIENT TABLE creates a persistent table without fail-safe.
Choose table type based on data retention needs.
Temporary tables are session-scoped; permanent and transient persist beyond session.

Full Transcript

This visual execution shows how Snowflake creates three types of tables: permanent, temporary, and transient. First, you choose the table type, then define columns, and run the create command. Permanent tables stay forever until dropped. Temporary tables exist only during the session and are removed automatically when the session ends. Transient tables persist like permanent tables but do not have fail-safe data retention. The execution table traces each step, showing creation and what happens after the session ends. The variable tracker shows the existence of each table before creation, after creation, and after session ends. Key moments clarify why temporary tables disappear and the difference between permanent and transient tables. The quiz tests understanding of table lifetimes and retention. This helps beginners see how table types behave in Snowflake practically.

Practice

1. Which type of table in Snowflake is best suited for storing data that needs to persist permanently and be recoverable after accidental deletion?

easy

A. TEMPORARY table

B. PERMANENT table

C. TRANSIENT table

D. EXTERNAL table

Solution

1. Step 1: Understand table types in Snowflake

   Permanent tables store data permanently and include fail-safe for recovery.

2. Step 2: Compare with other table types

   Temporary tables last only for the session, and transient tables do not have fail-safe, so they are not recoverable after deletion.

3. Final Answer:

   PERMANENT table -> Option B

4. Quick Check:

   Permanent = long-term, recoverable [OK]

Hint: Permanent tables keep data safe and recoverable long-term [OK]

Common Mistakes:

• Confusing transient with permanent tables
• Thinking temporary tables persist beyond session
• Assuming transient tables have fail-safe

2. Which of the following is the correct syntax to create a temporary table named session_data with columns id INT and value STRING in Snowflake?

easy

A. CREATE TABLE session_data TEMPORARY (id INT, value STRING);

B. CREATE TABLE TEMP session_data (id INT, value STRING);

C. CREATE TEMPORARY TABLE session_data (id INT, value STRING);

D. CREATE TEMP session_data (id INT, value STRING);

Solution

1. Step 1: Recall Snowflake syntax for temporary tables

   The correct keyword is TEMPORARY placed right after CREATE and before TABLE.

2. Step 2: Check each option's syntax

   CREATE TEMPORARY TABLE session_data (id INT, value STRING); uses correct order and keywords. Options A, B, and C use incorrect or unsupported syntax.

3. Final Answer:

   CREATE TEMPORARY TABLE session_data (id INT, value STRING); -> Option C

4. Quick Check:

   CREATE TEMPORARY TABLE = correct syntax [OK]

Hint: Use CREATE TEMPORARY TABLE for temporary tables [OK]

Common Mistakes:

• Using TEMP instead of TEMPORARY
• Placing TEMPORARY after table name
• Omitting TABLE keyword

3. What will be the result of the following Snowflake SQL commands?

CREATE TRANSIENT TABLE temp_cost (item STRING, price NUMBER);
INSERT INTO temp_cost VALUES ('apple', 1.2);
SELECT * FROM temp_cost;

medium

A. Syntax error due to TRANSIENT keyword

B. Error: Table does not exist

C. Empty result set

D. [{'item': 'apple', 'price': 1.2}]

Solution

1. Step 1: Understand transient table behavior

   Transient tables behave like permanent tables but without fail-safe. They accept inserts and can be queried normally.

2. Step 2: Analyze the commands

   The table is created, a row is inserted, and then selected. No errors or empty results expected.

3. Final Answer:

   [{'item': 'apple', 'price': 1.2}] -> Option D

4. Quick Check:

   Transient tables store and return inserted data [OK]

Hint: Transient tables act like permanent but no fail-safe [OK]

Common Mistakes:

• Thinking transient tables cannot store data
• Expecting syntax error with TRANSIENT keyword
• Assuming transient tables auto-delete data immediately

4. You run the following command in Snowflake:

CREATE TEMPORARY TABLE temp_users (user_id INT, name STRING);

Later, you try to query temp_users in a new session but get an error. What is the most likely cause?

medium

A. Temporary tables only exist during the session they were created in

B. Syntax error in table creation

C. Transient tables cannot be queried

D. Table was dropped manually

Solution

1. Step 1: Recall temporary table lifecycle

   Temporary tables exist only for the duration of the session that created them.

2. Step 2: Understand session behavior

   Querying in a new session fails because the temporary table no longer exists.

3. Final Answer:

   Temporary tables only exist during the session they were created in -> Option A

4. Quick Check:

   Temporary = session-only lifespan [OK]

Hint: Temp tables vanish after session ends [OK]

Common Mistakes:

• Assuming temp tables persist across sessions
• Confusing transient with temporary tables
• Ignoring session scope of temporary tables

5. You want to create a table in Snowflake that stores temporary data across sessions but does not use fail-safe to reduce storage costs. Which table type and creation statement should you use?

hard

A. CREATE TRANSIENT TABLE cost_savings (id INT, amount NUMBER);

B. CREATE TEMPORARY TABLE cost_savings (id INT, amount NUMBER);

C. CREATE PERMANENT TABLE cost_savings (id INT, amount NUMBER);

D. CREATE EXTERNAL TABLE cost_savings (id INT, amount NUMBER);

Solution

1. Step 1: Identify table types and fail-safe behavior

   Transient tables do not have fail-safe, reducing storage costs but keep data beyond session.

2. Step 2: Match requirement with table type

   Temporary tables are session-only, permanent tables have fail-safe, external tables are for external data.

3. Final Answer:

   CREATE TRANSIENT TABLE cost_savings (id INT, amount NUMBER); -> Option A

4. Quick Check:

   Transient = no fail-safe, cost-saving [OK]

Hint: Use transient tables to save costs without fail-safe [OK]

Common Mistakes:

• Choosing temporary tables which expire after session
• Using permanent tables with fail-safe enabled
• Confusing external tables with transient