Marking the data

-- The governed tag and its permitted values are created ONCE at account
-- level (see the access route); ASSIGN is granted to a group, not to everyone.
--   CREATE GOVERNED TAG pii VALUES ('email', 'phone', 'address');
-- Adding a value later is one declarative statement — the new list REPLACES
-- the old one, so never hand-edit it from memory:
--   ALTER GOVERNED TAG pii SET VALUES ('email', 'phone', 'address', 'name');

SET TAG ON COLUMN dwh_prod.gold.dim_customer.email   pii = email;
SET TAG ON COLUMN dwh_prod.gold.dim_customer.phone   pii = phone;
SET TAG ON COLUMN dwh_prod.gold.dim_customer.street  pii = address;

-- Use SET TAG, in DDL. NOT:
--   ALTER TABLE ... ALTER COLUMN email SET TAGS ('pii' = 'email')
-- That is the free-text tag syntax: it validates nothing against the governed
-- value list, so it accepts a value CREATE GOVERNED TAG would have refused.
-- Whether an assignment made that way is ALSO invisible to ABAC is not settled
-- here: the docs say creating a governed tag over a key that already carries
-- free-text assignments converts those assignments to governed ones, which
-- reads like one shared namespace rather than two. Reproduce it on the client
-- workspace before asserting either version to them. SET TAG is the statement
-- specified to do this job; the argument above is why it is also the safe one.

SELECT c.table_name, c.column_name
FROM   dwh_prod.information_schema.columns AS c
LEFT   JOIN dwh_prod.information_schema.column_tags AS t
       ON  t.schema_name = c.table_schema
       AND t.table_name  = c.table_name
       AND t.column_name = c.column_name
       AND t.tag_name    = 'pii'
WHERE  c.table_schema IN ('silver', 'gold')
  AND  c.column_name RLIKE '(email|phone|mobile|street|birth|iban)'
  AND  t.tag_value IS NULL;   -- any row here fails the build

CREATE POLICY mask_classified_pii
ON SCHEMA dwh_prod.gold
COMMENT 'Redact what the classifier found, until a steward maps it onto pii'
COLUMN MASK dwh_prod.gold.mask_pii
TO `account users`
EXCEPT `dwh_pii_readers`, `dwh_prod_pipelines`
FOR TABLES
MATCH COLUMNS has_tag('class.name')
           OR has_tag('class.email_address')
           OR has_tag('class.phone_number') AS class_col
ON COLUMN class_col;

-- Count the conditions: three, and three is the documented ceiling per
-- MATCH COLUMNS clause. A fourth classification needs a second policy, and two
-- column-mask policies that both resolve on one column error instead of
-- masking — for the analyst, on a dashboard, not for whoever added the policy.