SnowPro Associate: Platform Question 107
Single answer● TablesA retail company loads daily sales records into a large permanent table named SALES_FACT. Analysts frequently run time-based queries on the last 30 days of data, while finance occasionally needs to recover rows that were accidentally deleted. The company wants to reduce storage costs for historical data without losing the ability to recover recent changes. Which table type should be used for SALES_FACT?
- A
Permanent table, because it supports Time Travel and Fail-safe, and historical storage costs can still be managed through data retention settings and lifecycle practices
- B
Transient table, because it provides Fail-safe but avoids Time Travel storage charges for deleted or changed data
- C
Temporary table, because it persists across user sessions until explicitly dropped and has the lowest long-term storage cost
- D
External table, because it stores historical data outside Snowflake while still supporting the same recovery features as internal tables
Show answer and explanation
Correct answer: A
Explanation
The best answer is the permanent table. In Snowflake, permanent tables are designed for persistent business data and include both Time Travel and Fail-safe. Time Travel allows recovery of data from prior states for a defined retention period, and Fail-safe provides an additional recovery window managed by Snowflake. Transient tables can reduce storage costs because they do not have Fail-safe, but that tradeoff reduces recoverability. Temporary tables are session-scoped and not appropriate for long-lived production facts. External tables are metadata objects over external data and do not replace internal table recovery features. According to Snowflake documentation and best practices, permanent tables are the right fit when data durability and recoverability are required, while transient or temporary tables are better suited for staging or less critical data.
- A. Correct.
Correct. Permanent tables are the standard table type for durable production data. They support both Time Travel and Fail-safe, which is important when the business needs to recover accidentally deleted rows. Although historical data storage can increase costs, Snowflake allows organizations to manage retention periods and apply operational practices such as archiving older data when appropriate. This best fits a large fact table that must remain durable and recoverable.
- B. Incorrect.
Incorrect. Transient tables do support Time Travel, but they do not have Fail-safe. A common misconception is that transient tables remove all historical storage overhead while preserving the same recovery protections as permanent tables. They are intended for data that does not require Fail-safe-level recovery, so they are not the best choice here when finance needs stronger recovery support.
- C. Incorrect.
Incorrect. Temporary tables exist only for the duration of the session in which they are created and are not suitable for shared, durable production data like SALES_FACT. The misconception is confusing lower persistence with lower cost optimization for production workloads. Temporary tables are useful for short-lived session-based processing, not enterprise reporting tables.
- D. Incorrect.
Incorrect. External tables reference data stored outside Snowflake, typically in cloud storage, and are used to query external files. They do not provide the same behavior as internal permanent tables for row-level DML recovery, Time Travel, and Fail-safe. This option reflects a misunderstanding between storage location and recovery capabilities.