2021_07 Bundle¶

Enable DNSSEC signing for snowflakecomputing.com¶

With this release, the DNSSEC delegation is enabled.

This change provides the ability to verify that the DNS replies are sent by authorized Nameservers that secure the communication when connecting to Snowflake. It is expected that the DNSSEC delegation change does not require changes to any client used to connect to Snowflake.

However, the DNSSEC delegation will need to remain disabled for snowflakecomputing.com and its subdomains if you have created internal zones to route to snowflakecomputing.com and have DNSSEC validation enabled on your DNS Resolver.

To verify your DNS configuration, run the following command and verify the returned status value is NOERROR:

dig <snowflake_account_url> +dnssec +trace

If this status value is not returned, toggle the DNSSEC delegation setting and contact Snowflake Support.

Roles Granted to Users Not Dropped When the Grantor Role Is Dropped¶

Every grant in the Snowflake system has a grantor role. This is the role that provides the authorization to create the grant and can be seen in the granted_by column of the SHOW GRANTS statement.

With this release, the behavior of the system when a grantor role is dropped changed as follows:

Previously

Any grants of a role to a user by the grantor role were not revoked automatically. The SHOW GRANTS TO USER command output displayed the role grants with the grantor value showing NULL. The role-to-user grants were revoked manually using a role that has the global MANAGE GRANTS privilege.

Currently

Any grants of a role to a user by the grantor role are revoked automatically.

The following example shows the previous and current behaviors:

-- Set USERADMIN as the active role in the session.

USE ROLE USERADMIN;

-- Create custom role R1 to serve as the grantor role in this example.

CREATE ROLE r1;

-- Create a user to grant the role to.

CREATE USER user1;

GRANT ROLE r1 TO USER user1;

-- Set ACCOUNTADMIN as the active role in the session.

USE ROLE ACCOUNTADMIN;

-- Grant the ability to create custom roles to role R1.

GRANT CREATE ROLE ON ACCOUNT TO ROLE r1;

-- Set the R1 role as the active role in the session.

USE ROLE r1;

-- Create custom role R2 to grant to the user.

CREATE ROLE r2;

GRANT ROLE r2 TO USER user1;

-- Show the roles granted to user USER1.-- The new R2 role is granted to the user.-- Role R1 is identified as the grantor role in the
granted_by column.

SHOW GRANTS TO USER user1;
+-------------------------------+------+------------+--------------+------------+
| created_on                    | role | granted_to | grantee_name | granted_by |
|-------------------------------+------+------------+--------------+------------|
| 2021-06-18 10:02:56.404 -0700 | R2   | USER       | USER1        | R1         |
+-------------------------------+------+------------+--------------+------------+

-- Set USERADMIN as the active role in the session.

USE ROLE USERADMIN;

-- Drop role R1.

DROP ROLE r1;

-- Show the roles granted to user USER1.

SHOW GRANTS TO USER user1;

Previous behavior:

Dropping the grantor role r1 did not automatically revoke role r2 from user user1:

+-------------------------------+------+------------+--------------+------------+
| created_on                    | role | granted_to | grantee_name | granted_by |
|-------------------------------+------+------------+--------------+------------|
| 2021-06-18 10:02:56.404 -0700 | R2   | USER       | USER1        | R1         |
+-------------------------------+------+------------+--------------+------------+

Copy

Current behavior:

Dropping the grantor role r1 automatically revokes role r2 from user user1:

+-------------------------------+------+------------+--------------+------------+
| created_on                    | role | granted_to | grantee_name | granted_by |
+-------------------------------+------+------------+--------------+------------+
+-------------------------------+------+------------+--------------+------------+

Copy

Allow the POLICY_NAME column in the DESCRIBE TABLE and DESCRIBE VIEW output for All Accounts¶

With this release, the POLICY_NAME column is included in every DESCRIBE TABLE or DESCRIBE VIEW query result regardless of whether the Snowflake account is enabled to use the masking policy object.

The APPLY MASKING POLICY and APPLY ROW ACCESS privileges allow the DESCRIBE operation on tables and views only¶

With this release, a role with the global APPLY MASKING POLICY privilege or the global APPLY ROW ACCESS POLICY privilege can run a DESCRIBE operation only on table and view objects.

This change ensures that a role with either privilege can continue to run the DESCRIBE operation on the table or view prior to setting a policy, but now restricts the scope of running a DESCRIBE operation on objects that are not tables or views (e.g. warehouses, integrations).

Masking and Row Access Policy Updates: Allow a column to be specified in only one policy kind¶

With this release, a given table or view column can be specified in either a row access policy or a masking policy.

This change ensures that a role with the SELECT privilege on a given table or view cannot inadvertently read unmasked column data in the query result when the column data should be masked.

Time Travel: Queries Beyond Data Retention Period Fail¶

With this release, Time Travel query behavior has changed as follows:

Previously

If the historical version and the current version of a table were identical, queries beyond the table’s Time Travel data retention period could have completed successfully.

Currently

If a query is beyond the Time Travel data retention period for a table, the query fails. This change ensures the semantics for Time Travel are consistent and produces predictable query results regardless of when a table was last modified.

Previously, if no DML operations had been performed on a table, the historical version of the table was identical to the current version and, therefore, considered valid for queries. This would result in successful queries even beyond the Time Travel data retention period. For example, if the Time Travel data retention period was 1 day and the table was last modified 30 days ago, queries that targeted timestamps in the table between 2 to 30 days ago could have successfully returned results.

Trim List of Dangling References Shown in the Error Messages¶

If a primary database is replicated but references objects in another database, refreshing the secondary database fails with dangling references.

With this release, the error message for dangling references has changed as follows:

Previously

If a database refresh failed with dangling references, the complete list of dangling references was added to the error message. If the message exceeded the size limitation, overflow errors occurred.

Currently

If a database refresh fails with dangling references and the list of dangling references exceeds the size limitation, the message is truncated with the appended text … [ REFERENCES TRUNCATED ]. If the error message does not exceed the size limitation, the current behavior remains the same.

SYSTEM$WAIT Function: Changes to Behavior¶

With this release, the behavior of the SYSTEM$WAIT function has changed as follows:

Previously

SYSTEM$WAIT would sleep for the specified period during query compilation. While sleeping, the query did not check whether it had been cancelled. The result was that: - The query ran until the wait time was completed even if the user tried to cancel the query manually or the query exceeded Snowflake’s compilation timeout limit. - If the wait time was longer than the compilation timeout, and if the user did not try to cancel the query, then after the wait time was completed, Snowflake detected that the query exceeded the compilation timeout and treated the query as FAILED.

Currently

The wait occurs during the query execution phase. Also, SYSTEM$WAIT checks for cancellation at regular intervals. As a result, the new behavior is:

• If the user cancels the query while the query is waiting, the query stops running the next time that the query checks for cancellation. (However, there might still be a delay between the time that the user cancels the statement and the time that the cancellation takes effect.)

• The query is not cancelled automatically if the wait period exceeds the compilation timeout. After the wait, the query finishes normally (if the user does not cancel the query and there are no other errors).

LOGIN_HISTORY Functions: New Column in the Output¶

With this release, we will be adding a new column to the LOGIN_HISTORY , LOGIN_HISTORY_BY_USER table functions:

Parquet Data: Change to Handling of LIST Of Values During Loading¶

With this release, the handling of LIST value in Parquet data when loaded into Snowflake tables has changed as follows:

Previously

A LIST value was composed of a set of objects nested in an array and enclosed in an outer object.

This behavior did not comply with the Apache Parquet specification.

Currently

A LIST value is structured as a flat array of values. The data type of each value (e.g. integer, string) is set correspondingly.

With this change, the behavior complies with the Parquet specification.

For example, a list column in a Parquet file contains the value LIST [1, 2, 3].

When the value is loaded into a VARIANT column in Snowflake, the previous structure was previously as follows:

"simple_list": {
  "list": [
    {
      "item": 1
    },
    {
      "item": 2
    },
    {
      "item": 3
    }
  ]
}

The loaded VARIANT value is now as follows:

"simple_list": [
  1,
  2,
  3
]

Snowpipe: Pipes Stopped When the Referenced Stage is Modified, Recreated, or Dropped¶

With this release, when any one of the following DDL operations is executed on a stage referenced by one or more pipes, the behavior has changed:

• Modify the URL to the location of the files in external cloud storage (using ALTER STAGE).

• Drop the stage (using DROP STAGE).

• Recreate the stage (using the CREATE OR REPLACE STAGE syntax).

The behavior has changed as follows:

Previously

The execution state returned by the SYSTEM$PIPE_STATUS function for the pipe (executionState value) did not change. For example, if the state was RUNNING before the DDL operation was performed on the stage, that state was retained. An execution state of RUNNING changed to STALLED_COMPILATION_ERROR when Snowpipe attempted to load data from a file after the stage was dropped or the URL to cloud storage was changed (as a result of the stage being recreated or modified).

Currently

The execution state for the pipe changes to STOPPED_STAGE_DROPPED.

Previously, when any one of the listed DDL operations was executed on a stage, any pipe objects that referenced the stage had to be recreated (using the CREATE OR REPLACE PIPE syntax) before they could be used to load data again (via calls to the Snowpipe REST API endpoints or triggered by event notifications from the cloud storage service). This requirement has not changed. The behavior is identical regardless of the AUTO_INGEST setting for the pipe.

Snowpark Library 0.9.0: Change to the DataFrame union and unionByName Methods¶

In version 0.9.0 of the Snowpark library, the union and unionByName methods of the DataFrame class now perform a UNION, rather than a UNION ALL.

Previously

The DataFrame.union() and DataFrame.unionByName() methods performed a UNION ALL. If the same row is present in both input DataFrame objects, the returned DataFrame contains two copies of that row.

Currently

The DataFrame.union() and DataFrame.unionByName() methods perform a UNION. If the same row is present in both input DataFrame objects, the returned DataFrame only contains one copy of that row. If you need to perform a UNION ALL instead, call one of the following methods:

• DataFrame.unionAll()

• DataFrame.unionAllByName()

DataFrame.unionAllByName() is a new API method introduced in version 0.9.0 of the Snowpark library.