Snowflake for Healthcare Data

-- Create masking policy for member names
CREATE OR REPLACE MASKING POLICY phi_name_mask AS (val STRING)
RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('PHI_ANALYST', 'DATA_ENGINEER', 'CLINICAL_OPS')
      THEN val                     -- authorized roles see real value
    WHEN CURRENT_ROLE() IN ('ANALYTICS_VIEWER', 'FINANCE_ANALYST')
      THEN '***MASKED***'          -- restricted roles see masked value
    ELSE NULL                      -- all others see NULL
  END;

-- Create masking policy for dates (PHI for patients over 89)
CREATE OR REPLACE MASKING POLICY phi_dob_mask AS (val DATE)
RETURNS DATE ->
  CASE
    WHEN CURRENT_ROLE() IN ('PHI_ANALYST', 'DATA_ENGINEER')
      THEN val
    ELSE DATE_TRUNC('year', val)   -- show year only for non-PHI roles
  END;

-- Apply masking policies to PHI columns
ALTER TABLE marts_db.member.dim_member
  MODIFY COLUMN mbr_first_nm SET MASKING POLICY phi_name_mask;

ALTER TABLE marts_db.member.dim_member
  MODIFY COLUMN mbr_last_nm SET MASKING POLICY phi_name_mask;

ALTER TABLE marts_db.member.dim_member
  MODIFY COLUMN mbr_dob SET MASKING POLICY phi_dob_mask;

-- Access control mapping table
CREATE TABLE security_db.access_control.user_population_access (
  user_role       VARCHAR(100),
  plan_cd         VARCHAR(20),
  allowed_state   VARCHAR(2)
);

-- Row access policy — users only see their authorized population
CREATE OR REPLACE ROW ACCESS POLICY member_population_policy
AS (plan_cd VARCHAR, mbr_state_cd VARCHAR)
RETURNS BOOLEAN ->
  CURRENT_ROLE() IN ('DATA_ENGINEER', 'PHI_ANALYST')  -- full access roles
  OR EXISTS (
    SELECT 1
    FROM security_db.access_control.user_population_access
    WHERE user_role = CURRENT_ROLE()
      AND (plan_cd = plan_cd OR plan_cd = 'ALL')
      AND (allowed_state = mbr_state_cd OR allowed_state = 'ALL')
  );

-- Apply to member dimension
ALTER TABLE marts_db.member.dim_member
  ADD ROW ACCESS POLICY member_population_policy
  ON (plan_cd, mbr_state_cd);

-- Healthcare role hierarchy
CREATE ROLE phi_analyst;         -- full PHI access, audited
CREATE ROLE data_engineer;       -- platform admin, full access
CREATE ROLE claims_analyst;      -- claims data, PHI masked
CREATE ROLE quality_analyst;     -- quality measures, PHI masked
CREATE ROLE finance_analyst;     -- aggregated financials only
CREATE ROLE executive_viewer;    -- summary dashboards only

-- Grant schema-level access
GRANT USAGE ON DATABASE marts_db TO ROLE claims_analyst;
GRANT USAGE ON SCHEMA marts_db.claims TO ROLE claims_analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA marts_db.claims TO ROLE claims_analyst;

-- Quality analysts see quality + member (masked)
GRANT USAGE ON SCHEMA marts_db.quality TO ROLE quality_analyst;
GRANT USAGE ON SCHEMA marts_db.member TO ROLE quality_analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA marts_db.quality TO ROLE quality_analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA marts_db.member TO ROLE quality_analyst;

-- Warehouse access
GRANT USAGE ON WAREHOUSE analyst_wh TO ROLE claims_analyst;
GRANT USAGE ON WAREHOUSE analyst_wh TO ROLE quality_analyst;
GRANT USAGE ON WAREHOUSE hedis_wh TO ROLE quality_analyst;

-- Cluster claims fact table on service date and payer
-- Most queries filter on date range + payer combination
ALTER TABLE marts_db.claims.fact_claim_header
  CLUSTER BY (svc_dt_key, payer_key);

-- Cluster member eligibility on plan and effective date
ALTER TABLE marts_db.member.dim_member
  CLUSTER BY (plan_cd, eff_dt);

-- Monitor clustering effectiveness
SELECT SYSTEM$CLUSTERING_INFORMATION(
  'marts_db.claims.fact_claim_header',
  '(svc_dt_key, payer_key)'
);

-- Query optimization: always filter on clustering key first
SELECT
    m.plan_cd,
    SUM(f.paid_amt)     AS total_paid,
    COUNT(DISTINCT f.mbr_key) AS member_count,
    SUM(f.paid_amt) / COUNT(DISTINCT f.mbr_key) AS pmpm
FROM fact_claim_header f
JOIN dim_member m ON m.mbr_key = f.mbr_key
WHERE f.svc_dt_key BETWEEN 20250101 AND 20251231  -- clustering key first
  AND f.payer_key = 42                              -- clustering key second
GROUP BY m.plan_cd;

-- Dynamic table: current member eligibility summary
-- Refreshes automatically within 1 hour of source changes
CREATE OR REPLACE DYNAMIC TABLE marts_db.member.current_eligibility
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
AS
SELECT
    m.mbr_id,
    m.plan_cd,
    m.grp_nbr,
    m.mbr_state_cd,
    m.eff_dt,
    m.exp_dt,
    CASE
        WHEN m.exp_dt IS NULL OR m.exp_dt > CURRENT_DATE
        THEN TRUE ELSE FALSE
    END AS is_active,
    CURRENT_TIMESTAMP AS refreshed_at
FROM marts_db.member.dim_member m
WHERE m.curr_rec_flg = TRUE;