Snowflake table types: Explained

It’s Monday morning. You and a coworker are discussing our favorite topic—Snowflake infrastructure strategy. Which table should you use?

She asks you to try out a Hybrid table to collect user events from your web application. You ask her whether an Event table would work, and the both of you become sidetracked while you try to figure out what already exists in the warehouse. Then you ask yourselves what the differences even are, and, well, no one knows.

Table types in Snowflake can get confusing, and in reality, there are a few types that are very similar, and some that are purpose-built for specific use cases.

Let's go table by table, looking at what each Snowflake table does, what it's best for, and when you might want to use it.

Types of tables in Snowflake

Permanent, Temporary, and Transient tables are the most standard table types in Snowflake, and will cover the majority of your use cases. That said, there are a handful of specialized table types that could come in handy for specific situations. Let’s look at each type of Snowflake table, what it does, and when the best time to use it is. 

1. Permanent tables

When you go to create a table in Snowflake, a permanent table will be your default option. They’re your go-to option for long-term data storage. They are designed to keep your data secure and recoverable over extended periods, with support for Time Travel (up to 90 days) and a 7-day fail-safe. If you don’t know what table type you’re using, this would be a good guess.

Best for:
Storing critical business data that requires reliability, historical tracking, and disaster recovery.

When to use a Permanent table:

1. Storing customer profiles and purchase histories for long-term retention and analysis.
2. Maintaining transactional records for annual audits and compliance requirements.
3. Pretty much anything else—there’s rarely a bad time for them.

2. Temporary tables

Temporary tables are built for data that’s only needed for your current session. Afterward, it’s purged. Since the data is automatically dropped when the session ends, they’re lightweight and cost-effective. You might use them to temporarily stage data for cleaning prior to merging into a dataset that you’d like to backfill.

Best for:
Short-term data storage and transformations during ETL or analytics workflows.

When to use a Temporary table:

1. Running quick aggregations or transformations without needing to store the results.
2. Storing intermediate results during a complex query or pipeline process.
3. Holding data temporarily before inserting into permanent tables.

3. Transient tables

Transient tables are like a middle ground between temporary and permanent tables. They’re similar to permanent tables, with the key difference being that they lack fail-safe protection and have limited Time Travel (up to 1 day). Typically, you’d use them for intermediate storage needs where the data you’re working with isn’t mission-critical, but still needs to be accessible for more than one session.

Best for:
Intermediate storage for reusable data that isn’t backed by a long recovery window.

When to use a Transient table:

1. Holding data before it’s cleaned and moved into permanent tables.
2. Storing partially aggregated data for reporting or further calculations.
3. Acting as a temporary buffer for data transformations during multi-step ETL jobs.

4. External tables

External tables are made to let you query data stored outside of your Snowflake warehouse. They're read-only, so there's only so much you can do with External tables. Time Travel and fail-safe are not supported, but External tables are an easy way to integrate with data lakes or archived datasets without ingesting them into Snowflake.

Best for:
Querying datasets stored externally without importing them into Snowflake.

When to use an External table:

1. Querying archived log files stored in S3 for anomaly detection.
2. Analyzing historical datasets stored in external cloud storage without duplicating them.
3. Accessing infrequently used datasets directly from external storage to minimize Snowflake storage costs.

5. Dynamic tables

Dynamic tables are Snowflake’s answer to real-time and near-real-time analytics. These tables allow users to materialize the results of a query, using both object references that are part of any query along with an input for desired lag. Functionally, this will create a new table that references and merges data from upstream objects that update on a specified schedule.

Learn how dynamic tables in Snowflake compare to dbt.

Best for:
Near real-time data pipelines and live dashboards.

When to use a Dynamic table:

1. Keeping dashboards updated in near real-time with live sales data.
2. Aggregating sensor data to track equipment performance in real time.
3. Monitoring live customer behavior on an e-commerce site for targeted recommendations.

6. Event tables

Event tables are purpose-built for handling time-series or event-driven data (like user activity, for example). They're best at logging data, rather than regularly running transactions, and are particularly handy for debugging, monitoring, and compliance.

Best for:
Anyone who’s looking to develop on top of Snowflake should consider setting up event tables for debugging (provided you familiarize yourself with the cost model first).

When to use an Event table:

1. Capturing and analyzing website clickstream data to improve user experience.
2. Logging sensor data from connected devices to track usage patterns.
3. Storing transaction events in real time to identify anomalies and flag potential fraud.

7. Hybrid tables

Without getting too into the weeds of how data is stored and retrieved, let’s just say that hybrid tables are generally optimized for one of two workloads: use of data (e.g. for analytics) and storage of data (e.g. for transactions). Hybrid tables promise to support both of these workloads with optimizations for both a high volume of transactions (e.g. inserts and updates) and single-row lookups.

Best for:
Blending operational and analytical workloads for transactional systems.

When to use a Hybrid table:

1. Managing active user sessions in a web application while analyzing user behavior.
2. Tracking stock levels in real time for e-commerce or retail operations.
3. Providing instant analytics on real-time transactional data, such as order statuses.

8. Iceberg tables

Despite the cold water-related name, “Iceberg” wasn’t a term created by or for Snowflake. It’s actually a reference to the table format created by Netflix and is now Apache open-source-licensed. Snowflake has supported Iceberg file formats in the past, but only via external tables. They’re great for managing robust data sets in a distributed storage environment.

Best for:
Managing large, distributed datasets with advanced control.

When to use an Iceberg table:

1. Managing supply chain data across multiple regions for efficient distribution.
2. Organizing and querying a large-scale data lake for complex analytics.
3. Ensuring data consistency during multi-step updates in distributed systems.

Choosing the right Snowflake table

Not so confusing after all, right? Between Snowflake's more standard table types (Permanent, Temporary, Transient, External) and their newer ones (Dynamic, Event, Hybrid, Iceberg), there's a table that's tailor-fit for pretty much any use case. Or, when in doubt, just play it safe and go with a Permanent one.

Editor's note: was originally published in November 2023, and updated for accuracy and freshness in November 2024.