Creating tables (permanent, temporary, transient) in Snowflake - Try It Yourself

Creating tables (permanent, temporary, transient)

📖 Scenario: You are working with Snowflake, a cloud data platform. You need to create different types of tables to store data with different lifetimes and behaviors.Permanent tables keep data until you delete them. Temporary tables exist only during your session. Transient tables keep data but do not have fail-safe recovery.

🎯 Goal: Learn how to create permanent, temporary, and transient tables in Snowflake with the correct syntax and options.

📋 What You'll Learn

Create a permanent table named employees_permanent with columns id (integer) and name (string).

Create a temporary table named employees_temp with the same columns.

Create a transient table named employees_transient with the same columns.

💡 Why This Matters

🌍 Real World

Creating different types of tables helps manage data lifecycle and storage costs in cloud data platforms like Snowflake.

💼 Career

Knowing how to create and manage permanent, temporary, and transient tables is essential for data engineers and cloud database administrators.

1

Create a permanent table

Write a SQL statement to create a permanent table called employees_permanent with columns id as integer and name as string (VARCHAR).

Snowflake

-- Your code here to create the permanent table employees_permanent

Need a hint?

Use CREATE TABLE followed by the table name and column definitions.

2

Create a temporary table

Write a SQL statement to create a temporary table called employees_temp with columns id as integer and name as string (VARCHAR). Use the TEMPORARY keyword.

Snowflake

CREATE TABLE employees_permanent (
    id INTEGER,
    name VARCHAR
);
-- Your code here to create the temporary table employees_temp

Need a hint?

Use CREATE TEMPORARY TABLE to create a temporary table.

3

Create a transient table

Write a SQL statement to create a transient table called employees_transient with columns id as integer and name as string (VARCHAR). Use the TRANSIENT keyword.

Snowflake

CREATE TABLE employees_permanent (
    id INTEGER,
    name VARCHAR
);

CREATE TEMPORARY TABLE employees_temp (
    id INTEGER,
    name VARCHAR
);
-- Your code here to create the transient table employees_transient

Need a hint?

Use CREATE TRANSIENT TABLE to create a transient table.

4

Add a comment to the permanent table

Write a SQL statement to add a comment 'Permanent employee data' to the employees_permanent table using the COMMENT clause.

Snowflake

CREATE TABLE employees_permanent (
    id INTEGER,
    name VARCHAR
);

CREATE TEMPORARY TABLE employees_temp (
    id INTEGER,
    name VARCHAR
);

CREATE TRANSIENT TABLE employees_transient (
    id INTEGER,
    name VARCHAR
);
-- Your code here to add a comment to employees_permanent

Need a hint?

Add COMMENT = 'Permanent employee data' after the column definitions in the CREATE TABLE statement.