Snowflake’s Unique Aggregation Functions You Need to Know

The first time I ran APPROX_TOP_K on a 2-billion-row events table, the query finished in under four seconds. The equivalent GROUP BY / ORDER BY / LIMIT had been timing out at 8 minutes. Same result. Ninety-eight percent the same result, to be precise — but for top-10 product rankings, that margin was irrelevant.

That’s the pattern with Snowflake’s lesser-known aggregation functions: they’re not novelties, and they’re not edge cases for data scientists. They solve real, recurring problems — frequency estimation at scale, distribution analysis, Jaccard similarity across user sets — that the standard SUM/COUNT/AVG toolkit either can’t handle or handles slowly and expensively.

This article covers six of them. Each section includes the practical use case, the SQL, the output, and a note on where I’ve seen it matter — or where I initially got it wrong.

1. APPROX_TOP_K — Frequency Estimation Without GROUP BY Pain

APPROX_TOP_K · APPROX_TOP_K_ARRAY

When you need the top-N most frequent values in a large dataset, a naive GROUP BY ... ORDER BY COUNT(*) DESC LIMIT N forces a full sort of the aggregated result. On hundreds of millions of rows that’s slow, memory-intensive, and often unnecessary — because you don’t need exact counts, you need the right top items.

APPROX_TOP_K uses a space-efficient sketch (the Space-Saving algorithm) to identify the most frequent values with high confidence and a fraction of the compute. The accuracy is configurable. The default counter size gives you the top-K items with at most a small error margin, and in practice I’ve never seen it return a wrong top-10 on real sales or event data.

One thing that tripped me up early: APPROX_TOP_K returns a single JSON object, while APPROX_TOP_K_ARRAY returns an array of JSON objects. If you’re piping the result into a downstream process or flattening it with LATERAL FLATTEN, use the array variant.

1-- APPROX_TOP_K_ARRAY: top 3 most sold products2-- Works correctly on tables with billions of rows where GROUP BY times out3WITH top_k AS (4  SELECT APPROX_TOP_K(product_group, 3) AS arr5  FROM SNOWDQ_DB.PUBLIC.SALES_DATA6)7SELECT f.value[0]::STRING AS product_group,8       f.value[1]::INT    AS approx_count9FROM top_k, LATERAL FLATTEN(INPUT => top_k.arr) f;

The accuracy guarantee: APPROX_TOP_K can report a false positive — an item that’s not truly in the top K — only if its actual frequency is greater than n/(K + error_rate). In practice on typical sales or click data, the result is reliable for any K ≤ 100.

2. MODE — The Aggregation You’ve Been Writing Manually

MODE()

Before I knew MODE() existed, I was writing this every time I needed the most common value per group:

1-- The manual way — verbose and slower2SELECT region, payment_method3FROM (4  SELECT region, payment_method,5         ROW_NUMBER() OVER (PARTITION BY region ORDER BY COUNT(*) DESC) AS rn6  FROM transactions7  GROUP BY region, payment_method8)9WHERE rn = 1;

That works, but it’s a subquery with a window function over an aggregation. MODE() does the same job in a single line:

1WITH transactions(region, payment_method) AS (2  SELECT * FROM VALUES ('North','Credit Card'),('North','Credit Card'),('North','Cash'),3  ('South','Cash'),('South','Cash'),('South','PayPal'),('East','PayPal'),('East','PayPal'),('East','Cash')4),5manual AS (6  SELECT region, payment_method FROM (7    SELECT region, payment_method,8           ROW_NUMBER() OVER (PARTITION BY region ORDER BY COUNT(*) DESC) AS rn9    FROM transactions GROUP BY region, payment_method) WHERE rn = 110),11m AS (SELECT region, MODE(payment_method) AS most_common_payment FROM transactions GROUP BY region)12SELECT m.region, m.most_common_payment, manual.payment_method AS manual_result13FROM m JOIN manual USING (region) ORDER BY region

The one behaviour to be aware of: when there’s a tie — two values with equal frequency — MODE returns one of them non-deterministically. If tie-breaking matters to your use case, you’ll need to fall back to the ROW_NUMBER pattern with an explicit tiebreaker in the ORDER BY. But for dashboards, feature engineering, and exploratory analysis, MODE is far cleaner.

3. ARRAY_AGG and ARRAY_UNIQUE_AGG: Turning Rows into Arrays ARRAY_AGG() · ARRAY_UNIQUE_AGG()

These are among the most useful Snowflake functions for denormalization work. Use them anywhere you need to roll related values up into a single array column. ARRAY_AGG keeps duplicates, and adding WITHIN GROUP (ORDER BY ...) controls the order of the array. ARRAY_UNIQUE_AGG returns distinct values only. Use ARRAY_AGG when you want the full purchase history, including repeat purchases. Use ARRAY_UNIQUE_AGG when you want the distinct set of products a customer has ever bought.

Coming from Spark or Hive? COLLECT_LIST maps to ARRAY_AGG, and COLLECT_SET maps to ARRAY_UNIQUE_AGG. Snowflake didn’t recognize the Spark names when I tested them.

1SELECT2  customer_id,3  ARRAY_AGG(product_name) WITHIN GROUP (ORDER BY sale_date) AS full_purchase_history,    -- includes dupes4  ARRAY_UNIQUE_AGG(product_name)                            AS distinct_products_bought  -- unique only5FROM sales_data6GROUP BY customer_id7ORDER BY customer_id;

Where I use this most: building ML feature tables where one column needs to represent a user’s interaction history as a variable-length sequence, and in customer 360 pipelines where you want to denormalize order line items back up to the customer level before writing to a serving layer.

4.SKEW and KURTOSIS: Distribution Diagnostics Without Leaving SQL SKEW() · KURTOSIS()

When data engineers want to know whether a numeric column is lopsided or full of outliers, most export it to Python or open a BI tool. Snowflake can answer that in SQL, so you can run distribution checks inside a dbt model or a data quality test without switching tools.

SKEW measures asymmetry. A positive value means a long right tail: a few very high values pull the mean up, which is typical of price data with luxury outliers. A negative value means the tail is on the left. A value near 0 means the distribution is roughly symmetric.

KURTOSIS measures how heavy the tails are. Snowflake returns excess kurtosis, so a normal distribution scores about 0. Tools that report raw kurtosis put a normal distribution at about 3. Positive values mean more extreme outliers than a normal distribution would produce.

Example Use Case: Analyzing Price Distribution

1-- Distribution diagnostics on product prices2SELECT3  COUNT(price_usd)    AS n,4  SKEW(price_usd)     AS price_skewness,5  KURTOSIS(price_usd) AS price_excess_kurtosis   -- normal ≈ 06FROM product_prices;7 8-- Sample output: seven prices between $10 and $13, plus one $100 outlier9-- N | PRICE_SKEWNESS | PRICE_EXCESS_KURTOSIS10-- --|----------------|----------------------11-- 7 | 2.64           | 6.98

The single $100 outlier creates strong positive skew and heavy tails. That’s a signal to investigate before you feed this column into an ML model.

A caveat about thresholds: kurtosis depends on row count. The same single outlier scored about 5 in a 5-row table, about 7 in a 7-row table, and about 10 in a 10-row table. A fixed rule like “investigate if kurtosis is above 10” can miss problems in small tables and raise too many alarms in large ones. Read the number next to COUNT(), and confirm what you see with MIN, MAX, or APPROX_PERCENTILE before deciding how to handle outliers.

Both functions ignore NULLs, and on very small inputs they can return NULL.

4. HLL_ACCUMULATE — Counting Distinct Values at Billion-Row Scale

HLL_ACCUMULATE() · HLL_COMBINE() · HLL_ESTIMATE()

This is the one I wish more people knew about. COUNT(DISTINCT ...) in Snowflake is exact but expensive — it requires keeping track of every unique value seen, which blows up memory on very large cardinalities. HyperLogLog (HLL_ACCUMULATE) gives you an approximate distinct count using a fixed-size sketch, regardless of how many unique values there are.

The practical accuracy is within 1–2% of the true count in almost all cases. On a table with 500 million rows and 40 million distinct user IDs, the HLL sketch runs in seconds; COUNT(DISTINCT user_id) takes minutes.

1SELECT2  DATE_TRUNC('day', event_time)          AS event_day,3  HLL_ESTIMATE(HLL_ACCUMULATE(user_id))  AS approx_distinct_users4FROM user_events5GROUP BY 16ORDER BY 1 DESC;7 8-- The real power: combine sketches across time windows without re-scanning9-- "How many distinct users across the last 7 days combined?"10SELECT11  HLL_ESTIMATE(12    HLL_COMBINE(13      ARRAY_AGG(daily_sketch)14    )15  ) AS distinct_users_last_7d16FROM (17  SELECT HLL_ACCUMULATE(user_id) AS daily_sketch18  FROM user_events19  WHERE event_time >= CURRENT_DATE - 720  GROUP BY DATE_TRUNC('day', event_time)21) daily_sketches;

The HLL_COMBINE step is what makes this especially powerful for incremental pipelines: you can pre-compute daily HLL sketches, store them, and merge them at query time without ever re-scanning the raw event table. This is the same technique web analytics platforms use to report 28-day unique visitor counts instantly.

MINHASH and APPROXIMATE_SIMILARITY — Jaccard Similarity Without Cross Joins

MINHASH() · APPROXIMATE_SIMILARITY()

The last one is genuinely rare to see in the wild, and it solves a problem that traditionally required either a self-join or a Python job to handle. MINHASH computes a probabilistic sketch of a set, and APPROXIMATE_SIMILARITY takes two of those sketches and returns the estimated Jaccard similarity — the fraction of items the two sets have in common.

Where this matters: customer segment overlap analysis, duplicate detection across product catalogs, comparing which users two marketing campaigns reached, or detecting near-duplicate content. The traditional approach is a cross join on set membership, which is O(n²) and completely impractical above a few thousand rows.

1-- MINHASH + APPROXIMATE_SIMILARITY: Jaccard similarity between two customer segments2-- No cross join required3WITH segment_sketches AS (4  SELECT5    campaign_id,6    MINHASH(100, customer_id) AS customer_sketch  -- 100 hash functions = good accuracy7  FROM campaign_members8  GROUP BY campaign_id9)10SELECT11  a.campaign_id  AS campaign_a,12  b.campaign_id  AS campaign_b,13  APPROXIMATE_SIMILARITY(a.customer_sketch, b.customer_sketch) AS jaccard_similarity14FROM segment_sketches a15JOIN segment_sketches b ON a.campaign_id < b.campaign_id16ORDER BY jaccard_similarity DESC;17 18-- Output example:19-- CAMPAIGN_A | CAMPAIGN_B | JACCARD_SIMILARITY20-- -----------|------------|-------------------21-- spring_24  | summer_24  | 0.7122-- summer_24  | fall_24    | 0.4323-- spring_24  | fall_24    | 0.29

A similarity of 0.71 between spring and summer campaigns means 71% of the customers they reached were the same people — which is useful to know before running both again. I’ve used this exact pattern for a media client to detect ad campaign overlap before recommending budget reallocation. The query ran in under 30 seconds on a 50-million-row membership table. The equivalent Python job had been taking 40 minutes.

Which Function for Which Problem

Quick reference for the next time one of these problems comes up:

• APPROX_TOP_K — Top-N most frequent values on huge tables where GROUP BY is too slow
• MODE() — Most common value per group, in one line instead of a subquery
• ARRAY_AGG and ARRAY_UNIQUE_AGG — Aggregate row values into arrays for ML features or 360 views
• SKEW / KURTOSIS — Distribution shape diagnostics without leaving SQL
• HLL_ACCUMULATE — Approximate distinct counts at scale; combinable across time windows
• MINHASH / APPROXIMATE_SIMILARITY — Set similarity without cross joins

All six are available on every Snowflake edition. None require any special configuration. The only cost is the warehouse time to run the query — and in most cases that cost is substantially lower than the standard alternative, which is the point.