CertSafari
    Snowflake SnowPro Advanced: Security Engineer (SEA-C01)· Lessons

    Domain 4 · Lesson 18/21

    Snowflake forensic analysis: preserving evidence and building the incident timeline

    Conduct a post-security-incident forensic analysis.

    20 min read
    4.5% of exam
    9 sources
    Published 20 Oct 2026
    Docs as of 18 Oct 2026

    What you will be able to do

    • Preserve Account Usage audit records in tables you control before they age out, after allowing for ingestion latency
    • Freeze the pre-incident state of a table as evidence with a Time Travel clone, and explain why Fail-safe cannot serve that purpose
    • Collect logs and data as one evidence set, cloning the fastest-expiring data first and then copying the audit views into a restricted schema
    • Establish who authenticated, from where and with which credential, using LOGIN_HISTORY fields
    • Reconstruct the statements an attacker ran and scope the objects and columns they exposed or wrote
    • Correlate login, query and access evidence with events outside Snowflake into one timeline you can defend

    Key concept

    Evidence expiry clocks — Every piece of forensic evidence in Snowflake has its own deadline. Audit views keep a fixed look-back. The pre-change version of each modified row stays recoverable for only one retention period after the change that replaced it. Preservation means copying both kinds of evidence into objects you control before their clocks run out.

    1.Preserving audit views before the evidence ages out

    A post-incident forensic analysis races several clocks at once. The record of what happened sits in system views that Snowflake keeps for a fixed period. The data the attacker touched has its own recovery window, which is usually much shorter. Collecting evidence means copying both into objects you control before either clock runs out. Only then can you build a timeline that someone outside the security team can check.

    The look-back sets the outer limit. ACCOUNT_USAGE vs INFORMATION_SCHEMA: latency and retention compares the two view families in detail. For forensics, one fact matters: the Account Usage QUERY_HISTORY, ACCESS_HISTORY and LOGIN_HISTORY views go back 365 days, but the Information Schema QUERY_HISTORY table function covers only 7. If an intrusion is found in month eleven, you can still reconstruct it from the start. If it is found in month thirteen, its first weeks are already gone unless someone copied them.

    Latency limits the recent end. Account Usage views ingest with a delay of up to two hours for LOGIN_HISTORY and up to three hours for ACCESS_HISTORY. Say an analyst filters ACCESS_HISTORY at 09:00 for statements run between 08:30 and 08:50 and gets nothing back. That empty result proves nothing: the rows have not been ingested yet. Run the filter again once the latency has passed. In the meantime, use the Information Schema QUERY_HISTORY functions to see the recent statements.

    Where the forensic record lives, and the limits that decide when to collect it
    Evidence sourceLook-backCollection consideration
    QUERY_HISTORY (Account Usage view)365 daysFull statement history; use it for the complete incident window
    QUERY_HISTORY (Information Schema table function)7 daysUse it for the most recent hours while Account Usage rows are still arriving
    LOGIN_HISTORY (Account Usage view)365 daysLatency up to 120 minutes (2 hours)
    ACCESS_HISTORY (Account Usage view)365 daysLatency up to 180 minutes (3 hours)

    Preserving the audit record means copying it. For each Account Usage view, run a CREATE TABLE ... AS SELECT filtered to the incident window, with a margin on each side, into an evidence schema. You decide who can read that schema, and the rows in it no longer age out. Run each copy only after the latency for the last event you care about has passed, or the copy will miss the end of the incident. The copy is evidence in its own right. ACCESS_HISTORY records a CTAS by listing the new table in objects_modified, along with the columns written, so it captures who ran each preservation step and when.

    Checkpoint 1 of 9· Check yourself

    At 09:00 an analyst queries ACCOUNT_USAGE.LOGIN_HISTORY for a suspect user's logins between 08:30 and 08:50 and gets no rows back. What should the analyst conclude?

    Checkpoint 2 of 9· Exam question

    An analyst starts a forensic review at 09:00 and filters SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY for statements run between 08:30 and 08:50. The view returns no rows, although QUERY_HISTORY shows the statements ran. What is the most likely explanation and the right next step?

    Sources1234

    2.Freezing the pre-incident table state with a clone

    Time Travel and DATA_RETENTION_TIME_IN_DAYS keeps the prior versions of changed or dropped data for the length of the object's retention period. For forensics, the key point is that every change starts its own clock. You can recover the state before an attacker's UPDATE for one retention period after that UPDATE ran, and no longer. Once that period ends, past objects can no longer be cloned. Before anything else, check how much time is left.

    Checking the retention period of specific tables before planning evidence capturesql
    SHOW TABLES
      ->> SELECT "name", "retention_time"
            FROM $1
            WHERE "name" IN ('MY_TABLE1', 'MY_TABLE2');

    To preserve the earlier state, clone the table into a restricted schema and position the clone with BEFORE(STATEMENT => '<query_id>'). Take the query ID of the malicious statement from QUERY_HISTORY. BEFORE returns the data up to, but not including, the changes that statement made. A command such as CREATE TABLE orders_evidence CLONE orders BEFORE(STATEMENT => '<query_id>') creates a derived copy that starts out sharing storage with the source and then has its own life-cycle. Later work on production, including the clean-up, cannot change the evidence. Try the same position with a SELECT first to confirm it shows the rows you expect.

    Reading a table as it stood immediately before a specific statementsql
    SELECT * FROM my_table BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');

    Three tempting shortcuts each destroy evidence. CREATE OR REPLACE TABLE orders CLONE orders BEFORE(...) does restore production, but it replaces the table that holds the attacker's changes, and that post-incident state is evidence too. Preserve first and recover second. UNDROP works only on dropped objects, so it cannot bring back an earlier version of a table that still exists. Setting DATA_RETENTION_TIME_IN_DAYS to 0 does not freeze the table. It turns Time Travel off for the object and sends the modified data on toward Fail-safe.

    The STATEMENT form has a second limit that has nothing to do with retention: the query ID must belong to a query run within the last 14 days. For anything older, position the clone with TIMESTAMP, using the start time from QUERY_HISTORY. Cast that timestamp explicitly. An uncast value in the AT clause is treated as UTC, which can shift the evidence snapshot by hours.

    Fail-safe is the 7-day period that follows Time Travel on permanent tables, and it cannot hold your evidence. The period is non-configurable, and it is not offered as a way to read historical data. Recovery requires a case with Snowflake Support, is best-effort, and can take from several hours to several days. No AT, BEFORE, CLONE or UNDROP statement can reach it, and transient and temporary tables have no Fail-safe period at all. This affects chain of custody in two ways. You cannot preserve data you cannot query yourself. Data that a third party recovers for you arrives through a process your custody record has to document. Clone while the data is still in Time Travel, and treat Fail-safe as a last resort.

    Collecting and preserving evidence means gathering logs and data together, shortest clock first. A permanent table left on the standard 1-day retention loses its pre-change rows 24 hours after the attacker's statement, while the audit views still hold 365 days. So clone the affected tables first. Then copy the QUERY_HISTORY, LOGIN_HISTORY and ACCESS_HISTORY rows for the incident window into the same restricted schema. The views also need that second slot because of their ingestion latency: copying them once the latency has passed is what makes the copy complete. Note the query ID of every clone and copy statement as you run it.

    The two halves answer different questions, and each is weak alone. The logs say who did what, from where and when. The clone shows what the data looked like before they did it. A clone with no logs cannot attribute the change to anyone. Logs with no clone describe a change nobody can inspect once the retention period has passed.

    Checkpoint 3 of 9· Fill the gap

    Before you plan evidence capture, you need to know how long each table keeps prior versions of changed rows. Which column of the SHOW TABLES output completes this query?

    SHOW TABLES
      ->> SELECT "name", " ? "
            FROM $1
            WHERE "name" IN ('MY_TABLE1', 'MY_TABLE2');

    Checkpoint 4 of 9· Check yourself

    During an incident, a teammate suggests setting DATA_RETENTION_TIME_IN_DAYS = 0 on the compromised permanent table to "freeze" it until the investigation is done. What does that setting actually do?

    Checkpoint 5 of 9· 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?

    Checkpoint 6 of 9· 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?

    Sources56789

    3.Establishing who got in, from where, and with which credential

    LOGIN_HISTORY and authentication anomalies explains how to detect suspicious sign-ins. In a forensic analysis, the same view answers the first question on the timeline: which authentication event gave the attacker a foothold. Every attempt has an EVENT_ID, an EVENT_TIMESTAMP in UTC, a CLIENT_IP and an IS_SUCCESS flag. FIRST_AUTHENTICATION_FACTOR tells you the method. FIRST_AUTHENTICATION_FACTOR_ID tells you which credential was used, so you can tell which of a user's keys or tokens was compromised. SECOND_AUTHENTICATION_FACTOR is NULL when no MFA was used.

    Several columns explain how the connection was made. AUTHORIZING_INTEGRATION_NAME names the security integration that authorized an OAuth login. CLIENT_PRIVATE_LINK_ID identifies the private connectivity endpoint, if one was used. LOGIN_DETAILS records the malicious-IP protection category, the risk category and the blocking status. To spot password spraying, group the failed attempts by CLIENT_IP and look for one address that hits many different USER_NAME values and then succeeds. Failures where Snowflake could not resolve a user show a NULL USER_NAME, so keep those rows in the count.

    Two artefacts mislead a careless reading. Snowsight worksheets and Snowpark Container Services sessions log in from INTERNAL_SNOWFLAKE_IP/0.0.0.0, so a filter on the attacker's external IP misses any activity they ran through a worksheet. REPORTED_CLIENT_TYPE and REPORTED_CLIENT_VERSION are not authenticated. Any client can claim to be any driver, so treat those fields as a lead to check, not as proof.

    Checkpoint 7 of 9· Match them up

    You are reconstructing the login that gave an attacker a foothold. Match each LOGIN_HISTORY column to the question it answers.

    Tap a term, then the definition that fits it.

    Sources3

    4.Reconstructing what ran and what it exposed

    QUERY_HISTORY gives you the sequence of attacker activity. Filter on the compromised user_name and order by start_time. Then use session_id to split the list into sessions and role_name to see which privileges were active for each statement. query_text holds the statement itself, cut off at 100K characters. bind_values holds the values passed to it, unless ALLOW_BIND_VALUES_ACCESS is FALSE. execution_status separates the attempts that succeeded from those that failed, and a run of failures is often how privilege probing shows up. For exfiltration, look at rows_unloaded, at bytes_written_to_result for large result sets returned to the client, and at outbound_data_transfer_cloud and outbound_data_transfer_region when data was unloaded to another region or cloud.

    ACCESS_HISTORY (direct vs base objects, columns, writes) tells you the scope of the breach. A read of a view lists the view in direct_objects_accessed and its underlying tables and columns in base_objects_accessed. Use the base list to determine which sensitive columns left the account. policies_referenced records whether a masking or row access policy was enforced on each read, which shows whether the attacker saw clear values or masked ones. objects_modified covers writes. For an unload it shows the stage's stageKind and the external location URL, which points you to the bucket to examine. object_modified_by_ddl captures DDL, including policy changes and grants to a share.

    Stored procedures and multi-statement requests hide the detail behind a single entry. Each nested statement in ACCESS_HISTORY carries parent_query_id and root_query_id. Filtering on root_query_id assigns every table read inside a procedure to the top-level CALL that started it. When a connector sends several statements as one request, the query ID it returns is the parent of the individual statements.

    Expanding one multi-statement request into its individual statementssql
    SELECT query_id, parent_query_id, direct_objects_accessed FROM snowflake.account_usage.access_history WHERE parent_query_id = 6789;

    Checkpoint 8 of 9· Fill the gap

    A Python connector sent five statements as one request and returned query ID 6789. To list each statement in the request and the objects it read, which column belongs in the WHERE clause?

    SELECT query_id, parent_query_id, direct_objects_accessed FROM snowflake.account_usage.access_history WHERE  ?  = 6789;

    Sources124

    5.Correlating the evidence into one timeline

    The three views share keys, and those keys turn three lists into one chain of events. Each query's authn_event_id matches the EVENT_ID in LOGIN_HISTORY, so every statement links back to the authentication that made it possible. query_id links QUERY_HISTORY to ACCESS_HISTORY, and root_query_id groups nested work under the call that started it. The resulting chain reads: this credential, from this address, opened this session, ran these statements, and read or wrote these columns.

    Join keys that link Snowflake evidence to itself and to outside logs
    FieldLinks toWhat the link establishes
    authn_event_id (a QUERY_HISTORY column)EVENT_ID (a LOGIN_HISTORY column)Which login authorized each statement
    query_id (a QUERY_HISTORY column)query_id (an ACCESS_HISTORY column)Which objects and columns each statement read or wrote
    root_query_id (an ACCESS_HISTORY column)query_id of the top-level callWhich procedure call caused each nested access
    CLIENT_IP (a LOGIN_HISTORY column)Network device and identity provider logsWhich device or account outside Snowflake was the source
    location (the external stage URL in ACCESS_HISTORY)Cloud storage access logsWhere unloaded data went and who retrieved it

    Time zones are the most common way a merged timeline goes wrong. LOGIN_HISTORY describes EVENT_TIMESTAMP as UTC, and ACCESS_HISTORY describes query_start_time as UTC. QUERY_HISTORY describes start_time in the local time zone. Convert every timestamp explicitly to UTC before you merge Snowflake events with identity provider sign-ins, VPN sessions or bucket access logs. Otherwise a login can appear to come after the queries it authorized, and the sequence stops making sense to anyone reviewing it.

    Chain of custody rests on the same audit trail. Keep the copied views and the evidence clones in a dedicated schema that only the investigators can read. Each clone has its own life-cycle, so changes to production do not reach it. Every CTAS, clone and later read of the evidence objects is itself recorded in ACCESS_HISTORY with a user and a time. Write down the query ID of each preservation statement in the incident record. Anyone who later doubts the evidence can then trace each table back to the exact statement, user and moment that created it.

    Checkpoint 9 of 9· Match them up

    An incident timeline is built by joining the three Snowflake views to each other and to logs from outside Snowflake. Pair each field with the link it provides.

    Tap a term, then the definition that fits it.

    Sources1326

    Practise it for real

    Preserve the pre-change state of a table as a timestamped evidence clone and confirm the preservation step is itself audited

    1. 1.Run SHOW TABLES and filter the output to the affected table's "name" and "retention_time".

      Why: The retention period tells you how long you have to clone the pre-change state.

      You should see: One row showing the table's retention period in days.

    2. 2.Find the malicious statement in QUERY_HISTORY for the compromised user and copy its query_id, noting its start_time.

      Why: The query ID positions the snapshot precisely, and the start time is your fallback if the statement is more than 14 days old.

      You should see: A single query_id for the offending DML statement.

    3. 3.Run SELECT * FROM the affected table with a BEFORE(STATEMENT => ...) clause that uses the query ID you copied, and compare a sample of rows with the current table.

      Why: Checking the position before you clone confirms the snapshot excludes the attacker's change.

      You should see: Rows showing the values as they were before the statement ran.

    4. 4.Create the evidence copy with CREATE TABLE evidence_orders CLONE orders BEFORE(STATEMENT => 'the query ID you copied'), run in a restricted evidence schema and substituting your own table name for orders.

      Why: A clone has its own life-cycle, so later remediation of production cannot alter it.

      You should see: A new table whose contents match the BEFORE query.

    5. 5.After about three hours, query ACCESS_HISTORY for the clone statement's query_id.

      Why: The record of who created the evidence, and when, belongs in the chain of custody.

      You should see: A row with your user_name and query_start_time for the preservation step.

    Stuck? Get a nudge

    If the clone fails with an error about the statement, check whether the query ID is more than 14 days old, and switch to an explicitly cast TIMESTAMP.

    Sources

    (9)

    Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

    1. 1.
      “the table function restricts the results to activity over the past 7 days, versus 365 days for the Account Usage view”
      ↩︎ Preserving audit views before the evidence ages out
      “Text of the SQL statement. The limit is 100K characters.”
      ↩︎ Reconstructing what ran and what it exposed
      “Number of rows unloaded during data export.”
      ↩︎ Reconstructing what ran and what it exposed
      “This ID corresponds to the value in the event_id column in the LOGIN_HISTORY view.”
      ↩︎ Correlating the evidence into one timeline
      “Statement start time (in the local time zone).”
      ↩︎ Exam trap 4
    2. 2.
      “Latency for the view may be up to 180 minutes (3 hours).”
      ↩︎ Preserving audit views before the evidence ages out
      “Specifies information about the enforced masking policy set on the column and the enforced row access policy set on the table”
      ↩︎ Reconstructing what ran and what it exposed
      “The query ID of the topmost job in the chain or NULL if the job does not have a parent.”
      ↩︎ Reconstructing what ran and what it exposed
      “The statement start time (UTC time zone).”
      ↩︎ Correlating the evidence into one timeline
      “Latency for the view may be up to 180 minutes (3 hours).”
      ↩︎ Exam trap 1
    3. 3.
      “Latency for the view may be up to 120 minutes (2 hours).”
      ↩︎ Preserving audit views before the evidence ages out
      “ID of the credential used to authenticate the user (the first factor in multi-factor authentication, if used).”
      ↩︎ Establishing who got in, from where, and with which credential
      “INTERNAL_SNOWFLAKE_IP/0.0.0.0 appears as the client IP for login events triggered by internal Snowflake operations that support your usage.”
      ↩︎ Establishing who got in, from where, and with which credential
      “Time (in the UTC time zone) of the event occurrence.”
      ↩︎ Correlating the evidence into one timeline
    4. 4.
      “table_1 in the objects_modified column with the columns that were written to when creating the table.”
      ↩︎ Preserving audit views before the evidence ages out
      “Records in the Account Usage QUERY_HISTORY view do not always get recorded in the ACCESS_HISTORY view.”
      ↩︎ Reconstructing what ran and what it exposed
    5. 5.
      “Past objects can no longer be cloned.”
      ↩︎ Freezing the pre-incident table state with a clone
      “up to, but not including any changes made by the specified statement”
      ↩︎ Freezing the pre-incident table state with a clone
      “Create clones of entire tables, schemas, and databases at or before specific points in the past.”
      ↩︎ Freezing the pre-incident table state with a clone
      “The standard retention period is 1 day (24 hours) and is automatically enabled for all Snowflake accounts”
      ↩︎ Freezing the pre-incident table state with a clone
      “A retention period of 0 days for an object effectively deactivates Time Travel for the object.”
      ↩︎ Freezing the pre-incident table state with a clone
      “UNDROP <object> command for tables, schemas, databases, accounts, external volumes, and tags.”
      ↩︎ Freezing the pre-incident table state with a clone
      “End of Time Travel = time of the change + data retention period”
      ↩︎ Key concept
      “If the Time Travel retention period is set to 0, any modified or deleted data is moved into Fail-safe (for permanent tables)”
      ↩︎ Checkpoint
    6. 7.
      “The query ID must reference a query that has been executed within the last 14 days.”
      ↩︎ Freezing the pre-incident table state with a clone
      “The query ID must reference a query that has been executed within the last 14 days.”
      ↩︎ Exam trap 3
    7. 8.
      “Fail-safe provides a (non-configurable) 7-day period during which historical data may be recoverable by Snowflake.”
      ↩︎ Freezing the pre-incident table state with a clone
      “To recover data from Fail-safe, open a case with Snowflake Support.”
      ↩︎ Freezing the pre-incident table state with a clone
      “Fail-safe is not provided as a means for accessing historical data after the Time Travel retention period has ended.”
      ↩︎ Exam trap 2

    Ready to test yourself?

    Practise the 16 questions on this subdomain.

    Spotted a mistake, or was something unclear? Tell us.