SnowPro Advanced: Security Engineer Question 180
Single answerUse table metadata and access patterns to determine data aging strategiesA healthcare company stores PHI in a Snowflake table named PATIENT_EVENTS. Security policy requires minimizing the exposure window of older sensitive data while still supporting investigations for the last 90 days. The Security Engineer must recommend a data aging strategy based on actual table usage, not assumptions. Review shows that PATIENT_EVENTS has not been queried directly in 8 months, but a secure view over the table is queried heavily every day by analysts. Time Travel is currently set to 7 days on the schema, and Fail-safe remains at the Snowflake default. Which action is the MOST appropriate to take first to determine whether retention settings and archival controls can be tightened safely?
- A
Query ACCOUNT_USAGE.ACCESS_HISTORY and related query metadata to confirm whether the secure view is the access path and whether the base table is still referenced indirectly, then use that evidence to design retention and archival changes.
- B
Reduce Time Travel on PATIENT_EVENTS to 0 days immediately because the table itself has not been queried directly for 8 months, which proves the data is inactive.
- C
Clone PATIENT_EVENTS to a new archive schema and drop the original table, because cloning preserves the data without requiring further analysis of access patterns.
- D
Review only INFORMATION_SCHEMA.TABLES for row counts and bytes stored, because storage size is sufficient to determine whether the table should be aged out.
Show answer and explanation
Correct answer: A
Explanation
The best first action is to determine actual usage through Snowflake metadata that reflects object access patterns, especially because secure views can mask direct table usage. In Snowflake, ACCOUNT_USAGE.ACCESS_HISTORY is commonly used to analyze which base objects were accessed by queries, including access mediated by views, while query history helps correlate usage timing and consumers. This is critical for data aging strategies in a security context: before tightening retention or archiving sensitive data, confirm whether the data is still needed indirectly, such as through secure views, tasks, or applications. INFORMATION_SCHEMA object metadata and storage statistics are helpful for inventory and sizing, but they do not show the full access path. Similarly, changing Time Travel or dropping objects before validating dependencies is risky. Snowflake best practice is to use object metadata plus observed access patterns to make retention decisions that reduce data exposure without disrupting legitimate business access. Relevant Snowflake references include the Account Usage views for ACCESS_HISTORY and QUERY_HISTORY, as well as documentation on Time Travel, Fail-safe, secure views, and metadata views in INFORMATION_SCHEMA and ACCOUNT_USAGE.
- A. Correct.
Correct. For a security-focused data aging decision, the engineer should validate real access patterns using Snowflake metadata such as ACCOUNT_USAGE.ACCESS_HISTORY and query history views. A table may appear unused if users access it through a secure view rather than directly. ACCESS_HISTORY helps identify underlying object usage, including whether the secure view is the true access path to the base table. This is the right first step before changing Time Travel, retention, or archival because it prevents breaking active workloads and supports a defensible minimization strategy based on evidence.
- B. Incorrect.
Incorrect. Lack of direct queries against the base table does not mean the data is inactive. In this scenario, analysts query a secure view heavily, and that view may still depend on PATIENT_EVENTS. Reducing Time Travel immediately could undermine operational recovery needs and change the recovery posture without confirming actual dependencies. Time Travel settings should be adjusted only after validating access patterns and business requirements.
- C. Incorrect.
Incorrect. Zero-copy cloning is useful for creating copies efficiently, but it does not answer the key question of whether PATIENT_EVENTS is still actively used through dependent objects such as secure views. Dropping the original table without dependency and usage analysis could break production access. Cloning is not a substitute for determining data aging strategy from metadata and access history.
- D. Incorrect.
Incorrect. INFORMATION_SCHEMA.TABLES can provide structural and storage-related metadata, but row count and bytes stored do not indicate whether data is still being accessed through views or other downstream objects. Storage metrics may inform cost discussions, but they are insufficient on their own for a security-driven aging strategy focused on minimizing exposure while preserving required access.