Creating tables (permanent, temporary, transient) in Snowflake - Mechanics & Internals

Overview - Creating tables (permanent, temporary, transient)

What is it?

Creating tables in Snowflake means making places to store data. There are three main types: permanent, temporary, and transient tables. Permanent tables keep data until you delete them. Temporary tables last only during your session. Transient tables keep data but with fewer protections than permanent ones.

Why it matters

Tables organize and hold data so you can find and use it later. Without different table types, you might waste space or lose data unexpectedly. Choosing the right table type helps save money, improve speed, and protect important data.

Where it fits

Before learning this, you should understand basic databases and SQL commands. After this, you can learn about data loading, querying, and managing table security and performance.

Mental Model

Core Idea

Tables are like labeled boxes where data is stored, and the type of table decides how long the box stays and how it is treated.

Think of it like...

Imagine you have three kinds of boxes at home: a permanent storage box you keep forever, a temporary box you use only while cleaning and then throw away, and a transient box you keep for a while but don’t lock tightly.

┌───────────────┐
│   Tables in   │
│   Snowflake   │
└──────┬────────┘
       │
       ├─────────────┬───────────────┬───────────────┐
       │             │               │               │
┌─────────────┐ ┌─────────────┐ ┌───────────────┐
│ Permanent   │ │ Temporary   │ │ Transient     │
│ Table       │ │ Table       │ │ Table         │
│ - Lasts     │ │ - Lasts     │ │ - Lasts       │
│   until     │ │   session   │ │   until       │
│   deleted   │ │   ends      │ │   deleted     │
│ - Full data │ │ - Session-  │ │ - No fail-    │
│   safety    │ │   scoped    │ │   safe        │
└─────────────┘ └─────────────┘ └───────────────┘

Build-Up - 7 Steps

1

Foundation

What is a Table in Snowflake

Concept: Understanding what a table is and its role in storing data.

A table is a structured place to keep data in rows and columns. In Snowflake, tables hold your data so you can ask questions and get answers. Think of a table like a spreadsheet with named columns.

Result

You know that tables are the basic building blocks for storing data in Snowflake.

Understanding tables as structured data holders is the foundation for all database work.

2

Foundation

Basic Syntax to Create a Table

Concept: Learning the simple command to create a table with columns.

To create a table, you use the command: CREATE TABLE table_name (column1 datatype, column2 datatype, ...); For example: CREATE TABLE employees (id INT, name STRING); This makes a permanent table by default.

Result

You can create a basic table that holds data with specified columns.

Knowing the syntax lets you start organizing data immediately.

3

Intermediate

Permanent Tables: Default and Durable

🤔Before reading on: Do you think permanent tables keep data even if you close your session? Commit to your answer.

Concept: Permanent tables store data until you delete them and have full data protection.

Permanent tables are the default type. They keep data safe and stored until you explicitly drop them. Snowflake backs up these tables and protects them from accidental loss. They are good for important data you want to keep long-term.

Result

You understand that permanent tables are reliable and long-lasting storage.

Knowing permanent tables protect data fully helps you decide when data must be kept safe.

4

Intermediate

Temporary Tables: Session-Limited Storage

🤔Before reading on: Do you think temporary tables are visible to other users? Commit to your answer.

Concept: Temporary tables exist only during your session and are private to you.

Temporary tables are created with CREATE TEMPORARY TABLE. They last only while your session is active. When you disconnect, Snowflake deletes them automatically. Other users cannot see your temporary tables. They are useful for intermediate calculations or testing.

Result

You can create tables that clean themselves up automatically after use.

Understanding temporary tables help manage short-term data without cluttering storage.

5

Intermediate

Transient Tables: Less Protection, Lower Cost

🤔Before reading on: Do you think transient tables have the same backup protections as permanent tables? Commit to your answer.

Concept: Transient tables keep data until deleted but with fewer safety features to save costs.

Transient tables are created with CREATE TRANSIENT TABLE. They keep data like permanent tables but Snowflake does not keep fail-safe backups for them. This reduces storage costs but increases risk. Use transient tables for data that can be lost without big problems.

Result

You know how to balance cost and data safety by choosing transient tables.

Knowing transient tables trade safety for cost helps optimize storage expenses.

6

Advanced

Choosing Table Types for Use Cases

🤔Before reading on: Would you use a temporary table for storing customer orders long-term? Commit to your answer.

Concept: Matching table types to real-world needs improves efficiency and safety.

Use permanent tables for critical data you must keep safe. Use temporary tables for session-only data like intermediate results or testing. Use transient tables for data that is important but can be recreated or lost without harm, saving cost. This choice affects backup, recovery, and storage billing.

Result

You can pick the right table type for different data scenarios.

Understanding use cases prevents costly mistakes and data loss.

7

Expert

Internal Storage and Fail-safe Differences

🤔Before reading on: Do you think transient tables have fail-safe backups like permanent tables? Commit to your answer.

Concept: Snowflake stores table data differently based on table type, affecting recovery options.

Permanent tables benefit from Time Travel and Fail-safe features, allowing recovery of dropped or changed data for days. Transient tables support Time Travel but not Fail-safe, so recovery is limited. Temporary tables have no Time Travel or Fail-safe because they are session-scoped. This affects disaster recovery and compliance.

Result

You understand the internal protections and their limits for each table type.

Knowing internal storage and backup differences is key for designing resilient data systems.

Under the Hood

Snowflake stores table data in cloud storage with metadata in its control layer. Permanent tables have full Time Travel and Fail-safe, meaning data changes are stored for a set period and can be recovered. Transient tables skip Fail-safe to reduce storage overhead. Temporary tables exist only in session memory and metadata, disappearing when the session ends.

Why designed this way?

Snowflake designed these table types to balance data safety, cost, and use case flexibility. Permanent tables protect critical data fully. Transient tables reduce cost for less critical data by removing Fail-safe. Temporary tables support quick, session-scoped work without long-term storage.

┌─────────────────────────────┐
│        Snowflake Storage    │
│                             │
│  ┌───────────────┐          │
│  │ Permanent     │◄─────────┤
│  │ Table Data    │          │
│  │ + Time Travel │          │
│  │ + Fail-safe   │          │
│  └───────────────┘          │
│                             │
│  ┌───────────────┐          │
│  │ Transient     │◄─────────┤
│  │ Table Data    │          │
│  │ + Time Travel │          │
│  │ - Fail-safe   │          │
│  └───────────────┘          │
│                             │
│  ┌───────────────┐          │
│  │ Temporary     │◄─────────┤
│  │ Table Data    │          │
│  │ Session-only  │          │
│  │ No backups   │          │
│  └───────────────┘          │
└─────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think temporary tables are shared between users? Commit to yes or no.

Common Belief: Temporary tables are visible and accessible by all users in the database.

Reality: Temporary tables are private to the session that created them and are not visible to other users.

Why it matters: Assuming temporary tables are shared can cause confusion and errors when data is missing or inaccessible.

Quick: Do you think transient tables have the same backup protections as permanent tables? Commit to yes or no.

Common Belief: Transient tables have full backup and fail-safe protections like permanent tables.

Reality: Transient tables do not have Fail-safe backups, only Time Travel, making them less protected.

Why it matters: Misunderstanding this can lead to unexpected data loss and recovery failures.

Quick: Do you think dropping a temporary table deletes data permanently immediately? Commit to yes or no.

Common Belief: Dropping a temporary table permanently deletes its data with no recovery options.

Reality: Temporary tables do not support Time Travel or Fail-safe, so dropping them removes data immediately and irreversibly.

Why it matters: Knowing this prevents accidental loss of important session data.

Quick: Do you think creating a table without specifying type always makes a temporary table? Commit to yes or no.

Common Belief: If you don’t specify, Snowflake creates a temporary table by default.

Reality: Snowflake creates a permanent table by default unless you specify TEMPORARY or TRANSIENT.

Why it matters: Assuming the wrong default can cause data to persist longer than intended or increase costs.

Expert Zone

1

Transient tables still support Time Travel but only for a shorter retention period than permanent tables.

2

Temporary tables consume compute resources but do not incur storage costs since data is session-scoped.

3

Using transient tables in multi-cluster warehouses can affect failover behavior due to lack of Fail-safe.

When NOT to use

Avoid temporary tables for data that must persist beyond a session or be shared. Avoid transient tables for critical data requiring full backup and compliance. Use permanent tables when data durability and recovery are priorities.

Production Patterns

In production, permanent tables store core business data. Temporary tables are used in ETL pipelines for staging and transformations. Transient tables are common for intermediate results or data that can be regenerated, balancing cost and durability.

Connections

Data Backup and Recovery

Builds-on

Understanding table types helps grasp how backup and recovery features like Time Travel and Fail-safe work in Snowflake.

Cloud Storage Cost Optimization

Builds-on

Choosing transient tables over permanent ones is a practical example of balancing cost and data durability in cloud storage.

Session Management in Operating Systems

Analogy in different domain

Temporary tables behaving like session-scoped data is similar to how OS manages temporary files per user session, showing cross-domain resource lifecycle management.

Common Pitfalls

#1 Using a temporary table to store data needed after session ends.

Wrong approach:

CREATE TEMPORARY TABLE sales_backup (id INT, amount FLOAT);
-- Insert data and disconnect session
-- Data is lost after session ends

Correct approach:

CREATE TABLE sales_backup (id INT, amount FLOAT);
-- Data persists until explicitly dropped

Root cause:

Misunderstanding that temporary tables only last for the session lifecycle.

#2 Assuming transient tables have fail-safe backups.

Wrong approach:

CREATE TRANSIENT TABLE logs (event STRING);
-- Rely on fail-safe to recover dropped data (which does not exist)

Correct approach:

CREATE TABLE logs (event STRING);
-- Use permanent table for full backup and fail-safe

Root cause:

Confusing transient tables’ backup features with permanent tables.

#3 Not specifying table type and expecting a temporary table.

Wrong approach:

CREATE TABLE temp_data (id INT);
-- Table is permanent by default, not temporary

Correct approach:

CREATE TEMPORARY TABLE temp_data (id INT);
-- Table is temporary and session-scoped

Root cause:

Not knowing Snowflake’s default table type is permanent.

Key Takeaways

Snowflake tables come in three types: permanent, temporary, and transient, each with different lifetimes and protections.

Permanent tables store data safely and persist until deleted, suitable for critical data.

Temporary tables exist only during your session and are private, ideal for short-term work.

Transient tables reduce storage costs by skipping fail-safe backups but still keep data until deleted.

Choosing the right table type balances data safety, cost, and use case needs.

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