Creating tables (permanent, temporary, transient) in Snowflake - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a permanent table named employees.

Snowflake

CREATE [1] TABLE employees (id INT, name STRING);

Drag options to blanks, or click blank then click option'

ATEMPORARY

BPERMANENT

CTRANSIENT

DVOLATILE

Attempts:

3 left

💡 Hint

Common Mistakes

Using TEMPORARY or TRANSIENT keywords instead of PERMANENT.

Using VOLATILE which is not valid in Snowflake.

Permanent tables are created with the keyword PERMANENT or no keyword at all. Here, we explicitly use PERMANENT to create a permanent table.

2fill in blank

medium

Complete the code to create a temporary table named temp_sales.

Snowflake

CREATE [1] TABLE temp_sales (sale_id INT, amount FLOAT);

Drag options to blanks, or click blank then click option'

APERMANENT

BGLOBAL

CTRANSIENT

DTEMPORARY

Attempts:

3 left

💡 Hint

Common Mistakes

Using PERMANENT or TRANSIENT instead of TEMPORARY.

Using GLOBAL which is not a valid table type in Snowflake.

Temporary tables are created with the keyword TEMPORARY. They exist only during the user session.

3fill in blank

hard

Fix the error in the code to create a transient table named orders.

Snowflake

CREATE [1] TABLE orders (order_id INT, order_date DATE);

Drag options to blanks, or click blank then click option'

ATEMPORARY

BPERMANENT

CTRANSIENT

DVOLATILE

Attempts:

3 left

💡 Hint

Common Mistakes

Using TEMPORARY or PERMANENT instead of TRANSIENT.

Using VOLATILE which is invalid in Snowflake.

Transient tables are created with the keyword TRANSIENT. They do not have fail-safe but data persists beyond the session.

4fill in blank

hard

Fill both blanks to create a temporary table named session_data with two columns.

Snowflake

CREATE [1] TABLE session_data (user_id [2], login_time TIMESTAMP);

Drag options to blanks, or click blank then click option'

ATEMPORARY

BINT

CSTRING

DPERMANENT

Attempts:

3 left

💡 Hint

Common Mistakes

Using PERMANENT instead of TEMPORARY.

Using STRING for user_id when it should be INT.

The table is temporary, so use TEMPORARY. The user_id column is an integer, so use INT as the data type.

5fill in blank

hard

Fill all three blanks to create a transient table named product_info with columns id, name, and price.

Snowflake

CREATE [1] TABLE product_info (id [2], name [3], price FLOAT);

Drag options to blanks, or click blank then click option'

ATRANSIENT

BINT

CSTRING

DTEMPORARY

Attempts:

3 left

💡 Hint

Common Mistakes

Using TEMPORARY instead of TRANSIENT.

Mixing up data types for id and name columns.

The table is transient, so use TRANSIENT. The id column is an integer (INT) and the name column is text (STRING).

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