Proving it is still right

GRANT USE CATALOG ON CATALOG dwh_prod            TO `dwh_analysts`;
GRANT USE SCHEMA  ON SCHEMA  dwh_prod.gold       TO `dwh_analysts`;
GRANT SELECT      ON SCHEMA  dwh_prod.gold       TO `dwh_analysts`;
GRANT EXECUTE     ON FUNCTION dwh_prod.gold.mask_pii TO `dwh_analysts`;

-- gold only. No grant on silver, no grant on bronze.
-- Territory scoping is membership of dwh_territory_*, not a grant.

-- 1. drive the inventory loop; run SHOW per row, concatenate client-side.
--    EXCLUDE, do not include. An allow-list of table types silently drops
--    EXTERNAL, FOREIGN and -- the ones that matter here -- MANAGED_SHALLOW_CLONE
--    and EXTERNAL_SHALLOW_CLONE, which are exactly the copies the exemption
--    discussion leaks into. Views are the only type that cannot carry a policy.
SELECT table_catalog, table_schema, table_name, table_type
FROM   dwh_prod.information_schema.tables
WHERE  table_schema = 'gold'
  AND  table_type <> 'VIEW';

-- 2. candidate personal columns carrying no pii tag. Zero rows is a pass.
--    The list is the vocabulary, not dim_customer's column list: it fires when
--    any gold table grows an email or a street. customer_name is deliberately
--    absent -- in this B2B model it is the company name, not personal data.
SELECT c.table_name, c.column_name
FROM   dwh_prod.information_schema.columns c
LEFT   JOIN dwh_prod.information_schema.column_tags 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 = 'gold'
  AND  c.column_name IN ('email', 'phone', 'street')
  AND  t.tag_name IS NULL;

-- 3. every policy AND tag change in the last 30 days, with the actor.
--    The tag verbs are not optional extras: whoever can un-tag a column can
--    switch a mask off without touching a policy or a grant, so a review that
--    watches only createPolicy/deletePolicy has a hole the size of the scheme.
SELECT event_time,
       user_identity.email    AS actor,
       action_name,
       request_params         AS params   -- policy name, or the tagged object
FROM   system.access.audit
WHERE  service_name = 'unityCatalog'
  AND  action_name IN ('createPolicy', 'deletePolicy',
                       'createEntityTagAssignment', 'deleteEntityTagAssignment')
  AND  event_date >= current_date() - INTERVAL 30 DAYS
ORDER  BY event_time DESC;

-- One row per metric, so the output is a posture SNAPSHOT you can trend.
-- These are the same questions the Hub's Data page consolidates; computing
-- them yourself is what makes the Hub an upgrade rather than a migration.

-- 1. classification coverage: candidate personal columns carrying a pii tag.
SELECT 'pii_coverage_pct' AS metric,
       ROUND(100.0 * count_if(t.tag_name IS NOT NULL) / nullif(count(*), 0), 1) AS value
FROM   dwh_prod.information_schema.columns c
LEFT   JOIN dwh_prod.information_schema.column_tags 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 = 'gold'
  AND  c.column_name IN ('email', 'phone', 'street')

UNION ALL
-- 2. ownership decay: production objects owned by a human, not a group.
SELECT 'tables_owned_by_individual',
       count(*)
FROM   dwh_prod.information_schema.tables
WHERE  table_schema IN ('silver', 'gold')
  AND  table_owner LIKE '%@%'          -- a group name has no @

UNION ALL
-- 3. discoverability: gold tables shipped with no COMMENT.
SELECT 'gold_tables_without_comment',
       count_if(comment IS NULL OR trim(comment) = '')
FROM   dwh_prod.information_schema.tables
WHERE  table_schema = 'gold'

UNION ALL
-- 4. staleness: gold tables not written in 48h. Freshness is governance too --
--    an abandoned table is a table nobody can vouch for.
SELECT 'gold_tables_stale_48h',
       count_if(last_altered < current_timestamp() - INTERVAL 48 HOURS)
FROM   dwh_prod.information_schema.tables
WHERE  table_schema = 'gold';