Snowflake Hidden Aggregation Functions You Need

When you think of aggregation functions in SQL, SUM(), COUNT(), and AVG() likely come to mind first. These are the workhorses of data analysis, undoubtedly. However, Snowflake, a titan in the data cloud, offers a treasure trove of specialized, unique aggregation functions that often fly under the radar. These functions aren’t just novelties; they are powerful tools that can simplify complex analytical problems and provide insights you might otherwise struggle to extract.

Let’s dive into some of Snowflake’s most potent, yet often overlooked, aggregation capabilities.

1. APPROX_TOP_K (and APPROX_TOP_K_ARRAY): Finding the Most Frequent Items Efficiently

Imagine you have billions of customer transactions and you need to quickly identify the top 10 most purchased products, or the top 5 most active users. A GROUP BY and ORDER BY on such a massive dataset can be resource-intensive. This is where APPROX_TOP_K shines.

This function provides an approximate list of the most frequent values in an expression. While not 100% precise (hence “approximate”), it offers a significantly faster and more resource-efficient way to get high-confidence results, especially on very large datasets.

Example Use Case: Top Products by Sales

Let’s use some sample sales data.

-- Create some sample sales data
CREATE OR REPLACE TABLE sales_data (
    sale_id INT,
    product_name VARCHAR(50),
    customer_id INT
);

INSERT INTO sales_data VALUES
…

APPROX_TOP_K returns a single JSON object, while APPROX_TOP_K_ARRAY returns an array of JSON objects, which is often more convenient for downstream processing.

2. MODE(): Identifying the Most Common Value Directly

Often, you need to find the value that appears most frequently within a group. While you could achieve this with GROUP BY, COUNT(), and QUALIFY ROW_NUMBER(), Snowflake simplifies it with a dedicated MODE() function.

Example Use Case: Most Common Payment Method by Region

Imagine you want to know which payment method is most popular in each sales region.

-- Sample transaction data
CREATE OR REPLACE TABLE transactions (
    transaction_id INT,
    region VARCHAR(50),
    payment_method VARCHAR(50)
);

INSERT INTO transactions VALUES
…

The MODE() function cleanly returns the most frequent non-NULL value. If there’s a tie, it can return any one of the tied values.

3. COLLECT_LIST() and COLLECT_SET(): Aggregating Values into Arrays

These functions are incredibly powerful for denormalization or when you need to gather all related items into a single, iterable structure within a column.

• COLLECT_LIST(): Returns an array of all input values, including duplicates, in an arbitrary order.

• COLLECT_SET(): Returns an array of all distinct input values, also in an arbitrary order.

Example Use Case: Customer Purchase History

You want to see all products a customer has ever purchased, aggregated into a single list.

-- Using the sales_data from above
-- Aggregate all products purchased by each customer
SELECT
    customer_id,
    COLLECT_LIST(product_name) AS all_products_purchased,
    COLLECT_SET(product_name) AS distinct_products_purchased
FROM
    sales_data
…

These functions are game-changers for building semi-structured data points or preparing data for machine learning features.

4. SKEW() and KURTOSIS(): Advanced Statistical Insights

For data scientists and advanced analysts, understanding the shape of a data distribution is crucial. SKEW() and KURTOSIS() provide direct measures of this.

• SKEW(): Measures the asymmetry of the probability distribution of a real-valued random variable about its mean. A negative skew indicates the tail is on the left, a positive skew on the right.

• KURTOSIS(): Measures the “tailedness” of the probability distribution. High kurtosis means more extreme outliers (heavier tails), while low kurtosis means lighter tails.

Example Use Case: Analyzing Price Distribution

-- Sample product prices
CREATE OR REPLACE TABLE product_prices (
    product_id INT,
    price_usd DECIMAL(10, 2)
);

INSERT INTO product_prices VALUES
(1, 10.00), (2, 12.50), (3, 11.00), (4, 100.00), (5, 9.50),
…

This clearly shows a positive skew (the price of 100.00 is pulling the average up) and high kurtosis due to that outlier.

Conclusion: Unlock Deeper Insights with Snowflake Unique Aggregations

While the common aggregation functions are essential, mastering these Snowflake unique aggregations can elevate your analytical capabilities significantly. They empower you to solve complex problems more efficiently, prepare data for advanced use cases, and derive insights that might otherwise remain hidden. Don’t let these powerful tools gather dust; integrate them into your data analysis toolkit today.