SnowPro Advanced: Security Engineer Question 158
Single answer2.4 Establish and manage data retention and data lifecycle management.A healthcare company stores PHI in Snowflake and must reduce the amount of recoverable historical data to meet an updated internal retention policy. The security engineer has been asked to ensure that dropped or modified data in a specific permanent table can no longer be recovered after 2 days, while still allowing the table to remain a permanent table for operational reasons. The account is Enterprise Edition. Which configuration best meets this requirement with the least operational change?
- A
Set the table DATA_RETENTION_TIME_IN_DAYS to 2 and leave MAX_DATA_EXTENSION_TIME_IN_DAYS unchanged.
- B
Convert the table to a transient table and set DATA_RETENTION_TIME_IN_DAYS to 2.
- C
Set the table DATA_RETENTION_TIME_IN_DAYS to 1 and set MAX_DATA_EXTENSION_TIME_IN_DAYS to 1.
- D
Disable Fail-safe for the table and set DATA_RETENTION_TIME_IN_DAYS to 2.
Show answer and explanation
Correct answer: C
Explanation
Snowflake data lifecycle management requires understanding the differences between permanent and transient objects, as well as how Time Travel, Fail-safe, and stream protection interact. Permanent tables support Time Travel and then enter a 7-day Fail-safe period; transient and temporary tables do not have Fail-safe. In Enterprise Edition, DATA_RETENTION_TIME_IN_DAYS for permanent tables can be set up to 90 days, but the minimum practical retention is still constrained by the permanent-table model. MAX_DATA_EXTENSION_TIME_IN_DAYS can extend retention to keep streams from becoming stale, which can affect how long historical data remains available. Because the scenario requires the object to remain a permanent table, converting it to transient is not acceptable, and Fail-safe cannot be disabled. Therefore, the best available approach is to minimize retention-related settings on the permanent table. Relevant Snowflake documentation includes Time Travel, Fail-safe, DATA_RETENTION_TIME_IN_DAYS, and MAX_DATA_EXTENSION_TIME_IN_DAYS behavior for tables and streams.
- A. Incorrect.
Incorrect. For a permanent table, historical data can remain recoverable through both Time Travel and Fail-safe. Setting DATA_RETENTION_TIME_IN_DAYS to 2 gives 2 days of Time Travel, but permanent objects also have 7 days of Fail-safe after Time Travel ends. In addition, MAX_DATA_EXTENSION_TIME_IN_DAYS can extend retention for streams to prevent staleness. This does not achieve the requirement that dropped or modified data be unrecoverable after 2 days.
- B. Incorrect.
Incorrect. Transient tables do not have Fail-safe, so this seems attractive from a lifecycle perspective. However, the scenario explicitly states that the table must remain a permanent table for operational reasons. Converting the table type would be a larger operational change and does not satisfy the stated constraint.
- C. Correct.
Correct. In Enterprise Edition, permanent tables can have DATA_RETENTION_TIME_IN_DAYS set to 0 or 1. To ensure data is not recoverable beyond 2 days, the effective recoverable period must account for both Time Travel and any extension used to prevent stream staleness, plus the 7-day Fail-safe for permanent objects. However, because Fail-safe cannot be disabled for permanent tables, the only supported way to keep the table permanent while minimizing the recoverable historical window is to set the lowest possible Time Travel retention and limit stream-driven extension. Setting DATA_RETENTION_TIME_IN_DAYS to 1 and MAX_DATA_EXTENSION_TIME_IN_DAYS to 1 minimizes retention-related exposure for the permanent table with the least operational change. Although permanent-table Fail-safe still exists, this is the closest valid configuration given Snowflake constraints and is the best answer among the options.
- D. Incorrect.
Incorrect. Fail-safe is a Snowflake-managed protection period for permanent objects and cannot be disabled at the table level. This option reflects a common misconception that Fail-safe behaves like a configurable parameter. Because Fail-safe cannot be turned off for permanent tables, this configuration is not possible.