SnowPro Advanced: Security Engineer Question 247
Single answerAnalyze the QUERY_HISTORY and ACCESS_HISTORY views to identify suspicious query patterns and unauthorized data accessA security engineer is investigating a possible insider threat in Snowflake after a user account that normally runs dashboard queries appears to have accessed sensitive customer data overnight. The engineer needs to determine whether the user executed unusually broad queries and whether those queries actually touched restricted objects. Which approach should the engineer take to most effectively identify suspicious query patterns and confirm unauthorized data access?
- A
Query ACCOUNT_USAGE.QUERY_HISTORY for the user during the time window, look for anomalies such as off-hours activity, unusually large scans, uncommon client applications, or ad hoc SQL patterns, and join the resulting QUERY_ID values to ACCOUNT_USAGE.ACCESS_HISTORY to verify which base objects and columns were accessed.
- B
Query only ACCOUNT_USAGE.ACCESS_HISTORY for the user because it contains all query text, session context, and execution performance details needed to identify both suspicious patterns and object access.
- C
Query INFORMATION_SCHEMA.QUERY_HISTORY for the database that contains the sensitive tables, because database-level Information Schema views expose cross-account object access details and column lineage for all users.
- D
Use LOGIN_HISTORY to identify the IP address and assume any query run from a corporate IP is authorized; ACCESS_HISTORY is only needed when row access policies are enabled.
Show answer and explanation
Correct answer: A
Explanation
The most effective investigation pattern is to use ACCOUNT_USAGE.QUERY_HISTORY to detect anomalous SQL activity and then correlate those findings with ACCOUNT_USAGE.ACCESS_HISTORY to confirm which sensitive objects were accessed. In practice, a security engineer would first filter QUERY_HISTORY by user, time window, role, warehouse, client application, and query text characteristics to identify suspicious executions such as off-hours activity, large scans, unexpected COPY/SELECT patterns, or manual SQL from a tool the user does not normally use. The engineer would then join those QUERY_ID values to ACCESS_HISTORY to validate whether the suspicious statements touched restricted tables, views, or columns.
This reflects Snowflake best practice: QUERY_HISTORY is used for behavioral analysis and operational context, while ACCESS_HISTORY is used for object-level auditability and lineage of accessed data. ACCOUNT_USAGE views are generally preferred for centralized account-level auditing and security investigations. Relevant Snowflake documentation includes the ACCOUNT_USAGE views for QUERY_HISTORY and ACCESS_HISTORY, which describe their schemas and intended auditing use cases.
- A. Correct.
Correct. QUERY_HISTORY is the right place to investigate suspicious query behavior such as execution time, start time, query text, client-generated versus ad hoc SQL, rows produced, bytes scanned, warehouse used, and application context. ACCESS_HISTORY complements this by showing which objects were actually accessed by a query, including base objects and, where applicable, column-level details. Joining on QUERY_ID lets the engineer move from suspicious behavior to confirmed data access, which is exactly what is needed in an insider-threat investigation.
- B. Incorrect.
Incorrect. ACCESS_HISTORY is valuable for determining what data objects a query touched, but it is not the best single source for identifying suspicious query patterns. QUERY_HISTORY contains the operational and behavioral context needed for anomaly detection, such as query text, timing, execution characteristics, user, role, warehouse, and client/application indicators. Relying only on ACCESS_HISTORY misses much of the behavior-focused evidence.
- C. Incorrect.
Incorrect. INFORMATION_SCHEMA query history functions are more limited in scope and retention than ACCOUNT_USAGE views and are not the best source for a centralized security investigation across users and objects. Also, the statement incorrectly implies database-level Information Schema can provide broad cross-account access detail and lineage equivalent to ACCESS_HISTORY, which it does not. For account-level auditing of suspicious activity, ACCOUNT_USAGE views are the appropriate choice.
- D. Incorrect.
Incorrect. LOGIN_HISTORY can help correlate sign-in behavior, source IPs, and authentication events, but it cannot prove that the resulting queries were authorized or that sensitive tables were accessed appropriately. Corporate IP address alone is not sufficient evidence of legitimacy, especially for compromised credentials or insider misuse. ACCESS_HISTORY is not limited to scenarios involving row access policies; it is broadly used for auditing object access.