Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Create a Transient Table in Snowflake: Syntax and Example

To create a transient table in Snowflake, use the CREATE TRANSIENT TABLE statement followed by the table name and column definitions. Transient tables store data temporarily without fail-safe, making them ideal for short-term data storage.
📐

Syntax

The basic syntax to create a transient table in Snowflake is:

  • CREATE TRANSIENT TABLE: Keyword to create a transient table.
  • table_name: The name you assign to the table.
  • (column definitions): List of columns with data types.

Transient tables do not have fail-safe, so they are cheaper but less durable than permanent tables.

sql
CREATE TRANSIENT TABLE table_name (
  column1 DATA_TYPE,
  column2 DATA_TYPE
);
💻

Example

This example creates a transient table named temp_employees with three columns: id, name, and hire_date. It shows how to define data types and create the table.

sql
CREATE TRANSIENT TABLE temp_employees (
  id INT,
  name STRING,
  hire_date DATE
);
Output
Table TEMP_EMPLOYEES successfully created.
⚠️

Common Pitfalls

Common mistakes when creating transient tables include:

  • Using CREATE TABLE instead of CREATE TRANSIENT TABLE when transient behavior is desired.
  • Expecting fail-safe recovery on transient tables; transient tables do not have fail-safe.
  • Not specifying column data types correctly, causing errors.

Example of wrong and right usage:

sql
-- Wrong: creates a permanent table instead of transient
CREATE TABLE employees (
  id INT,
  name STRING
);

-- Right: creates a transient table
CREATE TRANSIENT TABLE employees (
  id INT,
  name STRING
);
📊

Quick Reference

KeywordDescription
CREATE TRANSIENT TABLECreates a transient table without fail-safe
table_nameName of the transient table
column_name DATA_TYPEDefines each column and its data type
No fail-safeTransient tables do not have fail-safe recovery
Use for temporary dataIdeal for short-term or staging data
✅

Key Takeaways

Use CREATE TRANSIENT TABLE to create tables without fail-safe in Snowflake.
Transient tables are cheaper but do not have fail-safe data recovery.
Always specify column names and data types correctly.
Transient tables are best for temporary or staging data.
Do not confuse transient tables with permanent tables.