Using same masking policy for different data types

Question

For a project I have to set a masking policy on multiple columns. These columns have different data types (e.g., varchar, date, number...). Is it possible to use the same masking policy on these different columns?

Here you can see my masking policy so far, but this only works for type varchar. I created different masking policies for each data type, but I would like to use 1 masking policy if possible.

create or replace masking policy name_mask as (val varchar) returns varchar ->
    case
        when current_role() in ('ROLE_1') then val
        when current_role() in ('ROLE_2') then md5(val)
        else '*********'
    end;

It should be noted that I would be not aware of the data type of the column before setting this masking policy. The masking policy should have to work for every data type.

Answer 1

This is not possible under the current implementation of masking policies.

When you define a masking policy, you need to declare the returning type. The returning type needs to match the type of the column it's being applied to:

Currently, Snowflake does not support different input and output data types in a masking policy, such as defining the masking policy to target a timestamp and return a string (e.g. MASKED); the input and output data types must match. https://docs.snowflake.com/en/sql-reference/sql/create-masking-policy.html

It's an interesting feature request tho!