2021_04 Bundle¶

Client Platform Support: Minimum Supported Version of macOS is 10.14¶

With this release, the minimum version of macOS supported for all Snowflake clients is 10.14:

• Versions of Snowflake drivers and connectors released after this date are officially supported only on macOS 10.14 and later.

• Versions of Snowflake drivers and connectors released prior to this date can still be used with macOS 10.13.

These changes are enabled by default in this release; however, you can use the bundle to disable them (if needed) for the next 4 weeks.

Cloning Data-related Objects Using Time Travel Before Creation Time Fails with Error¶

With this release, the behavior when cloning a database or schema using Time Travel at or before the point in time when the object was created has changed as follows:

Previously

The clone operation succeeded but the new database or schema was empty.

Currently

The clone operation fails with the following error:

Time travel data not available for <object_type> <source_object_name>. The requested time is either
beyond the allowed time travel period or before the object creation time.

Copy

Where:

• <object_type> is the type of data-related object that is cloned (i.e. database or schema).

• <source_object_name> is the name of the source object that is cloned.

Views: Changes to Comments in View Definitions¶

With this release, Snowflake has changed the way that comments in new view and materialized view definitions are presented in INFORMATION_SCHEMA.VIEWS and in the output of the GET_DDL function and SHOW VIEWS command:

Previously

• The VIEW_DEFINITION column in INFORMATION_SCHEMA.VIEWS included the COMMENT parameter.

• When you changed the comment for a view (for example, by using the COMMENT command), the change was not reflected in the view definition returned by the GET_DDL function.The change was also not reflected in the VARCHAR column of the output of the SHOW VIEWS command, although the COMMENT column contained the updated comment.

• In the view definitions returned by GET_DDL and SHOW VIEWS, the following characters were returned in the COMMENT parameter as is (without any changes):

  • double quotes ( " ) and dollar quotes ( $$ ) around the comment

  • single and double quote characters within the comment

  • backslash characters

  • Unicode characters within the 7-bit ASCII character range (from \u0000 to \u0080 )

  • Unicode characters within the \u10000 - \u10FFFF range

• In the view definitions returned by GET_DDL and SHOW VIEWS, the number of spaces in the comment match the number of spaces used when the view was defined.

Currently

For views created starting from this release:

• The VIEW_DEFINITION column in INFORMATION_SCHEMA.VIEWS includes the COMMENT parameter.

• When you change the comment for a view (for example, by using the COMMENT command), the change is reflected in the view definitions returned by the GET_DDL function and the SHOW VIEWS command.

• In the view definitions returned by GET_DDL and SHOW VIEWS, the following characters are replaced in the COMMENT parameter:

  • Double quotes ( " ) and dollar quotes ( $$ ) around the comment are replaced by single quotes.

  • A single quote ( ' ) within the comment is escaped with a second single quote ( '' ).

    Note

    Within a single or double quoted comment, if a single quote is escaped with a backslash ( \' ), the backslash is replaced by a single quote for escaping ( '' ).

• Double quotes ( " ) within the comment are replaced by escaped double quotes ( \" ).

• Backslash characters ( \ ) are handled differently, depending on how the comment string is quoted:

  • For dollar quoted comments, a single backslash is replaced by two backslash characters ( \\ ).

  • For single or double quoted comments, a single backslash in front of a non-special character is removed.

• Unicode characters within the 7-bit ASCII character range (from \u0000 to \u0080 ) are replaced by ASCII characters.

• Unicode characters within the \u10000 - \u10FFFF range are replaced by two Unicode characters.

  These changes make the GET_DDL output for views consistent with the output for tables.

  See the examples below for details.

• In the view definitions returned by GET_DDL and SHOW VIEWS, single spaces are used in the COMMENT parameter, regardless of the number of spaces used when the view was defined.

Note

This change does not affect views that were created before this release.

For example, suppose that the double quotes and consecutive spaces were used in the CREATE VIEW command:

CREATE VIEW ... COMMENT    =  "a comment";

With this behavior change, GET_DDL returns the comment with single quotes and single spaces:

CREATE VIEW ... COMMENT = 'a comment';

The following example shows the differences in the GET_DDL and SHOW VIEWS output when certain characters appear in the comments:

Note that this behavior change does not affect these characters as they appear in the COMMENT column of INFORMATION_SCHEMA.VIEWS and the SHOW VIEWS output:

this is a comment with a slash

this is a comment with a z non-special character

this is a comment with 'escaped quote'

this is a comment with "unescaped quote"

this is a comment with ~ oct, ~ hex, ~ unicode, and ~ ASCII < 128

this is a comment with € oct, € hex, € unicode, and € ASCII >= 128

comment = 'this is a comment with 😀 large unicode'

double dollar \"comment\" with ''escaped quotes'' and \\ double slash

Materialized Views: New Restrictions for Using UNION ALL¶

This release introduces new restrictions on materialized views that use UNION ALL:

Previously

You were able to define a materialized view that uses UNION ALL to combine the following types of queries:

• Queries that use SELECT DISTINCT

• Queries that use GROUP BY in one branch

• Queries that select aggregates

Note that these types of statements result in unexpected behavior or cause an error.

See the examples of these types of statements below.

Currently

Snowflake prevents you from defining a materialized view that uses UNION ALL to combine these types of queries.

The following are examples of materialized views that use UNION ALL to combine queries. With this behavior change, you will no longer be able to define these types of materialized views.

Example 1: Materialized views that use UNION ALL to combine SELECT DISTINCT queries

-- Fails with error
create or replace materialized view mv as
select distinct
  a,
  b,
  0 as c
from t1
union all
select distinct
  a,
  b,
  1 as c
from t1;

-- Fails with error
create or replace materialized view mv as
select distinct
  a,
  b,
  0 as c
from t1
union all
select
  a,
  b,
  1 as c
from t1;

-- Fails with error
create or replace materialized view mv as
select
  a,
  b,
  0 as c
from t1
union all
select distinct
  a,
  b,
  1 as c
from t1;

Example 2: Materialized views that use UNION ALL to combine queries that select aggregates

-- Fails with error
create or replace materialized view mv as
select
  min(a) as aa
from t1
union all
select
  max(a) as aa
from t1;

-- Fails with error
create or replace materialized view mv as
select
  a
from t1
union all
select
  max(a) as aa
from t1;

-- Fails with error
create or replace materialized view mv as
select
  min(a) as aa
from t1
union all
select
  a
from t1;

Joins: Changes to Column References in Joins¶

If two tables have columns that share the same names, you can use a natural join or the USING keyword to join the table on those matching columns.

With this release, we have changed the way in which Snowflake interprets references to columns in these types of joins.

• Changes to References to Matching Columns in the Table on the Right

• Changes to the Order of Columns in the Table on the Right

• Changes to References to Matching Columns in Two or More Tables on the Left

select t2.j from t1 left outer join t2 using(j) group by j;

DESCRIBE TASK / SHOW TASKS Commands: New ALLOW_OVERLAPPING_EXECUTION Column in Output¶

With this release, the output of the DESCRIBE TASK and SHOW TASKS commands include a new ALLOW_OVERLAPPING_EXECUTION column. For root tasks in a task tree, the column displays a Boolean value that indicates whether overlapping execution of the tree is explicitly allowed or not. For child tasks in a tree, the column displays NULL.

The default prevention of overlapping task tree executions is also included in this behavior change bundle. See Tasks: Overlapping Tree of Task Runs Prevented by Default, below.

SHOW REGIONS Command: New DISPLAY_NAME Column in Output¶

With this release, the output of the SHOW REGIONS command will include a new DISPLAY_NAME column. The column will display the more human-readable cloud region name. For example: US West (Oregon)

SHOW Commands: REGION_GROUP Column Display Change¶

Organizations that span multiple region groups use REGION_GROUP for intra-region group global operations. The REGION_GROUP column displays the region group where the account is located. This column is also displayed to any organization that is in a non-PUBLIC region group, such as a VPS or a government region group, even if those organizations only have one region group visible.

The output of the following SHOW commands include the REGION_GROUP column:

• SHOW REGIONS

• SHOW REPLICATION ACCOUNTS

• SHOW ORGANIZATION ACCOUNTS

• SHOW REPLICATION DATABASES

With this release, the output of the SHOW commands listed above have changed as follows:

Previously

The REGION_GROUP column is displayed to organizations that:

• Span multiple region groups

• Are in a non-PUBLIC region group

Currently

The REGION_GROUP column will only be displayed to organizations that span multiple region groups.

REMAINING_BALANCE_DAILY View: New Column¶

With this release, the following column has been added to the ORGANIZATION_USAGE.REMAINING_BALANCE_DAILY view:

ON_ERROR Copy Option: Partial Load of File List Possible When ABORT_STATEMENT is Explicitly Set¶

The optional FILES parameter for COPY INTO <table> statements enables loading specific data from a stage by filename. If one or more files in the list cannot be found, the load operation stops without loading any data. This behavior is controlled by the ON_ERROR copy option. Previously, there was a difference in behavior between the default ABORT_STATEMENT setting and the behavior when the value was explicitly set in lowercase (i.e. ON_ERROR = abort_statement) or mixed case (e.g. ON_ERROR = Abort_Statement).

With this release, when the COPY INTO <table> statement with the ON_ERROR option explicitly set to ABORT_STATEMENT in any letter case, and at least one of the explicitly specified files in the FILEs parameter cannot be found, the behavior has changed as follows:

Previously

The load operation continued to load data from the specified files list until it encountered a file that could not be found.

Currently

The load operation correctly fails the load. The operation returns the following error message:

Remote file '<file_url>' was not found. There are several potential causes.
The file might not exist. The required credentials may be missing or invalid.
If you are running a copy command, please make sure files are not deleted
when they are being loaded or files are not being loaded into two different
tables concurrently with auto purge option.

Copy

ON_ERROR Copy Option: All Errors Counted Instead of Error Rows¶

The ON_ERROR copy option for the COPY INTO <table> command determines the action to perform when a load operation finds error rows in a file. An error row refers to a single row that can include one or more errors. The SKIP_FILE_<num> | SKIP_FILE_<num%> copy option values enable skipping a file when the number or percentage of error rows exceeds a specified limit.

With this release, the behavior of data loads with ON_ERROR = SKIP_FILE_<num> | SKIP_FILE_<num%> set has changed as follows:

Previously

In a small number of use cases, the load operation counted the number of errors found in a data file instead of the number of error rows. As a result, when one or more rows in a file included multiple errors, the load operation skipped the file prematurely based on the ON_ERROR setting. In addition, the number of errors was also sometimes incorrectly returned in the ERRORS_SEEN column output.

Currently

The load operation counts and returns the number of error rows when determining whether to skip a file based on the ON_ERROR setting.

Data Loading, Data Unloading: Named File Formats With Delimited Identifiers¶

The following SQL commands can reference named file formats that describe data in staged files:

• COPY INTO <table>

• COPY INTO <location>

• SELECT, when querying staged data files or as a subquery in COPY INTO <table> statements

The syntax for each of these commands includes required single quotation marks enclosing the file format object name. Nevertheless, users sometimes omit the single quotation marks and instead enclose the object name in double quotation marks. Identifiers enclosed in double quotation marks (i.e. delimited identifiers) are case-sensitive and can include special characters or blank spaces.

With this release, when a delimited file format identifier omits the single quotation marks, the behavior has changed as follows:

Previously

The listed SQL commands ignored the double quotation marks. The delimited file format identifier was case-insensitive. If the identifier included any special characters or blank spaces, the command returned a user error.

If a delimited file format identifier was enclosed in single quotation marks as documented, the identifier was handled correctly.

Currently

The listed SQL commands handle delimited file format identifiers correctly as documented.

The following table shows examples of the behavior change:

When referencing named file formats in SQL statements, we strongly recommend enclosing the entire value in single quotation marks and following the documented identifier requirements to avoid any unintended behavior.

COPY INTO <table> Command: Invalid File Format Options Produce No Error When Combined With Named File Format¶

The COPY INTO <table> command supports any combination of a named file format and one or more explicit file format options.

With this release, the behavior of the command has changed as follows:

Previously

If a COPY INTO <table> statement included file format options that were invalid for the type of data files for loading, no error was produced if the statement also referenced a named file format whose options match the type.

Currently

A COPY INTO <table> statement that specifies file format options that are invalid for the type of data files for loading produces an error regardless of whether the statement also references a valid named file format.

For example:

CREATE FILE FORMAT my_json_format TYPE=json;

COPY INTO mytable FROM @mystage
  FILES=('example.json')
  FILE_FORMAT=(FORMAT_NAME=my_json_format VALIDATE_UTF8=false);

Copy

In addition, the CREATE FILE FORMAT command produces an error if invalid file format options are specified for a given file type.

Parquet Data Unloading: Column Statistics in Unloaded Parquet Files¶

Column statistics written to unloaded Parquet files contain metadata such as the min value, max value, and number of NULLs for a column in a rowgroup. Third-party tools can use available metadata, for example, to minimize the amount of data read when a queried value falls outside of the min/max values for the column.

With this release, data unload operations have stopped writing column statistics for any fixed-point numeric columns (NUMBER, DECIMAL, INT, etc.). Note that this change does not affect COPY INTO <location> operations that explicitly cast numeric column values to a Snowflake logical data type that maps directly to a Parquet data type.

This change is required to address an issue in the Parquet library used to calculate statistics that records incorrect metadata for fixed-point numeric columns in certain circumstances. When this issue is fixed, data unload operations will resume writing column statistics for fixed-point numeric columns. This improvement will be announced in the release notes.

Parquet Data Unloading: Precision Retained When Unloading Floating Point Columns to Parquet Data¶

With this release , the behavior when unloading data from floating point virtual columns (FLOAT or DOUBLE data type) in either standard (local) or external tables to Parquet files has changed as follows:

Previously

In certain cases, data from virtual columns with either the FLOAT or DOUBLE data type were unloaded as FLOAT data (32-bit) in the Parquet data files instead of DOUBLE data (64-bit), which could cause a loss of precision.

Currently

Floating point data are unloaded to Parquet data files as DOUBLE data, retaining the precision of the data.

Snowpipe: Paused Pipe Objects Become Stale After 14 Days¶

When Snowpipe is configured to load data files automatically if triggered by cloud storage event notifications, pausing the pipe object stops Snowpipe from processing new event messages. A role with the required permissions can pause a pipe using the ALTER PIPE … SET PIPE_EXECUTION_PAUSED = TRUE command syntax.

With this release, the behavior when a paused pipe object is explicitly resumed has changed as follows:

Previously

A role with the required permissions could resume the pipe that has been paused for any length of time using ALTER PIPE … SET PIPE_EXECUTION_PAUSED = FALSE, unless ownership of the pipe was granted to another role, in which case calling the SYSTEM$PIPE_FORCE_RESUME function was necessary.

When the pipe object was resumed, Snowpipe processed all event notifications received while the pipe was paused.

Currently

When a pipe is paused, event messages received for the pipe enter a limited retention period. The period is 14 days by default. If a pipe is paused for longer than 14 days, it is considered stale.

To resume a stale pipe, a role with the required permissions must call the SYSTEM$PIPE_FORCE_RESUME function and input the new STALENESS_CHECK_OVERRIDE argument. This argument indicates an understanding that the administrator is resuming a stale pipe.

For example, resume the stale stalepipe1 pipe in the mydb.myschema database and schema:

SELECT SYSTEM$PIPE_FORCE_RESUME('mydb.myschema.stalepipe1','staleness_check_override');

Copy

While the stale pipe was paused, if ownership of the pipe was transferred to another role, then resuming the pipe also requires the additional new OWNERSHIP_TRANSFER_CHECK_OVERRIDE argument.

For example, resume the stale stalepipe2 pipe in the mydb.myschema database and schema, which transferred to a new role:

SELECT SYSTEM$PIPE_FORCE_RESUME('mydb.myschema.stalepipe1','staleness_check_override, ownership_transfer_check_override');

Copy

Note that the arguments can be input in either order.

In addition, event notifications received while a pipe is paused are retained for only a limited period of time (14 days). As each notification reaches the end of this period, Snowflake schedules it to be dropped from the internal metadata. If the pipe is later resumed, Snowpipe may process notifications older than 14 days on a best effort basis. Snowflake cannot guarantee that these older notifications are processed.

For example, if a pipe is resumed 15 days after it was paused, Snowpipe generally skips any event notifications that were received on the first day the pipe was paused (i.e. that are now more than 14 days old). If the pipe is resumed 16 days after it was paused, Snowpipe generally skips any event notifications that were received on the first and second days after the pipe was paused. And so on.

Tasks: Overlapping Tree of Task Runs Prevented by Default¶

A simple tree of tasks is composed of a single, scheduled root task and one or more child tasks that are triggered when their predecessor task runs successfully to completion.

With this release, the behavior has changed as follows when the amount of time required to complete all tasks in a tree exceeds the explicit scheduled time set in the definition of the root task:

Previously

Snowflake only ensured that a single instance of a root task was executed at a given time. If the root task was still running when the next scheduled execution time occurs, then that scheduled time was skipped.

This guarantee did not extend to child tasks. If the next scheduled run of the root task occurred while the current run of a child task was still executing, the previous behavior could lead to multiple parallel instances of the same non-root task running concurrently.

Multiple instances of a task tree running concurrently could produce unexpected or undesirable results. For example, if a task in a tree consumed records in a stream, those records would not necessarily be available to any instances of the same task tree that were running concurrently.

Currently

Only one instance of a particular tree of tasks is allowed to run at a time by default. The next run of a root task is scheduled only after all child tasks in the tree have finished running. This means that if the cumulative time required to run all tasks in the tree exceeds the explicit scheduled time set in the definition of the root task, at least one run of the task tree is skipped.

In the following example, a run of a single task tree is scheduled to start when a prior run has not completed yet. The period of overlap, or concurrency, is identified in red. The diagram identifies the span of time when each task queued before running in the warehouse:

Overlapping runs may be tolerated (or even desirable) when read/write SQL operations executed by overlapping runs of a task tree do not produce incorrect or duplicate data. However, for other task trees, this behavior change requires task tree owners (i.e. the role with the OWNERSHIP privilege on all tasks in the tree) to both set an appropriate schedule on the root task and to choose an appropriate warehouse size to ensure an instance of the task tree finishes to completion before the root task is next scheduled to run.

A new task parameter, ALLOW_OVERLAPPING_EXECUTION, is introduced to manage the behavior for individual task trees. The default value is FALSE. When set to TRUE on a root task, the parameter allows multiple instances of the tree to run concurrently; that is, the setting restores the current behavior.

CREATE TASK t1
  WAREHOUSE = <warehouse_name>
  SCHEDULE = <schedule>
  ALLOW_OVERLAPPING_EXECUTION = TRUE
AS
  <sql>;