What you will be able to do
- Trace an incident's root cause to the credential, integration or share that was used
- Find and remove persistence mechanisms such as extra key pairs and disabled Time Travel
- Restore data with AT | BEFORE, CREATE ... CLONE and UNDROP
- Explain when Fail-safe applies and how recovery through it works
1.Finding the root cause and removing persistence
Containment stops the immediate damage. Eradication asks how the attacker got in and what they left behind. For logins, LOGIN_HISTORY answers the first question. FIRST_AUTHENTICATION_FACTOR shows the method (password, key pair and so on), FIRST_AUTHENTICATION_FACTOR_ID identifies the exact credential, and AUTHORIZING_INTEGRATION_NAME names the security integration behind an OAuth login. Combined with CLIENT_IP, these columns usually show whether the entry point was a stolen key, a token or an integration. Reconstructing what the attacker did afterwards in QUERY_HISTORY and ACCESS_HISTORY is forensic work, covered by the post-incident forensics objective.
Next, look for the ways the attacker could come back:
- Extra key pairs. A user can hold up to 10 key pairs by default, and a key added without DAYS_TO_EXPIRY never expires. Use SHOW USER KEY PAIRS on every affected user and remove any key you do not recognise. - Share consumers. SHOW GRANTS OF SHARE lists the accounts that have created a database from each share. Any account that should not be there is an exfiltration channel. - Disabled Time Travel. An attacker who sets DATA_RETENTION_TIME_IN_DAYS to 0 makes a later drop impossible to undo. This query finds schemas with Time Travel turned off:
SHOW SCHEMAS
->> SELECT "name", "retention_time"
FROM $1
WHERE "retention_time" = 0;Checkpoint 1 of 6· Check yourself
While eradicating, you find a key pair that the attacker registered on a service user without DAYS_TO_EXPIRY. If nobody acts, how long can it be used?
DAYS_TO_EXPIRY has no default value, so the key never expires on its own. It has to be removed or disabled. The 24 hours applies only to the old key during a rotation.
“the key pair has no expiration and remains valid until rotated, disabled, or removed”Source: docs.snowflake.com
2.Restoring data with Time Travel
Time Travel lets you work with data as it was at any point inside the retention period. Three SQL features matter for recovery:
- AT | BEFORE in a SELECT or CREATE ... CLONE picks the point in time. It takes a TIMESTAMP, an OFFSET in seconds from now, or a STATEMENT (a query ID). If you have the query ID of the attacker's statement from QUERY_HISTORY, BEFORE(STATEMENT => ...) gives you the data as it was just before that statement ran. - CREATE ... CLONE at a past point builds a copy of a table, schema or database. You can check the copy before touching anything live, which is also how Time Travel serves as a backup. - UNDROP restores dropped tables, schemas, databases, accounts, external volumes and tags.
CREATE SCHEMA S2 CLONE S1 AT(TIMESTAMP => '2025-04-01 12:00:00'); -- T1 is not cloned into S2Know what a point-in-time clone leaves out. External tables and internal stages are never cloned, and user tasks are not cloned when you clone a schema at a timestamp. A historical query also uses the table's current schema. If the attacker changed the columns, the old column layout does not come back with the data.
Checkpoint 2 of 6· Check yourself
You want a clone of a table exactly as it was before one destructive UPDATE, and you have that statement's query ID. Which AT | BEFORE parameter identifies it?
STATEMENT takes a query ID. OFFSET and TIMESTAMP mark a time instead, which is less precise when you already know which statement did the damage.
“STATEMENT (query ID for statement)”Source: docs.snowflake.com
Checkpoint 3 of 6· Exam question
A SOC engineer builds a Snowflake alert that should email the team within minutes when one user records five failed logins in ten minutes. The condition queries `SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY`, but in testing the alert only fires hours after the failed attempts. What change fixes the detection delay?
Correct answer: C — Query the `INFORMATION_SCHEMA.LOGIN_HISTORY` table function in the condition, because ACCOUNT_USAGE views can lag real events by up to two hours.
- A. Incorrect. QUERY_HISTORY records executed SQL statements, not authentication attempts. Failed logins never reach statement execution, so they are not visible there.
- B. Incorrect. ACCOUNT_USAGE latency is a property of how the shared SNOWFLAKE database is populated, not of the alert schedule. Evaluating every minute just re-reads the same stale data.
- C. Correct. The INFORMATION_SCHEMA table function reads recent login events without the ingestion delay of the ACCOUNT_USAGE view (up to about 120 minutes), so the alert condition sees failures promptly.
- D. Incorrect. The compute model of an alert only changes who pays for evaluation. The view contents and their latency are identical on serverless and warehouse-backed alerts.
Checkpoint 4 of 6· Exam question
An engineer ran `CREATE ALERT` with a one-minute schedule, an `IF (EXISTS (...))` condition that detects logins from a blocked country, and a `THEN CALL SYSTEM$SEND_EMAIL(...)` action. The role holds the needed privileges, yet no email ever arrives and the alert history is empty. What explains this?
Correct answer: D — The alert was created in a suspended state, so it will not evaluate its condition until someone runs `ALTER ALERT ... RESUME` on it.
- A. Incorrect. An alert is a standalone schedulable object with its own schedule, condition and action. No wrapper task is needed or used.
- B. Incorrect. The action clause is where the notification belongs; it can be a SQL statement such as a call to SYSTEM$SEND_EMAIL. Putting the call in the condition would not be valid.
- C. Incorrect. Alerts use their own privilege, EXECUTE ALERT, rather than EXECUTE TASK, and the stem says the role already holds the necessary privileges.
- D. Correct. Newly created alerts start suspended. Resuming the alert with ALTER ALERT ... RESUME is required before the schedule runs and the condition is evaluated.
Sources5
3.Retention limits and Fail-safe
Every recovery option depends on the retention period. The standard period is 1 day. On Enterprise Edition and above, permanent objects can keep up to 90 days. An object with 0 days of retention cannot be restored once dropped. To stop anyone lowering retention, an ACCOUNTADMIN can set MIN_DATA_RETENTION_TIME_IN_DAYS. Each object then gets the larger of its own DATA_RETENTION_TIME_IN_DAYS and that minimum.
| Layer | Duration | Who recovers | Notes |
|---|---|---|---|
| Time Travel (Standard Edition) | 0 or 1 day | You, with SELECT, CLONE or UNDROP | Default is 1 day |
| Time Travel (Enterprise+, permanent objects) | 0 to 90 days | You, with SELECT, CLONE or UNDROP | Transient and temporary tables: 0 or 1 day |
| Fail-safe | 7 days, cannot be changed | Snowflake, through a Support case | Best effort; several hours to several days |
Fail-safe starts when Time Travel ends. It is not a longer Time Travel. You cannot query it or run UNDROP against it, and only Snowflake can recover data from it. You open a case with Snowflake Support, recovery is best effort, and it can take hours or days. The serverless compute it uses appears in the metering views under service type FAILSAFE_RECOVERY. There are gaps as well. When retention is set to 0, changed or deleted data from transient tables is simply deleted rather than moved to Fail-safe. Tables loaded through the classic Snowpipe Streaming architecture cannot be recovered through Fail-safe at all.
Checkpoint 5 of 6· Check yourself
A permanent table was dropped 3 days ago and its Time Travel retention was 1 day. Which statement about recovering it is correct?
The table has left Time Travel, so UNDROP and CLONE no longer work. It is inside the 7-day Fail-safe window, which only Snowflake Support can use, on a best-effort basis.
“To recover data from Fail-safe, open a case with Snowflake Support.”Source: docs.snowflake.com
Checkpoint 6 of 6· Exam question
A company wants its SIEM collector to poll Snowflake audit data every five minutes. Security requires least privilege, no stored passwords, and no access for anyone who steals the collector's credentials from outside the collector network. Which design meets all three requirements?
Correct answer: B — Create a service user with key-pair authentication, a network policy limited to the collector's IPs, and a role granted `SNOWFLAKE.SECURITY_VIEWER`.
- A. Incorrect. ACCOUNTADMIN is vastly over-privileged for read-only log collection, and an MFA-protected person user cannot run unattended polling. Client certificates are not how Snowflake authenticates SQL clients.
- B. Correct. Key-pair authentication removes stored passwords, the user-level network policy confines use to the collector network, and the SECURITY_VIEWER database role gives read access to security-related ACCOUNT_USAGE views only.
- C. Incorrect. SECURITYADMIN manages grants and network policies rather than reading logs, and it is far more privileged than needed. An account-wide policy does not restrict this one user to the collector's IPs.
- D. Incorrect. A password, even rotated, is still a stored secret. Broad IMPORTED PRIVILEGES plus OWNERSHIP of schemas grants far more than reading audit views and ignores the network restriction.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Fail-safe gives you seven more days of self-service Time Travel after retention ends.Why is that wrong?
Only Snowflake can recover data from Fail-safe, through a Support case, on a best-effort basis. You cannot query it or run UNDROP against it.
Covered in Retention limits and Fail-safe
2.Cloning a schema at a past timestamp brings back everything in it, including tasks and stages.Why is that wrong?
Internal stages and external tables are not cloned, and user tasks are not cloned when you clone a schema at a timestamp.
Covered in Restoring data with Time Travel
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Name of the security integration that authorized the login.”
↩︎ Finding the root cause and removing persistence - 2.
“You can list a user’s named key pairs with SHOW USER KEY PAIRS”
↩︎ Finding the root cause and removing persistence - 3.
“Each user can have a maximum of 10 key pairs by default.”
↩︎ Finding the root cause and removing persistence“the key pair has no expiration and remains valid until rotated, disabled, or removed”
↩︎ Checkpoint - 4.
“SHOW GRANTS OF SHARE lists all accounts that have created a database from the share.”
↩︎ Finding the root cause and removing persistence - 5.
“UNDROP <object> command for tables, schemas, databases, accounts, external volumes, and tags.”
↩︎ Restoring data with Time Travel“Duplicating and backing up data from key points in the past.”
↩︎ Restoring data with Time Travel“When querying historical data in a table or non-materialized view, the current table or view schema is used.”
↩︎ Restoring data with Time Travel“When an object with no retention period is dropped, you will not be able to restore the object.”
↩︎ Retention limits and Fail-safe“the data retention period for an object is determined by MAX(DATA_RETENTION_TIME_IN_DAYS, MIN_DATA_RETENTION_TIME_IN_DAYS).”
↩︎ Retention limits and Fail-safe“any modified or deleted data is moved into Fail-safe (for permanent tables) or deleted (for transient tables)”
↩︎ Retention limits and Fail-safe“User tasks in a database or schema are not cloned when using CREATE SCHEMA … TIMESTAMP.”
↩︎ Exam trap 2“STATEMENT (query ID for statement)”
↩︎ Checkpoint - 6.
“Fail-safe provides a (non-configurable) 7-day period during which historical data may be recoverable by Snowflake.”
↩︎ Retention limits and Fail-safe“Data recovery through Fail-safe may take from several hours to several days to complete.”
↩︎ Retention limits and Fail-safe“Fail-safe doesn’t support tables that contain data ingested by the Snowpipe Streaming classic architecture.”
↩︎ Retention limits and Fail-safe“Fail-safe is not provided as a means for accessing historical data after the Time Travel retention period has ended.”
↩︎ Exam trap 1“To recover data from Fail-safe, open a case with Snowflake Support.”
↩︎ Checkpoint