What you will be able to do
- Use QUERY_HISTORY to find what statements a compromised identity ran, under which role, and how much data was affected
- Use ACCESS_HISTORY to identify the exact tables and columns read or modified, including reads inside stored procedures
- Use LOGIN_HISTORY to trace source IP, client and authentication factors, and to spot patterns such as password spraying
- Join Snowflake evidence into a single timeline and identify the keys that link it to logs from outside Snowflake
1.QUERY_HISTORY: what was done
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY has one row per statement for the past 365 days. You can filter it by time range, session, user or warehouse. In an investigation, the useful columns are query_text (the SQL itself, cut off at 100K characters), user_name, role_name (the role active when the statement ran), query_type, session_id, and start_time and end_time.
The volume columns show the impact. rows_deleted, rows_updated and rows_inserted measure tampering. bytes_written_to_result and rows_unloaded point to data that was pulled out, and the outbound_data_transfer_* columns show unloads to another region or cloud. Snowflake's Well-Architected guidance uses this view to find privilege escalation, security configuration changes and data object modifications. Each row's query_id is also the value you pass to Time Travel's BEFORE(STATEMENT => ...) to see the data before that change.
Watch how status is recorded. execution_status has three values: success, fail and incident. A query that was cancelled, perhaps by a responder or by the attacker, is not marked by status. It shows up through error_message text. The view's latency can be up to 45 minutes.
Checkpoint 1 of 6· Check yourself
You need to list the attacker's queries that a responder cancelled during containment. Which filter on QUERY_HISTORY finds them?
Cancelled queries are identified by their error_message text. execution_status only takes the values success, fail and incident.
“Canceled queries are identified by their error_message text (SQL execution canceled), not by their execution_status value.”Source: docs.snowflake.com
2.ACCESS_HISTORY: which objects and columns were touched
Query text tells you what was asked for, but working out which columns a query actually reached through views and functions is hard. ACCESS_HISTORY (Enterprise Edition or higher) does that work for you, one record per query_id, kept for a year. Its arrays separate three things:
- direct_objects_accessed: the objects named in the query, including those reached through SELECT *. - base_objects_accessed: the underlying objects the query needed in order to run. If the attacker read a view, this is where the base tables and columns show up. - objects_modified: the targets of writes. Each column comes with directSources and baseSources, which give lineage for data copied into staging tables.
object_modified_by_ddl records DDL, including attaching masking or row access policies. policies_referenced shows which policies were enforced on the columns that were read, which helps you judge whether the attacker saw masked or clear values.
{ "columns": [ { "columnId": 68610, "columnName": "CONTENT" } ], "objectDomain": "Table", "objectId": 66564, "objectName": "GOVERNANCE.TABLES.T1" }Stored procedures can hide access. Every child statement gets its own record, but parent_query_id and root_query_id link it back. root_query_id is the topmost job in the chain, so grouping by it gathers every table read inside one CALL. Also check for truncation: a -1 in a number field or TRUNCATED in a string field means some data may be missing from that record.
Checkpoint 2 of 6· Match them up
Match each ACCESS_HISTORY column to the forensic question it answers
Tap a term, then the definition that fits it.
Each column covers a different part of the access record. root_query_id is the topmost job in the chain, so it links nested reads back to one CALL.
“The query ID of the topmost job in the chain or NULL if the job does not have a parent.”Source: docs.snowflake.com
Checkpoint 3 of 6· Exam question
Which statement about Fail-safe is accurate when planning evidence recovery for a permanent table that an attacker truncated after its Time Travel retention expired?
Correct answer: B — Fail-safe keeps data for seven days beyond Time Travel on permanent tables, but only Snowflake can recover it, so it is a last resort rather than an analyst tool.
- A. Incorrect. Fail-safe is fixed at seven days and is not configurable. It cannot be queried with AT or BEFORE; those clauses only work within Time Travel.
- B. Correct. Fail-safe is a non-configurable seven-day period for permanent tables after Time Travel ends, and recovery requires contacting Snowflake Support on a best-effort basis. It is not self-service.
- C. Incorrect. The retention parameter controls Time Travel, not Fail-safe, and UNDROP only works while the dropped object is still inside its Time Travel window.
- D. Incorrect. Transient and temporary tables have no Fail-safe at all, and there is no FAIL_SAFE_DAYS property that analysts can change.
Sources3
3.LOGIN_HISTORY: who got in, from where, and how
LOGIN_HISTORY records every login attempt, successful or not, for the past year, with up to 2 hours of latency. Each column answers part of the question of how the attacker got in.
| Column | What it tells the investigator |
|---|---|
| CLIENT_IP | Source IPv4 or IPv6 address of the request |
| REPORTED_CLIENT_TYPE / REPORTED_CLIENT_VERSION | Client software as reported by the client (not authenticated) |
| FIRST_AUTHENTICATION_FACTOR / SECOND_AUTHENTICATION_FACTOR | How the user authenticated. SECOND is NULL when MFA was not used |
| FIRST_AUTHENTICATION_FACTOR_ID | Which specific credential was used |
| IS_SUCCESS / ERROR_CODE / ERROR_MESSAGE | Whether the attempt succeeded and why it failed |
| LOGIN_DETAILS | Malicious IP protection category, risk category and blocking status |
| AUTHORIZING_INTEGRATION_NAME / CLIENT_PRIVATE_LINK_ID | Security integration that authorized an OAuth login, or the private connectivity endpoint used |
Three details trip up investigators. First, REPORTED_CLIENT_TYPE is whatever the client claims to be, so an attacker can spoof it. Treat it as a hint, not proof. Second, failed attempts where Snowflake could not resolve the user, such as invalid OAuth client credentials or an unknown user in a key-pair attempt, appear with USER_NAME set to NULL. A credential attack can therefore look like nameless failures. Third, INTERNAL_SNOWFLAKE_IP/0.0.0.0 is Snowflake's own internal activity, for example Snowsight worksheet sessions. It is not a suspicious address. To find spraying, group failures by CLIENT_IP across many USER_NAME values and look for a later IS_SUCCESS from the same address.
Checkpoint 4 of 6· Check yourself
LOGIN_HISTORY shows that a suspicious session reported REPORTED_CLIENT_TYPE = JDBC_DRIVER. How much weight should that value carry?
Snowflake documents the reported client type and version as unauthenticated, so an attacker can set them to anything.
“Reported type of the client software, such as JDBC_DRIVER, ODBC_DRIVER, and so on. This information is not authenticated.”Source: docs.snowflake.com
Checkpoint 5 of 6· Exam question
An attacker ran an UPDATE against the ORDERS table that altered prices. The offending statement's query ID is known and is still inside the table's Time Travel window. The investigators need the pre-change data preserved as evidence without disturbing production. What should they do?
Correct answer: D — Run CREATE TABLE orders_evidence CLONE orders BEFORE(STATEMENT => '<query_id>') in a restricted schema, creating a zero-copy snapshot of the pre-update state.
- A. Incorrect. Replacing the production table with a clone restores data, but it also overwrites the current table, destroying the post-attack state that forensic work needs and altering live data.
- B. Incorrect. UNDROP only restores an object that was dropped. The table was updated, not dropped, so there is nothing for UNDROP to restore.
- C. Incorrect. Setting retention to zero disables Time Travel and makes historical data unavailable, which would destroy the evidence rather than freeze it.
- D. Correct. Cloning with BEFORE(STATEMENT => ...) creates a point-in-time copy without touching production, and it keeps the historical data beyond the original retention window as the clone owns its own micro-partitions.
Sources4
4.Building the timeline and correlating outside Snowflake
Inside Snowflake, two join keys build the whole chain. QUERY_HISTORY.authn_event_id = LOGIN_HISTORY.EVENT_ID links each statement to the login that authenticated it: IP, client and factors. ACCESS_HISTORY.query_id is the same identifier as in query history, so you can attach the exact columns read or written. Together you get one row per action: who logged in, from where, how, what they ran, and what they touched.
Check the time zones before you sort. The documentation gives LOGIN_HISTORY.EVENT_TIMESTAMP and ACCESS_HISTORY.query_start_time in UTC, while QUERY_HISTORY.start_time is described as local time zone. Normalise all three before you merge them.
For evidence outside Snowflake, the sources only give you the matching keys, not a procedure. The keys are CLIENT_IP, which you can compare with firewall or network logs, AUTHORIZING_INTEGRATION_NAME and the authentication factors, which you can compare with identity-provider sign-ins, and the UTC timestamps. Snowflake's Well-Architected guidance also recommends correlating the compromised user's activity with threat intelligence feeds. How to ingest or query IdP and network-device logs is not covered by the supplied documentation.
Checkpoint 6 of 6· Put it in order
Put these steps for reconstructing a single malicious action in order, following the join keys
- 1.Read CLIENT_IP and the authentication factors from that login event
- 2.Find the suspicious statement and its query_id in QUERY_HISTORY
- 3.Match that CLIENT_IP and timestamp against identity-provider and network logs
- 4.Use its authn_event_id to look up the matching EVENT_ID in LOGIN_HISTORY
The statement supplies authn_event_id. That leads to the login record, which supplies the IP and factors used as keys into external logs.
“This ID corresponds to the value in the event_id column in the LOGIN_HISTORY view.”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Cancelled queries can be found by filtering QUERY_HISTORY on execution_status.Why is that wrong?
execution_status only takes the values success, fail and incident. Cancellation is recorded in error_message.
Covered in QUERY_HISTORY: what was done
2.A login from INTERNAL_SNOWFLAKE_IP/0.0.0.0 is a sign of a masked or suspicious source.Why is that wrong?
That address marks login events from Snowflake's own internal operations, such as Snowsight worksheet sessions.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Text of the SQL statement. The limit is 100K characters. Longer SQL statements are truncated.”
↩︎ QUERY_HISTORY: what was done“Role that was active in the session at the time of the query.”
↩︎ QUERY_HISTORY: what was done“Latency for the view may be up to 45 minutes.”
↩︎ QUERY_HISTORY: what was done“Statement start time (in the local time zone).”
↩︎ Building the timeline and correlating outside Snowflake“Canceled queries are identified by their error_message text (SQL execution canceled), not by their execution_status value.”
↩︎ Exam trap 1“Canceled queries are identified by their error_message text (SQL execution canceled), not by their execution_status value.”
↩︎ Checkpoint“This ID corresponds to the value in the event_id column in the LOGIN_HISTORY view.”
↩︎ Prediction - 2.https://www.snowflake.com/en/developers/guides/well-architected-framework-security-and-governanceSecondary source
“Query the QUERY_HISTORY view to identify any unauthorized changes, such as privilege escalation, security configuration changes, or data object modifications.”
↩︎ QUERY_HISTORY: what was done“Correlate the compromised user's activity with known threat intelligence feeds to identify potential malicious activity.”
↩︎ Building the timeline and correlating outside Snowflake - 3.
“directly named in the query explicitly or through shortcuts such as using an asterisk”
↩︎ ACCESS_HISTORY: which objects and columns were touched“A JSON array of all base data objects to execute a query, including columns, external functions, UDFs, and stored procedures.”
↩︎ ACCESS_HISTORY: which objects and columns were touched“A JSON array that specifies the objects that were associated with a write operation in the query.”
↩︎ ACCESS_HISTORY: which objects and columns were touched“If a column contains -1 in a number field or TRUNCATED in a string field, information in the column might have been truncated.”
↩︎ ACCESS_HISTORY: which objects and columns were touched“The statement start time (UTC time zone).”
↩︎ Building the timeline and correlating outside Snowflake“The query ID of the topmost job in the chain or NULL if the job does not have a parent.”
↩︎ Checkpoint - 4.
“IP address where the request originated. This value can be an IPv4 or IPv6 address.”
↩︎ LOGIN_HISTORY: who got in, from where, and how“Failed authentication attempts where the user could not be identified appear with a NULL USER_NAME.”
↩︎ LOGIN_HISTORY: who got in, from where, and how“If the user did not use multi-factor authentication, this value is NULL.”
↩︎ LOGIN_HISTORY: who got in, from where, and how“Time (in the UTC time zone) of the event occurrence.”
↩︎ Building the timeline and correlating outside Snowflake“For OAuth logins, this is the security integration used to grant access.”
↩︎ Building the timeline and correlating outside Snowflake“INTERNAL_SNOWFLAKE_IP/0.0.0.0 appears as the client IP for login events triggered by internal Snowflake operations that support your usage.”
↩︎ Exam trap 2“Reported type of the client software, such as JDBC_DRIVER, ODBC_DRIVER, and so on. This information is not authenticated.”
↩︎ Checkpoint