2021_04 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 May 2021.

Important

Unless otherwise noted, these changes were enabled through the 2021_04 bundle in Release 5.18.

Deprecated Features

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.

SQL Changes: General

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";
Copy

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

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

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

Previously

Currently

comment = 'this is a comment with a \ slash'

comment = 'this is a comment with a slash'

comment = 'this is a comment with a \z non-special character'

comment = 'this is a comment with a z non-special character'

comment = 'this is a comment with \'escaped quote\''

comment = 'this is a comment with ''escaped quote'''

comment = 'this is a comment with "unescaped quote"'

comment = 'this is a comment with \"unescaped quote\"'

comment = 'this is a comment with \176 oct, \x7E hex, \u007E unicode, and ~ ASCII < 128'

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

comment = 'this is a comment with \200 oct, \x80 hex, \u0080 unicode, and ASCII >= 128'

comment = 'this is a comment with \u0080 oct, \u0080 hex, \u0080 unicode, and \u0080 ASCII >= 128'

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

comment = 'this is a comment with \uD83D\uDE00 large unicode'

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

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

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;
Copy

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;
Copy

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

Previously, when a statement referred to a matching column in the table on the right, the statement used the column in the table on the left. With this release, references to these matching columns use the specified table.

Previously

When tables are joined on a matching column, references to the matching column in the table on the right side of the join used the column in the table on the left side of the join.

For example, in the following statements:

select B.col1 from A left join B using (col1);

select B.col1 from A natural
left join B (where table A and B both have a column named col1);
Copy

B.col1 returned the value from A.col1.

Currently

When tables are joined on a matching column, references to the matching column in the table on the right side of the join now use the column in the table on the right side of the join.

For example, in the following statements:

select B.col1 from A left join B using (col1);

select B.col1 from A natural
left join B (where table A and B both have a column named col1);
Copy

B.col1 now returns the value from B.col1.

Note that this might result in errors when the table of the matching column is not specified. For example, if a statement specifies the table of the matching column in the SELECT clause but not in the GROUP BY clause, the matching column in the GROUP BY clause might resolve to a different table:

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

The GROUP BY clause resolved to column t2.j, and this statement executes without errors.

Currently

The GROUP BY clause now resolves to column t1.j, and this statement results in a "select not in group by" error.

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

In addition, the order of columns when using SELECT * has changed:

Previously

When tables were joined on a matching column and SELECT * was used, the matching column from the table on the right was listed first.

For example, in the following statement:

select t2.* from
(select col1, j) as t1
left outer join
(select col2, j) as t2
using (j);
Copy

select t2.* returned the columns in the following order: t1.j (the matching column), col2.

Currently

When tables are joined on a matching column, using SELECT * from the table on the right now returns the columns in their natural order.

For example, in the following statement:

select t2.* from
(select col1, j) as t1
left outer join
(select col2, j) as t2
using (j);
Copy

select t2.* now returns the columns in the following order: col2, t2.j (the matching column).

If an existing view definition uses SELECT * to select everything from the table on the right, that view must be recreated. With this release, selecting from that existing view results in the error:

SQL compilation error: View columns mismatch with view definition for view '<view_name>'
at line <n>, position <m>, please re-create the view
Copy

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

This release also changes the way in which Snowflake interprets references to matching columns in the tables on the left side of the join when more than two tables are joined:

Previously

When more than two tables are joined on a matching column, references to a matching column in the leftmost tables used the column from the last table containing that column.

For example, suppose that tables t1 , t2 , and t3 are joined on the matching column j and have a matching column a:

select t1.a from
(select a) as t1 join
(select a, j) as t2 join
(select a, j) as t3
using (j);
Copy

t1.a returned the value from t2.a (the last table specified in the left side of the join).

Currently

When more than two tables are joined on a matching column, references to a matching column in the leftmost tables now use the column from the specified table.

For example, suppose that tables t1 , t2 , and t3 are joined on the matching column j and have a matching column a:

select t1.a from
(select a) as t1 join
(select a, j) as t2 join
(select a, j) as t3
using (j);
Copy

t1.a now returns the value from t1.a (the table specified in the SELECT clause).

Note that if the USING clause specifies a matching column that appears in the tables on the left side of the join, Snowflake returns an “ambiguous column” error with this release.

For example, in this statement, the USING clause refers to column a, which could either be t1.a or t2.a:

select * from
(select a) as t1 join
(select a, j) as t2 join
(select a, j) as t3
using (a);
Copy
Previously

The statement resolved USING (a) to USING (t2.a).

Currently

The statement now returns an “ambiguous column” error.

If an existing view definition uses SELECT * to select everything from a table used to create the left side of the join, that view is invalid with this release.

For example, suppose that you defined a view in this way:

create view myview as
select t1.* from
(select a, b) as t1 join
(select a, c, j) as t2 join
(select a, j) as t3
using (j);
Copy

When you created the view, the view contained four columns (including the matching column for the join and the columns from t2, the last table used for the left side of the join):

j, t1.b, t2.a, t2.c

With this release, select t1.* returns only two columns:

t1.a, t1.b

Previously

Using SELECT * from the view returned the four columns from the query.

Currently

The query now returns two columns (instead of four), and Snowflake reports the error:

SQL compilation error: View definition for <view_name> declared 4 column(s),
but view query produces 2 column(s).
Copy

In addition, errors now occur if a statement selects a column from a table used in the join and that column is present in the join but not in the specified table. For example, the following statement selects a column c that is not in table t1:

select t1.c from
(select a, b) as t1 join
(select a, c, j) as t2 join
(select a, j) as t3
using (j);
Copy
Previously

This statement selected column c from the result of the join.

Currently

The statement now results in the following error:

SQL compilation error: invalid identifier 'T1.C'
Copy

SQL Changes: Functions and Commands

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:

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.

SQL Changes: Account Usage

REMAINING_BALANCE_DAILY View: New Column

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

Column Name

Data Type

Description

ROLLOVER_BALANCE

NUMBER(38,2)

The amount of rollover balance in currency that is available for use at the end of the date.

At the end of a contract term, it is calculated as sum(AMOUNT) from the CONTRACT_ITEMS view - sum(USAGE_IN_CURRENCY) from the USAGE_IN_CURRENCY_DAILY view.

Data Loading and Unloading Changes

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.

Note

The CREATE STAGE and ALTER STAGE commands already correctly handled delimited identifiers that are not enclosed in single quotation marks and therefore are not affected by this behavior change.

The following table shows examples of the behavior change:

File Format Identifier

Previously

Currently

FILE_FORMAT = (FORMAT_NAME = "db1.schema1.format1"))

The SQL command ignored the double quotation marks and referenced the FORMAT1 file format in database DB1 and SCHEMA1.

The SQL command recognizes the double quotation marks and references the db1.schema1.format1 file format in the current database and schema in the session.

The command returns an error because the file format does not exist.

FILE_FORMAT = (FORMAT_NAME = "db1.schema1.Format 1"))

The file format name includes capitalization and a blank space. The SQL command ignored the double quotation marks and referenced the FORMAT 1 file format in database DB1 and SCHEMA1.

The command returns an error because the file format does not exist.

The SQL command recognizes the double quotation marks and references the db1.schema1.Format 1 file format in the current database and schema in the session.

The command returned an error because the file format does not exist.

FILE_FORMAT = (FORMAT_NAME = db1.schema1."Format 1"))

The file format name includes capitalization and a blank space. The file format name alone is delimited, but the SQL command ignored the double quotation marks and referenced the FORMAT 1 file format in database DB1 and SCHEMA1.

The command returned an error because the file format does not exist.

The SQL command recognizes the double quotation marks and references the Format 1 file format in the DB1 database and SCHEMA1 schema.

The command succeeds because the delimited file format identifier was intentional.

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.

Note

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.

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.

Note

This behavior change has no effect on pipe objects that are configured to receive Snowpipe REST API calls to load data (i.e. AUTO_INGEST = FALSE in the pipe definition).

Data Pipeline Changes

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 task tree runs

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.

Note

This behavior change has no effect on standalone tasks. If no tasks in your account have the PREDECESSOR parameter set (i.e. are child tasks in a tree of tasks), then this behavior change does not affect your account.

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.

Managing Overlapping Task Trees

  1. If feasible, increase the scheduling time between runs of the root task.

  2. Consider increasing the size of the warehouse that runs large or complex SQL statements or stored procedures in the task tree.

  3. Analyze the SQL statements or stored procedure executed by each task. Determine if code could be rewritten to leverage parallel processing.

If none of the above solutions align a task tree with its required schedule, consider whether it is necessary to allow concurrent runs of the tree by setting ALLOW_OVERLAPPING_EXECUTION = TRUE on the root task.

Allowing Overlapping Runs of Individual Task Trees

As mentioned earlier, a new task parameter, ALLOW_OVERLAPPING_EXECUTION, enables task owners to allow multiple instances of a task tree to run concurrently. The default value is FALSE. Set ALLOW_OVERLAPPING_EXECUTION = TRUE to allow concurrent runs of a task tree when you create the root task (using CREATE TASK) or later (using ALTER TASK).

The following example creates a new root task, t1 , with ALLOW_OVERLAPPING_EXECUTION set to TRUE:

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

The following example modifies existing root task t2 to allow overlapping runs of its tree:

ALTER TASK t2 SET ALLOW_OVERLAPPING_EXECUTION = TRUE;