SnowPro Advanced: Security Engineer Question 170
Single answerDefine appropriate retention strategies for structured and semi-structured data:A healthcare analytics company stores patient encounter data in Snowflake. Core relational attributes are stored in structured columns, and the full incoming clinical payload is retained in a VARIANT column for audit and reprocessing purposes. The security team must enforce these requirements: (1) patient data must be recoverable for at least 14 days after accidental deletion or overwrite, (2) long-term storage costs for historical semi-structured payloads must be minimized, and (3) audit investigators must be able to query older payloads only when needed without keeping them in high-cost active storage. Which approach best meets these requirements?
- A
Set a 14-day Time Travel retention period on the table, then periodically unload older VARIANT data to an encrypted external stage and remove those older rows from the active table while keeping metadata needed to reload or reference them.
- B
Set the table's Time Travel retention period to 0 days and rely on Fail-safe for the 14-day recovery requirement, because Fail-safe provides a lower-cost recovery window for accidental deletes and updates.
- C
Keep all structured and semi-structured data in the same table indefinitely, and reduce storage costs by clustering on the VARIANT column so Snowflake can compress the historical payloads more aggressively.
- D
Convert the VARIANT column to VARCHAR before storing it, because string data has lower retention overhead than semi-structured data and can still be queried efficiently for investigations.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to combine Snowflake Time Travel for short-term operational recovery with an archive strategy for older semi-structured payloads. In Snowflake, Time Travel provides access to historical data for a defined retention period, allowing recovery from accidental DML operations such as deletes and updates. Fail-safe exists after Time Travel but is Snowflake-managed and is not designed for normal user access or self-service recovery, so it should not be used to satisfy a business requirement for 14-day recoverability. For cost control, rarely accessed historical VARIANT data should not remain indefinitely in active tables if the organization wants to minimize storage costs. A common best practice is to retain recent data in Snowflake for operational recovery and query needs, while archiving older semi-structured payloads to encrypted external object storage. This is especially relevant when structured columns remain actively queried but full raw payloads are needed only occasionally for audit or reprocessing. Relevant Snowflake documentation includes guidance on Time Travel and Fail-safe behavior, data retention parameters, and external stages/unload patterns for managing cold data.
- A. Correct.
Correct. A 14-day Time Travel retention period supports user-accessible recovery from accidental deletes or updates for the required window. For older semi-structured payloads that are rarely queried, unloading them to encrypted external storage and removing them from the active Snowflake table is an effective cost-management strategy. This preserves recoverability in Snowflake for recent changes while shifting cold data to lower-cost storage that can still be queried later through reload processes or external-table-style patterns depending on design. This aligns with a practical retention strategy for structured versus semi-structured data when audit access is infrequent.
- B. Incorrect.
Incorrect. Fail-safe is not a substitute for Time Travel and is not intended for routine user-directed recovery. Fail-safe is a Snowflake-managed disaster recovery mechanism after Time Travel expires, and customers cannot directly query or self-restore data from Fail-safe on demand. Therefore, setting Time Travel to 0 days would fail the requirement that patient data be recoverable for at least 14 days after accidental deletion or overwrite by the security or operations team.
- C. Incorrect.
Incorrect. Keeping all payloads indefinitely in active Snowflake storage does not meet the cost-minimization goal for historical semi-structured data. Also, clustering is primarily for query performance and partition pruning, not a retention or storage-tiering mechanism. Clustering on a VARIANT column is not an appropriate answer to long-term retention cost management, and it does not create a lower-cost archive model for infrequently accessed audit payloads.
- D. Incorrect.
Incorrect. Converting VARIANT to VARCHAR removes native semi-structured semantics and makes downstream querying and governance less effective. It does not inherently create a better retention model or guaranteed lower storage cost, and it would likely make investigations harder because the JSON would need to be reparsed. The requirement is about retention strategy and recoverability, not datatype simplification.