RBAC Role Hierarchy - xo-data Snowflake Implementation¶

Date: 2025-11-26 Status: Design Phase Context: Defining roles and permissions before loading data to new BRONZE/SILVER/GOLD structure

Role Hierarchy Diagram¶

ACCOUNTADMIN (built-in, emergency only)
    │
    ├─ SECURITYADMIN (built-in)
    │      │
    │      └─ XO_SECURITY_ADMIN (custom, manages all xo-data roles)
    │
    └─ SYSADMIN (built-in)
           │
           ├─ XO_DATA_ENGINEER (custom, parent role)
           │      │
           │      ├─ XO_DATA_ENGINEER_DEV (custom)
           │      └─ XO_DATA_ENGINEER_PROD (custom, read-only on prod)
           │
           ├─ XO_DATA_ANALYST (custom, parent role)
           │      │
           │      ├─ XO_ANALYST_CONDENAST (custom, CND_DB only)
           │      ├─ XO_ANALYST_WARBYPARKER (custom, WBP_DB only)
           │      └─ XO_ANALYST_EXECUTIVE (custom, cross-client aggregates)
           │
           └─ XO_SERVICE_ACCOUNT (custom, parent role)
                  │
                  ├─ XO_AIRFLOW_LOADER_DEV (custom, writes to *_DEV.BRONZE)
                  ├─ XO_AIRFLOW_LOADER_PROD (custom, writes to *_PROD.BRONZE)
                  ├─ XO_DBT_TRANSFORMER_DEV (custom, transforms *_DEV)
                  └─ XO_DBT_TRANSFORMER_PROD (custom, transforms *_PROD)

Hierarchy principles: - Parent roles inherit permissions from child roles - XO_ prefix distinguishes our custom roles from Snowflake built-ins - Environment separation: DEV vs PROD roles - Client isolation: Analyst roles per client database

Role Definitions¶

1. XO_SECURITY_ADMIN¶

Purpose: Manage user access, create roles, grant permissions

Users: Security team, senior data engineering leads

Permissions: - ✅ Create and manage users - ✅ Create and manage custom roles - ✅ Grant/revoke permissions - ❌ Cannot delete databases (no SYSADMIN access) - ❌ Cannot access billing (no ACCOUNTADMIN)

Inherits from: SECURITYADMIN

Use case: When you need to onboard a new analyst or service account

2. XO_DATA_ENGINEER (Parent Role)¶

Purpose: Base permissions shared by all data engineers

Users: Data engineering team members

Permissions: - ✅ USAGE on all databases (DEV and PROD) - ✅ USAGE on all schemas - ✅ USAGE on warehouses (XO_DEV_WH, XO_PROD_WH) - ✅ SELECT on all layers (to validate transformations)

Inherits from: SYSADMIN

Note: This is a parent role - actual engineers get DEV or PROD child roles

3. XO_DATA_ENGINEER_DEV¶

Purpose: Full access to development environment for pipeline development

Users: All data engineers (your account, team members)

Permissions:

On *_DEV databases (CND_DB_DEV, WBP_DB_DEV, CORE_DB_DEV): - ✅ CREATE TABLE in BRONZE schemas - ✅ SELECT, INSERT, UPDATE, DELETE on BRONZE tables - ✅ CREATE TABLE in SILVER schemas (for testing dbt locally) - ✅ SELECT, INSERT, UPDATE, DELETE on SILVER tables - ✅ CREATE TABLE in GOLD schemas (for testing dbt locally) - ✅ SELECT, INSERT, UPDATE, DELETE on GOLD tables - ✅ CREATE STAGE, CREATE FILE FORMAT (for COPY INTO testing) - ✅ USAGE on XO_DEV_WH warehouse

On *_PROD databases: - ✅ SELECT on all layers (read-only, for debugging) - ❌ No CREATE, INSERT, UPDATE, DELETE (prod writes via CI/CD only)

Inherits from: XO_DATA_ENGINEER

Use case: Daily development work - build pipelines, test transformations, debug issues

4. XO_DATA_ENGINEER_PROD¶

Purpose: Read-only production access for debugging (optional role)

Users: Data engineers needing to investigate production issues

Permissions:

On *_PROD databases: - ✅ SELECT on BRONZE/SILVER/GOLD - ✅ USAGE on schemas - ✅ USAGE on XO_PROD_WH (small warehouse for queries) - ❌ No write operations (CREATE, INSERT, UPDATE, DELETE)

Inherits from: XO_DATA_ENGINEER

Use case: Debug production data issues without risk of accidental writes

Note: This is optional - you may decide all prod writes go through CI/CD only

5. XO_DATA_ANALYST (Parent Role)¶

Purpose: Base permissions shared by all analysts

Users: Client stakeholders, internal analysts

Permissions: - ✅ USAGE on assigned databases - ✅ SELECT on GOLD schemas (aggregated metrics, no PII) - ✅ USAGE on XO_ANALYST_WH warehouse - ❌ No access to BRONZE (raw PII) - ❌ No write operations anywhere

Inherits from: PUBLIC (minimal base)

Note: This is a parent role - actual analysts get client-specific child roles

6. XO_ANALYST_CONDENAST¶

Purpose: CondeNast client stakeholder access - their data only

Users: CondeNast managers, their BI tools (Tableau/Power BI)

Permissions:

On CND_DB_PROD: - ✅ USAGE on database - ✅ USAGE on GOLD schema - ✅ SELECT on all GOLD tables (DM_SCORECARD_GPP, DM_METRICS_DAILY, etc.) - ⚠️ Maybe SELECT on SILVER (if senior analyst needs transaction-level data) - ❌ No access to BRONZE (PII protection)

On WBP_DB_PROD: - ❌ No access (client isolation)

On CORE_DB_PROD: - ✅ SELECT on GOLD.DIM_CLIENTS (shared dimension) - ❌ No access to other CORE data

Inherits from: XO_DATA_ANALYST

Use case: CondeNast dashboard users, report consumers

7. XO_ANALYST_WARBYPARKER¶

Purpose: Warby Parker client stakeholder access - their data only

Users: Warby Parker managers, their BI tools

Permissions:

On WBP_DB_PROD: - ✅ USAGE on database - ✅ USAGE on GOLD schema - ✅ SELECT on all GOLD tables (DM_METRICS_DAILY, etc.) - ⚠️ Maybe SELECT on SILVER (if senior analyst needs it) - ❌ No access to BRONZE (PII protection)

On CND_DB_PROD: - ❌ No access (client isolation)

On CORE_DB_PROD: - ✅ SELECT on GOLD.DIM_CLIENTS (shared dimension)

Inherits from: XO_DATA_ANALYST

Use case: Warby Parker dashboard users, report consumers

8. XO_ANALYST_EXECUTIVE¶

Purpose: Internal leadership - cross-client aggregate metrics only

Users: Your company's CEO, CFO, executive team

Permissions:

On CORE_DB_PROD: - ✅ SELECT on GOLD schema (cross-client aggregates) - DM_HEADCOUNT (total headcount across all clients) - DM_REVENUE (aggregated revenue metrics) - DM_CLIENT_PERFORMANCE (client comparison metrics)

On CND_DB_PROD and WBP_DB_PROD: - ✅ SELECT on high-level GOLD tables only (no client-specific details) - Example: Monthly totals, not individual conversations

Inherits from: XO_DATA_ANALYST

Use case: Executive dashboards showing company-wide performance

Important: This role sees aggregated data across clients but not client-specific PII or transactions

9. XO_AIRFLOW_LOADER_DEV¶

Purpose: Airflow service account for loading raw data to BRONZE (development)

Users: Airflow service account (not a person)

Permissions:

On *_DEV databases: - ✅ USAGE on database - ✅ USAGE on BRONZE schema - ✅ CREATE TABLE in BRONZE (for new pipeline tables) - ✅ INSERT into BRONZE tables (append-only) - ✅ SELECT on BRONZE (to validate loads) - ✅ CREATE STAGE, CREATE FILE FORMAT (for COPY INTO) - ✅ USAGE on XO_DEV_WH warehouse - ❌ No UPDATE or DELETE (pipelines should append, not modify) - ❌ No access to SILVER/GOLD (dbt handles that)

On *_PROD databases: - ❌ No access (separate service account for prod)

Inherits from: XO_SERVICE_ACCOUNT

Use case: Airflow DAGs running in dev environment, loading from APIs to BRONZE

10. XO_AIRFLOW_LOADER_PROD¶

Purpose: Airflow service account for loading raw data to BRONZE (production)

Users: Airflow service account (not a person)

Permissions:

On *_PROD databases: - ✅ USAGE on database - ✅ USAGE on BRONZE schema - ✅ CREATE TABLE in BRONZE (for new pipeline tables, via CI/CD approval) - ✅ INSERT into BRONZE tables (append-only) - ✅ SELECT on BRONZE (to validate loads) - ✅ CREATE STAGE, CREATE FILE FORMAT - ✅ USAGE on XO_PROD_WH warehouse - ❌ No UPDATE or DELETE - ❌ No access to SILVER/GOLD

On *_DEV databases: - ❌ No access (separate service account for dev)

Inherits from: XO_SERVICE_ACCOUNT

Use case: Production Airflow DAGs loading from APIs to BRONZE

11. XO_DBT_TRANSFORMER_DEV¶

Purpose: dbt service account for transforming BRONZE → SILVER → GOLD (development)

Users: dbt service account (not a person)

Permissions:

On *_DEV databases: - ✅ USAGE on database - ✅ USAGE on all schemas (BRONZE, SILVER, GOLD) - ✅ SELECT on BRONZE (read source data) - ✅ CREATE TABLE, CREATE VIEW in SILVER schema - ✅ INSERT, UPDATE, DELETE in SILVER (for incremental models) - ✅ CREATE TABLE, CREATE VIEW in GOLD schema - ✅ INSERT, UPDATE, DELETE in GOLD (for incremental models) - ✅ USAGE on XO_DEV_WH warehouse - ❌ No write access to BRONZE (read-only)

On *_PROD databases: - ❌ No access (separate service account for prod)

Inherits from: XO_SERVICE_ACCOUNT

Use case: dbt running locally or in CI for development/testing

12. XO_DBT_TRANSFORMER_PROD¶

Purpose: dbt service account for transforming BRONZE → SILVER → GOLD (production)

Users: dbt service account (not a person)

Permissions:

On *_PROD databases: - ✅ USAGE on database - ✅ USAGE on all schemas - ✅ SELECT on BRONZE (read source data) - ✅ CREATE TABLE, CREATE VIEW in SILVER schema - ✅ INSERT, UPDATE, DELETE in SILVER - ✅ CREATE TABLE, CREATE VIEW in GOLD schema - ✅ INSERT, UPDATE, DELETE in GOLD - ✅ USAGE on XO_PROD_WH warehouse - ❌ No write access to BRONZE (read-only)

On *_DEV databases: - ❌ No access (separation of environments)

Inherits from: XO_SERVICE_ACCOUNT

Use case: dbt running in production CI/CD pipeline

Access Patterns by Layer¶

BRONZE Layer (Restricted - HIGH Sensitivity)¶

Contains: Raw PII, customer emails, phone numbers, conversation text

Who can access: - ✅ XO_DATA_ENGINEER_DEV (read/write in *_DEV) - ✅ XO_DATA_ENGINEER_PROD (read-only in *_PROD) - ✅ XO_AIRFLOW_LOADER_DEV (write to *_DEV) - ✅ XO_AIRFLOW_LOADER_PROD (write to *_PROD) - ✅ XO_DBT_TRANSFORMER_DEV (read from *_DEV) - ✅ XO_DBT_TRANSFORMER_PROD (read from *_PROD) - ❌ All analyst roles (no access)

Why restricted: Compliance (GDPR, CCPA), PII protection, security

SILVER Layer (Developer Access - MEDIUM Sensitivity)¶

Contains: Cleaned data, typed columns, some identifiable info (customer_id, agent_id)

Who can access: - ✅ XO_DATA_ENGINEER_DEV (read/write in *_DEV) - ✅ XO_DATA_ENGINEER_PROD (read-only in *_PROD) - ✅ XO_DBT_TRANSFORMER_DEV (read/write *_DEV) - ✅ XO_DBT_TRANSFORMER_PROD (read/write *_PROD) - ⚠️ Senior analysts (optional, with business justification) - ❌ Client stakeholders (too sensitive)

Why semi-restricted: Still has transaction-level data, could be used to identify individuals

GOLD Layer (Open Access - LOW Sensitivity)¶

Contains: Aggregated metrics, pre-calculated KPIs, no PII

Who can access: - ✅ All data engineers - ✅ All dbt transformers - ✅ All analyst roles (client-specific) - ✅ BI tools (Tableau, Power BI) - ✅ Executives

Why open: Safe for broad access, optimized for analytics, no compliance risk

Warehouse Strategy¶

XO_DEV_WH (Development Warehouse)¶

Purpose: Development and testing work

Size: SMALL (cost-efficient)

Auto-suspend: 5 minutes

Who can use: - XO_DATA_ENGINEER_DEV - XO_AIRFLOW_LOADER_DEV - XO_DBT_TRANSFORMER_DEV

XO_PROD_WH (Production Warehouse)¶

Purpose: Production pipelines (Airflow + dbt)

Size: MEDIUM (handles production load)

Auto-suspend: 10 minutes

Who can use: - XO_AIRFLOW_LOADER_PROD - XO_DBT_TRANSFORMER_PROD - XO_DATA_ENGINEER_PROD (for debugging)

XO_ANALYST_WH (Analyst Warehouse)¶

Purpose: Ad-hoc analyst queries, BI tool queries

Size: SMALL (analyst workload)

Auto-suspend: 5 minutes

Who can use: - All XO_ANALYST_* roles - BI tools (Tableau, Power BI)

Key Governance Principles Applied¶

1. Principle of Least Privilege ✅¶

Each role has exactly the minimum permissions needed: - Analysts can't write data - Airflow can only append to BRONZE - dbt can't modify BRONZE - Client analysts can't see other clients' data

2. Separation of Duties ✅¶

Different systems have different responsibilities: - Airflow: Extract and load to BRONZE - dbt: Transform BRONZE → SILVER → GOLD - Analysts: Read from GOLD only

3. Environment Isolation ✅¶

DEV and PROD have separate roles: - No risk of dev code touching prod data - Each environment has dedicated service accounts

4. Client Data Isolation ✅¶

Database-level separation enforced by roles: - XO_ANALYST_CONDENAST: CND_DB only - XO_ANALYST_WARBYPARKER: WBP_DB only - Impossible to grant wrong access (database-level REVOKE)

5. Audit Trail ✅¶

All queries logged in Snowflake: - Who (user/role) accessed what data - When they accessed it - What query they ran

Query to check audit log:

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE USER_NAME = 'AIRFLOW_PROD'
ORDER BY START_TIME DESC;

User-to-Role Mapping Examples¶

Your Team Members¶

-- You (data engineer) - full dev access, read-only prod
GRANT ROLE XO_DATA_ENGINEER_DEV TO USER oscar_colunga;
GRANT ROLE XO_DATA_ENGINEER_PROD TO USER oscar_colunga;

-- Other data engineer
GRANT ROLE XO_DATA_ENGINEER_DEV TO USER jane_smith;

-- Security admin (senior engineer)
GRANT ROLE XO_SECURITY_ADMIN TO USER senior_lead;

Service Accounts¶

-- Airflow dev environment
CREATE USER airflow_dev_svc PASSWORD='...' DEFAULT_ROLE='XO_AIRFLOW_LOADER_DEV';
GRANT ROLE XO_AIRFLOW_LOADER_DEV TO USER airflow_dev_svc;

-- Airflow prod environment
CREATE USER airflow_prod_svc PASSWORD='...' DEFAULT_ROLE='XO_AIRFLOW_LOADER_PROD';
GRANT ROLE XO_AIRFLOW_LOADER_PROD TO USER airflow_prod_svc;

-- dbt dev environment
CREATE USER dbt_dev_svc PASSWORD='...' DEFAULT_ROLE='XO_DBT_TRANSFORMER_DEV';
GRANT ROLE XO_DBT_TRANSFORMER_DEV TO USER dbt_dev_svc;

-- dbt prod environment
CREATE USER dbt_prod_svc PASSWORD='...' DEFAULT_ROLE='XO_DBT_TRANSFORMER_PROD';
GRANT ROLE XO_DBT_TRANSFORMER_PROD TO USER dbt_prod_svc;

Client Stakeholders¶

-- CondeNast manager
CREATE USER cnd_manager PASSWORD='...' DEFAULT_ROLE='XO_ANALYST_CONDENAST';
GRANT ROLE XO_ANALYST_CONDENAST TO USER cnd_manager;

-- Warby Parker manager
CREATE USER wbp_manager PASSWORD='...' DEFAULT_ROLE='XO_ANALYST_WARBYPARKER';
GRANT ROLE XO_ANALYST_WARBYPARKER TO USER wbp_manager;

-- Your CEO
CREATE USER ceo PASSWORD='...' DEFAULT_ROLE='XO_ANALYST_EXECUTIVE';
GRANT ROLE XO_ANALYST_EXECUTIVE TO USER ceo;

Next Steps¶

Review this hierarchy - Does it match your team structure and security needs?
Create permission matrix - Detailed table showing exact grants per role
Write Snowflake SQL migrations - Create roles and grants in apps/snowflake-bronze/permissions/
Test in DEV - Deploy roles to DEV first, validate access patterns
Deploy to PROD - Once validated, deploy same roles to PROD

Questions to consider: - Do you need XO_DATA_ENGINEER_PROD role, or should all prod access be read-only via CI/CD? - Should senior analysts get SILVER layer access, or GOLD only? - Do you need additional client-specific roles (ANALYST_IONOS, ANALYST_NBA, etc.)?