SnowPro Advanced: Security Engineer Question 373
Single answerPerform a forensic analysis:A Snowflake security engineer is investigating a suspected data exfiltration incident. A sensitive table in the FINANCE schema may have been queried by an internal user outside normal business hours and then exported. The engineer needs to determine whether the user accessed the table, what SQL was executed, and whether results were likely copied out of Snowflake. Which approach provides the most effective forensic analysis using Snowflake-native telemetry?
- A
Query ACCOUNT_USAGE.ACCESS_HISTORY together with ACCOUNT_USAGE.QUERY_HISTORY and review statements such as SELECT and COPY INTO
; correlate by query identifiers, user, role, objects accessed, and timestamps. - B
Query INFORMATION_SCHEMA.TABLES to identify the owner of the sensitive table and assume the owner is responsible for any exfiltration because ownership implies query execution.
- C
Review only LOGIN_HISTORY to verify the user logged in outside business hours; if the login exists, that is sufficient to prove the table was queried and exported.
- D
Use SHOW GRANTS ON TABLE for the sensitive table and conclude that any role with SELECT privilege definitely queried and exported the data during the incident window.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use Snowflake-native audit and access telemetry, primarily ACCOUNT_USAGE.QUERY_HISTORY and ACCOUNT_USAGE.ACCESS_HISTORY. QUERY_HISTORY provides the executed SQL text and execution context, while ACCESS_HISTORY helps determine what underlying objects were accessed by a given query. In a suspected exfiltration case, investigators typically narrow to the incident window, identify the user and role in use, review the SQL text for sensitive-table reads, and look for unloading patterns such as COPY INTO commands targeting stages or external locations. LOGIN_HISTORY and grant metadata can support the investigation, but they do not establish that data was actually queried or exported. This aligns with Snowflake best practices for auditing and forensic review using Account Usage views and access monitoring capabilities.
- A. Correct.
Correct. Forensic analysis in Snowflake should rely on account usage telemetry that records executed queries and object access. QUERY_HISTORY helps identify the exact SQL text, execution time, user, role, warehouse, and query ID. ACCESS_HISTORY helps determine which objects were actually accessed by a query, which is especially important when views or complex SQL are involved. Looking for statements such as SELECT against the sensitive table and COPY INTO
can help establish whether data was queried and potentially unloaded to an external location. Correlating by query ID, user, role, and time window is a standard investigative approach. - B. Incorrect.
Incorrect. INFORMATION_SCHEMA.TABLES can provide metadata about objects, but object ownership does not indicate who queried or exported data. Ownership is an administrative privilege concept, not an audit trail of actual activity. Choosing this option reflects the misconception that permission assignment or ownership is equivalent to usage evidence.
- C. Incorrect.
Incorrect. LOGIN_HISTORY is useful to establish whether and when a user authenticated, from where, and with what client details, but a login event alone does not prove a query was run, which objects were accessed, or whether data was exported. It is supporting evidence, not sufficient evidence for exfiltration analysis by itself.
- D. Incorrect.
Incorrect. SHOW GRANTS ON TABLE identifies which roles have privileges on the object, but it does not show actual usage. A role having SELECT privilege means access was possible, not that it happened. This option confuses authorization state with observed activity, which is a common forensic mistake.