COF-C03 Question 100
Single answerTemporaryA data engineering team runs a nightly ETL process in Snowflake that creates several intermediate tables used only within the same database session. The team wants to minimize long-term storage of these transient processing results and ensure the objects are not visible after the ETL session ends. Which table type should the team use for these intermediate objects?
- A
Permanent tables, because they support Time Travel and Fail-safe for short-lived data
- B
Transient tables, because they do not have Fail-safe and are automatically dropped at the end of the session
- C
Temporary tables, because they exist only for the session and are automatically purged when the session ends
- D
External tables, because they avoid Snowflake storage charges for intermediate ETL data
Show answer and explanation
Correct answer: C
Explanation
The correct choice is temporary tables. In Snowflake, temporary tables are designed for non-permanent, session-scoped work such as staging intermediate ETL results, scratch data, or one-time transformations. They are only accessible within the creating session and are automatically removed when the session ends. This behavior directly matches the requirement to avoid long-term persistence and post-session visibility. By contrast, transient tables persist beyond a session and must be explicitly dropped, though they do not have Fail-safe. Permanent tables are fully durable objects intended for ongoing storage. External tables reference external data and are unrelated to this use case. This aligns with Snowflake documentation on table types and best practices for using temporary objects for session-based workloads.
- A. Incorrect.
Incorrect. Permanent tables are intended for durable data storage and continue to exist until explicitly dropped. While they support Time Travel and Fail-safe, those features are not appropriate for session-only intermediate ETL objects that should disappear automatically after the session ends.
- B. Incorrect.
Incorrect. Transient tables do reduce data retention overhead by not having Fail-safe, but they are not automatically dropped at the end of the session. They persist until explicitly dropped, so they do not meet the requirement for session-scoped visibility and lifecycle.
- C. Correct.
Correct. Temporary tables are session-specific objects in Snowflake. They are available only within the session in which they are created and are automatically dropped, with data purged, at the end of that session. This makes them the best fit for ETL intermediate results that should not remain visible afterward.
- D. Incorrect.
Incorrect. External tables are used to query data stored outside Snowflake, such as in cloud object storage. They are not designed for internal intermediate ETL processing results created and consumed within a Snowflake session.