1. Concept Overview / Core Concept / Direct Confirmation Snowflake stores table data in immutable, columnar micro-partitions (50 MB to 500 MB uncompressed). Query pruning effectiveness determines whether queries scan only relevant micro-partitions based on filter predicates or needlessly scan the entire table.

2. Why This Answer Is Correct / Detailed Breakdown When queries filtering on `order_date` scan a disproportionate number of micro-partitions, it indicates poor natural clustering (clustering overlap). By evaluating partition pruning metrics in the Query Profile and calculating clustering depth using `SYSTEM$CLUSTERING_DEPTH`, the engineer can identify whether establishing an explicit clustering key is necessary to co-locate records by date and region.

3. Why Other Options Are Incorrect / Distractor Analysis - **A. Database Object Naming Standards:** Naming conventions are organizational best practices that have zero impact on physical query compilation or storage partition scanning. - **B. Virtual Warehouse Comments:** Documentation strings on warehouses do not affect query planning or data skipping. - **D. Role Hierarchy Ownership:** Access roles enforce security privileges; they do not influence the physical layout of data files on cloud storage.

4. Architectural & Execution Context / Physical Execution Snowflake micro-partitions automatically capture min/max metadata for every column. The Cloud Services layer utilizes this metadata during compilation to eliminate non-matching micro-partitions before compute nodes read data from remote cloud storage.

5. Practical Code Example

-- Evaluating clustering depth and partition pruning metadata
SELECT SYSTEM$CLUSTERING_INFORMATION('orders_large', '(order_date, customer_region)');

-- Checking partition pruning efficiency for a sample query
EXPLAIN 
SELECT COUNT(*), SUM(order_total) 
FROM orders_large 
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31' 
  AND customer_region = 'EMEA';

6. Key Takeaway Always inspect micro-partition pruning efficiency and clustering depth when queries scan excessive partitions on high-cardinality date or dimension filters.

7. Best Practice & Production Recommendation Only define clustering keys for large multi-terabyte tables with high query volume and clear, consistent query filter predicates, as automatic clustering incurs ongoing background credit costs.

8. Real-World Engineering Scenario An e-commerce order table with 15B rows took 95 seconds per query because date records were scattered across 80,000 micro-partitions. After defining a clustering key on `(order_date, customer_region)`, partition scanning dropped by 92% and query latency decreased to 4 seconds.

9. Common Exam Trap / Anti-Pattern / Misconception Do not automatically define clustering keys on small tables (<1 TB). Snowflake's natural ingestion clustering is usually sufficient, and clustering small tables wastes credits.

10. Related InsightfulSaga Tutorial Master these concepts in [Snowflake Performance Tuning](/tutorials/snowflake/snowflake-micro-partitions).