Creating tables (permanent, temporary, transient) in Snowflake - Practice Exercises

Challenge - 5 Problems

🧠 Conceptual

intermediate

Difference in data retention for table types

Which type of Snowflake table retains data even after the user session ends and supports Time Travel and Fail-safe features?

A Temporary table

B Transient table

C External table

D Permanent table

💡 Hint

Think about which table type is designed for long-term storage with full data protection.

Permanent tables store data persistently and support Time Travel and Fail-safe. Temporary tables exist only during the session. Transient tables skip Fail-safe. External tables reference external storage.

❓ service_behavior

intermediate

Session scope of temporary tables

What happens to a temporary table in Snowflake when the user session that created it ends?

A The temporary table is automatically dropped and data is lost.

B The temporary table persists and can be accessed by other sessions.

C The temporary table is converted to a permanent table.

D The temporary table is archived for 7 days.

💡 Hint

Consider the lifespan of temporary tables relative to user sessions.

Temporary tables exist only during the session that created them. When the session ends, the table and its data are dropped automatically.

❓ Configuration

advanced

Creating a transient table with clustering

Which SQL statement correctly creates a transient table named sales_data with clustering on the region column?

A CREATE TABLE TRANSIENT sales_data (id INT, region STRING) CLUSTER ON region;

B CREATE TRANSIENT TABLE sales_data (id INT, region STRING) CLUSTER BY (region);

C CREATE TRANSIENT TABLE sales_data (id INT, region STRING) CLUSTERED BY region;

D CREATE TRANSIENT TABLE sales_data (id INT, region STRING) CLUSTER BY region;

💡 Hint

Check the correct syntax for clustering keys in Snowflake table creation.

The correct syntax uses CLUSTER BY (column_name) after the column definitions. Option B matches this syntax exactly.

❓ security

advanced

Data protection differences between permanent and transient tables

Which statement correctly describes the difference in data protection features between permanent and transient tables in Snowflake?

A Permanent tables support Time Travel and Fail-safe, transient tables do not support Fail-safe.

B Permanent tables support Time Travel and Fail-safe, transient tables support only Time Travel.

C Transient tables support Time Travel and Fail-safe, permanent tables do not.

D Both permanent and transient tables do not support Fail-safe.

💡 Hint

Recall which table type skips Fail-safe to reduce storage costs.

Permanent tables support both Time Travel and Fail-safe. Transient tables support Time Travel but skip Fail-safe, reducing storage costs but lowering data protection.

❓ Architecture

expert

Choosing table type for temporary session data with no recovery needs

You need to store intermediate data during a user session that should be automatically removed when the session ends. The data does not require Time Travel or Fail-safe. Which table type should you choose?

A External table

B Transient table

C Temporary table

D Permanent table

💡 Hint

Consider the table type that exists only during the session and does not keep data after.

Temporary tables exist only during the session and are dropped automatically afterward. They do not support Time Travel or Fail-safe, making them ideal for temporary session data.