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.