Performance monitoring and optimization: free practice questions

Correct (A): For permanent tables, Time Travel retention is defined by DATA_RETENTION_TIME_IN_DAYS (30 days here). After the Time Travel window expires, Snowflake moves the data into Fail-safe, which provides an additional 7 days of recovery accessible only by Snowflake Support. Total potential recovery window = 30 + 7 = 37 days. Since 40 days have elapsed, both Time Travel and Fail-safe have expired and the data is permanently gone — recovery is NOT possible. Incorrect (B): Snowflake does not retain data permanently. Once Fail-safe expires, data is purged and unrecoverable. Incorrect (C): UNDROP TABLE is a Time Travel operation and is only available within the DATA_RETENTION_TIME_IN_DAYS window (30 days). After that window, UNDROP is not possible. The 90-day limit refers to the maximum configurable retention on Enterprise edition, not a fixed recovery window. Incorrect (D): Fail-safe is always 7 days for permanent tables regardless of Snowflake edition. It is not extended to 30 days on Enterprise edition.

An idle warehouse still consumes credits for every second it remains in a STARTED state. Enabling AUTO_SUSPEND with a short timeout (such as 60 seconds) ensures the warehouse suspends automatically after the configured period of inactivity, eliminating idle credit waste. This is the standard Snowflake cost-optimization recommendation for warehouses with intermittent workloads. Option A is incorrect — upgrading the size increases the credit consumption rate per hour; it would make idle costs worse, not better. Option C is incorrect — MIN_CLUSTER_COUNT = 0 is not a valid Snowflake configuration; the minimum value for MIN_CLUSTER_COUNT is 1. Multi-cluster settings control concurrency scaling, not idle-state suspension. Option D is incorrect — MAX_CLUSTER_COUNT controls how many clusters can spin up under concurrency load; it does not affect idle credit consumption of the base warehouse.

When rows are deleted, Snowflake retains the original micro-partitions for the duration of the Time Travel retention period (7 days here), and these partitions do incur storage charges. However, Time Travel queries and clones work via metadata references to the existing micro-partitions — they do not create redundant full data copies at the moment of access. This is an important cost nuance. Option A is incorrect — cloning via Time Travel uses zero-copy cloning with metadata pointers; it does not immediately duplicate all data. Option C is incorrect — Time Travel storage is absolutely billable; it is a significant storage cost factor, especially for high-churn tables with long retention periods. Option D is incorrect — restoring data does not reset or extend the original Time Travel window; the original deleted micro-partitions' retention clock runs from the time of deletion.

Setting DATA_RETENTION_TIME_IN_DAYS = 0 on a permanent table disables the Time Travel window (no historical versions are accessible via AT/BEFORE syntax). However, Snowflake's Fail-Safe for permanent tables is a backend safeguard — its availability is not directly controlled by the retention setting, but with a 0-day Time Travel window, micro-partitions transition to Fail-Safe immediately upon data change/drop. Fail-Safe is still not user-accessible (self-service), but Snowflake Support may be able to recover data during the internal 7-day Fail-Safe period. — Option A is wrong: while it correctly states that Time Travel is disabled, saying Fail-Safe 'still provides a 7-day recovery window' implies user-accessible self-service, which is incorrect. — Option B is wrong: Fail-Safe is not disabled by setting retention to 0 on a permanent table; it is a Snowflake-managed backend mechanism that persists independently. — Option D is wrong: setting retention to 0 does NOT convert the table to a Transient table; Transient tables must be explicitly created as such and have their own properties (no Fail-Safe at all). A permanent table with retention=0 still has Fail-Safe.

Correct (B): In Snowflake, warehouse size determines the number of compute nodes and the total memory available. Larger warehouses provide more aggregate memory, allowing intermediate data (such as hash tables for joins or sort buffers) to remain in RAM. When memory is insufficient, Snowflake spills first to local SSD and then to remote storage. A larger warehouse eliminates this by providing enough in-memory capacity. Incorrect (A): Network bandwidth is not the bottleneck for spilling. Spilling is caused by insufficient memory for intermediate results, not by slow data ingestion from storage. Incorrect (C): QAS is not automatically enabled by increasing warehouse size; it must be explicitly enabled. Additionally, QAS does not specifically address spilling within a warehouse. Incorrect (D): The result cache (query result cache) stores final query results, not intermediate processing data. It only helps if the exact same query is re-run; it has no effect on spilling during execution and is not sized by warehouse.