SnowPro Advanced: Security Engineer Question 181
Single answerUse table metadata and access patterns to determine data aging strategiesA Security Engineer is reviewing a Snowflake environment that stores customer support data, including PII, in several large tables. The company wants to reduce long-term exposure of sensitive data and lower storage cost without disrupting current analytics. Internal policy requires that data actively used by analysts remain quickly accessible, but data that has not been queried recently should be archived or removed based on retention rules. The engineer must recommend an approach that uses Snowflake metadata and observed access patterns to decide which tables are candidates for aging. Which approach best meets the requirement?
- A
Use ACCOUNT_USAGE.ACCESS_HISTORY and QUERY_HISTORY to identify tables with little or no recent query activity, combine that with table metadata such as row counts and storage metrics from ACCOUNT_USAGE.TABLE_STORAGE_METRICS / TABLES, and then apply retention, archival, or deletion actions according to policy.
- B
Use only the table creation date from INFORMATION_SCHEMA.TABLES, and archive any table older than the policy threshold because older tables are less likely to contain active data.
- C
Use warehouse credit consumption from WAREHOUSE_METERING_HISTORY to determine which tables are inactive, and archive tables associated with the lowest-cost warehouses.
- D
Use masking policies on all sensitive columns and keep all historical tables online indefinitely, because masking eliminates the need for a separate data aging strategy.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to evaluate both object metadata and access patterns before deciding on a data aging action. In Snowflake, governance decisions should be based on evidence such as whether tables are actually being queried and how much data they contain. Relevant sources include SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY for object access, QUERY_HISTORY for query activity, and metadata views such as ACCOUNT_USAGE.TABLES and TABLE_STORAGE_METRICS for storage-related details. This supports the security principle of data minimization: retain sensitive data only as long as it is needed and in the appropriate storage state. By contrast, relying only on object age, warehouse cost, or masking policies would not provide an accurate or complete basis for aging decisions. This matches Snowflake governance and security best practices that emphasize monitoring usage, understanding object metadata, and enforcing retention according to policy.
- A. Correct.
Correct. This approach aligns with Snowflake best practices because it combines actual access patterns with table metadata. ACCESS_HISTORY can help identify which objects were accessed by queries, and QUERY_HISTORY provides supporting evidence about usage over time. Table metadata such as row counts, bytes, and storage characteristics from views like ACCOUNT_USAGE.TABLES and TABLE_STORAGE_METRICS helps quantify impact and prioritize candidates. Using both usage and metadata supports a defensible aging strategy for sensitive data, allowing teams to archive or purge data according to policy rather than relying on assumptions.
- B. Incorrect.
Incorrect. Creation date alone is not a reliable indicator of whether data is still actively used. An older table may still be queried frequently for reporting, audits, or investigations. This option reflects a common misconception that age equals inactivity. Effective data aging should consider both retention requirements and actual access behavior, not just object age.
- C. Incorrect.
Incorrect. WAREHOUSE_METERING_HISTORY shows warehouse-level compute consumption, not which specific tables are active or inactive. Low warehouse usage does not prove that a table stored in Snowflake is unused, and high warehouse usage does not indicate which tables should be retained. This confuses compute monitoring with object-level access analysis.
- D. Incorrect.
Incorrect. Masking policies reduce exposure of sensitive columns at query time, but they do not replace retention and minimization controls. Keeping all historical sensitive data online indefinitely increases risk and storage cost, even if masked. Security best practice is defense in depth: use masking where appropriate, but also apply lifecycle management and aging strategies based on business and compliance requirements.