DEA-C02 Storage and Data Protection — Timed 10 Questions

Question 1

A Data Engineer needs to ensure that a table containing sensitive PII can be restored to any point in time within the last 90 days. Which configuration must be applied to the table?

Accepted Answer

Alter the table to set DATA_RETENTION_TIME_IN_DAYS = 90.. To achieve 90 days of Time Travel, the DATA_RETENTION_TIME_IN_DAYS parameter must be explicitly set to 90 at the table level. By default, tables in the Standard Edition have 1 day, while Enterprise Edition has up to 90 days. Setting this allows the UNDROP command and time-travel queries to access data history for the full duration, ensuring compliance with strict data recovery requirements for sensitive datasets.

Answer

Enable Fail-safe on the database schema.

Answer

Create a transient table with a retention period of 90 days.

Answer

Run the UNDROP command daily to reset the retention clock.

Question 2

Which TWO statements are true regarding Snowflake Fail-safe?

Accepted Answer

Fail-safe is a 7-day period that occurs after the Time Travel period ends.. Fail-safe is a 7-day historical data protection period that occurs after the Time Travel window ends. It is designed to recover data in the event of a catastrophic system failure or data corruption. It cannot be configured by users, nor can it be accessed through standard SQL queries. Data in Fail-safe is only recoverable by Snowflake support, making it the final line of defense.

Answer

Fail-safe can be enabled or disabled at the account level.

Answer

Fail-safe allows users to query historical data using the AT clause.

Answer

Fail-safe provides a 30-day window for data recovery.

Question 3

Which THREE actions can be performed on a table while it is in a state where it could be recovered via UNDROP?

Accepted Answer

Create a new table with the same name as the dropped table.. When a table is dropped, it enters a suspended state where it still exists in the metadata but is hidden from standard queries. While in this state, the table can be recovered using UNDROP. Furthermore, other objects in the same database can be created, dropped, or altered. The metadata associated with the table remains intact, allowing for schema operations and maintenance tasks to continue on the parent schema or database.

Answer

Query the data inside the dropped table using SELECT.

Answer

Execute DML statements like INSERT or UPDATE on the dropped table.

Question 4

A Data Engineer is implementing a data lifecycle policy and needs to reduce storage costs. Which action will have the most immediate impact on reducing storage costs related to Time Travel?

Accepted Answer

Altering the table to decrease DATA_RETENTION_TIME_IN_DAYS.. Storage costs for Time Travel are directly tied to the DATA_RETENTION_TIME_IN_DAYS parameter. Reducing this value decreases the amount of historical data maintained for that object. When this parameter is reduced, Snowflake initiates a background process to purge data that falls outside the new, shorter retention window, thereby freeing up storage and reducing associated monthly costs more effectively than other management tasks.

Answer

Running the PURGE command on the table.

Answer

Creating a zero-copy clone of the table.

Answer

Enabling auto-clustering on the table.

Question 5

Which object type allows for the longest DATA_RETENTION_TIME_IN_DAYS setting in a Snowflake Standard Edition account?

Accepted Answer

Permanent table (1 day).. In Snowflake Standard Edition, the maximum retention period for any object is 1 day. This is a platform-wide restriction for that specific edition. While Enterprise and Business Critical editions allow up to 90 days, Standard Edition users are limited to 1 day for all tables, regardless of whether they are permanent or transient. This constraint is critical for architects planning disaster recovery strategies on lower-tier accounts.

Answer

Transient table (30 days).

Answer

Permanent table (90 days).

Answer

Temporary table (7 days).

Question 6

What happens to the data in a table after it has been deleted and the Time Travel period has expired?

Accepted Answer

It becomes available for recovery by Snowflake Support for 7 days.. Once the Time Travel retention period concludes, the data transitions into the 7-day Fail-safe period. During this time, the data is still physically stored but is no longer accessible through standard SQL queries. After the Fail-safe period completes, the data is permanently purged by Snowflake's automated background processes, and it becomes unrecoverable by users or even Snowflake Support, finalizing the data lifecycle for deleted information.

Answer

It is immediately purged and unrecoverable.

Answer

It is moved to cold storage and incurs no further costs.

Answer

It remains queryable via the UNDROP command.

Question 7

Which THREE of the following are valid methods to recover data that has been accidentally deleted or modified?

Accepted Answer

Using the SELECT ... AT (OFFSET => ...) syntax.. Data recovery in Snowflake depends on the severity of the loss and the elapsed time. Time Travel is the primary mechanism for recovering data within a configured window (up to 90 days). If the Time Travel window has passed, Fail-safe offers a 7-day emergency window for catastrophic recovery. Additionally, database snapshots or external backups are valid strategies for long-term recovery when built-in retention periods are insufficient for the business requirements.

Answer

Running the RECOVER TABLE command in SQL.

Answer

Using the ALTER TABLE ... RESET DATA.

Question 8

Which of the following is a consequence of dropping a database?

Accepted Answer

All contained objects move to a dropped state.. Dropping a database drops all objects contained within it, including schemas and tables. All these objects enter a 'dropped' state and are subject to the same Time Travel retention period as the database itself. If the database is not restored via UNDROP within the retention window, all contained objects will follow the same lifecycle, eventually entering Fail-safe and then being permanently purged by the system, ensuring complete data cleanup upon deletion.

Answer

The underlying data in the tables is immediately deleted.

Answer

Objects within the database can no longer be individually recovered.

Answer

Fail-safe protection for the database is disabled.

Question 9

A data engineer is managing a multi-terabyte table that experiences frequent updates to recent records while older records remain static. The engineer notices that storage costs are increasing faster than the data volume. Which Snowflake feature is most likely contributing to these costs due to how it handles data modifications?

Accepted Answer

Continuous Data Protection (CDP). Snowflake utilizes immutable micro-partitions, meaning any update to a row creates a new version of the entire micro-partition. The old micro-partition is retained in Time Travel and potentially Fail-safe to ensure data protection and point-in-time recovery. For high-churn tables, this versioning leads to significant storage overhead as multiple versions of the same data blocks are stored concurrently.

Answer

Search Optimization Service

Answer

Query Acceleration Service

Answer

Materialized View Maintenance

Question 10

A company is using Snowflake Enterprise Edition. They have a permanent table with a DATA_RETENTION_TIME_IN_DAYS set to 90. Which TWO statements are true regarding the storage lifecycle of data in this table?

Accepted Answer

The table will occupy Fail-safe storage for 7 days after the 90-day Time Travel period ends.. In Snowflake Enterprise Edition and higher, permanent tables allow for extended Time Travel up to 90 days, providing a robust window for point-in-time recovery. Once the Time Travel period expires, the data moves into Fail-safe for exactly 7 days, during which only Snowflake Support can recover it in the event of a disaster.

Answer

Data can be recovered using the UNDROP command for up to 97 days after the table is dropped.

Answer

Users can query historical data from 95 days ago using the AT or BEFORE clauses.

Answer

Fail-safe storage for this table can be disabled by setting the FAIL_SAFE_ENABLED parameter to FALSE.