SnowPro Advanced: Security Engineer Question 171
Single answerDefine appropriate retention strategies for structured and semi-structured data:A healthcare analytics company stores patient encounter data in Snowflake. Structured relational tables contain billing and visit records that must be recoverable for 90 days after accidental deletion. Semi-structured JSON payloads from medical devices are landed daily into a raw table and must be retained for 7 years for audit purposes, but they are rarely queried after the first month. The security engineer must recommend a retention strategy that meets recovery and compliance requirements while controlling storage costs. Which approach is the MOST appropriate?
- A
Set the raw JSON table DATA_RETENTION_TIME_IN_DAYS to 2555 so the data remains queryable in Time Travel for 7 years, and keep the structured tables at 90 days.
- B
Keep the structured tables at 90 days of Time Travel, and for the raw JSON data, unload older records to an external stage/object storage with lifecycle and governance controls, then remove them from the Snowflake table after the required active period.
- C
Set all databases and tables to 1 day of Time Travel, and rely on Fail-safe to satisfy both the 90-day recovery requirement and the 7-year audit retention requirement.
- D
Convert the raw JSON payloads into temporary tables after 30 days so they no longer incur retention-related storage charges, while preserving access through table metadata.
Show answer and explanation
Correct answer: B
Explanation
The key design principle is to match the retention mechanism to the requirement. Snowflake Time Travel is intended for short-term data recovery from accidental updates, deletes, or drops, while Fail-safe is a limited, Snowflake-only recovery layer and not a compliance archive. For structured data that must be recoverable for 90 days, configuring appropriate Time Travel retention is the right mechanism if the edition and object settings support that duration. For semi-structured data that must be retained for 7 years but is rarely queried, keeping all of it in Snowflake solely for retention is usually not the most cost-effective strategy. A better pattern is to archive historical data to external object storage with appropriate security and lifecycle controls, while retaining only the active subset in Snowflake. This aligns with Snowflake best practices around using Time Travel for operational recovery and externalized archival storage for long-term retention, especially for large semi-structured datasets.
- A. Incorrect.
Incorrect. Time Travel retention in Snowflake is not designed to keep data queryable for 7 years. Standard Time Travel supports much shorter retention periods, and using Time Travel as a long-term archival strategy is neither supported nor cost-effective. This option confuses short-term recovery capabilities with compliance-grade archival retention.
- B. Correct.
Correct. This approach separates short-term operational recovery from long-term archival retention. Keeping structured tables at 90 days satisfies the stated recoverability requirement where supported by the account edition and object settings. For semi-structured raw JSON that is rarely queried after the first month, a common best practice is to retain only the actively used portion in Snowflake and archive older data to governed external object storage. This controls Snowflake storage costs while still meeting long-term audit retention requirements.
- C. Incorrect.
Incorrect. Fail-safe is a Snowflake-managed disaster recovery mechanism, not a user-accessible retention feature for business recovery or compliance archiving. Users cannot query or restore data directly from Fail-safe on demand to meet a 90-day recovery objective, and Fail-safe does not satisfy a 7-year retention policy.
- D. Incorrect.
Incorrect. Temporary tables are session-scoped and are dropped at the end of the session, so they are unsuitable for long-term retention. This option reflects a misunderstanding of temporary object behavior and would undermine both audit retention and future access requirements.