COF-C03 Question 90
Single answer1.5 Explain Snowflake storage conceptsA data engineering team loads several terabytes of sales data into a Snowflake table every night. During troubleshooting, an engineer notices that the same table appears to consume additional storage after large DELETE operations, even though no new business data was added. The team wants to understand why storage usage can increase temporarily after DML changes and what Snowflake is doing behind the scenes. Which explanation best describes this behavior?
- A
Snowflake rewrites affected micro-partitions and retains old versions for Time Travel and Fail-safe, so both old and new storage can coexist temporarily after changes.
- B
Snowflake stores deleted rows in the virtual warehouse cache until the warehouse is suspended, which causes table storage to increase.
- C
Snowflake copies the entire table to a hidden stage before each DELETE statement, and that backup counts toward storage until manually removed.
- D
Snowflake converts deleted rows into uncompressed files in cloud storage, which remain attached to the table until reclustering finishes.
Show answer and explanation
Correct answer: A
Explanation
Snowflake stores table data in compressed, columnar, immutable micro-partitions in cloud storage. Because micro-partitions are immutable, DML operations such as UPDATE, DELETE, and MERGE are implemented by creating new micro-partitions and updating metadata rather than changing existing files in place. Older versions can remain accessible during the Time Travel retention period and subsequently in Fail-safe, which explains why storage may temporarily increase even after rows are deleted. This is a key Snowflake storage concept tested on SnowPro Core: understanding how micro-partitions, zero-copy architecture, and data protection features affect storage behavior. Relevant Snowflake documentation covers micro-partitions, Time Travel, and Fail-safe, and explains that storage growth after DML is expected until historical data versions age out according to retention policies.
- A. Correct.
Correct. Snowflake stores table data in immutable micro-partitions. When rows are updated or deleted, Snowflake does not modify existing micro-partitions in place. Instead, it creates new micro-partitions reflecting the change and metadata points to the current version. Older partition versions are retained for the configured Time Travel period and then for Fail-safe, which can temporarily increase storage usage after DML operations.
- B. Incorrect.
Incorrect. Virtual warehouse cache is compute-related and separate from persistent table storage. Warehouse cache can improve query performance by keeping data locally on compute nodes, but it does not determine long-term table storage consumption and is not where deleted rows are stored for recovery purposes.
- C. Incorrect.
Incorrect. Snowflake does not create a full hidden stage backup of the table before each DELETE. Internal stages are used for file staging, not as an automatic table-backup mechanism for DML operations. Recovery from recent changes is instead supported through Snowflake's storage architecture, including micro-partition versioning, Time Travel, and Fail-safe.
- D. Incorrect.
Incorrect. Snowflake does not convert deleted rows into uncompressed files tied to reclustering. Reclustering reorganizes micro-partitions for clustering efficiency, but deleted-row retention is governed by Snowflake's immutable micro-partition design and data protection features, not by temporary uncompressed files.