COF-C03 Question 97
Single answerTable typesA data engineering team loads clickstream events into Snowflake every 5 minutes. Analysts only need to query the most recent 48 hours of data for operational dashboards, and the data should not add to Fail-safe storage costs after it is no longer needed. The team also wants to reduce storage costs for this short-lived dataset while keeping the table available across user sessions. Which table type is the best fit for this requirement?
- A
Create a permanent table with a 0-day data retention period
- B
Create a transient table for the clickstream data
- C
Create a temporary table for the clickstream data
- D
Create an external table over staged clickstream files
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use a transient table. In Snowflake, permanent, transient, and temporary tables differ primarily in persistence and data protection behavior. Permanent tables are the default and include Fail-safe, which can increase storage costs after data is deleted. Temporary tables avoid long-term storage but are limited to the user session and therefore are not suitable when multiple sessions or ongoing dashboard workloads need access to the same table. Transient tables persist until explicitly dropped, remain available across sessions, and do not have a Fail-safe period, making them a common best practice for staging or short-lived operational data where lower storage cost is important. This aligns with Snowflake documentation on table types and data lifecycle tradeoffs, especially the distinction that transient objects do not incur Fail-safe storage.
- A. Incorrect.
Incorrect. A permanent table can have Time Travel retention adjusted, including 0 or 1 day depending on edition and object settings, but permanent tables still use Fail-safe. The scenario explicitly wants to avoid adding to Fail-safe storage costs after the data is no longer needed, so a permanent table is not the best fit.
- B. 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 with limited retention but do not have a Fail-safe period, which helps reduce storage costs for short-lived data such as recent clickstream events retained for 48 hours.
- C. Incorrect.
Incorrect. Temporary tables are session-scoped and are dropped automatically at the end of the session in which they were created. The requirement says the table should remain available across user sessions, so a temporary table would not meet the operational dashboard use case.
- D. Incorrect.
Incorrect. External tables are used to query data stored outside Snowflake, typically in cloud object storage. While they can be useful for data lake scenarios, they do not address the stated need as directly as a transient table, and they introduce a different architecture rather than selecting the appropriate Snowflake table type for short-lived persisted data.