CCNA Storage and Data Protection Questions

Enterprise Edition raises the maximum Time Travel retention for permanent tables from one day to ninety days, enabling the 30-day window. Without Enterprise Edition, a 30-day retention setting would not be permitted, so verifying the edition is a prerequisite for meeting the requirement.

Zero-copy cloning creates a new table that initially references the same micro-partitions as the source, so no data is physically duplicated at creation. This satisfies the requirement of no additional storage up front. Storage diverges only when either table is modified, at which point new micro-partitions are written and the clone stops sharing those changed partitions.

When UPDATE operations rewrite micro-partitions, Snowflake writes new versions and retains the prior versions for the duration of the Time Travel retention period. With a 60-day window and repeated full-table updates, many historical versions accumulate, driving up Time Travel storage even though the current row count is unchanged.

For transient tables, the DATA_RETENTION_TIME_IN_DAYS parameter is capped at 1. Snowflake returns an error when you attempt to set a higher value. To get 30 days of Time Travel, the object must be a permanent table in an Enterprise Edition (or higher) account. The transient table type is the constraint here, not the account edition.

High average overlaps and a depth of 8.4 indicate that the values for C1 and C2 are scattered across many different micro-partitions. This overlap prevents efficient partition pruning during query execution, as the warehouse must open multiple partitions to find all relevant records for a specific key range.

Once a dropped table passes its Time Travel retention period, it enters Fail-safe for an additional 7 days. During Fail-safe, the table cannot be restored by the user with UNDROP; only Snowflake Support can recover it, and that recovery is not guaranteed. Since 20 days exceeds the 14-day retention, UNDROP is not possible.

Transient tables in Snowflake are designed for temporary data and support a maximum retention period of only one day. This architectural constraint prevents users from extending the Time Travel window beyond 24 hours, regardless of the Snowflake edition or account settings, ensuring efficient storage for short-lived, transient datasets.

Scheduled zero-copy clones provide independent copies that reference shared micro-partitions initially, costing little until data diverges. If the source is modified or dropped, a clone can serve as a recovery point. This complements Time Travel by giving the team a self-service restoration option that does not depend on Snowflake Support, supporting the stated goals.

Raising DATA_RETENTION_TIME_IN_DAYS extends the window for future changes. Historical versions already purged under the previous 1-day setting are not restored. This accurately reflects how the retention parameter applies going forward without retroactively recovering expired data, which is the correct behavior for SALES_ARCHIVE.

Increasing the retention setting tells Snowflake to hold onto data versions for the new, longer period starting from the time the parameter is updated. This allows for a longer Time Travel window for future data states, supporting the requirement to access historical data for analysis or recovery operations.

In Snowflake Standard Edition, the maximum Time Travel retention for permanent tables is 1 day. Setting DATA_RETENTION_TIME_IN_DAYS to 1 enables recovery of the table if it is dropped within that 1-day period. This is the minimum setting that provides recovery capability while keeping storage costs low. It meets the requirement to recover the table and minimizes storage overhead.

Initial cloning only creates new metadata entries. As long as the micro-partitions remain identical between the source and the clone, no new storage is consumed. Only when DML operations create new micro-partitions will the storage usage diverge, and Snowflake will bill for the unique data blocks.

Transient tables persist until explicitly dropped and can be seen by multiple users and sessions, fulfilling the requirement. They support Time Travel up to one day but lack the 7-day Fail-safe period, which significantly lowers storage costs for large-scale intermediate data that does not need disaster recovery.

UNDROP relies on Time Travel to restore a dropped table, and the recovery window equals the table's retention setting. With 5 days of retention, the table was recoverable for 5 days after the drop. At 6 days, that window has closed, so UNDROP fails. This is why timely recovery action matters.

The table has a 14-day Time Travel retention. A deletion that occurred 20 days ago is outside that window, so historical data for that point is no longer available to Time Travel. The earlier successful recovery does not extend the retention period. The team would need a longer retention setting or an external backup strategy.

Permanent tables are the only table type covered by Fail-safe. After the Time Travel retention period elapses, a 7-day Fail-safe window applies, during which Snowflake Support can assist with recovery. This matches the engineer's requirement for post-retention protection, making a permanent table the correct choice.

UNDROP TABLE restores the table with its original properties, including the DATA_RETENTION_TIME_IN_DAYS setting. The retention period is not changed by the drop or undrop operation. The restored table will continue to have a 5-day Time Travel retention, allowing further recovery if needed. This is the correct behavior.

With DATA_RETENTION_TIME_IN_DAYS set to 1, only the most recent 24 hours are queryable through Time Travel. A 40-hour-old version is outside that window, and Fail-safe cannot be queried by users at all. Because retention cannot be extended retroactively, the historical version is effectively unrecoverable without Snowflake Support intervention in a genuine disaster case.

In Snowflake Enterprise Edition, permanent tables support a maximum Time Travel retention of 90 days, configurable via DATA_RETENTION_TIME_IN_DAYS. This allows recovery of historical data for up to 90 days using AT or BEFORE clauses. This setting is ideal for critical tables requiring long recovery windows, but it increases storage costs because historical versions are retained.

Transient tables support up to 1 day of Time Travel on Enterprise Edition, which satisfies the 24-hour recovery need, and they do not incur Fail-safe storage. This makes them well suited for high-volume, short-lived data such as raw clickstream events. The absence of Fail-safe directly reduces storage cost, matching the team's objective.

A transient table with a 1-day retention still supports Time Travel within that window, so the engineer can query the pre-truncation version using AT (TIMESTAMP => ...) and re-insert the rows. This is the reliable in-place recovery method. Note the retention window must not have elapsed, and the TIMESTAMP must fall inside the retention period.

The BEFORE clause with a STATEMENT identifier reads the table as it existed immediately before the specified statement executed, which is exactly the pre-delete snapshot. Selecting from that snapshot and inserting the missing rows re-establishes the deleted data while preserving rows written after the delete. This works because the table's 30-day retention window still covers the twenty-minute-old delete, and the query ID can be retrieved from the Query History view.

Snowflake zero-copy cloning creates a new table that shares the same micro-partitions as the source without copying data. Storage is only charged for new micro-partitions created when the source or clone is modified after cloning. This makes the initial storage impact effectively zero, which is the defining benefit of the CLONE keyword for large tables.

The total lifespan of the data after a drop is the sum of its configured Time Travel (10 days) and the system-defined Fail-safe (7 days). Only after this cumulative 17-day window does Snowflake release the storage and permanently delete the micro-partitions from the underlying cloud storage service.

A permanent table in Enterprise Edition supports up to 90 days of Time Travel, so 60 is valid. Time Travel and Fail-safe are sequential windows: after the Time Travel period expires, Snowflake retains the data for an additional 7 days of Fail-safe, which is only accessible through Snowflake Support. The total recoverability is therefore 60 days of self-service Time Travel followed by 7 days of Fail-safe.

Transient tables have a default and maximum Time Travel retention of 1 day. Because 30 hours exceeds that 24-hour window, the historical data is no longer available for Time Travel. The engineer must design the reload process to retain data longer, or use a permanent table with a longer DATA_RETENTION_TIME_IN_DAYS setting.

Fail-safe is a Snowflake feature that provides a 7-day window after the Time Travel retention period ends. During this period, data can only be recovered by Snowflake Support. It is not user-accessible via SQL. This applies to permanent tables that have Time Travel enabled. Transient and temporary tables do not have Fail-safe. This statement correctly describes the duration and access method for Fail-safe recovery.

Transient tables never have Fail-safe coverage, and setting DATA_RETENTION_TIME_IN_DAYS to 0 removes Time Travel as well. The table remains available for normal queries, but no historical data can be recovered. This matches the engineer's cost-saving goal without affecting current data access, making it the correct outcome for STAGE_EVENTS.

Zero-copy cloning creates a new table that shares the same underlying micro-partitions as the source, so no data is physically duplicated at creation time. Storage is only added when one side diverges and new micro-partitions are written. This makes cloning near-instant and cost-efficient until changes occur.

Every time Automatic Clustering or an UPDATE statement modifies data, new micro-partitions are written. With 90 days of Time Travel, every single version of those partitions created over the last three months is preserved. In a high-activity environment, this historical data can easily dwarf the size of the active data.

Snowflake's hierarchical parameter model ensures that objects inherit settings from their parent containers unless explicitly overridden. By updating the schema, any table within it that has not been specifically configured with its own retention period will now follow the schema's new 30-day Time Travel policy.

Because zero-copy cloning is a metadata-only operation, no physical data is moved or copied. The new table references the same immutable micro-partitions as the source. Initial storage consumption is effectively zero, making this a highly efficient way to manage multiple environment versions without needing redundant data storage.

The RETENTION_TIME of '1' indicates a 1-day Time Travel window. During this period, the metadata for the dropped table is preserved, allowing the UNDROP TABLE command to succeed. This is the only recovery mechanism available for transient tables before the data is permanently deleted.

Cloned tables share micro-partitions with the source, and Snowflake bills each distinct micro-partition once regardless of how many tables reference it. The 500 GB of new loads and 100 GB of updated rows create partitions unique to the clone, which are billed. The unchanged 5 TB remains shared and is charged once across both tables, so the incremental storage attributable to the clone reflects only the changed data.

UNDROP restores the table object along with the retention configuration it had at the time of the drop. Since FACT_ORDERS had a 14-day retention period when it was dropped, the restored table retains that same 14-day setting, and the remaining Time Travel window continues from the original drop timestamp.

Enterprise Edition and higher (including Business Critical) are specifically designed to support long-term Time Travel up to 90 days. This capability allows organizations to meet rigorous compliance and historical data access requirements that are not achievable within the standard 1-day retention limit of the entry-level edition.

Temporary tables exist only within a session and are dropped automatically when the session ends. They have minimal Time Travel and no Fail-safe coverage, so they do not incur Fail-safe storage costs. For session-scoped scratch data, temporary tables provide the lowest protection and lowest storage overhead, matching the engineer's cost-reduction goal.

With DATA_RETENTION_TIME_IN_DAYS set to 0, Snowflake retains no historical versions for the table, so queries using AT or BEFORE clauses return no prior state. Because the batch job truncated the table, the only recovery path would have been Time Travel, which was effectively disabled by the zero-day retention setting.

With DATA_RETENTION_TIME_IN_DAYS set to 1, the table is recoverable via Time Travel for only 1 day after being dropped. After 2 days, the Time Travel period has expired, and the table has moved to Fail-safe, which does not support UNDROP. Thus, the UNDROP fails because the table is no longer in Time Travel.

Transient tables are capped at a maximum Time Travel retention of 1 day regardless of account edition. Setting 14 days exceeds that ceiling, so the statement is rejected. The 1-day value is the only valid nonzero retention for transient objects, which is why the attempt to extend it fails in this scenario.

When you reduce the retention period, historical data older than the new period becomes inaccessible and is eventually purged. However, storage costs may not decrease immediately because the data must first be purged from storage, which can take time. Additionally, Fail-safe still applies for 7 days after the retention period, so storage costs for Fail-safe may persist. This is the correct behavior.

The UNDROP operation restores the table to its exact state, including all defined parameters like DATA_RETENTION_TIME_IN_DAYS. Since it was explicitly set to 5 before the DROP command, that configuration is preserved in the metadata and reapplied upon restoration, ensuring no changes to the intended retention policy occur.

Because the table name is already in use by the new table, the UNDROP operation will error out. This prevents the unintentional destruction of the new table. The user is required to intervene—either by renaming the new table or dropping it—before the older, dropped version can be successfully brought back.

Fail-safe acts as a disaster recovery mechanism for critical system failures. If data is permanently lost beyond the reach of Time Travel, Snowflake Support can potentially recover data from the Fail-safe window. This 7-day window is mandatory, immutable, and ensures that data remains protected against unexpected infrastructure-level incidents.

On Enterprise Edition, permanent tables support DATA_RETENTION_TIME_IN_DAYS up to 90, so 30 is valid and provides the required 30-day Time Travel window for recovery from accidental deletions or modifications. After Time Travel expires, the table also benefits from the 7-day Fail-safe period, adding further protection. This configuration directly satisfies the stated requirement.

Standard Edition supports a maximum Time Travel retention of 1 day for permanent tables. The default is 1 day, and it can be set to 0 to disable Time Travel. Values above 1 day, such as 90, require Enterprise Edition or higher. This is why a 90-day setting is invalid on Standard Edition and must be corrected or the account upgraded.

Zero-copy cloning creates a new database that shares the same micro-partitions as the source. Initially, no additional storage is consumed. When data in the clone is modified, new micro-partitions are created for the changes, and storage is charged for those new partitions. The original micro-partitions remain shared until they are no longer referenced by either database. This makes the clone instantly available with minimal storage overhead.

Transient tables support Time Travel with a maximum retention of 1 day. Setting DATA_RETENTION_TIME_IN_DAYS to 1 enables recovery within 24 hours and, because transient tables have no Fail-safe period, no additional storage costs are incurred after the retention period expires. This directly meets the requirement.