September 2022¶

The following new features, behavior changes, and updates (enhancements, fixes, etc.) have been introduced this month. If you have any questions about these additions, please contact Snowflake Support.

Important

Each release may include updates that require the web interface to be refreshed.

As a general practice, to ensure these updates do not impact your usage, we recommend refreshing the web interface after each Snowflake release has been deployed.

New Features¶

Support for Redirecting Client Connections — General Availability¶

With this release, we are pleased to announce the general availability (GA) of support for Redirecting client connections, which enables seamless redirection of client connections across Snowflake accounts in different regions and clouds for business continuity. Client Redirect, together with Account Replication and Failover, enables recovery of an account and client connections.

Client Redirect is implemented through a Snowflake connection object, which represents a connection URL that can be failed over across accounts. When Snowflake clients make a connection to Snowflake using the connection URL, Snowflake routes the request to the primary account.

When required, a single SQL statement can update the connection URL to point to a different account as the primary. All Snowflake client applications using the connection URL are seamlessly routed to this new primary account.

SQL Updates¶

ALTER TABLE & ALTER VIEW Commands: Add the FORCE Keyword to Replace a Masking Policy on a Column in a Single Statement¶

Syntax	Keyword	Description
ALTER TABLE <name> { ALTER \| MODIFY } [COLUMN ] <col_name> SET MASKING POLICY<policy_name> [ USING ( <col_name> , cond_col_1 , … ) ] [ FORCE ] . ALTER VIEW <name> { ALTER \| MODIFY } [COLUMN ] <col_name> SET MASKING POLICY<policy_name> [ USING ( <col_name> , cond_col_1 , … ) ] [ FORCE ]	`FORCE`	Replaces a masking policy that is currently set on a column with a different masking policy in a single statement. If a masking policy is not currently set on the column, specifying this keyword has no effect.

Syntax

Keyword

Description

ALTER TABLE <name> { ALTER | MODIFY } [COLUMN ] <col_name> SET MASKING POLICY<policy_name> [ USING ( <col_name> , cond_col_1 , … ) ] [ FORCE ]

ALTER VIEW <name> { ALTER | MODIFY } [COLUMN ] <col_name> SET MASKING POLICY<policy_name> [ USING ( <col_name> , cond_col_1 , … ) ] [ FORCE ]

FORCE

Replaces a masking policy that is currently set on a column with a different masking policy in a single statement.

If a masking policy is not currently set on the column, specifying this keyword has no effect.

New SQL Functions¶

The following function(s) are introduced in this release:

Function Category	New Function	Description
Table functions	SYSTEM$STREAM_BACKLOG	Returns the set of table versions between the current offset for a specified stream and the current timestamp.
String & Binary Functions	SOUNDEX_P123	Returns a string that contains a phonetic representation of the input string, and retains the Soundex code number for the second letter when the first and second letters use the same number.

Search Optimization Service: Support for Column Configuration, Substring and Regular Expression Searches, VARIANT, and GEOGRAPHY — Preview¶

We are pleased to announce a preview of support for enabling search optimization for specific columns in a table.

In addition, we are introducing a preview of support for queries with the following types of predicates in the search optimization service:

Predicates that use string patterns (e.g. LIKE, ILIKE, etc.) and POSIX regular expressions (e.g. REGEXP).
Predicates that use fields in VARIANT, ARRAY, and OBJECT columns.
Predicates that use selected geospatial functions (queries of GEOGRAPHY columns).

You can enable support for these methods for specific columns and VARIANT fields in a table. For example, for a given column, you can enable support for optimizing equality and string pattern searches.

This feature is being rolled out across different regions. For the list of regions in which this feature is enabled, see Feature Rollout: Search Optimization Service Support for Column Configuration, String Patterns, VARIANT, and GEOGRAPHY.

Preview features are intended for evaluation and testing purposes and are not recommended for use in production.

For more information, see Search Optimization Service.

Search Optimization Service: Support for Joins¶

Note

The rollout of this feature is temporarily on hold and is not yet available in any region. See Feature Rollout: Support for Joins in the Search Optimization Service.

Data Loading Updates¶

Snowpipe: Support for Pattern Matching — General Availability¶

The COPY INTO <table> command includes a PATTERN clause that filters a set of staged files during the load operation using a regular expression. With this release, we are pleased to announce the general availability of pattern matching support in Snowpipe for both automated and non-automated loads.

To apply pattern matching in your Snowpipe data loads, specify the PATTERN clause in the COPY INTO <table> statement when you create a pipe using CREATE PIPE.

Snowflake recommends that you enable cloud event filtering to reduce costs, event noise, and latency for Snowpipe. Only use the PATTERN option when your cloud provider’s event filtering feature is not sufficient. For more information about configuring event filtering for each cloud provider, see the following pages:

Amazon S3:

Configuring event notifications using object key name filtering
Microsoft Azure Event Grid:

Understand event filtering for Event Grid subscriptions
Google Cloud Pub/Sub:

Filtering messages

Data Pipeline Updates¶

Tasks: DAG Support — General Availability¶

With this release, we are pleased to announce the general availability of Directed Acyclic Graph (DAG) support for tasks. A DAG is a series of tasks composed of a single root task and additional tasks, organized by their dependencies. Previously, users were limited to task trees, in which each task had at most a single predecessor (parent) task. In a DAG, each non-root task can have dependencies on multiple predecessor tasks, as well as multiple subsequent (child) tasks that depend on it.

Since the preview release, we have added support for specifying multiple predecessor tasks when creating a new task (using CREATE TASK … AFTER) or later (using ALTER TASK … ADD AFTER).

Serverless Tasks — General Availability¶

With this release, we are pleased to introduce the general availability of the serverless compute model for tasks. Serverless tasks rely on Snowflake-managed compute resources for task runs instead of user-managed compute resources (i.e. virtual warehouses). Snowflake resizes and scales these compute resources automatically as required for each workload, and suspends the service for a specific task to save costs.

Tasks: Automatically Suspend Tasks After Failed Runs¶

With this release, task owners can automatically suspend their tasks after a specified number of consecutive failed runs. This option can reduce costs by suspending tasks that consume Snowflake credits but fail to run to completion. Failed task runs include runs in which the SQL code in the task body either produces a user error or times out. Task runs that are skipped, canceled, or that fail due to a system error are considered indeterminate and are not included in the count of failed task runs.

The improvement is implemented as a new task parameter, SUSPEND_TASK_AFTER_NUM_FAILURES, which can be set when creating a task (using CREATE TASK) or later (using ALTER TASK). The parameter can also be set at the account, database, and schema levels.

The parameter accepts any integer value 0 and greater. The default value is 0 (no automatic suspension). Set the parameter on a standalone task or the root task in a Directed Acyclic Graph (DAG) of tasks.

The SUSPEND_TASK_AFTER_NUM_FAILURES parameter can be set on a standalone task or the root task in a DAG. When the parameter is set to a value greater than 0, the following behavior applies to runs of the standalone task or DAG:

Standalone tasks are automatically suspended after the specified number of consecutive task runs either fail or time out.
The root task is automatically suspended after the run of any single task in a DAG fails or times out the specified number of times in consecutive runs.

The setting applies to tasks that rely on either Snowflake-managed compute resources (i.e. serverless compute model) or user-managed compute resources (i.e. a virtual warehouse).

Tasks: Expanded Scope for Task Parameters¶

With this release, you can set the following parameters at the account, database, schema, and task levels:

Previously, these parameters could only be set on individual tasks.

For more information about parameter hierarchies, see Parameter hierarchy and types. For more information about parameters that can also be set at the parent account, database, and schema levels, see Object parameters.

Data Governance Updates¶

Replace a Masking Policy on a Column in a Single Statement¶

With this release, Snowflake adds support to specify the FORCE keyword when replacing a masking policy that is currently set on a column in a single statement with either an ALTER TABLE or ALTER VIEW command. Prior to the FORCE keyword being available, replacing a masking policy on a column required two separate statements:

Unset the existing policy.
Set the new policy.

Using the FORCE keyword removes the time interval between the UNSET and SET operations to ensure that column data remains protected while replacing a masking policy on a column.

For details see:

Replace a masking policy on a column.
ALTER TABLE & ALTER VIEW Commands: Add the FORCE Keyword to Replace a Masking Policy on a Column in a Single Statement (in this topic).

Data Classification on GCP — General Availability¶

With this release, Snowflake is pleased to announce the general availability of Introduction to Classification for accounts that are Enterprise Edition (or higher) on GCP. The feature was previously made generally available on Amazon Web Services (AWS) and Microsoft Azure in June 2022.

Classification enables categorizing potentially personal and/or sensitive data stored in Snowflake tables and views, which can then be used to enable a variety of data governance, sharing, and privacy use cases.

Web Interface Updates¶

Secondary Roles Support in Snowsight Worksheets¶

With this release, Snowsight worksheets support secondary roles, meaning the following worksheet features display all objects on which the required minimum privileges are available via either the worksheet role or the current user’s secondary roles:

Context selector
Object browser
Autocomplete suggestions in the query editor