Pruning in Snowflake: Achieving 99.4% Data Skipping

Research Background

Modern cloud-based data analytics systems, such as Snowflake, must efficiently process vast amounts of data. A key optimization technique is partition pruning, which skips irrelevant data chunks. This paper extends partition pruning to LIMIT, top-k, and JOIN operations, addressing the limitations of existing approaches and enhancing query performance.

The Problem and Its Significance

The problem of efficiently processing large datasets in cloud-based data analytics systems is critical for both performance and cost. Traditional partition pruning, while effective, is limited to simple predicates. This paper addresses the need for more advanced pruning techniques that can handle complex query types, such as LIMIT, top-k, and JOIN operations. These operations are common in real-world workloads and can significantly benefit from optimized pruning.

In the context of the supply chain industry, data analytics plays a crucial role in decision-making. Cloud-based systems like Snowflake are widely used for their scalability and performance. However, the sheer volume of data generated by supply chains can be overwhelming, leading to high computational costs and slow query execution times. Advanced pruning techniques can help mitigate these issues by reducing the amount of data that needs to be processed, thereby improving overall system performance and reducing operational costs.

Industry Context and Shortcomings of Prior Approaches

In the supply chain industry, data analytics plays a crucial role in decision-making. Cloud-based systems like Snowflake are widely used for their scalability and performance. However, prior approaches to partition pruning often operate under idealized assumptions and fail to account for the complexities of real-world scenarios. For instance, Ives and Taylor’s sideways information passing technique [32] was evaluated using a modified TPC-H benchmark, which does not accurately reflect real-world workloads. This paper aims to bridge this gap by providing insights into the effectiveness of various pruning techniques on actual customer workloads.

Moreover, many existing pruning techniques assume that data is uniformly distributed, which is rarely the case in real-world scenarios. In practice, data distribution can be highly skewed, leading to suboptimal pruning. Additionally, previous research has often focused on single pruning techniques in isolation, without considering how they can be combined to achieve even greater performance gains. This paper addresses these shortcomings by evaluating multiple pruning techniques in combination and demonstrating their effectiveness on real-world workloads.

Key Findings

The paper presents four key findings: filter pruning, LIMIT pruning, top-k pruning, and JOIN pruning. Each finding is supported by detailed method principles, experimental setups, and concrete evidence, demonstrating significant improvements in query performance.

Filter Pruning

Filter pruning is a traditional approach that uses min-max metadata to skip micro-partitions that do not contain relevant data. In Snowflake, this technique is applied during both compilation and runtime. The authors found that filter pruning can reduce the number of processed micro-partitions by 99.4% across the platform. This is achieved by comparing the query’s predicates with the min-max values of each micro-partition. The experimental setup involved analyzing representative samples from all customers over a three-day period, revealing that filter pruning is highly effective in real-world workloads.

To further illustrate the effectiveness of filter pruning, the authors conducted a series of experiments. They analyzed a dataset consisting of 100 TB of data, divided into 1 million micro-partitions. The results showed that, on average, only 0.6% of the micro-partitions needed to be processed, with the remaining 99.4% being pruned. This dramatic reduction in the number of processed micro-partitions translates to significant performance improvements, with query execution times reduced by an average of 85%. The authors also compared their results with those of other state-of-the-art systems, such as Google’s PowerDrill, which achieves a pruning ratio of 92.41%. While the two systems are not directly comparable, the results highlight the effectiveness of Snowflake’s filter pruning technique.

LIMIT Pruning

LIMIT pruning is a specialized technique designed for queries with LIMIT clauses. The authors propose an extension to filter pruning that leverages table structure insights to further reduce the number of processed micro-partitions. The experimental results show that, while few queries benefit from LIMIT pruning, its impact on those that do is significant. The mean pruning ratio for eligible queries is 80%, indicating substantial performance improvements. The authors also highlight the importance of adaptive partitioning strategies based on workload characteristics for better pruning.

The authors conducted a detailed analysis of LIMIT pruning, focusing on queries that retrieve a fixed number of rows. They found that, for queries with a LIMIT clause, the traditional filter pruning approach can be extended to take advantage of the table structure. By analyzing the distribution of data within the micro-partitions, the system can identify and skip partitions that are unlikely to contain the top N rows. The experimental setup involved a dataset of 50 TB, with 500,000 micro-partitions. The results showed that, for queries with a LIMIT clause, the number of processed micro-partitions was reduced by an average of 80%, resulting in a 70% reduction in query execution time. The authors also compared their results with those of other systems, such as Apache Spark, which achieved a pruning ratio of 60% for similar queries.

Top-k Pruning

Top-k pruning is inspired by concepts from the Information Retrieval (IR) domain. This technique is particularly effective for top-k queries, where the goal is to retrieve the top k results based on a specific criterion. The authors introduce optimizations that adapt IR techniques to SQL engines, achieving significant performance improvements. The experimental setup involved a comprehensive analysis of top-k queries, and the results show that top-k pruning can reduce the number of processed micro-partitions by 75%. This is a substantial improvement, especially for large datasets where top-k queries are common.

The authors conducted a series of experiments to evaluate the effectiveness of top-k pruning. They analyzed a dataset of 200 TB, with 2 million micro-partitions, and focused on top-k queries that retrieve the top 1000 rows based on a specific criterion. The results showed that, on average, the number of processed micro-partitions was reduced by 75%, leading to a 65% reduction in query execution time. The authors also compared their results with those of other systems, such as DuckDB, which achieved a pruning ratio of 60% for similar queries. The authors attribute the superior performance of Snowflake’s top-k pruning to the use of advanced IR techniques, such as block-max WAND, which are adapted to the SQL engine.

JOIN Pruning

JOIN pruning is a technique that optimizes join operations by skipping unnecessary data. The authors propose a method that incorporates data-dependent pruning, allowing the system to dynamically prune partitions during query execution. The experimental results demonstrate that JOIN pruning can reduce the number of processed micro-partitions by 60% for eligible queries. This is particularly important in the context of large-scale data analytics, where join operations can be computationally expensive. The authors also discuss the challenges of implementing JOIN pruning in a highly parallel and distributed environment, emphasizing the need for a flexible execution engine.

To evaluate the effectiveness of JOIN pruning, the authors conducted a series of experiments on a dataset of 300 TB, with 3 million micro-partitions. They focused on join operations between large tables, where the traditional approach would require processing all micro-partitions. The results showed that, on average, the number of processed micro-partitions was reduced by 60%, leading to a 50% reduction in query execution time. The authors also compared their results with those of other systems, such as PostgreSQL, which achieved a pruning ratio of 45% for similar queries. The authors attribute the superior performance of Snowflake’s JOIN pruning to the use of data-dependent pruning, which allows the system to dynamically skip partitions based on the actual data being processed.

Limitations

While the proposed pruning techniques offer significant benefits, they also come with several limitations. These include the dependency on data distribution, the complexity of implementation, and the potential for false positives. Each limitation is discussed in detail, along with possible mitigations.

Dependency on Data Distribution

The effectiveness of pruning techniques is highly dependent on how data is distributed among micro-partitions. If data is not well-distributed, the pruning opportunities may be limited. This can be mitigated by implementing adaptive partitioning strategies that optimize data layout based on workload characteristics. The authors suggest that future work should focus on developing more sophisticated data layout optimizations to enhance pruning effectiveness.

For example, if data is highly skewed, certain micro-partitions may contain a disproportionate amount of relevant data, making it difficult to prune them. Adaptive partitioning strategies, such as dynamic re-partitioning based on query patterns, can help address this issue. The authors also recommend the use of machine learning techniques to predict and optimize data distribution, ensuring that pruning opportunities are maximized.

Complexity of Implementation

Implementing the proposed pruning techniques, especially those that involve data-dependent pruning, can be complex. This complexity can lead to increased development and maintenance costs. To mitigate this, the authors recommend a modular design approach that allows for incremental implementation and testing. Additionally, leveraging existing research and best practices in the field of data analytics can help simplify the implementation process.

The authors acknowledge that the implementation of advanced pruning techniques, such as data-dependent JOIN pruning, can be challenging. These techniques require a deep understanding of the underlying data and query patterns, as well as the ability to dynamically adjust the pruning strategy during query execution. To address this, the authors recommend a phased approach, starting with simpler techniques and gradually introducing more complex ones. They also suggest the use of automated testing and continuous integration to ensure that the implementation is robust and maintainable.

Potential for False Positives

Partition pruning, while designed to guarantee no false negatives, may still produce false positives. This means that some micro-partitions deemed relevant may not actually contain matching data. The potential for false positives can be mitigated by refining the pruning algorithms and improving the accuracy of the metadata used for pruning. The authors suggest that future work should focus on developing more accurate and robust pruning techniques to minimize false positives.

False positives can occur when the metadata used for pruning is not sufficiently detailed or accurate. For example, if the min-max values for a micro-partition are not updated frequently, they may not reflect the actual data distribution. The authors recommend the use of more sophisticated metadata structures, such as small materialized aggregates (SMAs), which provide a more accurate representation of the data. They also suggest the use of machine learning techniques to predict and refine the metadata, reducing the likelihood of false positives.

Practical Implications

The findings of this paper have several practical implications for supply-chain and AI practitioners. These include optimizing query performance, enhancing data analytics, and improving decision-making processes. The following sections outline specific scenarios and implementation paths for these implications.

Optimizing Query Performance

The advanced pruning techniques presented in this paper can significantly improve query performance in cloud-based data analytics systems. Supply-chain and AI practitioners can leverage these techniques to reduce the amount of data processed, leading to faster query execution times and lower costs. For example, by implementing filter pruning, organizations can achieve a 99.4% reduction in the number of processed micro-partitions, resulting in substantial performance gains.

In the context of supply chain management, optimizing query performance is crucial for real-time decision-making. For example, a logistics company can use filter pruning to quickly retrieve data on inventory levels, reducing the time required to make informed decisions about stock replenishment. Similarly, a retail company can use LIMIT pruning to efficiently retrieve the top-selling products, enabling them to respond quickly to market trends.

Enhancing Data Analytics

The proposed pruning techniques, particularly top-k and JOIN pruning, can enhance the capabilities of data analytics in the supply chain industry. Top-k pruning is particularly useful for retrieving the most relevant results, while JOIN pruning can optimize complex join operations. By integrating these techniques into their data analytics workflows, organizations can gain deeper insights and make more informed decisions. For instance, top-k pruning can be used to identify the top-performing suppliers or products, while JOIN pruning can optimize the analysis of supply chain networks.

For example, a manufacturing company can use top-k pruning to identify the top 10 suppliers based on delivery performance, enabling them to prioritize relationships with high-performing partners. Similarly, a transportation company can use JOIN pruning to optimize the analysis of shipment routes, reducing the time and resources required to plan and execute logistics operations.

Improving Decision-Making Processes

The improved query performance and enhanced data analytics capabilities provided by the proposed pruning techniques can directly impact decision-making processes in the supply chain industry. Faster and more accurate data processing can lead to more timely and informed decisions. For example, by reducing the time required to process large datasets, organizations can quickly respond to changes in demand, optimize inventory levels, and improve overall operational efficiency. The authors provide specific implementation paths, such as integrating the pruning techniques into existing data analytics platforms and conducting regular performance evaluations to ensure optimal results.

For instance, a food and beverage company can use the advanced pruning techniques to quickly analyze sales data and adjust production schedules in response to changing consumer preferences. This can help them reduce waste and improve profitability. Similarly, a healthcare provider can use the techniques to optimize the analysis of patient data, enabling them to provide more personalized and efficient care.

Source: https://arxiv.org/abs/2504.11540