ARA-C01 Question 236
Single answerTable typesA retail company runs a high-volume Snowflake data platform. Its ARCHITECT team is redesigning table storage to reduce costs without breaking data sharing and recovery requirements. The platform has the following objects:
- A SESSION_STAGE_EVENTS table used only within individual ETL sessions for intermediate deduplication and discarded at the end of each session.
- A DAILY_LOAD_AUDIT table that stores operational load metrics for 3 days and does not need Fail-safe, but analysts occasionally query it across sessions.
- A PRODUCT_MASTER table that is shared with external partners through Secure Data Sharing and must support Time Travel and Fail-safe.
Which table design best matches these requirements while minimizing storage cost?
- A
Use a temporary table for SESSION_STAGE_EVENTS, a transient table for DAILY_LOAD_AUDIT, and a permanent table for PRODUCT_MASTER.
- B
Use a transient table for SESSION_STAGE_EVENTS, a temporary table for DAILY_LOAD_AUDIT, and a permanent table for PRODUCT_MASTER.
- C
Use temporary tables for SESSION_STAGE_EVENTS and DAILY_LOAD_AUDIT, and a transient table for PRODUCT_MASTER.
- D
Use permanent tables for all three objects because only permanent tables support efficient querying and metadata optimization.
Show answer and explanation
Correct answer: A
Explanation
Snowflake supports three primary table types relevant here: temporary, transient, and permanent. Temporary tables are session-scoped and automatically dropped at the end of the session, which makes them a strong fit for ETL scratch or staging workloads. Transient tables persist until explicitly dropped and support Time Travel with limited retention, but they do not have Fail-safe, reducing storage cost for non-critical data. Permanent tables support both Time Travel and Fail-safe and are the standard choice for durable business data. For data sharing and critical master data, architects typically choose permanent tables because they provide the strongest recovery posture. The key architectural trade-off is balancing persistence and recoverability against storage cost. This aligns with Snowflake documentation on table types, data retention, and Fail-safe behavior.
- A. Correct.
Correct. Temporary tables are session-scoped and are ideal for ETL intermediates that should disappear automatically at session end. Transient tables persist beyond a session but do not incur Fail-safe storage, making them appropriate for short-lived operational data that may be queried across sessions. Permanent tables are required for standard long-term data retention patterns, including Fail-safe, and are appropriate for a shared master data object such as PRODUCT_MASTER.
- B. Incorrect.
Incorrect. A transient table would persist beyond the ETL session, which is unnecessary for SESSION_STAGE_EVENTS and could increase storage if cleanup is missed. A temporary table for DAILY_LOAD_AUDIT is also incorrect because temporary tables are not intended for cross-session access; once the creating session ends, the table is removed. The permanent table choice for PRODUCT_MASTER is appropriate, but the first two choices do not meet the scenario requirements.
- C. Incorrect.
Incorrect. Using a temporary table for DAILY_LOAD_AUDIT conflicts with the requirement that analysts query it across sessions, because temporary tables are session-specific. Using a transient table for PRODUCT_MASTER is also a poor fit because transient tables do not have Fail-safe, while the scenario explicitly requires Fail-safe support for this shared, business-critical table.
- D. Incorrect.
Incorrect. Permanent tables are not required for efficient querying, pruning, clustering, or metadata optimization. Snowflake query performance characteristics are not based on table type in the way implied here. Choosing permanent tables for all objects would satisfy persistence requirements, but it would increase storage costs unnecessarily by applying Fail-safe to objects that do not need it.