ARA-C01 Question 235
Single answerTable typesA financial services company runs a daily ETL pipeline in Snowflake that stages 2 TB of raw transaction data, performs several complex transformations, and then loads a curated reporting table. During development, architects notice that the intermediate staging tables are heavily rewritten during each run, are only needed for the duration of the ETL session, and do not need Time Travel or Fail-safe recovery. However, the final curated reporting table must support historical recovery and be available to downstream consumers across sessions. To reduce storage costs without affecting the reporting table's recoverability, which table design should the architect choose?
- A
Use temporary tables for the intermediate staging data and a permanent table for the curated reporting layer
- B
Use transient tables for both the intermediate staging data and the curated reporting table because transient tables reduce storage costs and support all recovery features needed for reporting
- C
Use permanent tables for the intermediate staging data and a temporary table for the curated reporting table so the ETL can recreate the reporting layer each day
- D
Use external tables for the intermediate staging data and a transient table for the curated reporting table to minimize Snowflake storage charges
Show answer and explanation
Correct answer: A
Explanation
Snowflake table types should be selected based on data lifecycle, persistence needs, and recovery requirements. Temporary tables are ideal for session-scoped ETL intermediates because they are automatically purged at session end and avoid unnecessary long-term storage overhead. Transient tables persist until dropped and omit Fail-safe, making them suitable for persistent but lower-protection data sets; however, they are not the best fit when data only needs to exist during one ETL session. Permanent tables provide the highest level of built-in data protection, including Time Travel and Fail-safe, and are therefore the correct choice for curated reporting data that must remain available across sessions and support recovery. This aligns with Snowflake best practices and documentation on temporary, transient, and permanent table behavior, especially around persistence scope, Time Travel retention, and Fail-safe support.
- A. Correct.
Correct. Temporary tables exist only for the session in which they are created, making them well-suited for ETL scratch or staging data that is short-lived and not needed after the session ends. They do not incur Fail-safe costs and are appropriate when Time Travel and recovery are unnecessary. The curated reporting table should be permanent because permanent tables support standard data protection features, including Time Travel and Fail-safe, and remain available across sessions for downstream users and workloads.
- B. Incorrect.
Incorrect. Transient tables can reduce storage costs relative to permanent tables because they do not have Fail-safe, but they still persist beyond the session until explicitly dropped. More importantly, the scenario states that the curated reporting table must support historical recovery. Permanent tables are the appropriate choice when full data protection, including Fail-safe, is required. Choosing transient for the reporting table weakens recoverability.
- C. Incorrect.
Incorrect. This reverses the requirements. Permanent tables would preserve the intermediate staging data longer than necessary and may increase storage costs through retained historical data and Fail-safe. A temporary table for the curated reporting layer would be inappropriate because it disappears at the end of the session, making it unavailable to downstream consumers across sessions.
- D. Incorrect.
Incorrect. External tables are used to query data stored externally, such as in cloud object storage, and are not a general replacement for Snowflake-managed ETL intermediate tables. They do not fit a scenario where the data is being heavily transformed and rewritten inside Snowflake. In addition, using a transient table for the curated reporting layer would not satisfy the stated need for stronger historical recovery.