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:
- 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
- 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
- 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:
- 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.