What Is Snowflake ARRAY_AGG?

The Snowflake ARRAY_AGG function is an aggregation function that consolidates input values into an array. It is a versatile function used primarily for transforming rows of data into a single array format, which can be extremely useful for various data analysis and manipulation tasks. The function can be employed as both an aggregate function and a window function, offering a flexible approach to data aggregation.

Aggregate function


ARRAY_AGG([DISTINCT] <expr1>) [WITHIN GROUP (<orderby_clause>)]


Window function


ARRAY_AGG([DISTINCT] <expr1>) [WITHIN GROUP (<orderby_clause>)] OVER ([PARTITION BY <expr2>])


The primary syntax for ARRAY_AGG includes options for using it as an aggregate function and as a window function. The expr1 is a required expression, typically a column name, whose values will be aggregated into an array. The DISTINCT keyword is optional and removes duplicate values from the array.

The WITHIN GROUP orderby_clause is also optional and orders the values within the array based on specified expressions. The OVER() clause specifies the function as a window function.

How does the ARRAY_AGG function work?

The ARRAY_AGG function collects values from a specified column and groups them into an array. This array can then be used for further analytical purposes or data transformation. The function can handle both distinct and non-distinct values, and it allows for ordering within the array using the WITHIN GROUP clause.

The function returns a value of type ARRAY. Note that the maximum data size for a single call is 16MB. Here are some usage notes:

• Order of Elements: If WITHIN GROUP (ORDER BY) is not specified, the order of elements within the array is unpredictable.
• Clauses Consistency: The DISTINCT and WITHIN GROUP clauses must refer to the same column.
• NULL Values: NULL values are omitted from the output.
• Window Function Limitation: As a window function, ARRAY_AGG does not support the ORDER BY sub-clause in the OVER() clause or window frames.

What are the practical applications of ARRAY_AGG?

The ARRAY_AGG function is particularly useful for transforming rows of data into a single array, making it easier to handle and analyze grouped data. Below are a few practical applications:

Non-pivoted Output Example

SELECT O_ORDERKEY AS order_keys
FROM orders
WHERE O_TOTALPRICE > 450000
ORDER BY O_ORDERKEY;


This query selects order keys from the orders table where the total price is greater than 450,000 and orders them by order key.

Pivoted Output Using ARRAY_AGG

SELECT ARRAY_AGG(O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY ASC)
FROM orders
WHERE O_TOTALPRICE > 450000;


This query aggregates the order keys into an array, ordering them by order key in ascending order.

Using DISTINCT

SELECT ARRAY_AGG(DISTINCT O_ORDERSTATUS) WITHIN GROUP (ORDER BY O_ORDERSTATUS ASC)
FROM orders
WHERE O_TOTALPRICE > 450000;


This query aggregates distinct order statuses into an array, ordering them by order status in ascending order.

Using Separate ORDER BY Clauses

SELECT O_ORDERSTATUS, ARRAY_AGG(O_CLERK) WITHIN GROUP (ORDER BY O_TOTALPRICE DESC)
FROM orders
WHERE O_TOTALPRICE > 450000
GROUP BY O_ORDERSTATUS
ORDER BY O_ORDERSTATUS DESC;


This query groups the data by order status and aggregates the clerks into an array, ordering them by total price in descending order.

How does ARRAY_AGG compare to other aggregation functions?

While ARRAY_AGG serves a unique purpose of transforming data into arrays, it is helpful to compare it with other common aggregation functions to understand its distinct advantages and limitations.

Function Purpose Key Features ARRAY_AGG Aggregates data into an array Supports DISTINCT, WITHIN GROUP, and windowing COUNT Counts the number of rows Can count all rows or distinct values SUM Sums values in a column Useful for numeric data AVG Calculates the average of values Suitable for numerical data MIN Finds the minimum value in a column Useful for both numerical and non-numerical data MAX Finds the maximum value in a column Suitable for both numerical and non-numerical data

What are the best practices for using ARRAY_AGG?

When using the ARRAY_AGG function, consider the following best practices to maximize its effectiveness and efficiency:

• Ensure Proper Use of DISTINCT: When using the DISTINCT keyword, ensure that it is necessary to remove duplicate values to avoid unintentional data loss or performance overhead.
• Leverage WITHIN GROUP for Order: Use the WITHIN GROUP (ORDER BY ) clause to maintain a predictable order of elements within the array, which can be crucial for subsequent data analysis tasks.
• Handle Large Data Sizes: Be mindful of the 16MB data size limit for a single call to ARRAY_AGG. For large datasets, consider breaking the data into smaller subsets or using alternative aggregation methods.
• Optimize Performance with Partitioning: When using ARRAY_AGG as a window function, leverage the PARTITION BY clause to divide the data into partitions, which can significantly improve performance by reducing the amount of data processed in each call.

How to Use the ARRAY_AGG Function in Snowflake

1. Basic Usage

To start using the ARRAY_AGG function, you need to understand its basic syntax and how to apply it to your data.

SELECT ARRAY_AGG(column_name)
FROM table_name;


This query aggregates the values in the specified column into an array.

2. Using DISTINCT

To remove duplicate values from the array, use the DISTINCT keyword.

SELECT ARRAY_AGG(DISTINCT column_name)
FROM table_name;


This query aggregates distinct values in the specified column into an array.

3. Ordering Within the Array

To maintain a specific order of elements within the array, use the WITHIN GROUP (ORDER BY ) clause.

SELECT ARRAY_AGG(column_name) WITHIN GROUP (ORDER BY column_name ASC)
FROM table_name;


This query aggregates the values in the specified column into an array, ordering them in ascending order.

4. Using as a Window Function

To use ARRAY_AGG as a window function, include the OVER() clause.

SELECT ARRAY_AGG(column_name) OVER (PARTITION BY another_column)
FROM table_name;


This query aggregates the values in the specified column into an array, partitioning the data by another column.

Common Challenges and Solutions

While using the ARRAY_AGG function, you might encounter some common challenges. Here are solutions to address them:

• Data Size Limitations: The 16MB limit for a single call can be restrictive for very large datasets. To work around this, consider breaking the data into smaller subsets or using alternative aggregation methods.
• Performance Overhead: Aggregating large volumes of data into an array can introduce performance overhead. Optimize queries and consider alternative aggregation methods for performance-critical applications.
• Handling of NULL Values: NULL values are automatically omitted from the output. If NULL values need to be explicitly included or handled, consider using additional logic in your query.

Recap of Snowflake ARRAY_AGG Function

The ARRAY_AGG function in Snowflake is a powerful tool for aggregating data into arrays, offering a versatile approach for data transformation and analysis. Here are the key takeaways:

• Versatility: The ARRAY_AGG function can be used as both an aggregate function and a window function, providing flexibility in data aggregation.
• Ordering and Distinct Values: The function supports the DISTINCT keyword to remove duplicates and the WITHIN GROUP clause to order elements within the array.
• Performance Considerations: Be mindful of the 16MB data size limit and optimize queries to handle large datasets efficiently.

By understanding its syntax, practical applications, and best practices, you can leverage the ARRAY_AGG function to efficiently handle and analyze grouped data in Snowflake.