Finding out how much of each table your queries really read

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.

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

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.

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