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 September 2022.
Unless otherwise noted, these changes are in the 2022_06 bundle, which was enabled by default in the 6.32 release.
SQL Changes — General¶
Fail-safe Storage: Bug Fix for Corner Cases that Result in Underbilling¶
Due to an internal system error, some permanent tables were not billed for storage for Fail-safe bytes. Specifically, if a transient table is created as a clone of a permanent table, and the permanent table is subsequently dropped, Snowflake did not bill for Fail-safe storage of the permanent table.
Billing for Fail-safe has changed as follows:
Some permanent tables were not billed for Fail-safe storage.
Customers are billed for Fail-safe storage for all permanent tables.
SQL Changes — Commands & Functions¶
CREATE DATABASE & CREATE SCHEMA Commands: OR REPLACE Clause Results in Dangling References for Policies¶
Snowflake allowed the CREATE OR REPLACE DATABASE and CREATE OR REPLACE SCHEMA commands to execute on the database or schema containing a masking policy or row access policy that protect an object in a different database or schema. For example:
A masking policy named
db1.s1.p1protects a column named
A row access policy named
db1.s1.p2protects a table named
The result was a dangling reference which caused all queries on the column or object to fail.
Note that this behavior also applied to CLONE statements such as
CREATE OR REPLACE SCHEMA S1 CLONE S2;.
The CREATE OR REPLACE DATABASE or CREATE OR REPLACE SCHEMA command fails if the result is a dangling reference on a policy-protected object. Snowflake returns either of the following error messages:
For CREATE OR REPLACE DATABASE:
Cannot drop database because: Policy '<db.schema.policy>' used by schema '<db.schema>' in another database
For CREATE OR REPLACE SCHEMA:
Cannot drop schema because: Policy '<db.schema.policy>' used by another schema '<db.schema>'
If either of the two error messages occur, query the Account Usage POLICY_REFERENCES view, use a role to unset the masking or row access policy, and then retry the CREATE OR REPLACE statement.
Query the view:
Cross-schema policy references that need to be removed prior to replacement:
select * from snowflake.account_usage.policy_references where policy_db=<policy_db> and policy_schema=<policy_schema_to_replace> and ref_schema_name != <policy_schema>;
Cross-database policy references that need to be removed prior to replacement:
select * from snowflake.account_usage.policy_references where policy_db=<policy_db_to_replace>’ and ref_database_name != <policy_db>;
Unset the policies:
For masking policies:
alter table <table_name> modify column <col_name> unset masking policy;
For row access policies:
alter table <table_name> drop all row access policies;
Retry the CREATE OR REPLACE command.
Note that, with CLONE operations, you should store the policy objects in a separate database or schema prior to running the CLONE statements.
INFER_SCHEMA Function: New ORDER_ID Column in Output¶
The output of the INFER_SCHEMA function now includes a new ORDER_ID column which indicates the column order in the staged files.
Currently, when you create a table with the column definitions derived from a set of staged files (using CREATE TABLE … USING TEMPLATE), the column order in the table is randomized. While table column ordering does not matter for Snowflake, this may cause confusion when you compare the file column order to the table column order. The new ORDER_ID column in the INFER_SCHEMA output can help you ensure tables created with the detected schema have the same column order.
You can retrieve the schema of any file by using INFER_SCHEMA, as the following example demonstrates. The output includes a new column ORDER_ID and is sorted by ORDER_ID automatically for the single schema scenario:
SELECT * FROM TABLE( INFER_SCHEMA( LOCATION=>'@***_****_STAGE/' , FILE_FORMAT=>'FFPARQUET' ) );
Also, you can create a table using the detected schema from staged files and sort the columns by ORDER_ID, as the following example demonstrates:
CREATE OR REPLACE TABLE BIG_TABLE USING TEMPLATE ( SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*)) WITHIN GROUP (ORDER BY ORDER_ID) // NEW FROM TABLE( INFER_SCHEMA(LOCATION=>'@***_****_STAGE/', FILE_FORMAT=>'FFPARQUET') ) ); DESC TABLE BIG_TABLE;
Note that sorting the columns by ORDER_ID only applies if all staged files share a single schema. If the set of staged data files includes multiple schemas with shared column names, the order represented in the ORDER_ID column might not match any single file.
SQL Changes — Usage Views & Information Schema Views/Table Functions¶
FUNCTIONS View (Account Usage): New Columns in View¶
The output of the Account Usage FUNCTIONS view now includes the following new columns:
Specifies the packages requested by the function.
Lists all packages installed by the function. Output for Python functions only.