create temporary table snowflake

HaC 2025

2 min read

·

08-10-2024

46

0

Share

Creating Temporary Tables in Snowflake: A Step-by-Step Guide

Snowflake's temporary tables offer a powerful way to store and manipulate data within a specific session, making them invaluable for complex queries, data exploration, and performance optimization.

What are Temporary Tables?

In simple terms, temporary tables are like temporary storage bins for your data within a Snowflake session. They exist only for the duration of your session and are automatically dropped when the session ends. This makes them ideal for:

• Intermediate Data Storage: Storing results of complex computations or subqueries for later use.
• Data Exploration: Experimenting with different data transformations and aggregations without affecting your permanent tables.
• Performance Optimization: Reducing the need to repeatedly query the same data, especially for large datasets.

Types of Temporary Tables in Snowflake

Snowflake offers two types of temporary tables:

• Global Temporary Tables: Accessible across all sessions within a warehouse. Useful for sharing data between different queries or procedures.
• Local Temporary Tables: Accessible only within the session where they are created. Ideal for data manipulation and analysis within a single session.

Creating a Temporary Table

Let's dive into creating a temporary table in Snowflake. We'll focus on creating a local temporary table, but the syntax for global temporary tables is very similar.

1. Using the CREATE TEMPORARY TABLE Statement

CREATE TEMPORARY TABLE my_temp_table (
    column1 VARCHAR(100),
    column2 INT,
    column3 DATE
);

Explanation:

• CREATE TEMPORARY TABLE: The statement to create a temporary table.
• my_temp_table: The name of your temporary table.
• column1 VARCHAR(100), column2 INT, column3 DATE: Defines the table schema, including column names and data types.

2. Adding Data to the Temporary Table

Once created, you can populate the temporary table using standard INSERT statements:

INSERT INTO my_temp_table (column1, column2, column3)
VALUES ('Value 1', 10, '2023-12-12'), ('Value 2', 20, '2023-12-13');

3. Querying the Temporary Table

You can interact with the temporary table just like any other table in Snowflake.

SELECT * FROM my_temp_table;

Practical Example

Let's illustrate with a practical scenario. Imagine you need to analyze customer data based on their age group.

-- Create a temporary table to store age groups
CREATE TEMPORARY TABLE customer_age_groups (
    age_group VARCHAR(20),
    customer_count INT
);

-- Calculate customer counts for different age groups
INSERT INTO customer_age_groups (age_group, customer_count)
SELECT 
    CASE
        WHEN age < 18 THEN 'Under 18'
        WHEN age BETWEEN 18 AND 30 THEN '18-30'
        ELSE 'Over 30'
    END AS age_group,
    COUNT(*) AS customer_count
FROM customers;

-- Query the temporary table to view the results
SELECT * FROM customer_age_groups;

Key Advantages of Temporary Tables:

• Session-Scoped: Temporary tables are isolated to your current session, eliminating potential conflicts with other users.
• Data Integrity: Changes made to temporary tables don't affect your permanent data, ensuring data integrity.
• Performance Gains: Storing frequently used data locally in a temporary table can significantly improve query performance.

Conclusion

Creating temporary tables in Snowflake is a powerful tool for simplifying complex queries, exploring data, and optimizing performance. Understanding the different types of temporary tables and their usage patterns will allow you to write more efficient and effective Snowflake code.