18Audit & lineage

SELECT event_time,
       action_name,
       request_params.full_name_arg  AS securable,
       response.status_code          AS status,
       source_ip_address
FROM   system.access.audit
WHERE  service_name        = 'unityCatalog'
  AND  user_identity.email = 'sp-dwh-prod-loader'
  AND  event_date >= current_date() - INTERVAL 7 DAYS
ORDER  BY event_time DESC
LIMIT  200;

-- information_schema is PER CATALOG: this must be dwh_prod's, not system's.
WITH pii_tables AS (
  SELECT DISTINCT
         catalog_name || '.' || schema_name || '.' || table_name AS full_name
  FROM   dwh_prod.information_schema.column_tags
  -- The governed tag is 'pii' with values email | phone | address (see the
  -- Access & ABAC route). Match on the KEY, not on a value -- a value filter
  -- silently drops any class you add later, and this query failing open looks
  -- exactly like a clean review.
  WHERE  tag_name = 'pii'
),
touches AS (
  SELECT user_identity.email          AS principal,
         request_params.full_name_arg AS full_name,
         event_time,
         response.status_code         AS status
  FROM   system.access.audit
  WHERE  service_name = 'unityCatalog'
    AND  action_name IN ('getTable', 'generateTemporaryTableCredential')
    AND  event_date >= current_date() - INTERVAL 90 DAYS
)
SELECT t.principal,
       t.full_name,
       count_if(t.status =  200) AS allowed,
       count_if(t.status <> 200) AS refused,
       max(t.event_time)         AS last_touch
FROM   touches t
JOIN   pii_tables p ON p.full_name = t.full_name
GROUP  BY t.principal, t.full_name
ORDER  BY allowed DESC;

SELECT event_time,
       user_identity.email                 AS changed_by,
       request_params.securable_type       AS securable_type,
       request_params.securable_full_name  AS securable,
       request_params.changes              AS changes,
       source_ip_address
FROM   system.access.audit
WHERE  service_name = 'unityCatalog'
  AND  action_name IN ('updatePermissions', 'updateSecurable',
                       'createCatalog', 'deleteCatalog')
  AND  event_date >= current_date() - INTERVAL 1 DAY
  AND  coalesce(request_params.securable_full_name, '') LIKE 'dwh_prod%'
ORDER  BY event_time DESC;

SELECT DISTINCT
       source_table_full_name,
       source_column_name,
       entity_type,
       entity_id
FROM   system.access.column_lineage
WHERE  target_table_full_name = 'dwh_prod.gold.fct_order_line'
  AND  target_column_name     = 'amount_eur'
  AND  event_date >= current_date() - INTERVAL 90 DAYS
ORDER  BY source_table_full_name;

SELECT target_table_full_name AS downstream_table,
       entity_type,
       count(*)        AS runs,
       max(event_date) AS last_seen
FROM   system.access.table_lineage
WHERE  source_table_full_name = 'dwh_prod.silver.sales_order_line'
  AND  event_date >= current_date() - INTERVAL 90 DAYS
GROUP  BY target_table_full_name, entity_type
ORDER  BY last_seen DESC;

INSERT INTO gold.dim_customer (
  customer_sk, customer_id, customer_name, country,
  customer_segment, _tech_valid_from, _tech_is_current
)
SELECT 'UNKNOWN',
       'UNKNOWN',
       'Unknown customer',
       'UNKNOWN',
       'UNKNOWN',   -- NOT 'C'. A real segment value here folds every
                    -- unresolved line into a segment finance reports on.
       TIMESTAMP '2020-01-01 00:00:00',
       true
FROM   (SELECT 1) AS seed          -- a WHERE clause needs a relation
WHERE  NOT EXISTS (
  SELECT 1 FROM gold.dim_customer WHERE customer_sk = 'UNKNOWN'
);

-- Evidence: governance group only.
GRANT USE CATALOG ON CATALOG system        TO `dwh-governance`;
GRANT USE SCHEMA  ON SCHEMA  system.access TO `dwh-governance`;
GRANT SELECT ON TABLE system.access.audit          TO `dwh-governance`;
GRANT SELECT ON TABLE system.access.table_lineage  TO `dwh-governance`;
GRANT SELECT ON TABLE system.access.column_lineage TO `dwh-governance`;

-- Working tool: engineers get lineage, and not audit.
GRANT USE CATALOG ON CATALOG system        TO `dwh-engineers`;
GRANT USE SCHEMA  ON SCHEMA  system.access TO `dwh-engineers`;
GRANT SELECT ON TABLE system.access.table_lineage  TO `dwh-engineers`;
GRANT SELECT ON TABLE system.access.column_lineage TO `dwh-engineers`;