How to create a temporary table in Snowflake

What is a temporary table?

Temporary tables are tables in Snowflake that only exist for the current session, after which they and their data are deleted.

The syntax for creating one is similar to creating a regular table:

create temporary table <table_name> (<column_name> <data_type>);

Creating a temporary table in Snowflake

Creating a temporary table works in much the same way as a regular table. Which means that there are two common approaches.

Create a temporary table using insert into

The first way is to create the table structure, and then fill this table with data through insertion.

-- Create the table object
create temporary table temp_orders (order_id number, order_date date);

-- Insert data row by row
insert into temp_orders 
	values (1,'2023-01-01');

-- Or, insert data from select statement
insert into temp_orders
	select 
		order_id, order_date 
	from orders 
	where order_date >= '2023-01-01';

Create a temporary table as select query

The second way is to create a temporary table by using the results of a select query, this is the most straightforward approach.

create temporary table temp_orders as
	select 
		order_id, order_date 
	from orders 
	where order_date >= '2023-01-01';

Building further on the above query, we could also add the create or replace constraint, to make sure we overwrite the current temporary table.

create or replace temporary table temp_orders as
	select 
		order_id, order_date 
	from orders 
	where order_date >= '2023-01-01';

When should you use a temporary table?

Temporary tables have a limited set of use cases, but they can be incredibly useful when employed correctly.

Let's walk through a good use case.

Say you want to create several data visualisations from the same dataset in a reporting tool. Instead of running the same full query multiple times, you could load the data into a temporary table and then query that instead.

This would most likely be cheaper and quicker than making the same full query multiple times, as you won't be computing and creating the same dataset repeatedly, just once. Then, after the visualisations are created, the table is dropped automatically, leaving no data debt behind.

Common questions regarding temporary tables

Temporary tables can be difficult to understand, especially as there are several alternatives that cover similar use cases. Let's try to clear things up.

What are the differences between temporary tables and CTEs?

Temporary tables and CTEs (Common Table Expressions) can be used to solve similar problems, but there are some major differences:

1. Temporary tables create an object in the instance which can be queried repeatedly, whilst a CTE is a part of a single SQL query and does not create any objects
2. Temporary tables are primarily used when you have a large dataset that you want to refer to multiple times for a short period of time
3. Temporary tables can be used as a way to improve the performance of a query, whilst CTEs almost never speed up a query

What are the differences between temporary and transient tables?

Transient and temporary tables have very similar features, but there is one key difference:

1. Transient tables are only deleted when explicitly dropped, unlike temporary tables which are automatically dropped after the session

This means that transient tables live in the space between temporary and permanent tables, where you don't want persistent permanent data, but you still need the data to exist for an extended period of time.

Related articles

Tables

Create table
Create or replace table
Create table as select in
Drop table

Views

Materialized view vs table
View vs table

References

Temporary and transient table documentation