Snowflake Masking policy: replace int column by '*****'

Question

I'm using the snowflake masking policy to hide value from unauthorized role, but I can't find how replace int column by '*****'. I can replace it with null, but using this can be confusing (not knowing if it is really null or just hide).

The policy I'm using to replace int column by null:

create or replace masking policy values_mask as (val int) returns int ->
  case
    when current_role() in ('ADMIN') then val
    else null
  end;

How I apply the policy:

alter table databse.schema.table modify column value set masking policy values_mask;

I'm want else to retrun '****'. I have tried returns string or other similar change. But afterwards It creates errors.

Answer 1

The output data type must match input data type:

CREATE MASKING POLICY:

RETURNS arg_type_to_mask

The return data type must match the input data type of the first column that is specified as an input column.

...

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.

---

The value '****'(string) is definitely not a valid integer value(column data type), therefore masking policy with requested signature will not work.