2021_01 Bundle

The following deprecated features and behavior changes were introduced this month. If you have any questions about these changes, please contact Snowflake Support.

For more details about the new features, enhancements, and fixes introduced this month, see February 2021.

Important

Unless otherwise noted, these changes were enabled through the 2021_01 bundle in Release 5.5.

Security Changes

DUO Sign-in & MFA Enrollment

In this release, Snowflake updates the web interface and uses the DUO Web SDK to facilitate logging into Snowflake with single sign-on (SSO) and enrollment in multi-factor authentication (MFA).

Previously

Users can authenticate using DUO Push, entering a passcode from the DUO mobile application, or request SMS passcodes to their mobile device.

Currently

After enrollment, users can use the DUO mobile to authenticate using DUO Push, following instructions from a phone call to their mobile device, or entering a passcode from the DUO Mobile application.

Access to Persisted Large Query Results Reduced to 6 Hours

Note

This behavior change was implemented to improve security and was not pre-announced because it was determined to have minimal to no visible user impact.

Snowflake persists (i.e. caches) the results of a query for a period of time. When this time period expires, the result is purged from the system. Snowflake uses persisted query results to avoid regenerating results when a query and the underlying table data have not changed (i.e. “retrieval optimization”).

With this release, the behavior of persisted query results has changed as follows:

Previously

The security token used by Snowflake clients to access persisted query results expired after 24 hours.

Currently

The security token used to access large persisted query results (i.e. more than 100 KB in size) expires after 6 hours. A new token can be retrieved to access results while they are still in cache.

Note that smaller persisted query results do not use an access token.

For persisted query results of all sizes, the cache expires after 24 hours.

Note that this change does not affect the token provided to the Snowflake Connector for Spark (“Spark connector”), which continues to expire after 24 hours regardless of the size of the persisted query results. The Spark connector leverages the longer cache expiration time to avoid timeouts in some use cases.

SQL Changes: General

Transactions: Change to Automatic Aborting of Idle Blocking Transactions

If an automated process such as a script or a third-party tool starts a transaction in Snowflake and then fails, the transaction is left open and idle. This scenario can also occur if a session disconnects abruptly, leaving any transactions that are running in the session unable to commit or roll back. After a certain time, Snowflake aborts idle transactions automatically and rolls them back.

As announced previously, with this release, the guidelines that Snowflake follows to automatically abort idle transactions have changed as follows:

Previously

All idle transactions are aborted and rolled back after 4 hours by default.

Currently

Any transaction that blocks another transaction from acquiring a lock on the same table and is idle for 5 minutes is automatically aborted and rolled back.

Any idle transactions that do not block other transactions from modifying the same table and are older than 4 hours are aborted and rolled back.

VARIANT Data Type: Trailing Zeroes Removed When Casting VARIANT with Fixed-Point Value to VARCHAR

A VARIANT value can contain a fixed-point (NUMBER) value. When Snowflake casts such a VARIANT to VARCHAR, the result can contain trailing zeros, which can interfere with string comparison. For example, the numeric values 5 and 5.00 are equivalent in a numerical comparison, but the string values 5 and 5.00 are not equivalent in a string comparison.

As announced previously, with this release, the behavior of casting has changed as follows:

Previously

Casting a fixed-point value inside a VARIANT to a VARCHAR could result in varying numbers of trailing zeroes.

Currently

Casting a fixed-point value inside a VARIANT to a VARCHAR results in no trailing zeros.

Stored Procedures and UDFs: Upgrade to the JavaScript Engine — Non-bundle Change

Note

This change was introduced in the 5.3.1 scheduled patch released on Feb 4, 2021.

As announced previously, the JavaScript engine for JavaScript stored procedures and user-defined functions (UDFs) has been upgraded to a newer version. The newer version of the engine improves performance and offers new JavaScript features from ECMAScript 2020.

As a result of this upgrade, the SfDate.toString method returns the full name of the time zone, rather than the abbreviation. For example, the method returns Coordinated Universal Time, rather than UTC.

Stored Procedures and Scoped Transactions — Non-bundle Change

Attention

This change was implemented outside Snowflake’s standard behavior change process to mitigate the impact of the previous behavior, based on user feedback. If the change causes any issues in your environment, please contact Snowflake Support. Ref: 271480

With this release, Snowflake now issues an error message when implicitly rolling back an open transaction at the end of a stored procedure:

Previously

If a stored procedure contained an explicit or implicit BEGIN TRANSACTION statement, but did not have a corresponding explicit or implicit COMMIT or ROLLBACK, then Snowflake would silently execute an implicit rollback at the end of the stored procedure.

Currently

The implicit rollback is no longer silent; it is accompanied by an error message.

SQL Changes: Account Usage

Note

As a general rule, changes to Account Usage objects are not included in the monthly bundle and are enabled after the release.

COPY_HISTORY View: Changes to Columns — Non-bundle Change

As announced previously, the following changes have been made in this release to the Account Usage COPY_HISTORY view:

Column Name

Data Type

Description of Change

Additional Notes

FIRST_COMMIT_TIME

TIMESTAMP_LTZ

New column

Column displays the date and time when the first chunk of the file is committed. Snowpipe may load a file in multiple chunks that are separately committed.

FILE_NAME

TEXT

Column value changed to match documentation

Previous value: File name and full path to the source file. . Current value: File name and relative path to the source file.

STAGE_LOCATION

TEXT

Column value changed to match documentation

Previous value: null . Current value: Name of the stage where the source file is located.

SQL Changes: Information Schema

POLICY_REFERENCES Table Function: New Column

As announced previously, with this release, Snowflake added a new column to the Information Schema POLICY_REFERENCES table function:

Column Name

Data Type

Description

REF_ARG_COLUMN_NAMES

ARRAY

Returns NULL for rows in the query result in which a Column-level Security masking policy is set.

Ecosystem Changes

Note

As a general rule, changes to Snowflake clients are not included in the monthly bundle and are enabled only after the specified version of the client has been installed.

Snowflake JDBC Driver 3.12.17 (and Later): Session Timezone Used for Returned Dates and Times — Non-bundle Change

As announced previously, version 3.12.17 (and later) of the JDBC Driver now uses the timezone of the session (rather than the JVM timezone) for the getDate(), getTime(), and getTimestamp() methods of the ResultSet class:

Previously

getDate(), getTime(), and getTimestamp() used the timezone of the JVM for the returned Date, Time, and Timestamp objects.

Currently

getDate(), getTime(), and getTimestamp() use the timezone of the session for the returned Date, Time, and Timestamp objects.

This makes the results from the JDBC Driver consistent with the results from the web interface and other clients.

Snowflake JDBC Driver 3.13.0: JNA Classes No Longer Packaged — Non-bundle Change

As announced previously, version 3.13.0 of the JDBC Driver no longer packages the Java Native Access (JNA) classes with the JDBC Driver JAR file:

Previously

The JNA classes were included in the JDBC Driver JAR file.

Currently

The JNA classes are no longer included in the JDBC Driver JAR file.

Connection caching for browser-based SSO and multi-factor authentication (MFA) token caching require the JNA classes. If you are relying on this feature in your application, you must set up your application to depend on the following libraries:

Note that for systems that use the AArch64 architecture (e.g. the Apple M1 chip), you must use v5.7.0 (or later) of the JNA libraries. JNA versions prior to v5.7.0 are not compatible with Windows and macOS systems that run on AArch64.

Data Loading / Unloading Changes

COPY INTO <table> Command: LIMIT and TOP <n> Disallowed

The LIMIT or TOP <n> clause constrains the maximum number of rows returned by a query. The use of either clause in a COPY transformation (i.e. a query inside a COPY INTO <table> statement) is undocumented and has never been officially supported.

As announced previously, with this release, the behavior has changed as follows:

Previously

COPY INTO <table> statements that included either clause did not return an error. The statements constrained the maximum number of rows loaded into a table. However, the rows loaded were nondeterministic due to the unsupported ORDER BY clause and the parallel scanning of data.

Currently

COPY INTO <table> statements that include either clause return the following error:

LIMIT and TOP are not supported for COPY with transform during loading.