DAA-C01 Question 20
Single answerData elements including statistics maintained by SnowflakeA data analyst is troubleshooting why a query on a 4 TB SALES_FACT table still scans many micro-partitions even though the query filters on ORDER_DATE and REGION. The analyst wants to understand which data elements Snowflake automatically maintains and uses to help prune micro-partitions without requiring manual statistics collection. Which statement best describes the statistics Snowflake maintains for this purpose?
- A
Snowflake automatically stores metadata such as the minimum and maximum values for columns in each micro-partition, along with other properties that support pruning.
- B
Snowflake requires ANALYZE TABLE to collect optimizer statistics for each column before micro-partition pruning can occur.
- C
Snowflake stores histogram statistics only for clustered tables; unclustered tables cannot benefit from pruning.
- D
Snowflake relies primarily on user-created indexes to determine which micro-partitions to scan for selective filters.
Show answer and explanation
Correct answer: A
Explanation
Snowflake automatically collects and maintains metadata about micro-partitions at write time. This metadata includes data ranges for columns and other summary information that allows the optimizer to eliminate irrelevant micro-partitions during query execution. This capability is one of the main reasons Snowflake does not require manual statistics maintenance or traditional indexing for many analytic workloads. In practice, when filters align well with the natural organization of data or with clustering keys, micro-partition pruning becomes more effective and reduces scan volume. See Snowflake documentation on micro-partitions, data clustering, and partition pruning for details on the metadata Snowflake stores and how it is used by the optimizer.
- A. Correct.
Correct. Snowflake automatically maintains metadata for each micro-partition, including information such as column ranges (minimum and maximum values), number of distinct values, and other properties that can be used for partition pruning. This is a core architectural feature of Snowflake and does not require users to run manual statistics collection commands.
- B. Incorrect.
Incorrect. This reflects a common misconception carried over from traditional database platforms. Snowflake does not require users to run ANALYZE TABLE to gather statistics for micro-partition pruning. Snowflake automatically collects and maintains the metadata it needs for pruning and query optimization.
- C. Incorrect.
Incorrect. Snowflake does not depend on user-managed histogram statistics in the way some traditional systems do, and pruning is not limited to clustered tables. Even unclustered tables benefit from automatic micro-partition metadata; clustering can improve pruning effectiveness, but it is not a prerequisite for pruning.
- D. Incorrect.
Incorrect. Snowflake does not use traditional user-created indexes for this purpose. Query pruning is driven by automatically maintained micro-partition metadata rather than manual index structures. Choosing this option usually indicates confusion with on-premises RDBMS optimization techniques.