March 2023 Behavior Changes¶
This topic describes the following behavior changes (if any) for the month:
Features that were deprecated.
Bundled changes that were enabled.
Other, non-bundled changes that were implemented.
If you have any questions about these changes, please contact Snowflake Support.
For details about the new features, enhancements, and fixes introduced this month, refer to March 2023.
Important
Unless otherwise noted, these changes are in the 2023_01 bundle, which was enabled by default in the 7.7 release.
Security Changes¶
SHOW INTEGRATIONS Command: USAGE Privilege Required to View Output¶
The behavior of the SHOW INTEGRATIONS command has changed as follows:
- Previously
Snowflake returned all integrations regardless of the privileges granted to the role in use to run the command.
- Currently
Snowflake returns integrations for which the role has the USAGE privilege on the integration or the OWNERSHIP privilege on the integration.
System Functions: MONITOR SECURITY Privilege Required to Execute Certain System Functions¶
The behavior of these system functions has changed as follows:
SYSTEM$GET_CMK_KMS_KEY_POLICY
SYSTEM$GET_CMK_AKV_CONSENT_URL
SYSTEM$GET_GCP_KMS_CMK_GRANT_ACCESS_CMD
- Previously
These functions were not guarded by any specific privilege.
- Currently
To call these functions, the active role or a role in the active role hierarchy must have the MONITOR SECURITY privilege.
Query History: Redacted SQL Upon Syntax Error¶
The views, pages, and functions that provide a query history now redact the content of a query that fails due to a syntax or parsing error:
- Previously
When a query failed due to a syntax or parsing error, its content could be viewed in the views, pages, and functions that provide a query history.
- Currently
The query history redacts the content of a query that fails due to a syntax or parsing error. The query text is replaced with
<redacted>
.
This implementation is done mainly for security reasons, where sensitive information like passwords cannot be redacted for queries with invalid syntax (which is done for syntactically valid queries). However, the user who executed the query would still be able to view the un-redacted query.
Note that “redacted” means that only the query text will be redacted, not the whole row in the query history for that syntactically invalid query.
In order to clarify who can see this text un-redacted, please be aware that the USER who executed the query (no matter what role they use) can see the query text. However, another user (even if they use the same role used for executing the failed query) will not be able to see the query text. The entry in the QUERY_HISTORY view is available for everyone who has the necessary privileges to check this view.
SQL Changes — General¶
Account Identifiers: New Length Limits¶
Snowflake now enforces limits on the number of characters used for the following account identifiers:
Organization name: Maximum of 10 characters.
Account name: Maximum of 31 characters.
Connection name: Maximum of 31 characters.
Account locator (legacy): Maximum of 31 characters.
- Previously
There were no hard limits for the length of account identifiers.
- Currently
Character limits for account identifiers are enforced when creating or renaming an account.
These new limits are not applied to existing accounts or organizations, unless you rename them. However, existing account identifiers that exceed these limits might cause problems in the future with some Snowflake features.
Materialized Views: Using Time Travel to Query Historical Data Produces Expected Error Message¶
When Time Travel is incorrectly used to query historical data for materialized views, an error message now states that Time Travel is not supported:
- Previously
Although Time Travel is not supported for materialized views, queries with the AT or BEFORE keyword in the FROM clause might have returned unexpected results. In some cases, queries might also have returned an unexpected error message. For example, for the Time Travel clause:
CAST('2022-11-29 00:00:00' AS TIMESTAMP_LTZ(9))
.- Currently
Querying historical data using Time Travel on materialized views produces a SQL compilation error:
Time travel is not supported for materialized views.
SQL Changes — Commands & Functions¶
SHOW Commands: New Column Added to Output for Certain Commands¶
The output of these SHOW commands now includes a new OPTIONS column:
SHOW ACCESS POLICIES
The column was added to support future functionality. Currently, this column returns an empty string.
SHOW ORGANIZATION ACCOUNTS Command: New Column in Output¶
The following column has been added to the output of the SHOW ORGANIZATION ACCOUNTS command:
Column Name |
Data Type |
Description |
---|---|---|
IS_ORG_ADMIN |
BOOLEAN |
Indicates whether the ORGADMIN role is enabled in an account. An account with the ORGADMIN role enabled is called the ORGADMIN account. |
To help minimize the impact of this addition, the column has been added as the last column in the output.
ARRAY_CAT Function: Changes to NULL Handling¶
The way in which the ARRAY_CAT function handles NULL input values has changed:
- Previously
If you passed NULL as an input argument to ARRAY_CAT, the function reported the following error:
NULL result in a non-nullable column
- Currently
If you pass NULL as an input argument to ARRAY_CAT, the function returns NULL, rather than reporting an error.
ARRAY_POSITION Function: Changes to Finding the Position of a NULL Value¶
When you call the ARRAY_POSITION function and pass in a value as the first argument, the function returns the position of the first ARRAY element with that value.
The ARRAY_POSITION function has changed when you specify NULL as the first argument:
- Previously
The function returned NULL. For example:
SELECT ARRAY_POSITION(NULL, [10, NULL, 30]); +--------------------------------------+ | ARRAY_POSITION(NULL, [10, NULL, 30]) | |--------------------------------------| | NULL | +--------------------------------------+
- Currently
The function returns the position of the first NULL in the ARRAY. For example:
SELECT ARRAY_POSITION(NULL, [10, NULL, 30]); +--------------------------------------+ | ARRAY_POSITION(NULL, [10, NULL, 30]) | |--------------------------------------| | 1 | +--------------------------------------+
This change was implemented for consistency with the ARRAY_CONTAINS function. When you use the ARRAY_CONTAINS function to determine if an ARRAY contains NULL, the function returns TRUE.
GET_DDL Function: Tags Set on Streams, Tasks, and Pipes Included in Output¶
The output of the GET_DDL function now include any tag objects that are set on a stream, task, or pipe. The tag associations are specified in the WITH clause of the CREATE OR REPLACE <object> command and allow creating or replacing an object with tags already assigned to the object.
SQL Changes — Usage Views & Information Schema Views/Table Functions¶
Account Usage: New and Changed Columns in Certain Views¶
To differentiate between account-level roles and database roles, the following changes have been made to the specified Account Usage views in the shared SNOWFLAKE database:
New column, OWNER_ROLE_TYPE, has been added to the following views:
The new column specifies the type of role (ROLE or DATABASE_ROLE) that owns the object.
GRANTS_TO_ROLES view:
Existing column, GRANTED_TO, now differentiates between ROLE and DATABASE_ROLE. Previously, it was always ROLE,
New column, GRANTED_BY_ROLE_TYPE, that displays ROLE or DATABASE_ROLE depending on whether the grantor is an account-level or database role.
QUERY_HISTORY view:
New column, ROLE_TYPE, that displays ROLE or DATABASE_ROLE depending on whether the job was executed by an account-level or database role.
QUERY_HISTORY View (Account Usage): New Columns Added¶
The following columns have been added to the Account Usage QUERY_HISTORY view in the shared SNOWFLAKE database.
Column Name |
Data Type |
Description |
---|---|---|
TRANSACTION_ID |
NUMBER |
Specifies either the ID of the transaction that wraps the statement or 0 if the statement is not executed within a transaction. The new column can be used to join data in the QUERY_HISTORY and LOCK_WAIT_HISTORY views to examine multi-statement transactions. |
CHILD_QUERIES_WAIT_TIME |
NUMBER |
Specifies the number of milliseconds to complete a child job for a query. Note that a child job only applies to certain Snowflake queries. Snowflake pauses the main query, the child job completes, and then the main query resumes. |
REPLICATION_DATABASES View (Information Schema): Changes to Column Values¶
The following columns in the Information Schema REPLICATION_DATABASES view use the legacy account identifier format:
PRIMARY
REPLICATION_ALLOWED_TO_ACCOUNTS
FAILOVER_ALLOWED_TO_ACCOUNTS
The column values have changed as follows:
- Previously
The account identifier used the format:
<region_group>.<snowflake_ region>.<account_locator>
- Currently
The account identifier uses the format:
<organization_name>.<account_name>
TABLES View (Account Usage): Changes to the RETENTION_TIME Column¶
The RETENTION_TIME column value in the Account Usage TABLES view has changed as follows:
- Previously
In the following cases, the RETENTION_TIME column might have displayed an incorrect data retention time for tables:
If the DATA_RETENTION_TIME_IN_DAYS was not explicitly set for a transient table, the RETENTION_TIME column displayed the value inherited from a parent object. This value might have been incorrect. The maximum data retention time for transient tables is 1 day.
If the MIN_DATA_RETENTION_TIME_IN_DAYS parameter was set for an account, the RETENTION_TIME column ignored this minimum retention time and might have displayed an incorrect value for a table.
- Currently
The RETENTION_TIME column now display the correct data retention time for tables.
TABLES, VIEWS, and EXTERNAL_TABLES Views (Account Usage, Information Schema): New Columns Added¶
New columns for tracking DDL operations have been added to the following Account Usage and Information Schema views:
ACCOUNT_USAGE:
INFORMATION_SCHEMA:
New columns:
Column Name |
Data Type |
Description |
---|---|---|
LAST_DDL |
TIMESTAMP |
Specifies the timestamp of the last DDL operation performed on the table or view, including database refreshes for replication (if the refresh changes visible properties for the table/view). |
LAST_DDL_BY |
TEXT |
Specifies the username of the user who performed the last DDL operation on the table or view. |
TASK_HISTORY View (Account Usage): Change to Status for Failed and Auto-suspended Tasks¶
The Account Usage TASK_HISTORY view has changed as follows when examining a task that has been auto-suspended by hitting its SUSPEND_TASK_AFTER_NUM_FAILURES limit:
- Previously
The task displayed the state
FAILED
.- Currently
The task displays the state
FAILED_AND_AUTO_SUSPENDED
.
Snowflake CLI, Connectors, Drivers, and SQL API Changes¶
Some Unused Data No Longer Sent to Drivers, Connectors, and Clients¶
The server no longer includes some unused data in the response sent to drivers, connectors, and clients:
- Previously
When the server sent a response to drivers, connectors, and clients, the response included some data that is not used.
- Currently
The server no longer returns this unused data in the response.
Data Pipeline Changes¶
Streams: CREATE STREAM with INSERT_ONLY = TRUE Not Allowed on Non-external Tables¶
CREATE STREAM with INSERT_ONLY = TRUE specified for a non-external table now produces an error and the statement fails:
- Previously
The Snowflake Documentation states that the use of INSERT_ONLY is not allowed on non-external tables. However, when INSERT_ONLY was specified, the command executed successfully, but INSERT_ONLY was not enforced.
- Currently
Any attempt to create an INSERT_ONLY stream on non-external tables throws the following error:
Streams of type INSERT_ONLY can only be created on external tables.
Streams: CREATE STREAM and CREATE | ALTER VIEW Propagate CHANGE_TRACKING Parameter to Underlying Objects¶
CREATE STREAM, CREATE VIEW, and ALTER VIEW now propagate CHANGE_TRACKING = TRUE to associated underlying tables and views, and fails accordingly when insufficient permissions are encountered:
- Previously
CREATE STREAM … CHANGE_TRACKING = TRUE statements attempted to enable CHANGE_TRACKING recursively on base tables, and they failed if insufficient permissions were encountered.
ALTER/CREATE VIEW … CHANGE_TRACKING = TRUE statements attempted to enable CHANGE_TRACKING recursively on base views and tables of the target view. If insufficient permissions were encountered, the statement should have failed, but didn’t.
- Currently
CREATE STREAM and ALTER/CREATE VIEW now correctly propagate CHANGE_TRACKING = TRUE to the underlying database objects. Both types of statements fail if permissions are insufficient for any part of the base object tree.
Note
With CREATE STREAM, if sufficient permissions aren’t available, base tables and views are left unchanged. With ALTER/CREATE VIEW, base objects are left unchanged, but the view itself has CHANGE_TRACKING enabled.
Streams: Joins on Views for Append-only Streams No Longer Produce Unexpected Results¶
When querying APPEND_ONLY streams on views that use joins, results are now consistent between the left and right side join contents:
- Previously
Append-only stream queries over a view that used joins could return results that were inconsistent with the expectations of an append-only stream. In addition, they possibly included updates that have happened since the stream offset.
- Currently
Append-only stream queries return inserts that have occurred on either join input, joined with the current values of the tables at the stream offset.
Task Parameters Preserved When Cloning Tasks¶
When a task is cloned, the target (cloned) task now includes the same parameter set with each parameter value set to match the original source task:
- Previously
The cloned task had the default value for each parameter. That is, parameter values were not copied from the source task.
- Currently
All parameters set on the source task are copied intact to the clone task instance.
Task Parameters Preserved When Cloning Databases, Schemas, and Tables¶
When a schema, database, or table is cloned, the target (cloned) object now includes the same parameter set, with each parameter value set to match the source:
- Previously
The following four parameters were not propagated to the clone target instance if set on the source schema or database:
ENABLE_STREAM_TASK_REPLICATION
SUSPEND_TASK_AFTER_NUM_FAILURES
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE
USER_TASK_TIMEOUT_MS
Note that these parameters do not apply to tables.
- Currently
All parameters set on the schema, database, or table are now copied intact to the cloned target instance.
Data Governance Changes¶
Tag Must Exist When Calling System Functions¶
The behavior of the SYSTEM$GET_TAG_ON_CURRENT_COLUMN and SYSTEM$GET_TAG_ON_CURRENT_TABLE functions has changed as follows:
- Previously
If the tag did not exist, Snowflake allowed using these functions when creating or altering a masking policy or row access policy. The query on the protected column failed because the tag did not exist.
- Currently
The tag must exist when using these functions while creating a masking policy or row access policy. If the tag does not exist, Snowflake returns the following error message:
Tag '<tag_name>' does not exist or not authorized.
Replication Changes¶
Failover Groups: Change to GRANTED_ON Column in SHOW GRANTS Output¶
The SHOW GRANTS ON <object_type> <object_name> command lists all the privileges granted on a specific object. The value in the GRANTED_ON column is the type of that object. For failover groups, this value is REPLICATION_GROUP.
The value in the GRANTED_ON column for SHOW GRANTS ON FAILOVER GROUP <failover_group_name> has changed as follows:
- Previously
The GRANTED_ON column value for failover groups was REPLICATION_GROUP.
- Currently
The GRANTED_ON column value for failover groups is now FAILOVER_GROUP.
Integrations: Read-only Secondary Integrations Enforced¶
A replication or failover group can include integrations by adding the INTEGRATIONS type to its OBJECT_TYPES list and specifying the integration type in the ALLOWED_INTEGRATION_TYPES list. Object types that are included in a replication or failover group are read-only in target accounts. For example, if the USERS type is included, users cannot be created, modified, or deleted in a target account.
This behavior is now enforced for integration types included in replication or failover groups as follows:
- Previously
Integration types that were replicated from a source account to a target account in a replication or failover group could be modified in the target account.
For example, if INTEGRATIONS was included in the OBJECT_TYPES list for a replication or failover group, and API INTEGRATIONS was included in the ALLOWED_INTEGRATION_TYPES list, API integrations could be created, modified, or deleted in target accounts.
- Currently
Integration types that are replicated from a source account to a target account in a replication or failover group are now read-only in the target account.
For example, if INTEGRATIONS is included in the OBJECT_TYPES list for a replication or failover group, and API INTEGRATIONS is included in the ALLOWED_INTEGRATION_TYPES list, API integrations are read-only in target accounts. Consequently, API integrations cannot be created, modified, or deleted in target accounts.