DAA-C01 Question 19
Select 3Data elements including statistics maintained by SnowflakeA data analyst is investigating why a query on a 4 TB SALES_FACT table is still scanning more data than expected, even though the table is clustered by ORDER_DATE. The analyst wants to understand which data elements Snowflake automatically maintains and can use for micro-partition pruning without requiring manually created statistics. Which information should the analyst rely on when reasoning about Snowflake's pruning behavior?
- A
Per-micro-partition metadata such as minimum and maximum values for columns
- B
Per-micro-partition metadata such as the number of distinct values for columns
- C
User-created histograms collected with an ANALYZE TABLE command
- D
A manually maintained index that stores row locations for filtered values
- E
Metadata about overlap and clustering depth across micro-partitions
Show answer and explanation
Correct answers: A, B, E
Explanation
Snowflake automatically collects and maintains metadata about micro-partitions as data is loaded. This includes column-level properties such as minimum and maximum values, number of distinct values, and other attributes used internally by the optimizer. These data elements enable micro-partition pruning without requiring manual statistics maintenance. In addition, Snowflake provides clustering insight, including overlap and depth, to help users assess how effectively micro-partitions support pruning for common filter predicates. By contrast, Snowflake does not require traditional ANALYZE TABLE commands or user-managed indexes for this purpose. Relevant documentation includes Snowflake materials on micro-partitions and data clustering, which describe automatically maintained metadata and functions such as SYSTEM$CLUSTERING_INFORMATION.
- A. Correct.
Correct. Snowflake automatically stores metadata for each micro-partition, including column-level minimum and maximum values. These statistics are fundamental to partition pruning because Snowflake can eliminate micro-partitions whose value ranges cannot satisfy a predicate. This is one of the core data elements maintained by Snowflake and does not require user-managed statistics collection.
- B. Correct.
Correct. Snowflake also maintains additional column-level metadata for micro-partitions, including the number of distinct values. While min/max ranges are the most commonly discussed for pruning, NDV-style metadata is part of the automatically maintained micro-partition statistics Snowflake uses internally for optimization decisions. Candidates often overlook that Snowflake maintains richer metadata than only min/max.
- C. Incorrect.
Incorrect. Snowflake does not use a traditional user-invoked ANALYZE TABLE process to collect optimizer statistics in the way some other databases do. This option reflects a common misconception carried over from platforms such as Oracle or PostgreSQL. Snowflake automatically gathers and maintains metadata; users do not create histograms through ANALYZE TABLE for pruning behavior.
- D. Incorrect.
Incorrect. Snowflake does not rely on traditional manually maintained indexes that store row pointers for selective filters. Query performance is typically driven by micro-partition metadata, pruning, clustering, and optional features such as Search Optimization Service for specific lookup patterns. Standard B-tree-style index management is not part of normal Snowflake table optimization.
- E. Correct.
Correct. Snowflake exposes clustering-related information, including overlap and clustering depth, through system functions such as SYSTEM$CLUSTERING_INFORMATION. This metadata helps analysts understand how well data is organized for pruning across micro-partitions. Although it is not a traditional statistic like a histogram, it is maintained information directly relevant to Snowflake's pruning effectiveness and query performance analysis.