Snowflake Architecture · Topic 15 of 18
Data Recovery Solutions - Backup, Recovery and Data Corruption Impacts
Pick the right Snowflake recovery layer: Time Travel, cloning, backup sets, Fail-safe or replication, and see how data corruption spreads and is repaired.
- 5 min
- Match failure to layer: logical errors to Time Travel, cloning and backups; outages to replication and failover.
- Time Travel default is 1 day; up to 90 days only on Enterprise and higher for permanent objects.
- Fail-safe is 7 days after Time Travel, not configurable, recovered only by Snowflake Support, best effort.
What it is
Snowflake does not need classic backup jobs for most recovery cases, because several layers are built in. The architect's task is to match the failure to the layer: a mistaken delete, a bad load, a dropped table, a corrupted pipeline, a regional outage, or a requirement for an immutable copy.
Think in two groups. Logical failures (human error, bad code, bad data) are fixed with Time Travel, cloning and backups. Infrastructure failures (a region or cloud outage) are handled with replication and failover. Fail-safe is the last resort that only Snowflake can use.
How it works
The recovery layers
| Layer | Protects against | Window | Who restores |
|---|---|---|---|
| Time Travel | Accidental change, delete or drop | 1 day by default; up to 90 days on Enterprise and higher for permanent objects | You, with SQL |
| Zero copy cloning | Risky change; need a safe copy | Lasts as long as the clone exists | You, with SQL |
| Backup sets and policies | Need for scheduled, long lived, optionally immutable copies | Set by the policy's schedule and expiration | You, with SQL |
| Fail-safe | Extreme operational failure after Time Travel ends | 7 days, not configurable | Snowflake Support only |
| Replication and failover | Region, cloud or account outage | Depends on refresh schedule | You, by promoting a secondary |
No single layer covers everything. A replica protects against an outage but also copies a bad load. Time Travel protects against a bad load but not against a region outage.
Backup sets and backup policies
Backup sets and backup policies take policy driven point in time copies of a table, schema or database.
- A backup policy sets the schedule (minimum 60 minutes, or a cron expression), the expiration period and an optional retention lock.
- A backup set holds the backups of one table, schema or database and is a schema level object.
- A backup cannot be modified after it is created.
- Retention lock and legal hold need Business Critical or higher. A locked backup cannot be deleted by any role, including ACCOUNTADMIN, until it expires.
- Restore creates a new object from the backup, for example with
CREATE TABLE ... FROM BACKUP SET ... IDENTIFIER '<id>'.
Transient and temporary tables, external and hybrid tables, streams, pipes and stages are not included in schema and database backups. Use backup sets when compliance asks for a copy that cannot be altered or removed early.
Data corruption impacts
Corruption in Snowflake is almost always logical: wrong data written by a user, a job or an application. The platform itself keeps data redundant, so the question is how far the bad data has spread and how long ago it was written.
| Situation | Effect | Best response |
|---|---|---|
| Bad update or delete found within retention | Old micro partitions are still held | Query or clone the table with AT or BEFORE, then repair or swap |
| Table or schema dropped | Object is kept in history | UNDROP, after renaming any same named object |
| Bad data found after retention ended | Time Travel cannot reach it | Restore from a backup if one exists; Fail-safe only as a last resort via Support |
| Bad data already replicated | Secondary has the same bad data after a refresh | Fix from Time Travel on the primary, then refresh again; failover will not help |
| Bad data flowed to downstream tables | Streams, dynamic tables and tasks propagated it | Restore source, then rebuild or reprocess dependents |
| Corruption found late | Retention may already be too short | Increase retention for critical tables ahead of time |
Two points matter for design:
- Replication copies corruption. Failover is a disaster recovery tool, not a protection against bad data. Time Travel or backups are needed alongside it.
- Retention is a design decision. Set longer retention on critical tables, and keep staging data transient, because an extended window only helps for changes made after it is set.
Increasing retention after you spot corruption does not bring back changes that already left the Time Travel window.
Choosing quickly
| Requirement | Pick |
|---|---|
| Undo an update from last hour | Time Travel AT or BEFORE on the table |
| Undo a dropped table, schema or database | UNDROP |
| Test a risky migration without risk | Zero copy clone, drop it afterwards |
| Auditor needs a copy nobody can delete for a set period | Backup set with a retention lock (Business Critical or higher) |
| Survive loss of a region | Replication group or failover group |
| Data lost long ago, no backup, Time Travel expired | Fail-safe request to Snowflake Support, best effort |
Common mistakes
Treating replication as backup. A refresh copies whatever is on the primary, including mistakes.
Expecting to restore from Fail-safe yourself. Fail-safe cannot be queried or restored by users; only Snowflake Support can recover, and recovery can take hours to days.
Setting 90 days everywhere. Long retention on high churn or rebuildable tables adds storage cost. Use it on critical tables and use transient tables for staging.
Forgetting what a backup excludes. Streams, pipes, stages and transient tables are not in schema or database backups.
What to remember for the exam
- Match failure to layer: logical errors to Time Travel, cloning and backups; outages to replication and failover.
- Time Travel default is 1 day; up to 90 days only on Enterprise and higher for permanent objects.
- Fail-safe is 7 days after Time Travel, not configurable, recovered only by Snowflake Support, best effort.
- Backup sets hold policy based copies that cannot be modified; retention lock and legal hold need Business Critical or higher.
- Replication propagates corruption; keep Time Travel or backups beside it.
Check yourself
A bad load was found 3 hours ago on a permanent table with default settings. How do you recover?
Use Time Travel with
BEFORE (STATEMENT => ...)or an offset to clone or query the table as it was, then repair or swap it.Why does failover not fix a table that was corrupted by a faulty job?
The secondary was refreshed from the primary and holds the same bad data.
A regulator demands copies that even administrators cannot delete for a fixed period. Which feature?
A backup set with a backup policy that uses a retention lock, which needs Business Critical or higher.
Topic 15 of 18 in Snowflake Architecture
Test yourself on Snowflake Architecture
The full Snowflake ARA-C01 question bank has 687 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.