Sample questions
Wh Snowflake Storage MicropartitionsDifficulty 1
In Snowflake, when rows are loaded into a table, how are micro-partitions created?
- aThe user must manually define partition boundaries before the load starts
- bSnowflake automatically groups the loaded rows into micro-partitions✓
- cMicro-partitions only get created once a CLUSTER BY key is defined on the table
- dA separate scheduled warehouse job creates micro-partitions after the load finishes
Explanation:Micro-partitioning is fully automatic in Snowflake: as rows are loaded, Snowflake groups them into micro-partitions without the user defining partition keys or running any extra job. CLUSTER BY only influences how future data is organized, it is not a precondition for partition creation.
Wh Snowflake Storage MicropartitionsDifficulty 2
What is the approximate target size of a single Snowflake micro-partition, measured as uncompressed data?
- aBetween 1 MB and 8 MB of uncompressed data
- bBetween 1 GB and 5 GB of uncompressed data
- cBetween 50 MB and 500 MB of uncompressed data✓
- dExactly 128 MB of uncompressed data, a fixed value
Explanation:Snowflake targets micro-partitions containing between 50 MB and 500 MB of uncompressed data; the value actually stored on disk is smaller because Snowflake compresses the data. It is a target range, not a single fixed size.
Wh Snowflake Storage MicropartitionsDifficulty 2
A row inside an existing Snowflake micro-partition needs to be updated by an UPDATE statement. What actually happens at the storage layer?
- aSnowflake writes a new micro-partition with the changed data; the original is left unmodified✓
- bSnowflake locates the exact byte offset of the row and overwrites it in the existing micro-partition
- cSnowflake appends the changed row to the end of the same micro-partition file
- dSnowflake locks the micro-partition and rewrites only the affected column values
Explanation:Micro-partitions are immutable. An UPDATE never modifies an existing micro-partition in place; Snowflake produces new micro-partitions holding the post-update state and the old micro-partition is superseded.
Wh Snowflake Storage MicropartitionsDifficulty 1
Snowflake stores column data within a micro-partition in columnar form. What is the direct benefit of this for query scanning?
- aIt guarantees that every query touches the same number of bytes regardless of columns selected
- bIt removes the need for Snowflake to keep any statistics about the data
- cIt forces every query to read all columns of a table together as one unit
- dA query only needs to scan the columns it actually references, not the whole row✓
Explanation:Because each column is stored independently inside a micro-partition, Snowflake can scan only the columns referenced by a query instead of reading entire rows, cutting down the bytes scanned.
Wh Snowflake Storage MicropartitionsDifficulty 2
Which of the following is part of the metadata that Snowflake automatically maintains for each micro-partition?
- aA full row-level checksum used to validate query results at runtime
- bThe exact physical disk sector where the micro-partition is stored
- cThe range of values (min/max) and the number of distinct values for each column✓
- dA copy of every SQL statement that has ever touched that micro-partition
Explanation:For each micro-partition, Snowflake stores metadata including the range of values for each column and the number of distinct values, among other properties used for query optimization.
Wh Snowflake Storage MicropartitionsDifficulty 1
What does 'pruning' mean in the context of Snowflake micro-partitions?
- aUsing stored metadata to skip micro-partitions that cannot match the query✓
- bPhysically deleting old micro-partitions that are no longer needed for Time Travel
- cCompressing a micro-partition further to reduce its size on disk
- dSplitting a large micro-partition into several smaller ones during a load
Explanation:Pruning is the process where Snowflake's query optimizer uses per-micro-partition metadata (such as min/max ranges) to skip scanning micro-partitions that cannot possibly contain rows satisfying the query's filters.