Snowflake技术问询：Time Travel是否可作为备份的替代方案？

Great question—let’s break this down clearly: Snowflake’s Time Travel cannot fully replace traditional backups, and your own experience with schema recreation and missing constraints is a perfect illustration of why.

Key Limitation 1: Loss of Schema-Level Metadata (Like Constraints) #

When you recreated your schema and used Time Travel to restore tables, the disappearance of primary keys (PK) and foreign keys (FK) is expected behavior. Here’s the breakdown:

• Time Travel primarily preserves table data and basic column structures (like data type, column name) in its snapshots.
• Schema-level metadata such as constraints (PK/FK), indexes, row access policies, and table-level permissions are not included in Time Travel snapshots.
• Restoring a table into a newly created schema only brings back raw data and column definitions—you’ll need to manually redefine all constraints and other schema-level objects that were part of the original setup.

Key Limitation 2: Column Precision Restrictions #

Another critical constraint you’ve identified reinforces this point: if you reduce a column’s precision below the maximum precision present in the Time Travel snapshot data, you can’t restore the table without first reverting the column’s precision.

For example:

1. Suppose you have a sales_amount column defined as NUMBER(12,2), and historical data in Time Travel includes values like 1234567890.12 (which uses the full 12-digit precision).
2. Later, you modify the column to NUMBER(10,2) to save storage.
3. If you try to use Time Travel to restore the table to a point before the precision change, Snowflake will throw an error—since the existing column can’t accommodate the higher-precision historical data.
4. You must first alter the column back to at least NUMBER(12,2) before running the Time Travel restore.

So, When Should You Use Time Travel vs. Backups? #

• Time Travel is ideal for short-term recovery: Fixing accidental data deletions/updates, rolling back to a recent table state within your configured retention period (default 1 day, max 90 days for Enterprise accounts).
• Backups are necessary for: Long-term data retention (beyond Time Travel limits), disaster recovery scenarios, restoring full schema/account environments with all metadata intact, and compliance requirements that mandate immutable, offline-like storage.

In short: Time Travel complements backups, but it can’t replace them for full, long-term, or metadata-inclusive recovery needs.

内容的提问来源于stack exchange，提问作者Hennie de Nooijer