ADA-C01 Question 401
Single answerLatency and data retention considerationsA Snowflake administrator manages a production database that contains several large transient staging tables used only during daily ETL processing. The tables are loaded overnight and truncated after downstream loads complete. The company wants to reduce storage cost and shorten the time required for operational recovery activities, but it still needs production fact tables in the same database to support 7 days of Time Travel for accidental update recovery. Which action should the administrator take to best meet these requirements?
- A
Set DATA_RETENTION_TIME_IN_DAYS = 0 on the transient staging tables, while keeping the production fact tables at 7 days
- B
Convert the entire database to transient and set DATA_RETENTION_TIME_IN_DAYS = 7 at the database level
- C
Keep all tables permanent, but disable Fail-safe on the staging tables to reduce storage cost and recovery latency
- D
Set MAX_DATA_EXTENSION_TIME_IN_DAYS = 0 on the staging tables so that Time Travel history is removed immediately after truncation
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use object-appropriate table types and retention settings. In Snowflake, permanent tables support Time Travel and Fail-safe, while transient tables support Time Travel but do not include Fail-safe. For staging data that is short-lived and reloadable, transient tables with DATA_RETENTION_TIME_IN_DAYS = 0 are a common best practice to reduce storage costs associated with historical data and to minimize unnecessary recovery overhead. For production fact tables that require recovery from accidental changes, keeping them as permanent tables with 7 days of Time Travel preserves the needed protection. This scenario tests understanding of retention design, the differences between permanent and transient objects, and the fact that Fail-safe is not user-disableable for permanent tables. Relevant Snowflake documentation includes guidance on table types, Time Travel, Fail-safe, and object parameters such as DATA_RETENTION_TIME_IN_DAYS and MAX_DATA_EXTENSION_TIME_IN_DAYS.
- A. Correct.
Correct. Transient tables do not have Fail-safe, which reduces storage cost compared to permanent tables, and setting DATA_RETENTION_TIME_IN_DAYS = 0 on the staging tables minimizes Time Travel retention for objects that do not need recovery. At the same time, permanent production fact tables can remain configured with 7 days of Time Travel. This approach applies retention settings at the object level to align cost, recovery needs, and operational behavior.
- B. Incorrect.
Incorrect. Converting the entire database to transient would remove Fail-safe protection for all objects in that database, including production fact tables. In addition, setting a 7-day retention at the database level would not help reduce retention-related storage overhead for the staging tables. This is too broad and does not satisfy the requirement to preserve stronger recovery capabilities for production data while minimizing overhead for staging data.
- C. Incorrect.
Incorrect. Fail-safe cannot be disabled on permanent tables. Permanent tables include Fail-safe by design, while transient tables and temporary tables do not. This option reflects a common misconception that Fail-safe is independently configurable per permanent table.
- D. Incorrect.
Incorrect. MAX_DATA_EXTENSION_TIME_IN_DAYS is related to how Snowflake can extend retention for streams to avoid staleness, not a control to immediately remove Time Travel history after DML or truncation. It does not replace DATA_RETENTION_TIME_IN_DAYS for managing object-level retention and would not achieve the stated goal.