Catch Up on the Latest and Greatest from Snowflake: Semi-Structured Data Functions

As a Snowflake Elite Services Partner, we keep an eye on new releases and are happy to share the benefits they bring to you. And, in case you missed some of Snowflake’s releases this past April and May, we’re reviewing the substantial new SQL functions and the new features for the Snowsight User Interface.

What are these new Semi-Structured Data Functions?

One of the most significant new features is the addition of three new semi-structured data functions. These functions allow users to work with semi-structured data, such as JSON and XML, in a more structured way.

Semi-structured data is becoming increasingly common as more data are generated in a non-relational format making it challenging to use traditional SQL tools as it doesn’t fit neatly into a relational schema. Semi-structured data functions make working with these data types easier by providing a new way to query and manipulate them.

Specifically, these three functions enable users to generate an array of integers in a specific range quickly. They also allow users to pass a source array into a function to return an array with elements of a specified value removed or elements at a specified position removed.

As an example, say you know you’ll need to remove the last element of an array; use:

SELECT array_remove_at( [5, 3, 4, 1], -1)

To return:

[

  5,

  3,

  4

]

Where the array [5, 3, 4, 1] passed into the function is a contrived example, you can pass in any array you need to update.

Benefits of Semi-Structured Data Functions

There are several benefits to using these new functions in Snowflake. Benefits include:

• Reduced complexity: Semi-structured data functions can reduce the complexity of working with semi-structured data because the functions provide more straightforward ways to query and manipulate your data in a more structured way.
• Improved performance: Semi-structured data functions can improve the performance of subsequent queries on semi-structured data because the functions can pre-process the data, making it easier to query.

Conclusion

Snowflake’s April and May 2023 releases included many new features, making it a more robust and versatile data warehousing and analytics platform. We’re excited the new data functions we highlighted allow users to work with semi-structured data more flexibly, efficiently, and less complexly.

If you’d like to learn more about what Snowflake offers, please send an inquiry to hello@das42.com.