Storage and Data Protection practice questions

Enable Fail-safe on the database schema.

Why it fails: Fail-safe is an automated 7-day period that cannot be configured or manually triggered by users. It is a safety net for disaster recovery managed exclusively by Snowflake. It does not provide configurable point-in-time recovery for users or support a 90-day retention period for data access.

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

Why it fails: Transient tables have a maximum DATA_RETENTION_TIME_IN_DAYS of 1 day. They do not support the 90-day retention requirement necessary for long-term point-in-time recovery. Additionally, transient tables lack Fail-safe protection, making them unsuitable for scenarios where high durability and historical data access are mandatory.

Run the UNDROP command daily to reset the retention clock.

Why it fails: The UNDROP command is used to restore a dropped object, not to manage or extend the retention period of existing data. Running it daily does not impact the Time Travel configuration or extend the window during which historical data remains available for querying via AT or BEFORE clauses.

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

Why it fails: Fail-safe is a mandatory feature of the Snowflake service and cannot be disabled by users at the account, database, or table level. It is a fixed 7-day retention period designed for disaster recovery and operates independently of user-defined Time Travel settings or table configuration.

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

Why it fails: Fail-safe data is not accessible via standard SQL queries or the AT/BEFORE syntax. This period is strictly reserved for emergency data recovery performed by Snowflake Support. Users requiring historical data access must rely on the configured Time Travel retention period, which supports standard SELECT query operations.

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

Why it fails: The Fail-safe period is strictly limited to 7 days for all Snowflake accounts. It is not configurable to 30 days or any other duration. If data recovery is required beyond the 7-day Fail-safe period, the only alternative is to restore from a previously created database snapshot or backup file.

Query the data inside the dropped table using SELECT.

Why it fails: Once a table is dropped, it is no longer active in the namespace. It cannot be queried until the UNDROP command is executed to restore it to its active status. Any attempt to SELECT from a dropped table will result in an 'object does not exist' error.

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

Why it fails: DML operations are not permitted on dropped objects because the table is not part of the active, queryable schema. These operations require the table to be restored to an active state. Attempting to target a dropped table with DML will fail immediately, as the system cannot resolve the object name.

Running the PURGE command on the table.

Why it fails: Snowflake does not have a user-accessible 'PURGE' command to manually reclaim storage. Storage reclamation is handled automatically by the system based on the retention policies defined at the account, database, or table level. Manual attempts to clear storage are unnecessary and unsupported by the platform's architecture.

Creating a zero-copy clone of the table.

Why it fails: Cloning a table consumes metadata but does not duplicate the underlying data blocks. However, it does not reduce existing storage costs. If anything, maintaining clones can lead to higher storage usage if the data in the source and clone begins to diverge significantly over time, increasing overall storage footprint.

Enabling auto-clustering on the table.

Why it fails: Auto-clustering optimizes query performance by reorganizing data into micro-partitions. It does not reduce storage costs; in fact, the process of re-clustering can create temporary storage overhead during the reorganization. While beneficial for performance, it is not an effective mechanism for managing or reducing storage costs related to Time Travel.

Transient table (30 days).

Why it fails: Transient tables are limited to a maximum of 1 day of retention across all Snowflake editions, including Enterprise. They do not support a 30-day retention period. Furthermore, within the Standard Edition, no table type can exceed the 1-day account-level limitation, making this option technically incorrect on two fronts.

Permanent table (90 days).

Why it fails: The 90-day retention period is only available in Enterprise Edition and higher. Standard Edition is restricted to a maximum of 1 day for all permanent tables. Attempting to set this value higher in a Standard Edition account will result in an error, as it violates the edition's licensing constraints.

Temporary table (7 days).

Why it fails: Temporary tables have the same retention limitations as transient tables, capped at 1 day. They are not intended for long-term storage or extended Time Travel. The 7-day period mentioned is related to Fail-safe, which is a system-managed process and not a user-configurable Time Travel setting for temporary tables.

It is immediately purged and unrecoverable.

Why it fails: Data is not immediately purged after Time Travel expires. It enters the 7-day Fail-safe window. While this data is not queryable, it remains physically stored on the system to provide a final opportunity for recovery in the event of a catastrophic failure, ensuring an extra layer of protection.

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

Why it fails: Snowflake does not move deleted data to a 'cold storage' tier with zero costs. Data in the Fail-safe period remains on the same storage medium and continues to contribute to the account's monthly storage billing until it is finally purged at the end of the 7-day window.

It remains queryable via the UNDROP command.

Why it fails: The UNDROP command is used to restore a dropped table, not to query data that has 'aged out' of its retention period. Once the Time Travel window closes, the underlying table structure is no longer recoverable via the UNDROP command, as the data has moved into the Fail-safe state.

Running the RECOVER TABLE command in SQL.

Why it fails: There is no 'RECOVER TABLE' command in Snowflake. The correct command for restoring a dropped object is UNDROP. Furthermore, this only works while the table is within the Time Travel window. Since no such command exists, this is not a valid method for data recovery.

Using the ALTER TABLE ... RESET DATA.

Why it fails: There is no 'RESET DATA' command in Snowflake's DDL vocabulary. Attempting to use non-existent commands will result in a syntax error. Recovery strategies must rely on established features like Time Travel, Fail-safe requests, or external backups, rather than hypothetical or incorrectly named SQL administrative commands.

The underlying data in the tables is immediately deleted.

Why it fails: Deleting a database does not immediately delete the underlying data. All objects effectively move into a 'dropped' state. The data remains available for recovery via the UNDROP command for the duration of the Time Travel period, after which it enters the Fail-safe window for an additional 7 days.

Objects within the database can no longer be individually recovered.

Why it fails: Individual objects within the database can still be recovered via UNDROP as long as the parent database is also restored or as long as they were within the Time Travel window. It is common to restore a database and then selectively recover specific objects if needed for granular data restoration.

Fail-safe protection for the database is disabled.

Why it fails: Fail-safe is not a feature that can be enabled or disabled. When a database is dropped, the data within it continues to follow the standard Fail-safe lifecycle. This ensures that even if a database drop goes unnoticed for longer than the Time Travel window, the data remains recoverable via Support.

Search Optimization Service

Why it fails: Search Optimization creates separate access structures to speed up point lookups rather than duplicating micro-partitions. While it does incur its own storage and maintenance costs, it is not the primary mechanism behind the proliferation of micro-partition versions caused by frequent DML updates in standard table storage.

Query Acceleration Service

Why it fails: Query Acceleration Service provides additional compute resources to offload specific parts of a query's execution plan. It is a compute-centric feature designed to improve performance for outlier queries and does not impact the underlying storage cost or the way micro-partitions are versioned during DML operations.

Materialized View Maintenance

Why it fails: Materialized views occupy their own storage space and incur background maintenance costs, but they do not explain the storage growth of the base table itself. Their storage is managed independently, and while they sync with the base table, they are not the root cause of primary table versioning.

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

Why it fails: The UNDROP command only functions within the configured Time Travel window, which in this case is 90 days. Once the table enters the Fail-safe period (the final 7 days), it cannot be recovered via UNDROP; it requires a manual recovery process facilitated by Snowflake Support personnel.

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

Why it fails: The AT and BEFORE clauses are used for Time Travel queries, which are limited to the DATA_RETENTION_TIME_IN_DAYS setting. Since the retention is set to 90 days, any attempt to query data from 95 days ago will result in an error, as that data is no longer accessible via SQL.

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

Why it fails: Fail-safe is a mandatory feature for all permanent tables in Snowflake and cannot be disabled by users or account administrators. To avoid Fail-safe storage costs, data engineers must use transient or temporary table types, which are designed without this specific data protection layer for lower-priority data.

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

Why it fails: In Snowflake, primary keys are primarily for documentation and are not enforced by the engine. They do not create indexes or automatically influence the physical layout of micro-partitions. Therefore, the number of columns in a primary key has no direct impact on the efficiency of partition pruning.

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

Why it fails: While a larger warehouse provides more compute power and local SSD cache (which speeds up the scanning process), it does not change the metadata-based pruning logic itself. Pruning happens at the Global Services layer before the warehouse even begins to fetch the actual data blocks from storage.

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

Why it fails: While scanning files incurs compute costs, the number of files scanned is not a component of storage billing. Storage billing is strictly based on the capacity (bytes) of data at rest, not the frequency or volume of data access during query execution or acceleration.

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

Why it fails: VACUUM operations (or Iceberg snapshots expiration) are used to clean up old files and reduce storage costs over time. However, the 'frequency' of these operations is not a billing metric itself; rather, it is a maintenance task that affects the total bytes stored, which is the actual billable metric.

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

Why it fails: Snowflake storage billing is based on the unique data and metadata stored. Having multiple external table definitions pointing to the same files does not multiply the storage cost of those files, as the storage is only counted once regardless of how many logical references exist in the catalog.

RESTORE TABLE <table_name> FROM TIME_TRAVEL;

Why it fails: There is no 'RESTORE TABLE' command in Snowflake's SQL syntax for this purpose. While 'restore' is a common term in database management, Snowflake specifically uses the 'UNDROP' keyword to reverse the deletion of objects like tables, schemas, and databases within the Time Travel window.

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

Why it fails: This syntax is incorrect for several reasons. First, you cannot query a table that has been dropped using standard SELECT syntax. Second, the 'SELECT INTO' syntax is not supported in Snowflake for table creation; one would typically use 'CREATE TABLE ... AS SELECT' to achieve a similar result.

ALTER TABLE <table_name> SET DROPPED = FALSE;

Why it fails: Snowflake does not use an 'ALTER TABLE' property to manage the dropped state of an object. Dropping an object is a DDL operation that removes its metadata from the active catalog. Reversing this action requires the dedicated UNDROP command rather than a simple property change on the object.

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

Why it fails: Temporary tables are extremely limited in their Time Travel capabilities. They support a maximum of 1 day of Time Travel, though the retention period is effectively tied to the session's lifespan. Once the session ends and the table is dropped, the Time Travel data is also purged immediately.

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

Why it fails: Temporary tables do not have a Fail-safe period. Since they are intended for non-critical, short-lived data, Snowflake does not provide the disaster recovery protection of Fail-safe for them. This helps minimize storage costs for temporary workloads that do not require high levels of data durability.

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

Why it fails: Snowflake does not allow the direct conversion of a temporary table to a permanent table (or vice-versa) using an ALTER TABLE statement. To change the table type, a data engineer must create a new table of the desired type and copy the data using an INSERT or CTAS statement.

Set DATA_RETENTION_TIME_IN_DAYS to 1 on the permanent table.

Why it fails: Reducing retention to 1 day lowers Time Travel storage, but the table remains permanent and still has a mandatory 7-day Fail-safe period. Fail-safe storage costs would continue, so this does not fully minimize storage costs. The most effective action would be to change the table type to transient.

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

Why it fails: Zero-copy cloning creates a new table that shares micro-partitions, but dropping the original does not eliminate existing storage costs for the clone. The clone would still be a permanent table with its own retention settings, potentially incurring similar costs. This approach adds complexity without addressing the retention settings.

Set DATA_RETENTION_TIME_IN_DAYS to 0 on the permanent table.

Why it fails: Setting retention to 0 disables Time Travel, but the table remains permanent and still incurs 7-day Fail-safe storage costs. This does not minimize storage costs effectively because Fail-safe storage persists. The goal is to reduce both Time Travel and Fail-safe costs, which this option does not achieve.

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

Why it fails: Fail-safe is a non-queryable recovery layer that Snowflake Support can use to restore data after the Time Travel window expires. Engineers cannot directly query historical versions through Fail-safe. Choosing Fail-safe here would not satisfy the requirement to query the table as it existed at any point within the prior 30 days.

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

Why it fails: Standard Edition caps Time Travel retention at one day, not seven. A seven-day setting would not meet the 30-day requirement and, on Standard Edition, would not even be permitted. Choosing this configuration fails both the retention duration and the edition constraint for the stated requirement.

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

Why it fails: Zero-copy cloning creates a new table that shares micro-partitions at the time of the clone, but clones do not provide a continuous queryable history of the source table. A scheduled clone would only capture discrete snapshots and would consume additional metadata and storage management overhead. It does not replace Time Travel for point-in-time queries.

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

Why it fails: Time Travel storage is billed separately from the active table data. Snowflake charges for the additional storage consumed by retained historical versions beyond the active data. This option is incorrect because it assumes Time Travel has no cost impact, which contradicts how Snowflake meters storage for retained versions.

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

Why it fails: Setting retention to 0 disables Time Travel, but permanent tables still enter Fail-safe for up to 7 days after data changes. Fail-safe is independent of the Time Travel setting for permanent tables. This option incorrectly links the two mechanisms, implying that disabling Time Travel also eliminates Fail-safe storage charges.

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

Why it fails: Fail-safe is not directly queryable by any user, regardless of role. It is a Snowflake-managed recovery mechanism used only after Time Travel has expired, and recovery requires contacting Snowflake Support. This option misrepresents Fail-safe as a user-accessible storage tier, which it is not.

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

Why it fails: Fail-safe is a last resort used after Time Travel expires, and it is not a self-service mechanism. Since the table retains 30 days of Time Travel, the engineer can recover the data directly without Support. This option misplaces the recovery path and would unnecessarily delay restoration of the LEDGER_FACT rows.

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

Why it fails: UNDROP TABLE restores a dropped table object, not individual rows removed by a DELETE. Using it here would not bring back the deleted rows and could conflict with the existing table name. The correct approach is a Time Travel query against the table's historical state, not an UNDROP operation.

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

Why it fails: Time Travel covers DML operations such as DELETE, UPDATE, and INSERT, not just table drops. The historical versions remain accessible within the retention window. Claiming DELETE is excluded is factually wrong and would lead the engineer to abandon a straightforward recovery path for the LEDGER_FACT rows.

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

Why it fails: Snowflake does not support altering a table's type from transient to temporary; the table kind is fixed at creation time. There is no SET TEMPORARY clause in ALTER TABLE. Temporary tables also live only for the duration of the session that created them, which would break the hourly task pipeline that depends on a persistent staging object. The engineer would need to recreate the table to change its kind.

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

Why it fails: FAIL_SAFE_DAYS is not a settable account parameter in Snowflake; the seven-day Fail-safe period for permanent tables is fixed and cannot be configured or disabled. Fail-safe does not apply to transient tables at all, so no change is necessary. Attempting to set this parameter returns an error for an unknown parameter, and it would not affect a transient table's recoverability even if it existed.

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

Why it fails: Ninety days exceeds the maximum Time Travel retention allowed for any table type, and transient tables are capped at one day. This statement would fail because the parameter value is out of range for a transient table, and even if accepted it would extend recoverability rather than eliminate it, contradicting the compliance requirement. The account default for permanent tables does not override the transient table limit.

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

Why it fails: The syntax shown is valid for querying historical data with an absolute timestamp. If the retention period had not expired, the query would succeed. The error indicates the table does not exist, which typically means the retention period has expired or the table was purged. Syntax errors produce different error messages, not a 'table does not exist' error.

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

Why it fails: Time Travel can be used on dropped tables within the retention period by referencing the table name directly with the AT or BEFORE clause. The AT clause is valid for dropped tables as long as the retention period has not expired. The error is not caused by the table being dropped; it is caused by the retention period having expired or the timestamp being outside the retention window.

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.

Why it fails: In Snowflake Enterprise Edition, a dropped permanent table retains its configured DATA_RETENTION_TIME_IN_DAYS (up to 90 days) for Time Travel. The retention period is not automatically reduced to 1 day upon drop. Fail-safe adds 7 days after Time Travel, but Time Travel itself remains as configured. This option misstates the retention behavior for dropped tables.