Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Create Temporary Table in Snowflake: Syntax and Example

In Snowflake, you create a temporary table using CREATE TEMPORARY TABLE followed by the table name and columns. Temporary tables exist only during your session and are automatically dropped when the session ends.
📐

Syntax

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

  • CREATE TEMPORARY TABLE: Keyword to create a temporary table.
  • table_name: Name you choose for your temporary table.
  • (column_name data_type, ...): List of columns with their data types.

Temporary tables are session-scoped and automatically dropped at session end.

sql
CREATE TEMPORARY TABLE table_name (
  column1 STRING,
  column2 INT
);
💻

Example

This example creates a temporary table named temp_employees with two columns: name and age. It then inserts two rows and selects all data from the table.

sql
CREATE TEMPORARY TABLE temp_employees (
  name STRING,
  age INT
);

INSERT INTO temp_employees (name, age) VALUES ('Alice', 30), ('Bob', 25);

SELECT * FROM temp_employees;
Output
NAME | AGE -------|---- Alice | 30 Bob | 25
⚠️

Common Pitfalls

Common mistakes when creating temporary tables in Snowflake include:

  • Forgetting that temporary tables are session-specific and disappear after the session ends.
  • Trying to access a temporary table from a different session or user.
  • Using CREATE TABLE instead of CREATE TEMPORARY TABLE when a temporary table is needed.

Always ensure you create the table as temporary if you want it to be session-scoped.

sql
/* Wrong: Creates a permanent table instead of temporary */
CREATE TABLE temp_employees (
  name STRING,
  age INT
);

/* Right: Creates a temporary table */
CREATE TEMPORARY TABLE temp_employees (
  name STRING,
  age INT
);
📊

Quick Reference

KeywordDescription
CREATE TEMPORARY TABLECreates a temporary table that lasts only for the session
table_nameName of the temporary table
column_name data_typeDefines columns and their data types
Session ScopeTemporary tables are visible only in the current session
Auto DropTemporary tables are dropped automatically when the session ends
✅

Key Takeaways

Use CREATE TEMPORARY TABLE to create session-scoped tables in Snowflake.
Temporary tables are automatically dropped at the end of your session.
Temporary tables cannot be accessed outside the session they were created in.
Always specify columns and data types when creating the table.
Avoid confusing temporary tables with permanent tables by using the correct keyword.