As data scientists, we use GROUP BY constantly.
We aggregate. We summarize. We compute KPIs. We build feature tables.
And in many analytical queries, especially wide tables, we end up repeating column names twice:
- Once in the
SELECTclause - Again in the
GROUP BYclause
This repetition is tedious — and sometimes error-prone.
In Snowflake, there is a convenient feature that simplifies this pattern:
GROUP BY ALL
The Traditional Pattern
Consider a typical aggregation:
SELECT
region,
product_category,
sales_channel,
SUM(revenue) AS total_revenue,
COUNT(*) AS order_count
FROM sales
GROUP BY
region,
product_category,
sales_channel;Notice the duplication: every non-aggregated column must be explicitly listed twice.
When the number of grouping columns grows — 5, 10, sometimes more — the query becomes longer and easier to break during refactoring.
If you remove a column from SELECT but forget to remove it from GROUP BY, you introduce inconsistency.
Using GROUP BY ALL
With GROUP BY ALL, Snowflake automatically groups by all non-aggregated columns in the SELECT list.
The same query becomes:
SELECT
region,
product_category,
sales_channel,
SUM(revenue) AS total_revenue,
COUNT(*) AS order_count
FROM sales
GROUP BY ALL;That’s it. No duplication. The grouping columns are inferred from the SELECT clause.
Why This Matters in Practice
This is not just syntactic sugar. It improves:
- Readability — the grouping logic is visually cleaner.
- Maintainability — when modifying selected dimensions, you only change one place.
- Error Reduction — you eliminate mismatches between
SELECTandGROUP BY.
For data scientists who iterate quickly — especially during exploratory analysis — this reduces friction.
When Should You Use It?
GROUP BY ALL is particularly useful when:
- Building summary tables
- Creating feature engineering pipelines
- Writing intermediate analytical transformations
- Working with many dimension columns
However, if you are writing standardized queries meant to run across multiple database systems, portability matters — not all engines support this syntax.
A Broader Perspective
Small syntactic improvements can compound.
In modern data platforms like Snowflake, productivity features are not trivial — they reflect a design philosophy: reduce friction in analytical workflows.
As data scientists, we often spend more time transforming and summarizing data than training models. Anything that makes aggregation safer and cleaner is valuable.
Final Thoughts
Machine learning often receives the spotlight, but high-quality analytics begins with clean aggregation.
GROUP BY ALL is a small feature — but for daily analytical work, small improvements matter.
And sometimes, better engineering starts with better syntax.
