2022_06 Bundle¶

Replication Groups: Objects Types Limited to Databases and Shares for Accounts that are not Business Critical Edition¶

The account replication feature supports the replication of account objects including users, roles, databases, and shares. For the full list of supported object types, see Replicated Objects. Replication groups may include any or all supported object types.

This behavior has changed as follows:

Previously:

All supported account replication object types could be included in a replication group.

Currently:

Support for object types that can be added to a replication group for accounts that are not Business Critical Edition (or higher) is limited to databases and shares.

See the account replication feature support table for more details on feature support by edition.

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:

Previously:

Some permanent tables were not billed for Fail-safe storage.

Currently:

Customers are billed for Fail-safe storage for all permanent tables.

CREATE DATABASE & CREATE SCHEMA Commands: OR REPLACE Clause Results in Dangling References for Policies¶

The behavior of the CREATE DATABASE and CREATE SCHEMA commands has changed as follows:

Previously:

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.p1 protects a column named db2.s1.t1.c1.

• A row access policy named db1.s1.p2 protects a table named db2.s1.t1.

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

Currently:

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.

For example:

1. 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>;
     

     Copy

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

     Copy

2. Unset the policies:

   • For masking policies:

     alter table <table_name> modify column <col_name> unset masking policy;
     

     Copy

   • For row access policies:

     alter table <table_name> drop all row access policies;
     

     Copy

3. 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'
  )
  );

Copy

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;

Copy

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.

FUNCTIONS View (Account Usage): New Columns in View¶

The output of the Account Usage FUNCTIONS view now includes the following new columns: