DEA-C02 Storage and Data Protection Practice Test 2 — 15 Questions

Question 1

A data engineer is designing a disaster recovery plan for a Snowflake account. They need to protect a critical permanent table against accidental modification, and they want the ability to query the table as it existed at any point within the prior 30 days without needing Snowflake Support. Which feature should they configure, and what is the correct retention window for the account edition they should select?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Time Travel with DATA_RETENTION_TIME_IN_DAYS set to 30 on an Enterprise Edition account.. Time Travel provides queryable historical versions using AT and BEFORE clauses. On Enterprise Edition, permanent tables can have DATA_RETENTION_TIME_IN_DAYS set up to 90, so 30 days satisfies the requirement. Fail-safe is not queryable, Standard Edition caps retention at one day, and clones only capture discrete snapshots rather than continuous history.

Answer

Fail-safe with a 7-day window, because Fail-safe allows querying historical versions.

Answer

Time Travel with DATA_RETENTION_TIME_IN_DAYS set to 7, because Standard Edition caps retention at 7 days.

Answer

Zero-copy cloning with a daily scheduled task to snapshot the table, because clones provide queryable history.

Question 2

A data engineer is designing a storage strategy for a Snowflake account and wants to reduce storage costs while preserving recovery options for critical tables. Which two statements about Time Travel and Fail-safe are accurate? (Choose two.)

Accepted Answer

Transient tables do not have a Fail-safe period, so storage charges end after their Time Travel window closes.. Transient tables skip Fail-safe entirely, so their storage cost ends when the 1-day Time Travel window closes, while permanent tables continue to incur billable Fail-safe storage for up to 7 days after Time Travel expires. Together these two facts drive the cost-versus-recovery trade-off central to designing a Snowflake storage strategy.

Answer

Time Travel storage is always free because it is included in the base storage rate.

Answer

Setting DATA_RETENTION_TIME_IN_DAYS to 0 disables Time Travel but also disables Fail-safe for permanent tables.

Answer

Fail-safe storage can be queried directly by users with the appropriate role privileges.

Question 3

A Snowflake account uses Enterprise Edition. A permanent table named LEDGER_FACT has DATA_RETENTION_TIME_IN_DAYS set to 30. An engineer runs a DELETE statement that removes 40 percent of the rows, then immediately queries the table and finds the rows gone. Which statement correctly describes recovery options for the deleted rows?

Accepted Answer

The rows can be recovered by querying LEDGER_FACT AT (OFFSET => -60) and reinserting the missing rows.. Time Travel preserves historical versions of a table for the configured retention period, and DELETE operations are covered. Querying the table with an AT or BEFORE clause within the 30-day window returns the pre-delete data, which can be reinserted. This self-service recovery avoids Support involvement and restores the lost LEDGER_FACT rows quickly.

Answer

The rows can only be restored by contacting Snowflake Support to recover from Fail-safe.

Answer

The engineer must use the UNDROP TABLE command to restore the rows that were deleted.

Answer

The rows cannot be recovered because DELETE statements are not covered by Time Travel.

Question 4

A data engineer manages a Snowflake account where a transient table named STG_EVENTS is loaded hourly by a task. Compliance requires that the table never be recoverable after a DROP, and the table must not incur Fail-safe charges. The engineer needs to verify the current configuration of STG_EVENTS and, if needed, change it so both requirements are met. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Run ALTER TABLE STG_EVENTS SET DATA_RETENTION_TIME_IN_DAYS = 0 to eliminate Time Travel recovery.. A transient table already avoids the seven-day Fail-safe period that permanent tables incur, so the remaining requirement is to remove Time Travel recoverability. Setting DATA_RETENTION_TIME_IN_DAYS to 0 on the transient table disables Time Travel, making a DROP irreversible, and confirming the table kind with SHOW TABLES validates that no Fail-safe applies. Values above one day are invalid for transient tables, and table kind cannot be altered after creation.

Answer

Convert the table to a temporary table with ALTER TABLE STG_EVENTS SET TEMPORARY = TRUE.

Answer

Run ALTER ACCOUNT SET FAIL_SAFE_DAYS = 0 to remove the Fail-safe window for the table.

Answer

Run ALTER TABLE STG_EVENTS SET DATA_RETENTION_TIME_IN_DAYS = 90 to match the account default.

Question 5

A data engineer has a permanent table named SALES_FACT in a Snowflake Enterprise Edition account. The table has DATA_RETENTION_TIME_IN_DAYS set to 10. The table is dropped at 9:00 AM on Monday. On Wednesday at 3:00 PM, the engineer attempts to run SELECT * FROM SALES_FACT AT (TIMESTAMP => '2024-06-12 15:00:00'::TIMESTAMP); but receives an error that the table does not exist. What is the most likely reason for this error?

Accepted Answer

The timestamp specified is more than 10 days after the drop, so the table has moved to Fail-safe and is no longer accessible via Time Travel.. For a dropped table, Time Travel queries must reference a timestamp within the table's DATA_RETENTION_TIME_IN_DAYS window. Once that period ends, the table enters Fail-safe and cannot be queried with AT or BEFORE. The error 'table does not exist' occurs when the specified timestamp is outside the retention period, not because the table was dropped.

Answer

The table's 10-day retention period had not yet expired, so the error is due to a syntax issue in the AT clause.

Answer

Time Travel data is only accessible for tables that have not been dropped; the AT clause cannot be used on a dropped table.

Answer

The table was dropped, and the retention period for a dropped table is reduced to 1 day in Enterprise Edition, so the 10-day period did not apply.

Question 6

A data engineer manages a Snowflake Enterprise Edition account where a transient table named STG_EVENTS was created with DATA_RETENTION_TIME_IN_DAYS set to 10. The engineer needs to extend the retention period to 30 days to allow for longer recovery windows. What will happen when the engineer executes ALTER TABLE STG_EVENTS SET DATA_RETENTION_TIME_IN_DAYS = 30?

Accepted Answer

The command fails because transient tables cannot have a retention period longer than 1 day.. Transient tables in Snowflake enforce a maximum Time Travel retention of 1 day, regardless of the account edition. Setting DATA_RETENTION_TIME_IN_DAYS to 30 on a transient table produces an error because it exceeds this limit. To achieve longer retention, the table must be converted to a permanent table, which supports up to 90 days in Enterprise Edition.

Answer

The command succeeds but the retention period is automatically capped at 1 day.

Answer

The command succeeds and the table retains data for 30 days.

Answer

The command succeeds and the retention period is set to 30 days, but Fail-safe is disabled for the table.

Question 7

A data engineer needs to ensure that a temporary staging table used inside a transformation stored procedure is automatically removed and never contributes to long-term storage. The table is created and consumed entirely within a single session. Which table type best fits this requirement?

Accepted Answer

A temporary table, which exists only for the session in which it is created and is dropped automatically when the session ends.. Temporary tables are the only table type scoped to a session and dropped automatically when the session ends, which matches the requirement for a staging table used entirely within one procedure session. They also avoid Fail-safe and are limited to one day of Time Travel. Transient tables persist across sessions, permanent tables cannot be zeroed in Enterprise Edition, and external tables store no session-scoped data.

Answer

A permanent table with DATA_RETENTION_TIME_IN_DAYS set to 0, which removes all history and Fail-safe.

Answer

A transient table, which persists across sessions but never incurs Fail-safe charges.

Answer

An external table pointing to a stage, which holds no data in Snowflake storage at all.

Question 8

A data engineer is using Snowflake Enterprise Edition. A transient table named STAGING_TEMP is created with DATA_RETENTION_TIME_IN_DAYS set to 7. The engineer accidentally drops the table and then realizes the mistake 10 minutes later. The engineer attempts to recover the table using UNDROP. What is the outcome?

Accepted Answer

The UNDROP command succeeds, but the table is restored with a DATA_RETENTION_TIME_IN_DAYS of 1 day, not 7 days.. Transient tables in Snowflake have a maximum Time Travel retention of 1 day, regardless of the account edition. When the engineer set DATA_RETENTION_TIME_IN_DAYS to 7, Snowflake automatically reduced it to 1. After dropping the table, the engineer can use UNDROP within that 1-day window. The restored table will have a retention setting of 1 day, not 7. Thus, the UNDROP succeeds but with the corrected retention.

Answer

The UNDROP command fails because the table was dropped more than 1 day ago.

Answer

The UNDROP command fails because transient tables do not support Time Travel and cannot be recovered.

Answer

The UNDROP command succeeds because transient tables can be recovered within their Time Travel retention period.

Question 9

A data engineer accidentally dropped a permanent table named SALES_DATA in a Snowflake account that uses Standard Edition. The table had a DATA_RETENTION_TIME_IN_DAYS set to 1. The engineer realizes the mistake 30 hours after the drop. What recovery option is available?

Accepted Answer

The engineer cannot recover the table because it is beyond the Time Travel retention period and Fail-safe is not available in Standard Edition.. In Standard Edition, permanent tables have a maximum Time Travel retention of 1 day and no Fail-safe. After 30 hours, the table is beyond Time Travel and cannot be recovered using UNDROP. Without Fail-safe, there is no other recovery option. The engineer must recreate the table and reload data from backups or source systems.

Answer

The engineer can use the CLONE command to restore the table because cloning preserves historical versions.

Answer

The engineer can use UNDROP TABLE to restore the table because Time Travel retention is 1 day.

Answer

The engineer can recover the table from Fail-safe because Fail-safe provides 7 days of recovery.

Question 10

Refer to the exhibit. Why can the user not change the retention time of the table 'SENSITIVE_DATA' to 30 days?

Accepted Answer

The table is transient, which limits retention to one day.. The exhibit shows the table is a TRANSIENT table. Snowflake limits the retention period for transient tables to a maximum of one day. Because the table was created with the TRANSIENT keyword, it does not support long-term Time Travel, and the DATA_RETENTION_TIME_IN_DAYS parameter cannot be set to a value higher than 1. To support 30 days, the table must be recreated as a permanent table.

Answer

The user lacks the OWNERSHIP privilege on the table.

Answer

The table is currently locked by a running query.

Answer

The table has change tracking enabled.

Question 11

Which of the following describes the primary purpose of Snowflake's Fail-safe storage?

Accepted Answer

To provide a 7-day recovery period for catastrophic system failures.. Fail-safe is a critical component of Snowflake's data protection architecture, providing an immutable 7-day period that starts after the Time Travel window expires. It is intended for emergency recovery following system-level failures or catastrophic data loss. It is not designed for user-driven point-in-time recovery, which is the function of Time Travel, making Fail-safe a final, non-configurable safety mechanism for data durability.

Answer

To allow users to query data state from up to 90 days ago.

Answer

To facilitate zero-copy cloning of large production databases.

Answer

To enable cross-region replication for high availability.

Question 12

Refer to the exhibit. What is the DATA_RETENTION_TIME_IN_DAYS setting for the table after the UNDROP operation?

Accepted Answer

It remains 5.. When a table is dropped, its metadata and parameter settings are preserved. When the UNDROP command restores the table, it retrieves the original configuration, including the previously set DATA_RETENTION_TIME_IN_DAYS value of 5. This ensures that object-level policies remain consistent after recovery, allowing the Data Engineer to maintain control over historical data retention settings without needing to reconfigure them after accidental deletions.

Answer

It reverts to the database default.

Answer

It reverts to the account default.

Answer

It is reset to 1.

Question 13

Which of the following best describes the storage impact of zero-copy cloning a large table?

Accepted Answer

It incurs no additional storage cost at the time of cloning.. Zero-copy cloning is a metadata-only operation that does not duplicate existing micro-partitions. It creates a new logical object pointing to the same underlying storage blocks as the source. Consequently, there is no immediate storage cost at the time of creation. Storage costs only begin to accrue for the clone if DML operations (UPDATE, DELETE) create new versions of the data that diverge from the original table's state.

Answer

It immediately doubles the storage cost of the table.

Answer

It creates a physical copy that only shares unchanged data.

Answer

It requires the user to re-cluster the clone to avoid costs.

Question 14

What is the result of increasing the DATA_RETENTION_TIME_IN_DAYS from 1 to 5 days on an existing table?

Accepted Answer

The table begins retaining historical data for 5 days moving forward.. Increasing the retention period immediately updates the table's metadata. Going forward, Snowflake will begin retaining the historical data for 5 days instead of 1. This change does not retroactively 'create' historical data that has already been purged from the 1-day window; it only applies to the data state from the moment of the change onwards. This distinction is vital for planning data recovery and compliance audits.

Answer

All data from the last 5 days becomes immediately accessible.

Answer

The storage cost remains the same as it was.

Answer

The change takes effect only after the next vacuum process.

Question 15

Which Snowflake edition is required to support a 90-day Time Travel retention period?

Accepted Answer

Enterprise Edition.. Snowflake's Time Travel feature scales its capability based on the edition. Standard Edition is limited to a 1-day retention period. Enterprise Edition and Business Critical Edition support up to 90 days. This tier-based differentiation is a fundamental concept in Snowflake's storage and data protection architecture, requiring architects to align their service level agreements with the appropriate edition to meet data recovery and compliance needs.

Answer

Standard Edition.

Answer

Any edition supports 90 days by default.

Answer

Only the Trial Edition.