Behavior Change Management

This document explains how to check whether a particular behavior change bundle is enabled in your account and how to enable or disable it.

Overview

Snowflake implements behavior changes monthly in bundles included in regularly-scheduled releases. During the testing period and opt-out period for each behavior change bundle, you can enable or disable the bundle in your account. This document explains how to check whether a particular bundle is enabled in your account and how to enable or disable it.

In this document, the name of the behavior change bundle is in the form YYYY_NN. For the names of the currently available behavior change bundles, see Behavior Change Log.

Note

Behavior changes in bundles cannot be enabled/disabled individually. To enable/disable a behavior change, you must enable/disable the bundle containing the change.

Checking the Status of a Behavior Change Bundle in Your Account

To check whether a specific behavior change bundle is enabled in your account, call the SYSTEM$BEHAVIOR_CHANGE_BUNDLE_STATUS function. For example, to check the status of the bundle named 2024_02:

SELECT SYSTEM$BEHAVIOR_CHANGE_BUNDLE_STATUS('2024_02');
Copy
+-------------------------------------------------+
| SYSTEM$BEHAVIOR_CHANGE_BUNDLE_STATUS('2024_02') |
|-------------------------------------------------|
| DISABLED                                        |
+-------------------------------------------------+

To check the status of all currently available behavior change bundles, call the SYSTEM$SHOW_ACTIVE_BEHAVIOR_CHANGE_BUNDLES function:

SELECT SYSTEM$SHOW_ACTIVE_BEHAVIOR_CHANGE_BUNDLES();
Copy
+--------------------------------------------------------------------------------------------------------------+
| SYSTEM$SHOW_ACTIVE_BEHAVIOR_CHANGE_BUNDLES()                                                                 |
|--------------------------------------------------------------------------------------------------------------|
| [{"name":"2023_08","isDefault":true,"isEnabled":true},{"name":"2024_01","isDefault":false,"isEnabled":true}] |
+--------------------------------------------------------------------------------------------------------------+

Enabling a Behavior Change Bundle in Your Account

To enable a particular behavior change in your account, call the SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE function. For example, to enable the bundle named 2024_02:

SELECT SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2024_02');
Copy
+-------------------------------------------------+
| SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2024_02') |
|-------------------------------------------------|
| ENABLED                                         |
+-------------------------------------------------+

Disabling a Behavior Change Bundle in Your Account

To disable a particular behavior change in your account, call the SYSTEM$DISABLE_BEHAVIOR_CHANGE_BUNDLE. For example, to disable the bundle named 2024_02:

SELECT SYSTEM$DISABLE_BEHAVIOR_CHANGE_BUNDLE('2024_02');
Copy
+-------------------------------------------------+
| SYSTEM$DISABLE_BEHAVIOR_CHANGE_BUNDLE('2024_02')|
|-------------------------------------------------|
| DISABLED                                        |
+-------------------------------------------------+

Determining the Current Version of Your Account

To check the current version of Snowflake that is in your account, call the CURRENT_VERSION function. For example:

SELECT CURRENT_VERSION();
Copy
+-------------------+
| CURRENT_VERSION() |
|-------------------|
| 8.5.1             |
+-------------------+
Copy

Mitigating Masking Policy Return Value Updates

In a future behavior change release bundle, Snowflake plans to return values for length, precision, and scale in masking policy conditions (collectively: “return value updates”). When these return value updates are made, a query on a column protected by a masking policy fails when the following are true:

  • The bundle is enabled.

  • The masking policy conditions return a value whose length, scale, or precision is greater than the length, scale, or precision of the column to which the masking policy is assigned. For example:

    Create a policy:

    CREATE MASKING POLICY MP AS (s string)
    RETURNS STRING -> 'abcdef';
    
    Copy

    Assign the policy:

    CREATE TABLE t(col1 string(2));
    
    ALTER TABLE t MODIFY COLUMN col1 SET MASKING POLICY mp;
    
    Copy

    Query the column (fails):

    SELECT * FROM t;
    
    Copy

To determine the impact of this change and provide enough time to update the masking policy conditions to protect data, query the SNOWFLAKE.BCR_ROLLOUT.BCR_2022_07_DDM_ROLLOUT view to understand how the future return value updates affect your account.

The BCR_2022_07_DDM_ROLLOUT view is temporary. Snowflake will remove the view when the return value updates are generally enabled in a future behavior change bundle. At this point, you will not be able to query the view to determine affected columns and policies or prevent column query or masking policy assignment operation failures due to return value updates.

The view records data starting from July 2022. If a query on the view takes a long time to complete, you can specify the start date and end date session variables using a SET command. These variables help to reduce the number of rows to evaluate when you query the view. For example:

SET START_DATE = '2023-02-28';
SET END_DATE = '2023-11-30';
Copy

Identify Masking Policy & Column Associations

To query the view and mitigate the upcoming return value changes, do the following:

  1. Query the SNOWFLAKE.BCR_ROLLOUT.BCR_2022_07_DDM_ROLLOUT view. For example:

    USE ROLE ACCOUNTADMIN;
    SET START_DATE = '2023-02-28';
    SET END_DATE = '2023-11-30';
    SELECT * FROM SNOWFLAKE.BCR_ROLLOUT.BCR_2022_07_DDM_ROLLOUT;
    
    Copy
  2. Evaluate the REASON column in the BCR_2022_07_DDM_ROLLOUT View Reference section to determine what update needs to be made to the masking policy conditions.

  3. Update the masking policy conditions with an ALTER MASKING POLICY statement to ensure the column data remains protected and that policy assignment operations or protected column queries do not fail.

  4. Test the new policy conditions by querying the table columns to which the masking policies are assigned.

BCR_2022_07_DDM_ROLLOUT View Reference

The BCR_2022_07_DDM_ROLLOUT view (in the SNOWFLAKE.BCR_ROLLOUT schema) records information starting on July 15, 2022 and contains the following columns:

Column

Data type

Description

POLICY_NAME

VARCHAR

The name of the policy.

POLICY_ID

NUMBER

Internal/system-generated identifier for the policy.

POLICY_SCHEMA

VARCHAR

The parent schema of the policy.

POLICY_DATABASE

VARCHAR

The parent database of the policy.

POLICY_BODY

VARIANT

The conditions of the policy to mask or unmask the column data.

COLUMN_NAME

VARCHAR

The name of the column that has the policy.

TABLE_NAME

VARCHAR

The name of the table.

TABLE_ID

NUMBER

Internal/system-generated identifier for the table.

TABLE_SCHEMA

VARCHAR

The parent schema of the table.

TABLE_DATABASE

VARCHAR

The parent database of the table.

REASON

VARCHAR

Possible reason for the mismatch.

The REASON column can have one of the following values as specified by the REASON_MESSAGE column in the following table. The additional columns in the table below are to help administrators decide how to interpret the REASON column and update the masking policy conditions:

Reason Message

Cause

Action

POLICY_ASSOCIATION_ON_NOT_NULL_COLUMN

A masking policy is assigned to a NOT NULL column.

Verify the masking policy conditions do not return a NULL value anywhere in the policy conditions.

LENGTH_MISMATCH

A masking policy assigned to a column whose data type is VARCHAR or BINARY returns data with a larger length than the length of the column.

Modify the masking policy conditions to ensure the length of the return value has a length less than or equal to the length of the column.

PRECISION_MISMATCH

A masking policy assigned to a column whose data type is NUMBER returns a larger precision data than the precision of the column.

Modify the policy conditions to ensure the precision of the return value is less than or equal to the precision of the column.

SCALE_MISMATCH

A policy assigned to a column whose data type is NUMBER or TIMESTAMP returns data with a larger scale than the scale of the column.

Modify the policy conditions to ensure the scale of the return value is less than or equal to the scale of the column.

Unknown mismatch. Manual investigation recommended.

The masking policy returns a NULL value or the masking policy conditions contain a subquery.

Modify the policy so that the return value is less than or equal to the length, precision, or scale of the column.