COF-C03 Question 103
Single answerTransientA data engineering team loads large daily staging datasets into Snowflake and keeps them for 5 days before deleting them. The team wants to reduce storage costs associated with historical data retention, but they still need protection from accidental drops and the ability to recover the data for a short period if a mistake occurs. Which table type best meets these requirements?
- A
A permanent table, because it supports Fail-safe and the longest retention options
- B
A temporary table, because it avoids all historical storage costs and can be recovered after the session ends
- C
A transient table, because it supports Time Travel but does not incur Fail-safe storage costs
- D
An external table, because data recovery is handled automatically outside Snowflake
Show answer and explanation
Correct answer: C
Explanation
The best answer is a transient table. In Snowflake, transient tables are intended for non-permanent data that must survive beyond a session but does not need Fail-safe protection. This makes them well suited for staging, intermediate transformation outputs, and short-lived operational datasets. Compared to permanent tables, transient tables can reduce storage costs because they do not incur Fail-safe storage. Unlike temporary tables, they persist until explicitly dropped and are accessible across sessions. Snowflake documentation and best practices describe transient objects as appropriate when Time Travel is useful but the additional data protection and cost of Fail-safe are not required.
- A. Incorrect.
Incorrect. Permanent tables do provide Time Travel and Fail-safe protection, but the scenario specifically emphasizes reducing storage costs related to historical retention. Permanent tables incur Fail-safe storage costs after the Time Travel period, so they are not the best fit when short-term recoverability is needed without the extra Fail-safe cost.
- B. Incorrect.
Incorrect. Temporary tables exist only for the duration of the session and are not suitable for data that must remain available for 5 days across sessions. In addition, once the session ends, the table is purged and is not intended for recovery in the way described.
- C. Correct.
Correct. Transient tables are designed for data that needs to persist beyond a single session but does not require the same level of data protection as permanent tables. They support Time Travel for a limited retention period, which helps protect against accidental changes or drops, but they do not have a Fail-safe period. This makes them a common choice for staging or intermediate data when cost reduction is important.
- D. Incorrect.
Incorrect. External tables reference data stored outside Snowflake, typically in cloud object storage. They do not provide Snowflake-managed data recovery for internal table data and are not the right choice for this staging-table use case.