What you will be able to do
- Explain why SNOWFLAKE.ACCOUNT_USAGE, not Information Schema, is the evidence store for a post-incident investigation, and plan around its latency and edition limits
- Give a forensic analyst read access to the security audit views through SNOWFLAKE database roles
- Link Snowflake query and login records to an identity provider session so the IdP side of an incident timeline can be lined up with Snowflake's
- Use Time Travel (AT | BEFORE, CLONE, UNDROP) to capture the state data was in before an attacker changed it
- Tell apart what Time Travel lets you do and what Fail-safe only lets Snowflake do, and say what the documentation does and does not cover about chain of custody
Key concept
Account Usage as the forensic record — The ACCOUNT_USAGE schema in the shared SNOWFLAKE database keeps a year of login, query and access history, including records for dropped objects. That makes it the main source of evidence for reconstructing an incident after the fact. Data changes, however, can only be preserved through Time Travel, and only while the retention window is still open.
1.ACCOUNT_USAGE: where the evidence lives
When an incident is over, responders need a record of what happened. That record has to outlast the attacker's cleanup and cover more than the last few days. In Snowflake it is the ACCOUNT_USAGE schema of the shared SNOWFLAKE database. Its views mirror the Information Schema, with three differences that matter in an investigation. First, they include dropped objects, so a table the attacker dropped still has a history. Second, they keep data for a year. Third, they have latency.
The retention gap is large. Information Schema table functions such as QUERY_HISTORY keep between 7 days and 6 months of data, depending on the function. The Account Usage view of the same name keeps 365 days. Most breaches are found well after they begin, so Account Usage is the store an investigation relies on.
| View | Maximum latency | Edition | Retention |
|---|---|---|---|
| QUERY_HISTORY | 45 minutes | All accounts | 1 year |
| LOGIN_HISTORY | 2 hours | All accounts | 1 year |
| ACCESS_HISTORY | 3 hours | Enterprise Edition (or higher) | 1 year |
| SESSIONS | 3 hours | All accounts | 1 year |
Two things in this table shape an investigation. Latency means the most recent attacker activity may not show up yet. The documented figures are maximums, so if a query comes back empty for the last hour or two, query again later before deciding nothing happened. And ACCESS_HISTORY, the view that records which columns were read, needs Enterprise Edition or higher. An account on a lower edition can still see query text and logins, but not object-level access records.
Access to these views is a separate decision. Snowflake defines four database roles in the SNOWFLAKE database, and each has SELECT on a set of Account Usage views: OBJECT_VIEWER (object metadata), USAGE_VIEWER (historical usage), GOVERNANCE_VIEWER (governance information) and SECURITY_VIEWER (security information). Granting the right database role to an analyst's role gives targeted read access without handing out ACCOUNTADMIN.
Correlating with the identity provider. When users sign in through single sign-on, the identity provider (IdP) holds part of the story, and its logs live outside Snowflake. The supplied documentation does not describe the IdP's own log format, so treat those records as an input you collect from your IdP team. What Snowflake gives you is the join keys. QUERY_HISTORY carries an authn_event_id column whose value corresponds to the event_id in LOGIN_HISTORY, so every statement can be tied to the login that opened its session. LOGIN_HISTORY then supplies the CLIENT_IP, the reported client type and the authentication factors, which you compare against what the IdP recorded for the same user and time.
Know how IdP sessions relate to Snowflake sessions, because it changes the timeline. In an IdP-initiated login, the IdP sends a SAML response to Snowflake to start a session. After that, the two sessions live separately. An IdP session timing out does not end Snowflake sessions that are already open. Logging a user out of an IdP such as Okta does not end their active Snowflake sessions either. So activity can continue in Snowflake after the IdP shows the user as signed out. Also note that a login which came through an IdP configured with the account URL has a NULL CONNECTION value in LOGIN_HISTORY, so do not use that column to filter for SSO logins. Finally, align time zones before merging sources: LOGIN_HISTORY EVENT_TIMESTAMP and ACCESS_HISTORY query_start_time are in UTC, while QUERY_HISTORY start_time is in the local time zone.
Checkpoint 1 of 4· Check yourself
An investigator discovers a breach that started 40 days ago and needs every statement the compromised user ran. Which source still holds that history?
The Information Schema table function only covers the past 7 days. The Account Usage view keeps 365 days of query history.
“the table function restricts the results to activity over the past 7 days, versus 365 days for the Account Usage view.”Source: docs.snowflake.com
Checkpoint 2 of 4· 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?
Correct answer: A — ACCOUNT_USAGE views ingest with latency, and ACCESS_HISTORY can lag by roughly three hours, so re-run the filter later and use INFORMATION_SCHEMA query history functions meanwhile.
- A. Correct. ACCOUNT_USAGE views are not real-time, and ACCESS_HISTORY has one of the longest delays (up to about 180 minutes). Re-querying later and using INFORMATION_SCHEMA functions for immediate visibility is the standard workaround.
- B. Incorrect. No such parameter exists; access history is recorded automatically on Enterprise Edition or higher. An empty result for a recent window is a latency effect, not a missing setting.
- C. Incorrect. Missing privileges on ACCOUNT_USAGE cause an authorization error rather than a silently empty result, and database roles or IMPORTED PRIVILEGES can grant access without ACCOUNTADMIN.
- D. Incorrect. Fail-safe applies to table data after Time Travel, not to audit view rows, and Support is not involved in releasing ACCOUNT_USAGE records.
2.Time Travel: preserving the pre-incident state of data
Audit views tell you what was done. To see what the data looked like before it was done, you need Time Travel. Within an object's retention period you can query data that has since been updated or deleted, clone tables, schemas and databases as they were at a past point, and UNDROP dropped objects. The documentation lists "duplicating and backing up data from key points in the past" as a core use, and that is how evidence gets preserved here: you freeze the before-state as a clone before the retention window closes.
The AT | BEFORE clause sets the point in time in three ways: TIMESTAMP, OFFSET (seconds back from now), or STATEMENT, which takes a query ID. STATEMENT is the forensic one. Once QUERY_HISTORY has given you the query_id of the attacker's DELETE or UPDATE, BEFORE(STATEMENT => ...) returns the table exactly as it was just before that statement ran.
SELECT * FROM my_table BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');The window is set by DATA_RETENTION_TIME_IN_DAYS. The default is 1 day. On Enterprise Edition and higher, permanent objects can be set to anything up to 90 days. Transient and temporary objects stay at 0 or 1. An ACCOUNTADMIN can also set MIN_DATA_RETENTION_TIME_IN_DAYS at the account level, and the effective retention is then the larger of the two values. This matters in an investigation because an attacker who sets an object's retention to 0 cannot go below an account-level minimum. If the point you ask for falls outside the retention period, the query fails with an error. Run SHOW TABLES (or SHOW SCHEMAS or SHOW DATABASES with HISTORY) and read the retention_time column to see how much history is left.
Checkpoint 3 of 4· Fill the gap
QUERY_HISTORY gave you the query ID of the attacker's DELETE. Which parameter returns the table exactly as it was before that statement?
SELECT * FROM my_table BEFORE( ? => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');STATEMENT takes a query ID. Used with BEFORE, it excludes the changes that statement made. TIMESTAMP and OFFSET are time-based, and QUERY_ID is not a valid AT | BEFORE parameter.
Source: docs.snowflake.comSources5
3.Fail-safe and chain of custody
When Time Travel retention ends, historical data moves into Fail-safe. From then on you can no longer query it, clone it or undrop it yourself. Fail-safe is a fixed 7-day period, and it cannot be configured. During that time Snowflake may be able to recover the data. Recovery goes through a case with Snowflake Support, is best-effort, and can take from several hours to several days. Transient and temporary tables have no Fail-safe period, so once their 0–1 day of Time Travel is over, the history is gone. For permanent tables, the most history that can exist is Time Travel retention plus 7 days.
| Aspect | Time Travel | Fail-safe |
|---|---|---|
| Who can use it | You (SELECT, CREATE … CLONE, UNDROP) | Only Snowflake, through a Support case |
| Length | DATA_RETENTION_TIME_IN_DAYS: 0–90 days for permanent objects on Enterprise | 7 days, not configurable |
| Transient / temporary tables | 0 or 1 day | None |
| Speed | Immediate | Several hours to several days |
Chain of custody is a gap in the material. The supplied Snowflake documentation describes no formal custody procedure: nothing on evidence hashing, sign-off or custodian handover. Treat those as your organisation's own forensic process. What the documentation does give you is the raw material for that process. The Account Usage views are a year-long system record. Snowflake's Well-Architected guidance points investigators to QUERY_HISTORY and ACCESS_HISTORY for a potentially compromised user. And clones taken with AT | BEFORE give you a fixed copy of the data's state at a named point in time, which you can examine without touching the production table.
Checkpoint 4 of 4· Check yourself
Which statement about Fail-safe is correct?
Fail-safe cannot be configured, only Snowflake can recover from it, and transient and temporary tables have no Fail-safe period.
“Fail-safe provides a (non-configurable) 7-day period during which historical data may be recoverable by Snowflake.”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Once Time Travel retention ends, investigators can still query or clone the historical data from Fail-safe.Why is that wrong?
Fail-safe is not for customers to access. Only Snowflake can recover data from it, through a Support case, and recovery may take days.
Covered in Fail-safe and chain of custody
2.ACCESS_HISTORY is available in every Snowflake account, just like QUERY_HISTORY and LOGIN_HISTORY.Why is that wrong?
ACCESS_HISTORY needs Enterprise Edition or higher. QUERY_HISTORY and LOGIN_HISTORY have no edition restriction.
Covered in ACCOUNT_USAGE: where the evidence lives
3.If the identity provider shows a user's session as ended, the attacker's Snowflake sessions have ended too.Why is that wrong?
An IdP session timing out does not affect Snowflake sessions that are already open, so Snowflake activity can continue after the IdP session ends.
Covered in ACCOUNT_USAGE: where the evidence lives
4.Setting an object's DATA_RETENTION_TIME_IN_DAYS to 0 always destroys its Time Travel history.Why is that wrong?
If an account-level MIN_DATA_RETENTION_TIME_IN_DAYS is set, the effective retention is the larger of the two values.
Covered in Time Travel: preserving the pre-incident state of data
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Records for dropped objects are included in each view.”
↩︎ ACCOUNT_USAGE: where the evidence lives“ACCOUNT_USAGE schemas have four defined SNOWFLAKE database roles, each granted the SELECT privilege on specific views.”
↩︎ ACCOUNT_USAGE: where the evidence lives“The SECURITY_VIEWER role provides visibility into security-based information.”
↩︎ ACCOUNT_USAGE: where the evidence lives“Certain account usage views provide historical usage metrics. The retention period for these views is 1 year (365 days).”
↩︎ Key concept“ACCESS_HISTORY | Historical | 3 hours | Enterprise Edition (or higher) | Data retained for 1 year.”
↩︎ Exam trap 2 - 2.
“This ID corresponds to the value in the event_id column in the LOGIN_HISTORY view.”
↩︎ ACCOUNT_USAGE: where the evidence lives“the table function restricts the results to activity over the past 7 days, versus 365 days for the Account Usage view.”
↩︎ Checkpoint - 3.
“The IdP sends a SAML response to Snowflake to initiate a session”
↩︎ ACCOUNT_USAGE: where the evidence lives“When a user logs out of Okta, they are not automatically logged out of any of their active Snowflake sessions”
↩︎ ACCOUNT_USAGE: where the evidence lives“a user’s session in the IdP automatically times out, but this does not affect their Snowflake sessions.”
↩︎ Exam trap 3 - 4.
“the IdP directs the client to the account URL after authentication is complete.”
↩︎ ACCOUNT_USAGE: where the evidence lives - 5.
“Duplicating and backing up data from key points in the past.”
↩︎ Time Travel: preserving the pre-incident state of data“The following query selects historical data from a table up to, but not including any changes made by the specified statement:”
↩︎ Time Travel: preserving the pre-incident state of data“For permanent databases, schemas, and tables, the retention period can be set to any value from 0 up to 90 days.”
↩︎ Time Travel: preserving the pre-incident state of data“falls outside the data retention period for the table, the query fails and returns an error.”
↩︎ Time Travel: preserving the pre-incident state of data“the effective minimum data retention period for an object is determined by MAX(DATA_RETENTION_TIME_IN_DAYS, MIN_DATA_RETENTION_TIME_IN_DAYS).”
↩︎ Exam trap 4 - 6.
“To recover data from Fail-safe, open a case with Snowflake Support.”
↩︎ Fail-safe and chain of custody“Data recovery through Fail-safe may take from several hours to several days to complete.”
↩︎ Fail-safe and chain of custody“Fail-safe is not provided as a means for accessing historical data after the Time Travel retention period has ended.”
↩︎ Exam trap 1“Fail-safe is not provided as a means for accessing historical data after the Time Travel retention period has ended.”
↩︎ Prediction“Fail-safe provides a (non-configurable) 7-day period during which historical data may be recoverable by Snowflake.”
↩︎ Checkpoint - 7.
“Transient and temporary tables have no Fail-safe period.”
↩︎ Fail-safe and chain of custody - 8.https://www.snowflake.com/en/developers/guides/well-architected-framework-security-and-governanceSecondary source
“Use the QUERY_HISTORY and ACCESS_HISTORY views to investigate a potentially compromised user.”
↩︎ Fail-safe and chain of custody