How do I set every row in a column equal to a specific string?

Question

I have some columns I want to set to the string 'redacted' because they contain personal information.

Currently I am creating views in snowflake from the original tables via a select statement how can I add a specified string to the particular columns?

original

name    number
john   | 1
mary   | 2
nikhil | 3

desired state

name      number
Redacted | 1
Redacted | 2
Redacted | 3

current code to materialize views

CREATE OR REPLACE SECURE VIEW "DB"."Schema"."Table"
AS
SELECT
    Column1,
    Column2,
    Column3,
FROM "DB"."Schema"."Table"

Answer 1

Snowflake allows dynamic masking of PII.

Generally speaking you must:

1: Grant Masking Policy Privileges to Custom Role

-- create a masking policy administrator custom role

CREATE ROLE masking_admin;

-- grant privileges to masking_admin role.

GRANT CREATE MASKING POLICY on SCHEMA <schema_name> to ROLE masking_admin;

GRANT APPLY MASKING POLICY on ACCOUNT to ROLE masking_admin;

-- allow table_owner role to set or unset the ssn_mask masking policy (optional)

GRANT APPLY ON MASKING POLICY ssn_mask to ROLE table_owner;

2: Create a Masking Policy

create or replace masking policy email_mask as (val string) returns string ->
  case
    when current_role() in ('ANALYST') then val
    else '*********'
  end;

3: Apply the Masking Policy to a Table or View Column

-- apply masking policy to a table column

alter table if exists user_info modify column email set masking policy email_mask;

-- apply the masking policy to a view column

alter view user_info_v modify column email set masking policy email_mask;

In the example above, which I copied entirely from this page, we have created a role for security admin of masking rules, then used that role to create a masking policy which allows the ANALYST role to see the value of a column but shows asterisks to everyone else not in the ANALYST role, and then applied that masking rule to the email column of a table and a view.

The benefit of doing it this way, is you can define a consistent approach to how such information is masked (via the policy) and you don't have to hard-code changes to your views.