ADA-C01 Question 557
Single answerEstablish data retention periodsA financial services company uses Snowflake Enterprise Edition for production workloads and wants to reduce storage costs while still allowing analysts to recover accidentally deleted or updated data in a key reporting table for up to 7 days. The reporting table is in a schema where most transient staging tables should have minimal retention. As the Snowflake administrator, which action best meets the requirement while avoiding unnecessary retention on the staging objects?
- A
Set DATA_RETENTION_TIME_IN_DAYS = 7 on the specific permanent reporting table, and keep the transient staging tables with minimal or 0-day retention as appropriate.
- B
Convert the reporting table to a transient table and set DATA_RETENTION_TIME_IN_DAYS = 7 so Fail-safe is avoided while Time Travel remains available for 7 days.
- C
Set MIN_DATA_RETENTION_TIME_IN_DAYS = 7 at the account level so only the reporting table inherits 7 days, while transient staging tables remain unaffected.
- D
Set MAX_DATA_EXTENSION_TIME_IN_DAYS = 7 on the schema so the reporting table can be recovered for 7 days without changing table-level retention.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to set DATA_RETENTION_TIME_IN_DAYS = 7 on the specific permanent reporting table. In Snowflake, Time Travel retention is configurable at multiple levels, but applying it at the most granular level that satisfies the business requirement is a best practice when controlling storage cost and limiting operational impact. Because the company uses Enterprise Edition, permanent objects can have longer Time Travel periods than the default 1 day, up to the edition-supported maximum. In contrast, transient objects are intended for lower-cost, lower-protection use cases and should remain at minimal retention when possible. MIN_DATA_RETENTION_TIME_IN_DAYS is useful for enforcing a minimum retention policy across a scope, but it is not appropriate when only one object needs extended retention. MAX_DATA_EXTENSION_TIME_IN_DAYS is related to stream staleness protection, not the standard Time Travel retention setting. These behaviors are documented in Snowflake guidance for Time Travel, object parameters, and transient versus permanent table data protection.
- A. Correct.
Correct. Time Travel retention can be set at the object level using DATA_RETENTION_TIME_IN_DAYS, which allows the administrator to give the permanent reporting table 7 days of retention without increasing retention for unrelated objects. This aligns with the requirement to recover accidental deletes or updates for that table while keeping transient staging tables at minimal retention. Permanent objects in Enterprise Edition can support up to 90 days of Time Travel, so 7 days is valid.
- B. Incorrect.
Incorrect. Transient tables are designed to avoid Fail-safe and generally minimize data protection overhead, but they do not support a 7-day Time Travel period. Transient and temporary tables have limited Time Travel retention and are not appropriate when the requirement is to recover data changes for up to 7 days. Converting the reporting table to transient would weaken protection rather than meet the stated recovery objective.
- C. Incorrect.
Incorrect. MIN_DATA_RETENTION_TIME_IN_DAYS establishes a floor for retention and would affect objects in scope, not just the reporting table. Using it at the account level would likely increase retention more broadly than intended, including staging objects, which conflicts with the goal of avoiding unnecessary retention costs. It is the wrong tool when only one table needs a longer retention period.
- D. Incorrect.
Incorrect. MAX_DATA_EXTENSION_TIME_IN_DAYS controls how long Snowflake can extend retention for streams to prevent staleness; it is not the setting used to define standard Time Travel retention for recovering deleted or updated table data. Setting it on the schema would not by itself provide a 7-day recovery window for the reporting table.