COF-C03 Question 101
Single answerTemporaryA data engineering team runs a daily ETL process in Snowflake. During each run, the process creates several staging tables that are only needed for the duration of the user session and must not remain after the session ends. The team also wants to avoid the risk of these tables being recovered later through Time Travel. Which table type should the engineer use for these staging tables?
- A
Permanent tables, because they support full recovery and can be automatically dropped at session end
- B
Transient tables, because they do not have Fail-safe and are removed automatically when the session ends
- C
Temporary tables, because they exist only within the session and are purged when the session ends
- D
External tables, because they reference staged data without storing it in Snowflake
Show answer and explanation
Correct answer: C
Explanation
Temporary tables are designed for short-lived, session-specific workloads such as ETL staging, scratch work, and intermediate transformations. They are only visible within the creating session and are automatically dropped when that session ends. This makes them the best choice when data should not persist beyond the session. By contrast, transient tables are persistent until dropped, although they do not have Fail-safe; they are better for non-permanent data that still must survive across sessions. Permanent tables are intended for durable storage with full data protection features. Snowflake documentation distinguishes these table types based on persistence, recovery, and lifecycle behavior, and temporary tables are the recommended option for session-only staging use cases.
- A. Incorrect.
Incorrect. Permanent tables are the default table type and persist until explicitly dropped. They support Time Travel and Fail-safe, so they are not appropriate when the requirement is for the table to disappear automatically at the end of the session and not be recoverable later.
- B. Incorrect.
Incorrect. Transient tables do not have Fail-safe, which can make them useful for lower-cost, non-permanent data storage. However, they persist until explicitly dropped and are not automatically removed at the end of a session. They can still have Time Travel retention, so they do not meet the stated requirement as well as temporary tables.
- C. Correct.
Correct. Temporary tables are session-scoped objects in Snowflake. They are only available to the session that created them and are automatically dropped at the end of that session. Because they are session-based and purged when the session ends, they are the right fit for ETL staging tables needed only during a single run.
- D. Incorrect.
Incorrect. External tables are used to query data stored outside Snowflake, such as in cloud storage. They are not designed to provide session-scoped temporary relational storage for ETL intermediates.