SnowPro Advanced: Security Engineer Question 168
Single answerConfigure and enforce data retention policiesA financial services company stores customer transaction data in Snowflake. The Security Engineer must enforce the following policy: production tables must allow recovery of deleted or updated data for 30 days, while transient staging data used during daily ETL must not retain historical data longer than necessary to minimize storage cost and exposure. During a review, the engineer finds that a staging schema was created as a regular schema and contains standard tables. Which action best meets the policy requirements with the least operational complexity?
- A
Set DATA_RETENTION_TIME_IN_DAYS = 30 on the production database, and set DATA_RETENTION_TIME_IN_DAYS = 0 on each staging table in the existing regular schema.
- B
Convert the existing staging schema to a transient schema, and keep DATA_RETENTION_TIME_IN_DAYS = 30 at the account level so all objects inherit the same recovery window.
- C
Create a transient database or transient schema for staging workloads, move the staging objects there, and set the production database or schemas to DATA_RETENTION_TIME_IN_DAYS = 30.
- D
Leave all objects in place and rely on FAIL-SAFE to provide 30 days of recovery for production while using Time Travel only when needed for staging.
Show answer and explanation
Correct answer: C
Explanation
The best design is to separate workloads by object type and retention requirement. In Snowflake, standard objects support Time Travel and then enter Fail-safe, while transient objects do not have Fail-safe, making them appropriate for temporary or staging workloads where long retention is undesirable. For production data that must support 30-day recovery of deleted or updated data, configure DATA_RETENTION_TIME_IN_DAYS = 30 at an appropriate container level such as the database or schema, assuming the account edition supports that retention period. For staging ETL data, place objects in a transient database or transient schema so the reduced retention model is enforced structurally rather than through fragile per-table settings. This aligns with Snowflake best practices for minimizing unnecessary data retention while preserving required recovery capabilities. Relevant Snowflake documentation includes guidance on Time Travel, Fail-safe, transient tables/schemas/databases, and the DATA_RETENTION_TIME_IN_DAYS object parameter.
- A. Incorrect.
This is not the best answer. Setting 30 days on the production database is appropriate for standard production tables, but managing retention by setting 0 on each individual staging table in a regular schema increases administrative overhead and is easy to misconfigure as new tables are created. In addition, regular tables still participate in Fail-safe after Time Travel, which does not align with the goal of minimizing historical retention and exposure for staging data. A structural design using transient objects is the cleaner and more enforceable approach.
- B. Incorrect.
This is incorrect because Snowflake does not support converting an existing standard schema into a transient schema in place. Transient status is defined when the database, schema, or table is created. Also, setting 30 days at the account level for all objects would not satisfy the requirement to minimize retention for staging workloads.
- C. Correct.
This is correct. Transient databases, schemas, and tables are designed for data that does not require Fail-safe, which reduces storage duration and exposure compared to standard objects. Moving staging workloads into a transient database or schema provides a clear enforcement boundary for lower-retention ETL data. Separately configuring the production database or schemas with DATA_RETENTION_TIME_IN_DAYS = 30 meets the requirement for 30-day recovery via Time Travel on production data. This approach is easier to govern and scales better than per-table exceptions in a regular schema.
- D. Incorrect.
This is incorrect. Fail-safe is a Snowflake recovery mechanism primarily for disaster recovery and is not intended as a user-managed data retention strategy. It also does not replace a 30-day Time Travel requirement for production tables, because Time Travel and Fail-safe serve different purposes. In addition, relying on Fail-safe does not reduce retention exposure for staging data in regular objects.