Smart Query Sampling with Feature Coverage and Unsupervised Machine Learning

Snowflake is a Data Cloud Platform with billions of daily customer queries. As Snowflake continues to grow with larger query volume, more diverse customers, and frequent software upgrades, there is a higher than ever demand for efficient testing. Snowtrail is a Snowflake-internal testing infrastructure that prepares, executes, and measures workloads from eligible customer queries. One of Snowtrail’s challenges is to generate concise workloads that are both representative of customer queries and thorough in testing Snowflake software features. Existing Snowtrail query workloads are either explicitly provided by users or pseudo-randomly selected. Here, we provide a novel sampling solution based on clustering feature-vectorized queries with unsupervised tasks. Variance across columns and multivariate Kullback–Leibler divergence scores show that a small K-Means sampled workload contains more spread but is less similar to the original population distribution. Column-wise Kolmogorov–Smirnov tests shows a general distinction between the K-Means sampled population from the true distribution regardless of sample size. Runtime experiments confirm that K-Means-based sampling is likely at least quadratic time with regard to the population size and linear time with regard to the sample size, making it not as practical for overly large datasets. Finally, an elbow method analogous to the K-Means elbow method is implemented to select the ideal sample size based on balancing variance and similarity to the population. Overall, this work brings practical improvements to the Snowflake Data Cloud, and the approach described in the paper is generally applicable to data management systems with feature collection capabilities.