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, see April 2022.
Unless otherwise noted, these changes are in the 2022_02 bundle, which was enabled by default in an update to the 6.9 release.
SQL Changes — General¶
Hierarchical Data Queries: Iteration Limits No Longer Enforced¶
When querying hierarchical data, you can use recursive CTEs or the CONNECT BY command to iterate over each level of hierarchy.
The limit on the number of iterations, which was previously set to 100 (internally by Snowflake), is now no longer enforced:
If a query exceeded the maximum number of iterations (100), the query failed with the following error message:
Recursion exceeded max iteration count (n)
nwas the maximum number of iterations allowed.
The error code for this error was
There is no limit to the number of iterations performed.
Queries that previously failed with the error message above (in particular, queries that result in infinite loops) no longer fail and continue to run until the query either succeeds or times out, which can be controlled by setting the STATEMENT_TIMEOUT_IN_SECONDS parameter.
To determine if you had queries that exceeded the maximum number of iterations before the change was enabled, check the
QUERY_HISTORY view for queries that failed with the error code
SELECT * FROM snowflake.account_usage.query_history WHERE error_code = 100189;
With the change enabled, if these same queries ae executed, they will not fail. If an infinite loop occurs, the query will not terminate early. Instead, the query will run until it either succeeds or times out (e.g. exceeds the number of seconds set in the STATEMENT_TIMEOUT_IN_SECONDS parameter).
See Troubleshooting a Recursive CTE for information on how infinite loops can occur, how to identify them, and how to rectify them.
Time Travel: Inherited DATA_RETENTION_TIME_IN_DAYS Parameter Retained in Transient Tables¶
Transient tables did not inherit the DATA_RETENTION_TIME_IN_DAYS parameter setting from parent objects when the data retention time was
0days. Transient tables were created with a data retention time of
1day, regardless of the parent object’s data retention time.
Transient tables inherit the data retention time set on a parent object (schema, database, account) if the parent object’s DATA_RETENTION_TIME_IN_DAYS is set to
This change only affects newly-created transient tables and does not change the DATA_RETENTION_TIME_IN_DAYS setting for transient tables that were created before the change was enabled.
To generate a list of transient tables in an account where at least one of its parents (schema or database) has DATA_RETENTION_TIME_IN_DAYS
0, execute the statements in the following example. However, note the following before executing the statements:
The list includes transient tables that have the DATA_RETENTION_TIME_IN_DAYS parameter explicitly set to
If DATA_RETENTION_TIME_IN_DAYS is set to
0at the account level, execute the set of statements in the second example below to list all transient tables with DATA_RETENTION_TIME_IN_DAYS set to
Before unsetting the parameter for any table, we recommend that you verify Time Travel should be disabled for that table.show tables in account; set table_qid = ( select last_query_id() ); show schemas in account; set schema_qid = ( select last_query_id() ); show databases in account; set database_qid = ( select last_query_id() ); with table_v as ( select "database_name" as database_name, "schema_name" as schema_name, "name" as table_name, "kind" = 'TRANSIENT' as is_transient, "retention_time" as table_retention_time from table(result_scan($table_qid)) ), schema_v as ( select "name" as schema_name, iff( try_to_number("retention_time") is null, 0, try_to_number("retention_time") ) as schema_retention_time from table(result_scan($schema_qid)) ), database_v as ( select "name" as database_name, "retention_time" as database_retention_time from table(result_scan($database_qid)) ) select * from table_v left join schema_v using (schema_name) left join database_v using (database_name) where is_transient and table_retention_time = 1 and ( schema_retention_time = 0 or database_retention_time = 0 );
If DATA_RETENTION_TIME_IN_DAYS is set to
0 at the account level, execute the following statements to list all transient
tables with DATA_RETENTION_TIME_IN_DAYS set to
-- Verify account level DATA_RETENTION_TIME_IN_DAYS setting is 0 show parameters like 'DATA_RETENTION_TIME_IN_DAYS' in account; show tables in account; select "database_name" as database_name, "schema_name" as schema_name, "name" as table_name, "kind" = 'TRANSIENT' as is_transient, "retention_time" as table_retention_time from table(result_scan(last_query_id())) where is_transient and table_retention_time = 1;
To unset the DATA_RETENTION_TIME_IN_DAYS parameter for an existing transient table, which allows it to inherit the parameter setting from a parent object, use ALTER TABLE:
ALTER TABLE <table_name> UNSET DATA_RETENTION_TIME_IN_DAYS;
To verify the data retention time set on a table, use SHOW TABLES:
SHOW TABLES LIKE '<table_name>';
SQL Changes — Commands & Functions¶
SHOW ORGANIZATION ACCOUNTS Command: New Column¶
The following column has been added to the output of the SHOW TAGS command:
The previous account URL for a given account.
SHOW PROCEDURES Command: Output Includes Both User-created and Built-in Stored Procedures¶
Snowflake supports creating stored procedures as schema-level objects in any database in an account. The SHOW PROCEDURES command returns information about these user-created stored procedures.
With the introduction of data classification, Snowflake now also provides built-in stored procedures that can be called as global objects, similar to built-in functions.
The output for the SHOW PROCEDURES command has changed as follows to support built-in stored procedures:
The command returned only user-created stored procedures in the current or specified database/schema (or for the entire account).
To view the built-in stored procedures provided by Snowflake, you could use the BUILTIN keyword in the command. For example:
SHOW BUILTIN PROCEDURES;
However, note that Snowflake only provides a single built-in stored procedure, ASSOCIATE_SEMANTIC_CATEGORY_TAGS.
The function returns all stored procedures, including both user-created and built-in stored procedures.
This make the SHOW PROCEDURES command consistent with the SHOW FUNCTIONS command.
The change does not affect the BUILTIN or USER keywords, which can be used to explicitly return either built-in or user-created stored procedures. For example:
SHOW BUILTIN PROCEDURES; SHOW USER PROCEDURES;
SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS Function: Changes to Basis for Estimation¶
This function has changed to use a small sample as the basis for estimating the costs. With this change, the function reports more accurate cost estimates. However, the change impacts warehouse usage and output of the function, as well as may affect performance of the function, as described below:
The function used a simple model to estimate the costs. Because the function used a simple model to estimate the costs:
You did not need to have a warehouse in use when calling the function.
Because the function did not use a warehouse, you were not billed for warehouse usage for this function.
The function executed and returned within seconds.
In the returned JSON output, for the objects named
There was no
computationMethodfield was set to
The function now takes a small sample of data from the specified table, produces a temporary search access path, analyzes the cost of the process, and extrapolates the results to estimate the cost for the entire table. Because the function uses sampling to estimate the costs:
To call the function, you need to have a warehouse in use. If no warehouse is currently in use, the function prints the following message:
No active warehouse selected in the current session.
Select an active warehouse with the USE WAREHOUSE command. To execute this function, you can use an X-Small warehouse. The warehouse size has no effect on the speed and performance of this function.
Because the function uses a warehouse, you are now billed for warehouse usage for this function.
The function takes longer to execute and return results (somewhere in the range of 20 seconds to 10 minutes). As noted above, using a larger warehouse size does not result in the faster execution of this function.
In the returned JSON output, for the objects named
commentfield is set to
"estimated via sampling".
computationMethodfield is set to
SQL Changes — Usage Views & Information Schema Views/Table Functions¶
LOGIN_HISTORY View (Account Usage): New Column¶
The following new column has been added to the ACCOUNT_USAGE.LOGIN_HISTORY view:
Connection is a Snowflake object that represents a connection URL that can be failed over across accounts for business continuity and disaster recovery. The column displays the name of the connection used by the client. If a client is not using a connection URL, this field is null.
QUERY_HISTORY Views (Account Usage): Output Consistent with QUERY_HISTORY Function¶
The values for inbound and outbound data transfer bytes are inconsistent between the following:
INFORMATION_SCHEMA.QUERY_HISTORY table function output
The Account Usage views include inbound and outbound data transfer bytes when the data transfer cloud value (INBOUND_DATA_TRANSFER_CLOUD or OUTBOUND_DATA_TRANSFER_CLOUD respectively) is Null.
These views have changed as follows:
The QUERY_HISTORY views in ACCOUNT_USAGE and READER_ACCOUNT_USAGE included the following:
INBOUND_DATA_TRANSFER_BYTES column included data transfer bytes when the INBOUND_DATA_TRANSFER_CLOUD value is Null.
OUTBOUND_DATA_TRANSFER_BYTES column included data transfer bytes when the OUTBOUND_DATA_TRANSFER_CLOUD value is Null.
The views are now consistent with the output of the INFORMATION_SCHEMA.QUERY_HISTORY table function.
The INBOUND_DATA_TRANSFER_BYTES and OUTBOUND_DATA_TRANSFER_BYTES columns do not include bytes from file transfers when the associated INBOUND_DATA_TRANSFER_CLOUD or OUTBOUND_DATA_TRANSFER_CLOUD value is Null.
Data Pipeline Changes¶
DESCRIBE STREAM / SHOW STREAM Commands: New Columns in Output¶
The source object for the stream: table, view, directory table, or external table.
If the stream was created on a view, this column shows the underlying tables for the view.
The new columns have been inserted between the existing TABLE_NAME and TYPE columns.
Data Lake Changes¶
Directory Tables: Metadata Refreshed Once Automatically When Stage is Created¶
When you create a stage that includes a directory table, the directory table metadata now automatically refreshes immediately a single time. Refreshing the directory table metadata synchronizes the metadata with the current list of data files in the specified stage path.
Previously, in order to register existing data files in the directory table metadata, users had to execute an ALTER STAGE … REFRESH statement after the stage was created.
This improvement is implemented through the new REFRESH_ON_CREATE parameter for the CREATE STAGE command. When REFRESH_ON_CREATE = TRUE (default value), Snowflake automatically refreshes the directory table metadata automatically a single time when the stage is created.