Creating tables (permanent, temporary, transient) in Snowflake - Step-by-Step CLI Walkthrough

Introduction

When you want to store data in Snowflake, you create tables. Tables can be permanent, temporary, or transient. Each type controls how long data stays and how it is managed.

When you need to save data permanently for long-term use and backup.

When you want to store data only during your session and discard it after.

When you want to keep data temporarily without fail-safe backup to save costs.

When you want to test data transformations without affecting permanent storage.

When you want to manage data lifecycle with different retention and recovery needs.

Commands

This command creates a permanent table named my_permanent_table that stores data permanently until you delete it.

Terminal

CREATE TABLE my_permanent_table (id INT, name STRING);

Expected Output

Query executed successfully.

This command creates a temporary table named my_temp_table that exists only during your session and is dropped automatically after.

Terminal

CREATE TEMPORARY TABLE my_temp_table (id INT, name STRING);

Expected Output

Query executed successfully.

This command creates a transient table named my_transient_table that stores data without fail-safe backup but persists beyond your session.

Terminal

CREATE TRANSIENT TABLE my_transient_table (id INT, name STRING);

Expected Output

Query executed successfully.

This command lists all tables starting with 'my_' and ending with '_table' so you can verify your tables were created.

Terminal

SHOW TABLES LIKE 'my\_%\_table';

Expected Output

created_on                      name                  database_name  schema_name  kind      comment  owner

2024-06-01 12:00:00.000 +0000  MY_PERMANENT_TABLE    MY_DB          PUBLIC      TABLE     

2024-06-01 12:00:00.000 +0000  MY_TEMP_TABLE         MY_DB          PUBLIC      TEMPORARY 

2024-06-01 12:00:00.000 +0000  MY_TRANSIENT_TABLE    MY_DB          PUBLIC      TRANSIENT

Key Concept

If you remember nothing else, remember: permanent tables keep data forever, temporary tables last only during your session, and transient tables keep data without backup but beyond your session.

Common Mistakes

Creating a temporary table and expecting it to persist after the session ends.

Temporary tables are automatically dropped when your session ends, so data is lost.

Use permanent or transient tables if you want data to persist beyond your session.

Using transient tables without understanding they have no fail-safe backup.

Data in transient tables can be lost if accidentally deleted because fail-safe is disabled.

Use transient tables only when you can tolerate data loss and want to save storage costs.

Not specifying table type and assuming default is temporary.

The default table type is permanent, so data will persist and incur storage costs.

Explicitly specify TEMPORARY or TRANSIENT if you want those behaviors.

Summary

Use CREATE TABLE for permanent tables that store data indefinitely.

Use CREATE TEMPORARY TABLE for session-only tables that disappear after you disconnect.

Use CREATE TRANSIENT TABLE for tables that persist but skip fail-safe backup to save costs.

Verify table creation with SHOW TABLES command.