COF-C03 Question 102
Single answerTransientA data engineering team loads high-volume clickstream data into Snowflake and keeps it for only 5 days before it is re-created from source systems. The team wants to reduce storage costs associated with historical data retention, but they still need a short recovery window for accidental deletes or updates. Which Snowflake object type best fits this requirement?
- A
Create a permanent table and set DATA_RETENTION_TIME_IN_DAYS = 90
- B
Create a transient table with an appropriate Time Travel retention period
- C
Create a temporary table because it supports fail-safe-free recovery across sessions
- D
Create an external table because external tables provide the same recovery behavior as transient tables
Show answer and explanation
Correct answer: B
Explanation
The best choice is a transient table. In Snowflake, transient tables are intended for data that needs to persist beyond a session but does not require the extra data protection of Fail-safe. Because Fail-safe storage is not available for transient tables, they are commonly used for staging, transient operational data, and other re-creatable datasets where cost optimization matters. Unlike temporary tables, transient tables persist until explicitly dropped and can be shared across sessions and users. They still support Time Travel for a configurable short retention window, which satisfies the requirement to recover from accidental DML changes. This aligns with Snowflake guidance that permanent objects are best for business-critical data requiring Fail-safe, while transient objects are suitable when reduced data protection is acceptable in exchange for lower storage cost.
- A. Incorrect.
Incorrect. A permanent table includes Fail-safe storage costs and is intended for data that requires stronger long-term recovery protection. Setting a long retention period such as 90 days increases historical storage rather than reducing it, which conflicts with the team's cost-saving goal for short-lived data.
- B. Correct.
Correct. Transient tables are designed for data that does not require Fail-safe protection, which can reduce storage costs for short-lived or re-creatable data. They still support Time Travel, with a limited retention period, allowing recovery from accidental deletes or updates for a short window. This matches the stated need for lower cost plus short-term recoverability.
- C. Incorrect.
Incorrect. Temporary tables are session-scoped and are dropped automatically at the end of the session. They are useful for intermediate or scratch data, not for shared operational datasets that must remain available for multiple sessions or users over several days. Also, describing them as providing recovery across sessions is incorrect.
- D. Incorrect.
Incorrect. External tables reference data stored outside Snowflake and are used to query external files. They do not provide the same managed storage, Time Travel, and transient-table behavior for internal Snowflake table data. Choosing an external table would not address the requirement for a Snowflake-managed short recovery window on loaded clickstream data.