Snowflake launches new Array Functions

How Snowflake eases working with Data stored in Arrays

Photo by Fabian Mardi on Unsplash

When working with Snowflake SQL you will often come across data stored in Arrays. Here Snowflake now launches new functions which could help Data Engineers and Scientist.

Let’s look into the new functions and how they work — the first one is ARRAY_MAX :

ARRAY_MAX

This function returns the element with the highest value that is not a SQL NULL for a given input array[1]. Using it is pretty straight forward — here is a small example you can use as a blue print.

SELECT ARRAY_MAX([30, 0, NULL, 20, NULL]);

which produces the following output:

+------------------------------------+
| ARRAY_MAX([20, 0, NULL, 10, NULL]) |
|------------------------------------|
| 30                                 |
+------------------------------------+

ARRAY_MIN

Another new function when working with semi structured data is ARRAY_MIN with an input ARRAY it returns the element with the lowest value that is not a SQL NULL[1]. So it does exactly the opposite of ARRAY_MAX — so it makes sense Snowflake published them together;) So the following Query

SELECT ARRAY_MIN([20, 0, NULL, 10, NULL]);

will produce:

+------------------------------------+
| ARRAY_MIN([20, 0, NULL, 10, NULL]) |
|------------------------------------|
| 0                                  |
+------------------------------------+

Building Modern ELT Pipelines in Snowflake

ARRAY_SORT

While ARRAY_MAX and ARRA_MIN are maybe more intersting when doing Data Analysis the third new function ARRAY_SORT could also be interesting for Data Engineers. It returns an ARRAY that contains the elements of the input ARRAY sorted in ascending or descending order[1]. The function works with the following parameters:

ARRAY_SORT( <array> [ , <sort_ascending> [ …