What you will be able to do
- Compare permanent, temporary and transient tables by persistence, Time Travel and Fail-safe
- Predict how a temporary table behaves when it has the same name as an existing table
- Explain Iceberg storage and catalog options and how Iceberg tables differ from standard and external tables
- State what external tables can and cannot do
1.Permanent, temporary and transient tables
A plain CREATE TABLE gives you a permanent table, which is the default type. Snowflake also offers two types for transitory data that doesn't need to be kept long term: temporary and transient. The three types differ in how long the table lives and in how much recovery protection it gets, through Time Travel and Fail-safe.
| Type | Persistence | Time Travel retention (days) | Fail-safe (days) |
|---|---|---|---|
| Temporary | Remainder of session | 0 or 1 (default is 1) | 0 |
| Transient | Until explicitly dropped | 0 or 1 (default is 1) | 0 |
| Permanent (Standard Edition) | Until explicitly dropped | 0 or 1 (default is 1) | 7 |
| Permanent (Enterprise Edition and higher) | Until explicitly dropped | 0 to 90 (default is configurable) | 7 |
A temporary table exists only in the session that created it. Other users and sessions can't see it. When the session ends, its data is purged and can't be recovered by you or by Snowflake. Its Time Travel period can be 1 day, but in practice it lasts 24 hours or until the session ends, whichever comes first. Use temporary tables for ETL work or session-specific data.
A transient table lasts until someone drops it, and anyone with the right privileges can use it. It works like a permanent table except that it has no Fail-safe period. You pay for its storage but not for Fail-safe. The downside is that once Time Travel expires, the data is gone for good, so use transient tables only for data you don't need to protect or can rebuild outside Snowflake. You can't configure the Fail-safe period for any table type. Temporary tables also add to your storage bill while they exist, so drop large ones explicitly instead of leaving them in a session that stays open for days.
Checkpoint 1 of 6· Match them up
Match each table type to the property that sets it apart
Tap a term, then the definition that fits it.
Temporary tables are scoped to a session. Transient and permanent tables both last until dropped, and the only difference between them is Fail-safe.
“Transient tables are similar to permanent tables with the key difference that they do not have a Fail-safe period.”Source: docs.snowflake.com
Sources1
2.Creating temporary and transient tables, and the name-shadowing trap
You create either type by adding a keyword to CREATE TABLE. For temporary tables you can write TEMPORARY or the shorter TEMP. Creating a temporary table doesn't require the CREATE TABLE privilege on the schema.
CREATE TEMPORARY TABLE mytemptable (id NUMBER, creation_date DATE);Checkpoint 2 of 6· Fill the gap
Which keyword creates a table that persists across sessions but has no Fail-safe?
CREATE ? TABLE mytranstable (id NUMBER, creation_date DATE);TRANSIENT creates a table that lasts until it is dropped and has no Fail-safe. TEMPORARY tables end with the session.
Source: docs.snowflake.comTemporary tables belong to a database and schema like any other table, but because they are tied to a session, they don't have to follow the usual uniqueness rules. You can create a temporary table with the same name as an existing table in the same schema. Within your session, the temporary table then takes precedence and hides the other table, so every query and DDL statement in that session acts on the temporary table. Watch for this when you drop a table and then restore it with Time Travel, or when you use CREATE OR REPLACE.
Three more rules are worth knowing. You can't convert a temporary or transient table to any other type after it is created. Every table created in a transient schema is transient, and so is every schema created in a transient database. And if you clone a permanent table as a transient table, you get a zero-copy clone that initially shares the original's micro-partitions.
Checkpoint 3 of 6· Check yourself
A schema contains a permanent table named ORDERS. In your session you run CREATE TEMPORARY TABLE orders (...) and then DROP TABLE orders. Which table is dropped?
A temporary table can share a name with an existing table, and within the session it hides that table, so the DROP affects the temporary one.
“the temporary table takes precedence in the session over any other table with the same name in the same schema.”Source: docs.snowflake.com
Sources1
3.Apache Iceberg™ tables
Apache Iceberg™ tables give you the performance and query semantics of ordinary Snowflake tables, while the data lives in cloud storage you manage. They are designed for existing data lakes that you can't, or don't want to, move into Snowflake. Iceberg is an open table format that sits on top of data files in open formats. It supports ACID transactions, schema evolution, hidden partitioning and table snapshots. Snowflake supports Iceberg tables stored as Apache Parquet™ files.
You make two independent choices for an Iceberg table. The first is where the files are stored. The second is which catalog tracks the table's current metadata pointer.
| Choice | Option | What it means |
|---|---|---|
| Storage | Snowflake storage (EXTERNAL_VOLUME = SNOWFLAKE_MANAGED) | Snowflake stores and manages the files; permanent tables are protected by Fail-safe; Snowflake bills the storage |
| Storage | External volume (Amazon S3, Google Cloud Storage, Azure Storage) | You manage the files and are responsible for data protection and recovery; no Fail-safe; your cloud provider bills the storage |
| Catalog | Snowflake as the Iceberg catalog | Full Snowflake platform support with read and write access; Snowflake handles lifecycle maintenance such as compaction |
| Catalog | External catalog through a catalog integration | Limited Snowflake platform support; for example, a table managed by AWS Glue or Snowflake Open Catalog |
An external volume is an account-level object that stores the IAM entity Snowflake uses to connect to your storage. One external volume can serve many Iceberg tables. A catalog integration is also an account-level object. It tells Snowflake how table metadata is organized when Snowflake is not the catalog. Interoperability with other engines works in both directions: you can sync a Snowflake-managed Iceberg table to Snowflake Open Catalog so third-party engines can query it, and you can use Snowflake to query or write to tables that Open Catalog manages.
Compared with a standard table, the main difference is where the data lives. Iceberg data sits in open-format files, and in the external-volume case those files are in storage you own and protect yourself.
Checkpoint 4 of 6· Check yourself
A team keeps its Iceberg table files in its own S3 bucket and connects to them through an external volume. Which statement is true?
With external volume storage, the customer manages the files. Fail-safe applies only to permanent Iceberg tables in Snowflake storage.
“Snowflake doesn’t provide Fail-safe for these tables, and your cloud provider bills you for storage.”Source: docs.snowflake.com
Checkpoint 5 of 6· Check yourself
An Iceberg table's metadata is managed by AWS Glue. Which Snowflake object do you need so that Snowflake can work with it?
When Snowflake is not the Iceberg catalog, a catalog integration tells Snowflake how to find the table's metadata. An external volume still provides access to the files.
“you need a catalog integration if your table is managed by AWS Glue.”Source: docs.snowflake.com
Sources2
4.External tables
An external table lets you query files in an external stage as if they were a Snowflake table. Snowflake doesn't store or manage the stage. It stores only file-level metadata such as file names and version identifiers. External tables can read any format that COPY INTO <table> supports, except XML.
External tables are read-only, so you can't run DML against them. You can query them, join them and build views on them. Queries can be slower than against native tables. To speed them up, you can create a materialized view on the external table. For Parquet files, Snowflake recommends using Iceberg tables instead.
That recommendation shows how the two types differ. Both leave data outside Snowflake-managed table storage, but an external table only reads files in a stage, while an Iceberg table adds a table format with ACID transactions and supports writes.
Checkpoint 6 of 6· Check yourself
Queries against an external table over Parquet files in S3 are too slow, and the team also wants to update rows. Which approach do the docs point toward?
External tables are read-only and can be slower than native tables. For Parquet, the docs recommend Iceberg tables, which also support writes.
“For optimal query performance when you work with Parquet files, consider using Apache Iceberg™ tables instead.”Source: docs.snowflake.com
Sources3
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Transient tables get the same 7-day Fail-safe as permanent tables, just with shorter Time Travel.Why is that wrong?
Transient and temporary tables have no Fail-safe at all. Once Time Travel expires, the data can't be recovered.
Covered in Permanent, temporary and transient tables
2.External tables support INSERT, UPDATE and DELETE like any other table.Why is that wrong?
External tables are read-only. They support queries, joins and views, but not DML.
Covered in External tables
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“In addition to permanent tables, which is the default table type when creating tables, Snowflake supports defining tables as either temporary or transient.”
↩︎ Permanent, temporary and transient tables“When the session ends, data in the table is purged and is not recoverable”
↩︎ Permanent, temporary and transient tables“The Fail-safe period is not configurable for any table type.”
↩︎ Permanent, temporary and transient tables“creating a temporary table does not require the CREATE TABLE privilege on the schema in which the object is created.”
↩︎ Creating temporary and transient tables, and the name-shadowing trap“After creation, temporary tables cannot be converted to any other table type.”
↩︎ Creating temporary and transient tables, and the name-shadowing trap“All tables created in a transient schema, as well as all schemas created in a transient database, are transient by definition.”
↩︎ Creating temporary and transient tables, and the name-shadowing trap“the data in these tables cannot be recovered after the Time Travel retention period passes.”
↩︎ Exam trap 1“When a permanent table is deleted, it enters Fail-safe for a 7-day period.”
↩︎ Prediction“Transient tables are similar to permanent tables with the key difference that they do not have a Fail-safe period.”
↩︎ Checkpoint“the temporary table takes precedence in the session over any other table with the same name in the same schema.”
↩︎ Checkpoint - 2.
“combine the performance and query semantics of typical Snowflake tables with external cloud storage that you manage.”
↩︎ Apache Iceberg™ tables“Snowflake supports Iceberg tables that use the Apache Parquet™ file format.”
↩︎ Apache Iceberg™ tables“Sync a Snowflake-managed Iceberg table with Snowflake Open Catalog so that third-party compute engines can query the table.”
↩︎ Apache Iceberg™ tables“An Iceberg table that uses an external catalog provides limited Snowflake platform support.”
↩︎ Apache Iceberg™ tables“Snowflake doesn’t provide Fail-safe for these tables, and your cloud provider bills you for storage.”
↩︎ Checkpoint“you need a catalog integration if your table is managed by AWS Glue.”
↩︎ Checkpoint - 3.
“query data stored in an external stage as if the data were inside a table in Snowflake.”
↩︎ External tables“To improve query performance, you can use a materialized view based on an external table.”
↩︎ External tables“External tables are read-only. You can’t perform data manipulation language (DML) operations on external tables.”
↩︎ Exam trap 2“For optimal query performance when you work with Parquet files, consider using Apache Iceberg™ tables instead.”
↩︎ Checkpoint