SnowPro Advanced: Security Engineer Question 178
Single answerAutomate data archival and purging using lifecycle management best practicesA healthcare company stores daily claim extracts in an internal Snowflake stage before loading them into curated tables. Corporate policy requires raw staged files older than 90 days to be archived automatically, and then permanently removed after the archive retention period expires. The security team wants the process to be automated with the least operational overhead and aligned to Snowflake lifecycle management best practices. Which approach should the security engineer recommend?
- A
Configure a lifecycle policy on the cloud storage location backing the stage so objects transition to an archival storage class after 90 days and expire later according to retention requirements.
- B
Create a Snowflake masking policy on the stage to hide files older than 90 days and let Time Travel remove them automatically after the retention window.
- C
Use a task that runs REMOVE on the stage every 90 days and rely on Fail-safe to serve as the archive before final deletion.
- D
Convert the internal stage to a transient stage so staged files are automatically archived after 90 days and purged after Fail-safe ends.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use lifecycle management on the storage layer that backs the stage. Snowflake guidance for unstructured staged data is to rely on cloud storage lifecycle policies to transition objects to lower-cost archival tiers and then delete them after the required retention period. This is the most operationally efficient and policy-driven approach. Features such as masking policies, Time Travel, transient objects, and Fail-safe are often misunderstood as general-purpose retention controls, but they are intended for database object access protection or recovery semantics, not staged-file archival. In practice, a security engineer should work with the cloud storage administrators to define age-based lifecycle rules for the stage's backing storage in accordance with retention and purge requirements.
- A. Correct.
Correct. For staged files, Snowflake best practice is to manage archival and deletion through the underlying cloud storage lifecycle features. Internal stages are backed by provider-managed cloud storage, and lifecycle management policies on that storage are the appropriate mechanism to automatically transition objects to archive tiers and later delete them. This minimizes operational overhead and cleanly separates storage lifecycle from database object retention features such as Time Travel.
- B. Incorrect.
Incorrect. Masking policies apply to query results on table/view columns, not to files in stages. They do not manage object aging, archival, or deletion. Time Travel also applies to database objects such as tables, schemas, and databases, not as an archival mechanism for staged files.
- C. Incorrect.
Incorrect. A task invoking REMOVE can delete stage files, but it does not provide true archival. Fail-safe is a Snowflake recovery feature for database data, not a customer-managed archive tier for stage files. This option also increases operational overhead and does not meet the requirement to archive before purge using lifecycle best practices.
- D. Incorrect.
Incorrect. Transient objects reduce or eliminate Fail-safe for certain database objects, but they do not create automated archive workflows for staged files. Changing a stage to transient is not a valid mechanism for moving files into archive storage after 90 days.