SnowPro Advanced: Security Engineer Question 179
Single answerAutomate data archival and purging using lifecycle management best practicesA financial services company stores seven years of customer transaction history in Snowflake. Security policy requires that records older than 18 months be removed from analyst-facing tables, retained in a low-cost archive for an additional 5.5 years, and then permanently deleted. The team wants the process to be automated, auditable, and to minimize the risk of manual errors. Which approach BEST meets these requirements?
- A
Create a scheduled TASK that copies rows older than 18 months from the active table into an archive table stored on a lower-cost table type, then deletes those rows from the active table. After the required retention period, use another TASK to DELETE archived rows and rely on Snowflake retention settings and Fail-safe to complete permanent removal.
- B
Use Time Travel to query records older than 18 months when needed, and set DATA_RETENTION_TIME_IN_DAYS to the maximum so older data stays available without creating separate archive objects.
- C
Create a STREAM on the active table and use a TASK to TRUNCATE the table every month after unloading all data to an internal stage. Keep the files indefinitely in the stage and query them directly when auditors request historical data.
- D
Clone the active schema every month, revoke analyst access to older clones, and drop clones after 5.5 years to simulate archival and purging.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to automate archival and purge operations using Snowflake TASKs and separate archive tables, with retention controls enforced through scheduled SQL operations. In practice, organizations commonly move older records from active tables into restricted archive tables, then delete them from the active tables so analysts only see current data. When the regulatory retention period ends, another scheduled process deletes from the archive. Snowflake Time Travel and Fail-safe affect recoverability after DML and DROP operations, but they are not substitutes for a true archival design and are not intended for multi-year business retention requirements. Relevant Snowflake best practices include using TASKs for orchestration, optionally using stored procedures for more complex archival logic, applying least-privilege access to archive objects, and understanding that permanent removal follows the configured Time Travel retention period plus Fail-safe for permanent objects. Documentation areas to review include Snowflake Tasks, Streams and Tasks patterns, Time Travel, Fail-safe, and data retention parameters.
- A. Correct.
Correct. This approach aligns with lifecycle management best practices in Snowflake: automate movement of aging data out of production-facing tables with TASKs, retain it in separate archive tables with restricted access, and automate eventual deletion. Snowflake does not provide a special 'archive table' type, but storing archived data in a separate table and suspending or minimizing compute usage for archive access is a practical pattern. After DELETE operations, Snowflake data remains recoverable only for the configured Time Travel period and then Fail-safe, which supports controlled permanent removal rather than immediate physical destruction. This design is auditable because the SQL logic can be versioned and task history reviewed.
- B. Incorrect.
Incorrect. Time Travel is not a long-term archival solution. It is designed for short-term historical recovery and is limited by the configured DATA_RETENTION_TIME_IN_DAYS, which has platform-enforced limits and is far shorter than 5.5 years. Keeping data only in the active table also fails the requirement to remove records older than 18 months from analyst-facing tables.
- C. Incorrect.
Incorrect. STREAMs track change data capture; they are not intended to manage long-term archival retention by themselves. TRUNCATE removes all rows, which would not preserve the required 18-month active window unless paired with much more complex logic. Internal stages can store unloaded files, but keeping data indefinitely there is not a strong lifecycle management design by itself, and staged files are not a substitute for governed, queryable archive tables with clear retention controls and purge automation.
- D. Incorrect.
Incorrect. Zero-copy cloning is useful for environment creation, backup-like snapshots, and short-term testing, but it is not an efficient or well-governed long-term archival strategy for row-level lifecycle management. Monthly schema clones would increase administrative complexity, make retention enforcement harder, and do not directly implement the requirement to remove only records older than 18 months from analyst-facing tables while preserving controlled archive access.