Finding out how much of each table your queries really read

Overview

A market data provider had tables in the tens of billions of rows where query cost had grown out of proportion to result size. Clustering keys had been defined years earlier against query patterns that had since changed. AceMQ assessed pruning effectiveness across the largest tables.

Challenge

Snowflake performance and cost both come down to how many micro-partitions a query has to scan. When clustering keys stop matching filter predicates, pruning degrades quietly — queries keep returning correct results while scanning progressively more data. Automatic clustering also carries its own credit cost, which has to be weighed against the scanning it avoids.

Environment

Snowflake on Azure holding multi-year tick and reference data queried by research and reporting workloads.

Approach

AceMQ measured partitions scanned against partitions total for the dominant query patterns on each large table, which gives a direct read on pruning effectiveness. Candidate clustering keys were then evaluated against both the scanning they would avoid and the reclustering credits they would consume.

Solution

• Measured partitions scanned versus total partitions per query pattern to quantify actual pruning effectiveness
• Compared current clustering keys against the filter predicates queries use today rather than at design time
• Evaluated candidate clustering keys against both scan reduction and ongoing reclustering credit cost
• Assessed clustering depth on the largest tables to identify where reclustering was not keeping pace
• Reviewed table design for cases where partitioned tables or search optimization fit better than clustering
• Delivered per-table recommendations with expected scan reduction and credit impact stated for each

Outcome

Technologies