SnowPro Advanced: Security Engineer Question 159
Single answer2.4 Establish and manage data retention and data lifecycle management.A healthcare company stores PHI in Snowflake and must enforce a strict data lifecycle policy. Regulatory requirements state that operational data must remain recoverable for 30 days after accidental deletion, but once that period has passed, the company wants the shortest possible path to permanent removal to reduce long-term exposure. The Security Engineer notices that a database containing PHI was created with a 7-day DATA_RETENTION_TIME_IN_DAYS value, while the account is Enterprise Edition. Which action should the engineer take to best meet the requirement?
- A
Increase the database DATA_RETENTION_TIME_IN_DAYS to 30 so Time Travel covers the required recovery window, while understanding that Fail-safe will still apply after Time Travel expires.
- B
Set DATA_RETENTION_TIME_IN_DAYS to 0 so Snowflake skips both Time Travel and Fail-safe, permanently removing dropped PHI objects immediately.
- C
Keep DATA_RETENTION_TIME_IN_DAYS at 7 and rely on Fail-safe for the remaining 23 days because Fail-safe is intended for customer-directed self-service recovery.
- D
Create a masking policy on PHI columns and leave retention unchanged because masking policies satisfy recoverability and data lifecycle requirements.
- E
Move PHI tables to a transient database with DATA_RETENTION_TIME_IN_DAYS = 30 to preserve 30 days of recovery while avoiding Fail-safe exposure.
Show answer and explanation
Correct answer: A
Explanation
To satisfy a requirement for 30 days of recoverability in Snowflake, the engineer must use permanent objects with an appropriate DATA_RETENTION_TIME_IN_DAYS setting. In Enterprise Edition, permanent tables, schemas, and databases can support extended Time Travel retention beyond the standard 1 day, up to 90 days depending on edition and object type configuration. Time Travel provides the customer-usable recovery window for undrop and point-in-time access. After Time Travel expires, permanent objects enter a 7-day Fail-safe period, which cannot be disabled for permanent objects and is intended for Snowflake-managed recovery, not normal self-service restore operations. Transient objects reduce lifecycle exposure by avoiding Fail-safe, but they cannot meet a 30-day recoverability requirement. Best practice is to align DATA_RETENTION_TIME_IN_DAYS with the business recovery requirement while understanding the distinction between Time Travel and Fail-safe, as documented in Snowflake guidance for Time Travel, Fail-safe, and table types.
- A. Correct.
Correct. In Enterprise Edition, permanent tables can be configured with up to 90 days of Time Travel retention. Setting DATA_RETENTION_TIME_IN_DAYS to 30 ensures the company can self-service recover dropped or changed data for the required 30-day window. After Time Travel ends, Snowflake Fail-safe still applies for permanent objects for an additional 7 days. This does not eliminate post-retention exposure, but it is the best available configuration to satisfy the stated 30-day recoverability requirement while minimizing exposure as much as Snowflake allows for permanent objects.
- B. Incorrect.
Incorrect. Setting DATA_RETENTION_TIME_IN_DAYS to 0 can reduce or eliminate Time Travel for certain objects, but it does not bypass Fail-safe for permanent tables. Permanent database objects in Snowflake still enter Fail-safe after Time Travel, so they are not removed immediately. This option reflects a common misconception that retention = 0 means no residual recoverability at all.
- C. Incorrect.
Incorrect. Fail-safe is not designed for routine customer-managed recovery and should not be treated as an extension of the required operational recovery window. It is a Snowflake-managed disaster recovery mechanism, not a self-service feature for meeting a business requirement for 30-day recoverability. Keeping retention at 7 days fails the stated requirement.
- D. Incorrect.
Incorrect. Masking policies protect data visibility at query time, but they do not control object retention, Time Travel, Fail-safe, or deletion lifecycle behavior. This option confuses data access protection with data retention and recoverability controls.
- E. Incorrect.
Incorrect. Transient databases and transient tables do not support a 30-day retention period. Their Time Travel retention is limited compared with permanent objects, and while they do not have Fail-safe, they cannot satisfy the stated requirement for 30 days of recoverability. This is a plausible but incorrect tradeoff because it prioritizes reduced residual storage over the explicit recovery requirement.