Snowflake Features & Architecture - Tables and Views

Question 1

Table Type - Permanent

Answer 1

• Default table type
• Exists until explicitly dropped
• Time Travel - 90 days
• Fail-safe - yes

Question 2

Table Type - Temporary

Answer 2

• Used for transitory data
• Persist for duration of a session
• Time Travel - 1 day
• Fail-safe - No

Question 3

Table Type - Transient

Answer 3

• Exists until explicitly dropped
• No fail-safe period
• Time Travel - 1 day
• Fail-safe - No

Question 4

Table Type - External

Answer 4

• Query data outside Snowflake
• Read-only table
• Time Travel- 0 days
• Fail-safe - No

Question 5

View Type - Standard

Answer 5

CREATE VIEW MY_VIEW AS
SELECT COL1, COL2 FROM MY_TABLE;

• Does not contribute to storage cost.
• If source table is dropped, querying view returns error.
• Used to restrict contents of a table.

Question 6

View Type - Materialized

Answer 6

CREATE MATERIALIZED VIEW MY_VIEW AS
SELECT COL1, COL2, FROM MY_TABLE;

• Stores results of a query definition and periodically refreshes it.
• Incurs cost as a serverless feature.
• Used to boost performance of external tables.

Question 7

View Type - Secure

Answer 7

CREATE SECURE VIEW MY_VIEW AS
SELECT COL1, COL2 FROM MY_TABLE;

• Both standard and materialized can be secure.
• Underlying query definition only visible to authorized users.
• Some query optimizations bypassed to improve security.