SnowPro Advanced: Security Engineer Question 246
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 data exfiltration incident in Snowflake. An analyst suspects that a contractor role ran unusual SELECT statements overnight and may have accessed sensitive customer tables through a BI tool using a shared service user. The engineer needs to determine which user or role executed the queries, which objects were actually accessed, and whether the query pattern was unusual based on execution context. Which approach provides the most reliable investigation using Snowflake account usage views?
- A
Query ACCOUNT_USAGE.QUERY_HISTORY to identify suspicious statements by filtering on execution time, user_name, role_name, client application, and query text patterns, then join to ACCOUNT_USAGE.ACCESS_HISTORY using query_id to confirm the base objects and columns accessed by those queries.
- B
Query ACCOUNT_USAGE.ACCESS_HISTORY only, because it already contains the full SQL text, client application, and warehouse details needed to identify suspicious query behavior and the exact execution context.
- C
Query INFORMATION_SCHEMA.QUERY_HISTORY and use only the direct_objects_accessed column there, because INFORMATION_SCHEMA has a longer retention period and is better suited than ACCOUNT_USAGE for historical security investigations.
- D
Use LOGIN_HISTORY to identify the source IP address and assume that any successful login from the BI tool corresponds directly to the sensitive queries, since Snowflake guarantees a one-to-one mapping between login events and query activity.
Show answer and explanation
Correct answer: A
Explanation
For a Snowflake security investigation, QUERY_HISTORY and ACCESS_HISTORY serve complementary purposes. QUERY_HISTORY helps identify suspicious patterns such as unusual SQL statements, execution timing, role usage, warehouse choice, and client/tool context. ACCESS_HISTORY is then used to verify which base objects and columns were actually accessed, including cases where queries run through views or tools obscure direct table access. Joining these views by query_id is a practical and reliable method for correlating suspicious activity with actual data access. This aligns with Snowflake best practices for auditing query activity and object access using ACCOUNT_USAGE views. LOGIN_HISTORY can enrich the investigation with authentication context, but it should not be used as a substitute for query-level and object-level auditing.
- A. Correct.
Correct. QUERY_HISTORY is the best starting point to identify suspicious query patterns because it includes execution metadata such as query text, user_name, role_name, start/end times, warehouse, and client-related context. ACCESS_HISTORY complements this by showing what data was actually accessed, including base objects and columns referenced by a query. Joining the two on query_id is the most reliable way to correlate suspicious statements with actual data access and investigate whether a shared service account or BI tool was involved.
- B. Incorrect.
Incorrect. ACCESS_HISTORY is valuable for understanding what objects and columns were accessed, but it is not the best standalone source for full query behavior analysis. It does not replace QUERY_HISTORY for reviewing SQL text patterns and broader execution context. Relying on ACCESS_HISTORY alone can miss important indicators such as unusual query text, execution timing, query status, and other metadata available in QUERY_HISTORY.
- C. Incorrect.
Incorrect. This option combines two misconceptions. INFORMATION_SCHEMA query history functions/views are intended for more limited operational use and do not provide a longer retention period than ACCOUNT_USAGE. Also, direct object access details relevant to lineage and access auditing are associated with ACCESS_HISTORY, not something you should assume is sufficiently captured in INFORMATION_SCHEMA alone for a historical security investigation.
- D. Incorrect.
Incorrect. LOGIN_HISTORY can help identify authentication events and source IP information, but it cannot by itself prove which queries accessed which data. There is no guaranteed one-to-one relationship between a login event and specific query executions, especially with shared service users, session reuse, connection pooling, or BI tools. It is useful as supplemental evidence, not the primary method for query-to-data-access attribution.