SnowPro Advanced: Security Engineer Question 372
Single answerPerform a forensic analysis:A Security Engineer is investigating a suspected data exfiltration incident in Snowflake. An analyst believes a contractor account may have queried sensitive customer tables and copied results out of Snowflake during the last 7 days. The Security Engineer needs to reconstruct what SQL was executed by that user, determine which objects were accessed, and preserve enough evidence for follow-up analysis even if standard account usage retention windows are exceeded. Which approach BEST meets these requirements?
- A
Query ACCOUNT_USAGE views such as QUERY_HISTORY and ACCESS_HISTORY for the contractor user, then persist the relevant results into a secured internal forensic schema for retention and further analysis.
- B
Review only LOGIN_HISTORY for the contractor user because successful logins prove which tables were queried and whether data was exported.
- C
Use SHOW GRANTS TO USER for the contractor account as the primary evidence source because granted privileges definitively prove which tables were accessed.
- D
Inspect WAREHOUSE_METERING_HISTORY for the warehouses used by the contractor because warehouse consumption identifies the exact SQL text and exported result sets.
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, effective forensic analysis typically combines multiple metadata sources, with QUERY_HISTORY and ACCESS_HISTORY being the most relevant for reconstructing user activity and determining data access. QUERY_HISTORY helps identify the exact statements run, execution times, sessions, and query context. ACCESS_HISTORY provides object-level lineage and access evidence, which is especially valuable when queries reference views or complex SQL paths. LOGIN_HISTORY can complement the analysis by confirming authentication patterns and source details, but it is not sufficient by itself. SHOW GRANTS and warehouse metering data describe authorization and resource consumption, respectively, rather than actual object access. As a best practice, investigators should preserve relevant evidence by copying needed metadata into a secure internal schema or dedicated forensic repository because operational metadata views are not intended to serve as indefinite evidence stores. This aligns with Snowflake documentation and best practices around Account Usage, Information Schema history functions/views, and object access auditing.
- A. Correct.
Correct. For forensic analysis in Snowflake, QUERY_HISTORY is the primary source to reconstruct executed SQL statements, timing, session details, and query metadata. ACCESS_HISTORY is critical for understanding which underlying objects were actually accessed by the queries, which is especially important when views or indirect object references are involved. Persisting the results into a secured internal schema is a sound forensic practice because Snowflake history views have defined retention behavior and investigators often need to preserve evidence beyond standard windows. This option best addresses all three requirements: SQL reconstruction, object access determination, and evidence preservation.
- B. Incorrect.
Incorrect. LOGIN_HISTORY is useful for investigating authentication events, source IPs, client details, and login success or failure, but it does not prove which SQL statements were executed or which tables were accessed. An investigator might start with login history to validate session timing, but relying on it alone would miss the core forensic evidence needed for data access analysis.
- C. Incorrect.
Incorrect. SHOW GRANTS TO USER reveals privileges assigned to the user, not actual usage. A common misconception is to treat permissions as evidence of access. In reality, a user may have broad privileges and never use them, or may access objects indirectly through roles and views. Forensic analysis requires activity evidence such as query and access history, not just authorization state.
- D. Incorrect.
Incorrect. WAREHOUSE_METERING_HISTORY helps analyze compute consumption and warehouse usage trends, but it does not provide the exact SQL text, object-level access details, or proof of exported query results. It may help correlate activity to time windows or cost impact, but it is not a primary forensic source for reconstructing user actions.