Creating tables (permanent, temporary, transient) in Snowflake - Quick Revision & Summary

Recall & Review

beginner

What is a permanent table in Snowflake?

A permanent table stores data persistently until explicitly deleted. It keeps data safe even after sessions end or system restarts.

beginner

How does a temporary table differ from a permanent table?

A temporary table exists only during the user session. It is automatically dropped when the session ends, so data is not saved permanently.

intermediate

What is a transient table in Snowflake?

A transient table stores data persistently but does not have fail-safe protection. It is useful for data that can be lost without major impact.

intermediate

Which table type in Snowflake has fail-safe protection?

Permanent tables have fail-safe protection, which helps recover data after accidental loss for a limited time.

beginner

Write the basic SQL command to create a temporary table named 'temp_sales' with columns 'id' (integer) and 'amount' (decimal).

CREATE TEMPORARY TABLE temp_sales (id INT, amount DECIMAL(10,2));

Which Snowflake table type is automatically dropped at the end of the session?

A Permanent table

B Transient table

C External table

D Temporary table

Temporary tables exist only during the session and are dropped automatically when the session ends.

Which table type does NOT have fail-safe protection?

A Transient table

B Permanent table

C Temporary table

D All have fail-safe

Transient tables do not have fail-safe protection, unlike permanent tables.

What keyword is used to create a transient table in Snowflake?

A CREATE TEMPORARY TABLE

B CREATE TRANSIENT TABLE

C CREATE PERMANENT TABLE

D CREATE EXTERNAL TABLE

The keyword TRANSIENT is used to create transient tables.

Which table type is best for storing data that must be kept permanently and safely?

A Permanent table

B Transient table

C Temporary table

D External table

Permanent tables store data persistently with fail-safe protection.

What happens to data in a temporary table after the session ends?

A Data is saved permanently

B Data is moved to permanent table

C Data is lost and table is dropped

D Data is archived automatically

Temporary tables and their data are dropped when the session ends.

Explain the differences between permanent, temporary, and transient tables in Snowflake.

Think about how long the data lasts and protection levels.

Write the SQL commands to create one permanent, one temporary, and one transient table named 'perm_table', 'temp_table', and 'trans_table' with a single column 'id' integer.

Use the keywords TEMPORARY and TRANSIENT for special tables.