COF-C03 Question 7
Single answerDatabase Storage layerA data engineering team loads 2 TB of sales data into a Snowflake table every night. During the day, analysts run complex queries against the table while additional micro-batches update a small percentage of rows. The team is concerned that frequent DML operations might degrade query performance by causing large parts of the table to be rescanned. Which Snowflake Database Storage layer behavior best explains how Snowflake minimizes the impact of these updates on query performance?
- A
Snowflake stores table data in immutable micro-partitions and uses metadata to prune unnecessary partitions during query execution.
- B
Snowflake rewrites the entire table into a single compressed file after each update so that scans remain sequential and efficient.
- C
Snowflake locks updated partitions until the next automatic clustering cycle completes, preventing inconsistent reads.
- D
Snowflake caches updated rows in the virtual warehouse local storage and merges them into the table only when the warehouse is suspended.
Show answer and explanation
Correct answer: A
Explanation
Snowflake's Database Storage layer organizes table data into immutable micro-partitions stored in cloud storage. DML operations such as UPDATE, DELETE, and MERGE do not modify micro-partitions in place; instead, Snowflake writes new micro-partitions and uses metadata plus multiversion concurrency control (MVCC) to present the correct table version to each query. Because Snowflake tracks metadata about micro-partition contents, the optimizer can prune partitions that do not match query predicates, reducing the amount of data scanned. This architecture is a key reason Snowflake can handle concurrent analytics and ongoing data changes efficiently. See Snowflake documentation on micro-partitions, partition pruning, and Snowflake's architecture/storage model.
- A. Correct.
Correct. Snowflake stores table data in immutable, compressed columnar micro-partitions in cloud storage. When rows are updated, Snowflake creates new micro-partitions rather than modifying existing ones in place. It also maintains rich metadata, such as min/max values and other statistics, which allows partition pruning so queries scan only the relevant micro-partitions. This is a core Database Storage layer behavior that helps maintain performance even when DML occurs.
- B. Incorrect.
Incorrect. Snowflake does not rewrite an entire table into one large file after each update. Snowflake uses many micro-partitions, not a single file, and updates are handled by creating new versions of affected micro-partitions. Rewriting the full table would be inefficient and does not reflect Snowflake's architecture.
- C. Incorrect.
Incorrect. Snowflake supports ACID transactions with multiversion concurrency control, so readers can access a consistent version of the data without waiting for clustering operations. Updated partitions are not locked until automatic clustering finishes. This option confuses storage behavior with maintenance operations.
- D. Incorrect.
Incorrect. While Snowflake uses caching in several layers, updated rows are not held in warehouse local storage until suspension and then merged later. DML changes are committed to the table's storage representation using new micro-partitions. This option incorrectly describes Snowflake as if it used a deferred write-back mechanism tied to warehouse lifecycle events.