DEA-C02 Storage and Data Protection — 15 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.

Question 11

A data engineer is optimizing storage and performance for a massive fact table. Which THREE factors directly influence the efficiency of micro-partition pruning in Snowflake?

Accepted Answer

The data types of the columns used in the WHERE clause of the query.. Micro-partition pruning is the process where Snowflake skips irrelevant data blocks during a query. Its efficiency depends on how the data is physically organized (clustering), the order in which data was loaded (natural sort), and the filters used in the query. These factors determine how many partitions Snowflake must scan to return results.

Answer

The number of columns defined in the table's primary key constraint.

Answer

The size of the virtual warehouse used to execute the query.

Question 12

A data engineer is configuring Snowflake Iceberg tables. Which TWO factors determine the storage costs for these tables when Snowflake is the catalog provider?

Accepted Answer

The volume of data stored in the Parquet files within the external storage location.. For Iceberg tables where Snowflake serves as the catalog, Snowflake manages the metadata and the lifecycle of the data files. Storage costs are calculated based on the space consumed by the Parquet files in the external cloud storage and the metadata managed within Snowflake, similar to how standard tables are billed.

Answer

The number of files scanned by the Query Acceleration Service during data retrieval.

Answer

The frequency of manual VACUUM operations performed on the external stage.

Answer

The number of external tables that reference the same Iceberg metadata files.

Question 13

Which command should a data engineer use to restore a table that was accidentally deleted 30 minutes ago, assuming the table had a 1-day retention period?

Accepted Answer

UNDROP TABLE <table_name>;. The UNDROP TABLE command is the primary tool for recovering dropped tables in Snowflake. It works by restoring the table's metadata from the Time Travel layer. As long as the command is executed within the table's configured DATA_RETENTION_TIME_IN_DAYS, the table and its data can be fully recovered.

Answer

RESTORE TABLE <table_name> FROM TIME_TRAVEL;

Answer

SELECT * INTO FROM BEFORE (OFFSET => -1800);

Answer

ALTER TABLE <table_name> SET DROPPED = FALSE;

Question 14

What are TWO characteristic features of Temporary Tables in Snowflake regarding their storage and visibility?

Accepted Answer

They are visible only to the user who created them within their specific session.. Temporary tables are specialized objects designed for non-persistent data within a single session. They do not exist outside that session and are automatically cleaned up by Snowflake when the session terminates. This makes them ideal for storing transient results that should not be visible to other users or processes.

Answer

They support a maximum Time Travel retention period of 90 days.

Answer

They incur Fail-safe storage costs for 7 days after the session ends.

Answer

They can be converted to permanent tables using the ALTER TABLE command.

Question 15

A data engineer is managing a Snowflake environment with a mix of permanent and transient tables. The engineer wants to reduce storage costs associated with Time Travel and Fail-safe for a large permanent table that is only used for development. The table currently has DATA_RETENTION_TIME_IN_DAYS set to 10. What is the most effective action to minimize storage costs while retaining some recovery capability?

Accepted Answer

Change the table type to transient and set DATA_RETENTION_TIME_IN_DAYS to 1.. Converting a permanent table to transient removes the Fail-safe period and caps Time Travel at 1 day, which minimizes storage costs while allowing 24-hour recovery. Reducing retention on a permanent table leaves Fail-safe costs intact. Disabling Time Travel on a permanent table still incurs Fail-safe. Cloning does not change the underlying storage characteristics.

Answer

Set DATA_RETENTION_TIME_IN_DAYS to 1 on the permanent table.

Answer

Create a zero-copy clone of the table and drop the original.

Answer

Set DATA_RETENTION_TIME_IN_DAYS to 0 on the permanent table.