Snowflake Conditional Column Level Masking Use Cases¶

This document explains conditional column-level masking scenarios for the Snowflake connector, focusing on cases where masking conditions depend on one or more columns.

Case 1: Masking a Column Based on Its Own Value¶

This scenario demonstrates how to mask a column based on a condition applied to the same column.
In this example, the requirement is:

Mask the FirstName column only when the value of FirstName is Sam.

Objective¶

• Mask only those records where FirstName = 'Sam'
• Other records must remain visible and unchanged
• Masked value should appear as a constant string (e.g., "XXXXX")

Example Input Data¶

Expected Output After Masking¶

Masking Expression Used¶

This case is supported using the existing Custom Masking Expression:

1

CASE WHEN {col} = 'Sam' THEN 'XXXXX' ELSE {col} END;

Case 2: Masking a Column Based on the Value of Another Column¶

This scenario demonstrates how to mask one column based on the value of a different column.

In this example, the requirement is:

Mask the Email column only when the value of FirstName is Sam.

This is a conditional masking rule involving multiple columns, which requires additional handling in Privacera.

Objective¶

• Mask Email only when FirstName = 'Sam'
• All other Email values must remain visible and unchanged
• Masked value should appear as a constant string (e.g., "XXXXXXXXXX")

Example Input Data¶

Expected Output After Masking¶

Issue Description – Case 2 (Multi-Column Conditional Masking)¶

In Case 2, the masking policy attempts to mask the Email column based on the value of another column, FirstName.

This scenario introduces a dependency on two columns:

• Email
  The column explicitly defined in the masking policy and represented by {col}.

• FirstName
  A secondary column referenced inside the custom masking condition.

Limitation¶

The masking framework supports only one column per masking policy, which is the column defined in the policy itself. As a result:

• Only the Email column is available to the masking engine
• The FirstName column referenced inside the expression cannot be resolved
• The backend Snowflake native masking policy cannot be generated correctly

Impact¶

Due to this limitation, custom masking expressions that reference multiple columns fail, causing Case 2 to not work as expected.

Fix Description¶

As a part of the fix, a token (alias) will be introduced for use inside custom masking expressions.
This alias helps identify and extract any column names referenced within the expression, allowing masking logic to correctly process conditions involving multiple columns.

For example:

1

CASE WHEN obj.FIRSTNAME = 'Sam' THEN 'XXXXXXXXX' ELSE {col} END;

In above expression, obj is the token(alias) which is used in conjunction with the conditional column. This token can be configurable via properties

Configuration Properties¶

The alias token used in custom masking expressions can be configured using the following properties.

Snowflake Property¶

1

ranger.policysync.connector.0.masking.alias.token: obj

Privacera Manager Variable¶

1

CONNECTOR_SNOWFLAKE_MASKING_ALIAS_TOKEN: "obj"

It is mandatory to provide the column name in quotes in expression if column name case sensitive while creating the table or adding column into table.

For eg.

1

CASE WHEN obj."FirstName" = 'Sam' THEN 'XXXXXXXXX' ELSE {col} END