Data Governance

-- Role-Based Access Control (RBAC) in Snowflake
-- Create roles with specific permissions

-- Role hierarchy
CREATE ROLE data_analyst;
CREATE ROLE data_engineer;
CREATE ROLE data_admin;

-- Grant role inheritance
GRANT ROLE data_analyst TO ROLE data_engineer;
GRANT ROLE data_engineer TO ROLE data_admin;

-- Database-level access
GRANT USAGE ON DATABASE analytics TO ROLE data_analyst;
GRANT ALL ON DATABASE analytics TO ROLE data_engineer;

-- Schema-level access
GRANT USAGE ON SCHEMA analytics.marts TO ROLE data_analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.marts TO ROLE data_analyst;

-- Analysts cannot access staging or raw schemas
-- GRANT USAGE ON SCHEMA analytics.staging TO ROLE data_engineer;

-- Column-level masking for PII
CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING)
RETURNS STRING ->
    CASE
        WHEN CURRENT_ROLE() IN ('DATA_ADMIN', 'DATA_ENGINEER') THEN val
        ELSE REGEXP_REPLACE(val, '.+@', '***@')
    END;

ALTER TABLE analytics.dim_customer
    MODIFY COLUMN email SET MASKING POLICY email_mask;

-- Row-level security: Each team sees only their region's data
CREATE OR REPLACE ROW ACCESS POLICY region_policy AS (region VARCHAR)
RETURNS BOOLEAN ->
    CASE
        WHEN CURRENT_ROLE() = 'DATA_ADMIN' THEN TRUE
        WHEN CURRENT_ROLE() = 'ANALYST_NA' AND region = 'North America' THEN TRUE
        WHEN CURRENT_ROLE() = 'ANALYST_EU' AND region = 'Europe' THEN TRUE
        ELSE FALSE
    END;

ALTER TABLE analytics.fct_orders
    ADD ROW ACCESS POLICY region_policy ON (region);

# DataHub metadata definition for a dataset
dataset:
  urn: "urn:li:dataset:(urn:li:dataPlatform:snowflake,analytics.fct_orders,PROD)"
  name: fct_orders
  description: >
    Fact table containing all completed customer orders.
    Grain: one row per order. Updated hourly.
  platform: snowflake
  schema: analytics
  database: ANALYTICS

  ownership:
    owner: "@data-engineering-team"
    steward: "@alice-johnson"

  tags:
    - "pii:false"
    - "tier:gold"
    - "domain:commerce"
    - "refresh:hourly"

  glossaryTerms:
    - "Revenue"
    - "Customer Orders"
    - "E-commerce"

  columns:
    - name: order_id
      type: BIGINT
      description: "Unique identifier for each order"
      tags: ["primary-key"]
    - name: customer_id
      type: BIGINT
      description: "Foreign key to dim_customer"
    - name: total_amount
      type: DECIMAL(12,2)
      description: "Total order amount in USD after discounts"
      glossaryTerms: ["Revenue"]
    - name: order_date
      type: DATE
      description: "Date the order was placed"

  lineage:
    upstream:
      - "raw.app_public.orders"
      - "raw.app_public.order_items"
    downstream:
      - "analytics.monthly_revenue_report"
      - "ml.features.customer_orders"

-- GDPR / CCPA Compliance Patterns

-- 1. PII Classification: Tag columns containing personal data
COMMENT ON COLUMN analytics.dim_customer.email IS
    'PII: email address. Classification: CONFIDENTIAL. Retention: 3 years.';
COMMENT ON COLUMN analytics.dim_customer.customer_name IS
    'PII: full name. Classification: CONFIDENTIAL. Retention: 3 years.';

-- 2. Right to Deletion (GDPR Article 17)
-- Delete all data for a specific customer
CREATE OR REPLACE PROCEDURE delete_customer_data(customer_id_to_delete BIGINT)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
    -- Soft delete in dimension
    UPDATE analytics.dim_customer
    SET customer_name = 'DELETED',
        email = 'deleted@deleted.com',
        city = 'DELETED',
        is_deleted = TRUE,
        deleted_at = CURRENT_TIMESTAMP
    WHERE customer_id = :customer_id_to_delete;

    -- Anonymize in fact tables (preserve aggregates)
    UPDATE analytics.fct_orders
    SET customer_id = -1  -- Anonymized customer
    WHERE customer_id = :customer_id_to_delete;

    RETURN 'Customer ' || :customer_id_to_delete || ' data deleted';
END;

-- 3. Data Retention: Automatically purge old data
DELETE FROM analytics.raw_events
WHERE event_date < CURRENT_DATE - INTERVAL '2 years';

-- 4. Audit logging: Track who accessed what data
CREATE TABLE governance.access_log (
    log_id      BIGINT GENERATED ALWAYS AS IDENTITY,
    user_name   VARCHAR(100),
    query_text  TEXT,
    tables_accessed TEXT[],
    timestamp   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    row_count   BIGINT
);