SnowPro Advanced: Security Engineer Question 249
Single answerMonitor data access and data transfer history:A Snowflake security engineer is investigating whether sensitive customer records were exfiltrated during the past 7 days. The team needs to identify both: (1) which users queried a protected table, and (2) whether any data was unloaded from Snowflake to an external location. The account has Enterprise Edition enabled and standard ACCOUNT_USAGE views available. Which approach will best satisfy both requirements with the most direct built-in monitoring sources?
- A
Query ACCESS_HISTORY to identify reads against the protected table, and query COPY_HISTORY to find unload operations to external stages.
- B
Query LOGIN_HISTORY to identify which users accessed the table, and query QUERY_HISTORY to find all data transfers to external locations.
- C
Query ACCESS_HISTORY to identify object-level reads against the protected table, and query QUERY_HISTORY filtered for COPY INTO
statements to identify unload activity. - D
Query TABLE_STORAGE_METRICS to identify reads against the protected table, and query STAGE_STORAGE_USAGE_HISTORY to find all unload activity to external stages.
Show answer and explanation
Correct answer: C
Explanation
For monitoring sensitive data access, ACCESS_HISTORY in the ACCOUNT_USAGE schema is the appropriate forensic source because it records object access lineage for queries, including which base objects were accessed and by whom. This is significantly more reliable than inferring access from login activity. For monitoring data transfer history related to possible exfiltration, security engineers should inspect QUERY_HISTORY for unload commands such as COPY INTO
- A. Incorrect.
Incorrect. ACCESS_HISTORY is the correct built-in source for object-level data access lineage, including which base objects were accessed by queries. However, COPY_HISTORY is primarily used to monitor data loading and includes information about COPY INTO
operations, not outbound unload activity using COPY INTO. A common misconception is to assume COPY_HISTORY covers both load and unload directions. - B. Incorrect.
Incorrect. LOGIN_HISTORY only shows authentication events and session connection details; it does not indicate whether a user actually queried a specific table. QUERY_HISTORY does record executed SQL text and can help identify COPY INTO
commands, but using LOGIN_HISTORY for table access is not sufficient for monitoring data access. - C. Correct.
Correct. ACCESS_HISTORY is the most direct built-in source to determine which queries and users accessed a protected table at the object/column lineage level. For outbound transfer monitoring, Snowflake does not provide unload events in COPY_HISTORY; instead, QUERY_HISTORY should be searched for statements such as COPY INTO @stage, COPY INTO 's3://...', or similar unload SQL patterns. This combination best addresses both requirements using native account views.
- D. Incorrect.
Incorrect. TABLE_STORAGE_METRICS is for storage and retention-related metadata, not query-level access monitoring. STAGE_STORAGE_USAGE_HISTORY tracks storage usage over time, not specific unload statements or exfiltration events. These views are useful for capacity and cost analysis, not forensic investigation of table reads and outbound transfers.