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
In a nutshell
  • 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.

Failurewhat happened?mistake inside retentionTime Travelquery, clone, UNDROPFailureneed long term or locked copyBackup setpolicy based snapshotsFailureregion or account outageReplicationand failover groupFailurepast Time TravelFail safeSnowflake Support

How it works

The recovery layers

LayerProtects againstWindowWho restores
Time TravelAccidental change, delete or drop1 day by default; up to 90 days on Enterprise and higher for permanent objectsYou, with SQL
Zero copy cloningRisky change; need a safe copyLasts as long as the clone existsYou, with SQL
Backup sets and policiesNeed for scheduled, long lived, optionally immutable copiesSet by the policy's schedule and expirationYou, with SQL
Fail-safeExtreme operational failure after Time Travel ends7 days, not configurableSnowflake Support only
Replication and failoverRegion, cloud or account outageDepends on refresh scheduleYou, 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.

SituationEffectBest response
Bad update or delete found within retentionOld micro partitions are still heldQuery or clone the table with AT or BEFORE, then repair or swap
Table or schema droppedObject is kept in historyUNDROP, after renaming any same named object
Bad data found after retention endedTime Travel cannot reach itRestore from a backup if one exists; Fail-safe only as a last resort via Support
Bad data already replicatedSecondary has the same bad data after a refreshFix from Time Travel on the primary, then refresh again; failover will not help
Bad data flowed to downstream tablesStreams, dynamic tables and tasks propagated itRestore source, then rebuild or reprocess dependents
Corruption found lateRetention may already be too shortIncrease 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.
Bad change foundinside retentionclone AT or BEFOREClean copyvalidatedswap or restoreRepaired tableBad change foundafter retentionrestore fromBackup setor Support via Fail safe
Watch out

Increasing retention after you spot corruption does not bring back changes that already left the Time Travel window.

Choosing quickly

RequirementPick
Undo an update from last hourTime Travel AT or BEFORE on the table
Undo a dropped table, schema or databaseUNDROP
Test a risky migration without riskZero copy clone, drop it afterwards
Auditor needs a copy nobody can delete for a set periodBackup set with a retention lock (Business Critical or higher)
Survive loss of a regionReplication group or failover group
Data lost long ago, no backup, Time Travel expiredFail-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.