Data Governance: Time Travel (0-90 Days), UNDROP & 7-Day Fail-Safe

[!NOTE] Snowflake provides built-in enterprise disaster recovery without requiring daily full backup tapes.

1. Time Travel vs Fail-Safe

• Time Travel (0 to 90 Days):
  • Accessible directly by users via SQL (AT(TIMESTAMP => ...) or BEFORE(OFFSET => ...)).
  • Can restore dropped tables instantaneously using UNDROP TABLE table_name;.
  • Standard edition default: 1 day; Enterprise edition: up to 90 days.
• Fail-Safe (7 Days):
  • Begins immediately after Time Travel retention expires.
  • Dedicated exclusively for disaster recovery; accessible only by Snowflake Support for extreme hardware or data center failure events.

Staff Architecture: Time Travel, Fail-Safe Churn, Transient Tables & Horizon Governance

• Hidden Storage Cost of TRUNCATE + Reload (Time Travel + Fail-Safe): Standard Permanent tables retain up to 1–90 days of Time Travel + 7 days non-configurable Fail-Safe. Running hourly full-table CREATE OR REPLACE TABLE or TRUNCATE + INSERT on a 10TB staging table preserves 7+ days of dead historical micro-partitions—inflating storage bills 20x! Declare all scratch/staging/Bronze reload layers as CREATE TRANSIENT TABLE (0-day Fail-Safe, 0–1 day Time Travel).
• UNDROP & Point-in-Time Recovery: Mistakenly dropped a table or ran a bad DELETE without a WHERE clause? Instantly execute UNDROP TABLE orders; or CREATE TABLE orders_recovered CLONE orders AT(OFFSET => -60*15); in seconds with zero backup restore downtime.
• Snowflake Horizon Governance: Enforce Row Access Policies, Dynamic Data Masking Policies, and Tag-Based Object Classification (SNOWFLAKE.CORE.SEMANTIC_CATEGORY) inside unified RBAC functional/access roles (SYSADMIN, DATA_ENG, PII_READER).