March 2023 Behavior 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.

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.

SHOW Commands: New Column Added to Output for Certain Commands¶

The output of these SHOW commands now includes a new OPTIONS column:

• SHOW MASKING POLICIES

• SHOW ACCESS POLICIES

• SHOW SESSION POLICIES

• SHOW PASSWORD 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:

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.

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:

  • FILE_FORMATS

  • FUNCTIONS

  • MASKING_POLICIES

  • PIPES

  • ROLES

  • ROW_ACCESS_POLICIES

  • SCHEMATA

  • SEQUENCES

  • STAGES

  • TABLES

  • TAGS

  • 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.

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:

  • TABLES

  • VIEWS

• INFORMATION_SCHEMA:

  • TABLES and EXTERNAL_TABLES

  • VIEWS

New columns:

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.

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.

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.

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.

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.

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.