SnowPro Advanced: Security Engineer Question 169
Single answerConfigure and enforce data retention policiesA financial services company stores regulated trading data in a Snowflake Enterprise Edition account. The security team must enforce the following policy for a schema that contains highly sensitive tables: analysts must be able to recover recently changed or dropped data for up to 30 days, but after that period the data must no longer be recoverable by any role, including ACCOUNTADMIN. The team wants this enforced as a platform control rather than relying on manual processes. Which configuration best meets the requirement?
- A
Set DATA_RETENTION_TIME_IN_DAYS = 30 on the schema or on each table, and keep the account as Enterprise Edition.
- B
Set MAX_DATA_EXTENSION_TIME_IN_DAYS = 30 on the schema so that Time Travel lasts 30 days and data is purged immediately after.
- C
Set DATA_RETENTION_TIME_IN_DAYS = 1 on the schema, then create a task to copy changed rows daily into a history table and delete them after 30 days.
- D
Set DATA_RETENTION_TIME_IN_DAYS = 90 on the account, then use role-based access control to prevent ACCOUNTADMIN from restoring older data.
Show answer and explanation
Correct answer: A
Explanation
The key Snowflake control for configuring recoverability of changed or dropped data is DATA_RETENTION_TIME_IN_DAYS. For Enterprise Edition and higher, permanent tables, schemas, and databases can be configured with Time Travel retention up to 90 days. To meet a policy requiring 30 days of user-accessible recovery, the security engineer should set DATA_RETENTION_TIME_IN_DAYS = 30 at the appropriate scope, commonly the schema for consistent enforcement across sensitive tables.
After the Time Travel period expires, data enters Fail-safe for permanent objects. Fail-safe is a Snowflake-managed disaster recovery mechanism and is not available for customer-driven query or restore operations. That distinction is critical for the requirement stating that after 30 days the data must not be recoverable by any role, including ACCOUNTADMIN. In practice, roles cannot use Time Travel or UNDROP after the retention window has expired.
Relevant Snowflake documentation and best practices include the sections on Time Travel, Fail-safe, and object parameters such as DATA_RETENTION_TIME_IN_DAYS and MAX_DATA_EXTENSION_TIME_IN_DAYS. A common misconception is confusing MAX_DATA_EXTENSION_TIME_IN_DAYS, which relates to stream staleness protection, with the actual Time Travel retention control. Another common error is trying to use RBAC or custom ETL processes to mimic retention enforcement when Snowflake provides a native, auditable platform setting for this purpose.
- A. Correct.
Correct. In Enterprise Edition and higher, permanent objects can have Time Travel retention configured up to 90 days. Setting DATA_RETENTION_TIME_IN_DAYS = 30 on the schema (or individual tables) allows recovery of changed or dropped data for 30 days. After the configured Time Travel period ends, Snowflake keeps historical data only during Fail-safe, which is not accessible by customers or any role, including ACCOUNTADMIN. This satisfies the requirement that data is recoverable for 30 days and not recoverable by roles afterward, using a native platform control.
- B. Incorrect.
Incorrect. MAX_DATA_EXTENSION_TIME_IN_DAYS does not define the Time Travel retention period. It controls how much Snowflake can automatically extend retention for streams to avoid staleness in some scenarios. It is not the parameter used to set a 30-day recovery window for changed or dropped table data, so it would not enforce the stated retention policy.
- C. Incorrect.
Incorrect. This approach relies on custom tasks and history tables rather than Snowflake's native retention controls, which the scenario explicitly wants to avoid. It also changes the recovery model: copied data in a history table would still exist until manually deleted and could create additional governance and security risks. While technically possible for some business processes, it does not best meet the requirement for enforced platform-level retention and recovery.
- D. Incorrect.
Incorrect. Increasing DATA_RETENTION_TIME_IN_DAYS to 90 would allow longer customer-accessible recovery than required, not shorter. In addition, ACCOUNTADMIN is the highest-privilege administrative role in the account, and the requirement is to ensure data is no longer recoverable by any role after 30 days. RBAC is not the correct mechanism for limiting native Time Travel history once retained; the retention setting itself must be configured appropriately.