9.30 Release Notes: Preview¶

MERGE ALL BY NAME¶

When the target table and source must have the same number of columns and the same names for all of the columns, you can simplify MERGE operations by using MERGE ALL BY NAME.

MERGE statements can update each column in the target table with the values of the column with the same name from the source table. MERGE statements can also insert rows from the source table into the target table based on column names when there is no match. These MERGE ALL BY NAME operations are supported even when the column order is different in the target and source tables.

Aliases for PIVOT and UNPIVOT columns¶

In PIVOT queries, you can use the AS clause to specify aliases for the pivot column names.

In UNPIVOT queries, you can use the AS clause to specify aliases for column names that appear in the result of the UNPIVOT operation.

New SQL parameter: ENABLE_GET_DDL_USE_DATA_TYPE_ALIAS¶

The new ENABLE_GET_DDL_USE_DATA_TYPE_ALIAS parameter specifies whether the output returned by the GET_DDL function contains data type synonyms specified in the original DDL statement. This parameter is set to FALSE by default.

Reference table columns in lambda expressions when calling higher-order functions¶

You can now reference table columns in lambda expressions when calling higher-order functions such as FILTER, REDUCE, and TRANSFORM.

For example, you can specify the following lambda expression in a higher-order function that subtracts the value of table1.col2 from elements:

a -> a - table1.col2

SEARCH function supports PHRASE and EXACT search modes¶

The SEARCH function now supports two new search modes in addition to the existing OR and AND modes:

• PHRASE: The search semantics find a match if the tokens extracted from at least one of the columns or fields being searched match all of the tokens extracted from the search string, including the order and adjacency of the tokens.

• EXACT: The search semantics are the same as ‘PHRASE’ search semantics, except that the delimiter strings between the tokens must match exactly.

These new search modes provide more flexibility than the existing disjunctive OR and conjunctive AND search semantics.

Snowflake Scripting CONTINUE handlers¶

A CONTINUE handler can catch and handle exceptions without ending the Snowflake Scripting statement block that raised the exception. With the default EXIT handler, when an error occurs in a block, the flow is interrupted and the error is returned to the caller. You can use a CONTINUE handler when the error condition isn’t severe enough to warrant interrupting the flow.

Snowflake Scripting user-defined functions (UDFs) (General availability)¶

Snowflake Scripting UDFs are now generally available and are no longer in preview.

You can create SQL UDFs that contain Snowflake Scripting procedural language. Snowflake Scripting UDFs can be called in a SQL statement, such as a SELECT or INSERT statement. They are more flexible than a Snowflake Scripting stored procedure, which can only be called in a SQL CALL command.

Support for Scala version 2.13 (Preview)¶

You can specify Scala version 2.13 as the Scala runtime for Snowflake to use when executing Scala user-defined functions (UDFs), stored procedures, and Snowpark code.

Support for Scala 2.13 is in preview.

Data quality: FRESHNESS data metric function improvement¶

You can now associate the FRESHNESS data metric function (DMF) with a table without specifying a column argument, which lets you determine the last time a DML command acted on the table. Previously, you needed to associate the FRESHNESS with a timestamp column to determine the last time the table was modified.