COF-C03 Question 96
Single answerTable typesA retail company runs a daily ETL pipeline that loads clickstream data into a staging table, transforms it, and then publishes results to reporting tables. The staging data is only needed for a few hours during each run and does not need Fail-safe recovery. The team wants to minimize storage costs for this short-lived intermediate data while keeping the table available across multiple sessions and statements in the pipeline. Which table type should they use for the staging table?
- A
Temporary table, because it persists for all sessions until explicitly dropped and does not use Fail-safe
- B
Transient table, because it persists until dropped, is available across sessions, and has no Fail-safe period
- C
External table, because it avoids Snowflake-managed storage costs for short-lived staged data
- D
Permanent table, because Time Travel retention can be set to 0 days to remove Fail-safe charges
Show answer and explanation
Correct answer: B
Explanation
The best answer is transient table. In Snowflake, the main table types relevant here are temporary, transient, and permanent. Temporary tables are session-specific and automatically dropped when the session ends, so they are useful for truly session-local work. Transient tables persist until dropped and are accessible across sessions, but unlike permanent tables, they do not have a Fail-safe period, which can reduce storage costs for short-lived or non-critical data. Permanent tables provide the highest level of data protection, including Fail-safe, and are better suited for durable business data. Snowflake documentation on table types and data retention emphasizes these distinctions: temporary tables are session-bound, transient tables omit Fail-safe, and permanent tables include Fail-safe. For staging data shared across ETL steps or sessions, transient tables are a common best practice.
- A. Incorrect.
Incorrect. Temporary tables do not have a Fail-safe period, which can reduce cost, but they are session-scoped. They exist only for the session in which they are created and are dropped automatically at the end of that session. In this scenario, the table must remain available across multiple sessions in the ETL pipeline, so a temporary table does not meet the requirement.
- 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 persist until explicitly dropped, are accessible across sessions like permanent tables, and do not have a Fail-safe period. This makes them a strong fit for short-lived staging data where minimizing storage cost is important.
- C. Incorrect.
Incorrect. External tables are used to query data stored externally, such as in cloud object storage, and are not the right choice for a Snowflake-managed staging table used in an ETL workflow. They solve a different problem and do not replace the behavior of a regular Snowflake table type for transient intermediate processing.
- D. Incorrect.
Incorrect. Permanent tables do support configurable Time Travel retention, but they still include a Fail-safe period. Setting Time Travel to the minimum does not eliminate Fail-safe for permanent tables. Because the scenario explicitly states that Fail-safe recovery is not needed and cost should be minimized, a permanent table is not the best choice.