Storage and Data Protection Study Guide — DEA-C02

Storage and Data Protection

This domain tests your ability to design, configure, and troubleshoot Snowflake's full suite of data protection and storage capabilities. It is one of the larger topic areas on the SnowPro Advanced: Data Engineer (DEA-C02) exam under the current (carried-over, unverified) blueprint weighting, so weaknesses here directly threaten a passing score. Expect questions on exact parameter names, default values, tier-specific behaviors, recovery windows, replication topology, and cost implications of storage choices.

Time Travel

Time Travel lets you query, clone, or restore data as it existed at any point within a configurable retention window, using historical micro-partitions Snowflake retains after DML operations.

Configuration

Critical: Transient and temporary tables have no Time Travel — they default to 0 and cannot be changed. Setting DATA_RETENTION_TIME_IN_DAYS = 1 on a schema does not propagate to transient tables within it.

AT and BEFORE Syntax

-- Query data as of an exact timestamp
SELECT * FROM orders AT (TIMESTAMP => '2024-03-01 12:00:00'::TIMESTAMP_TZ);

-- Query data just before a specific transaction ID
SELECT * FROM orders BEFORE (STATEMENT => '<query_id>');

-- Clone as of an offset (seconds ago)
CREATE TABLE orders_backup CLONE orders AT (OFFSET => -3600);

UNDROP

UNDROP TABLE, UNDROP SCHEMA, and UNDROP DATABASE restore dropped objects within the retention window. If the parent schema was also dropped, you must UNDROP SCHEMA first, then UNDROP TABLE.

Common traps

• Exam may claim Standard Edition supports 90-day Time Travel — it does not (max 1 day).
• Setting retention to 0 does not make a permanent table transient; it just disables Time Travel.
• BEFORE (TIMESTAMP =>) is not valid syntax; only STATEMENT is accepted with BEFORE.
• Time Travel storage incurs charges — historical micro-partitions count toward your bill.
• Dropping and immediately re-creating a table with the same name produces a new object with a new retention window; the original is restorable by name only within its original window.

Worked scenario: A batch job accidentally deleted 2 million rows at 14:32 UTC. You have the query ID. The fastest, least-disruptive recovery is CREATE TABLE orders_restored CLONE orders BEFORE (STATEMENT => '<delete_query_id>'), then validate and swap using ALTER TABLE orders SWAP WITH orders_restored.

Fail-safe

Fail-safe is a Snowflake-managed, non-configurable 7-day recovery window that begins after the Time Travel retention period expires.

Figure: Data lifecycle from write through Fail-safe to permanent deletion.

Key point: Transient tables have no Fail-safe. This is the primary reason to choose transient tables for scratch or staging data — you eliminate both Time Travel and Fail-safe storage costs.

Common traps

• Fail-safe is sometimes described as "customer-accessible via special commands" — false. Only Snowflake Support can initiate Fail-safe recovery.
• The 7-day Fail-safe period is not configurable at any edition or tier.
• Total maximum data retention cost window for permanent tables = Time Travel + Fail-safe (e.g., 90 + 7 = 97 days on Enterprise).
• Transient tables save cost but sacrifice recoverability — exam tests whether you recognize this trade-off.