Bootstrap Setup Guide - First-Time Snowflake Setup

Date: 2025-11-26 Status: Ready for execution Purpose: Step-by-step guide for initial manual setup before running schemachange migrations

---

Overview

This guide provides the exact SQL commands you need to run manually in Snowflake to bootstrap your governance infrastructure. This is a one-time setup that solves the "chicken-and-egg" problem of needing databases and roles to exist before schemachange can manage schemas.

What you'll create manually: 1. 3 warehouses (DEV, PROD, ANALYST) - all SMALL size 2. 6 databases (CND_DB_DEV/PROD, WBP_DB_DEV/PROD, CORE_DB_DEV/PROD) 3. 15 custom roles with hierarchy (including XO_SCHEMACHANGE_DEPLOYER) 4. Service account users (schemachange, airflow, dbt) with key-pair auth 5. Database privilege grants (CREATE SCHEMA, USAGE) to operational roles 6. Warehouse permissions 7. ACCOUNT_USAGE access for query history archival

Security-first approach: - schemachange uses custom XO_SCHEMACHANGE_DEPLOYER role (NOT SYSADMIN) - schemachange can manage schemas/tables but CANNOT create/drop databases - Database creation requires manual engineer approval (this bootstrap process) - Principle of least privilege applied to all service accounts

After this manual setup: - schemachange can manage schemas (BRONZE, SILVER, GOLD) inside databases - GitHub Actions workflow can deploy schema changes automatically - Service accounts run with minimal required privileges

---

Prerequisites

Before starting, ensure you have:

• Snowflake account with ACCOUNTADMIN access
• Access to Snowflake UI or SnowSQL CLI
• Notepad to save generated passwords
• 15-20 minutes of focused time

---

Step-by-Step Bootstrap Process

Step 1: Log into Snowflake as ACCOUNTADMIN

-- In Snowflake UI or SnowSQL
USE ROLE ACCOUNTADMIN;

Why ACCOUNTADMIN: Only ACCOUNTADMIN can create roles, users, and transfer warehouse ownership.

---

Step 2: Create Warehouses

Copy and paste this entire block:

-- ============================================================================
-- STEP 2: CREATE WAREHOUSES
-- ============================================================================

USE ROLE ACCOUNTADMIN;

-- Development Warehouse (for engineers and dev pipelines)
CREATE WAREHOUSE IF NOT EXISTS XO_DEV_WH
    WITH
    WAREHOUSE_SIZE = 'SMALL'
    AUTO_SUSPEND = 300                -- 5 minutes (cost-optimized for intermittent use)
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE
    COMMENT = 'Development warehouse for data engineers, Airflow dev, and dbt dev. Intermittent workload pattern.';

-- Production Warehouse (shared across all clients)
CREATE WAREHOUSE IF NOT EXISTS XO_PROD_WH
    WITH
    WAREHOUSE_SIZE = 'SMALL'          -- Start small, scale based on actual usage
    AUTO_SUSPEND = 300                -- 5 minutes (batch workload: morning and evening jobs)
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE
    COMMENT = 'Production warehouse shared across all clients (CND, WBP, CORE). Batch job workload pattern. Per-client cost tracking via query history archival.';

-- Analyst Warehouse (for BI tools and client stakeholders)
CREATE WAREHOUSE IF NOT EXISTS XO_ANALYST_WH
    WITH
    WAREHOUSE_SIZE = 'SMALL'
    AUTO_SUSPEND = 300                -- 5 minutes (dashboard refreshes, ad-hoc queries)
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE
    COMMENT = 'Analyst warehouse shared across all BI tools and client analysts. RBAC ensures client isolation. Sporadic query pattern.';

-- Verify warehouses created
SHOW WAREHOUSES LIKE 'XO_%';

-- Expected output: 3 warehouses (XO_DEV_WH, XO_PROD_WH, XO_ANALYST_WH)

Checkpoint: Verify you see 3 warehouses in the output.

---

Step 3: Transfer Warehouse Ownership to SYSADMIN

-- ============================================================================
-- STEP 3: TRANSFER WAREHOUSE OWNERSHIP
-- ============================================================================

USE ROLE ACCOUNTADMIN;

-- Best practice: SYSADMIN owns infrastructure, not ACCOUNTADMIN
GRANT OWNERSHIP ON WAREHOUSE XO_DEV_WH TO ROLE SYSADMIN REVOKE CURRENT GRANTS;
GRANT OWNERSHIP ON WAREHOUSE XO_PROD_WH TO ROLE SYSADMIN REVOKE CURRENT GRANTS;
GRANT OWNERSHIP ON WAREHOUSE XO_ANALYST_WH TO ROLE SYSADMIN REVOKE CURRENT GRANTS;

-- Grant USAGE back to SYSADMIN (so we can grant to other roles later)
GRANT USAGE ON WAREHOUSE XO_DEV_WH TO ROLE SYSADMIN;
GRANT USAGE ON WAREHOUSE XO_PROD_WH TO ROLE SYSADMIN;
GRANT USAGE ON WAREHOUSE XO_ANALYST_WH TO ROLE SYSADMIN;

Checkpoint: Warehouses now owned by SYSADMIN (not ACCOUNTADMIN).

---

Step 4: Create Databases

-- ============================================================================
-- STEP 4: CREATE DATABASES
-- ============================================================================

USE ROLE SYSADMIN;

-- Create DEV databases
CREATE DATABASE IF NOT EXISTS CND_DB_DEV
    COMMENT = 'CondeNast DEV environment. Contains BRONZE, SILVER, GOLD layers.';

CREATE DATABASE IF NOT EXISTS WBP_DB_DEV
    COMMENT = 'Warby Parker DEV environment. Contains BRONZE, SILVER, GOLD layers.';

CREATE DATABASE IF NOT EXISTS CORE_DB_DEV
    COMMENT = 'Core/cross-client DEV environment. Contains shared data and query history archive.';

-- Create PROD databases
CREATE DATABASE IF NOT EXISTS CND_DB_PROD
    COMMENT = 'CondeNast PROD environment. Contains BRONZE, SILVER, GOLD layers.';

CREATE DATABASE IF NOT EXISTS WBP_DB_PROD
    COMMENT = 'Warby Parker PROD environment. Contains BRONZE, SILVER, GOLD layers.';

CREATE DATABASE IF NOT EXISTS CORE_DB_PROD
    COMMENT = 'Core/cross-client PROD environment. Contains shared data and query history archive.';

-- Verify databases created
SHOW DATABASES LIKE '%_DB_%';

-- Expected output: 6 databases (CND_DB_DEV, CND_DB_PROD, WBP_DB_DEV, WBP_DB_PROD, CORE_DB_DEV, CORE_DB_PROD)

Why create databases in bootstrap: - Security: Database creation is a high-privilege operation requiring manual approval - Schemachange will manage schemas (BRONZE, SILVER, GOLD) and tables inside these databases - Schemachange will automatically create CHANGE_HISTORY schema on first run

Checkpoint: Verify you see 6 databases in the output.

---

Step 5: Create Custom Roles

-- ============================================================================
-- STEP 5: CREATE CUSTOM ROLES
-- ============================================================================

USE ROLE SECURITYADMIN;

-- All role creation must be done as SECURITYADMIN
-- (SYSADMIN cannot create roles - insufficient privileges)

-- Parent Roles
CREATE ROLE IF NOT EXISTS XO_SECURITY_ADMIN
    COMMENT = 'Security administrator for xo-data. Manages users, roles, and permissions. Cannot access data.';

CREATE ROLE IF NOT EXISTS XO_DATA_ENGINEER
    COMMENT = 'Parent role for all data engineers. Provides base permissions shared across dev/prod.';

CREATE ROLE IF NOT EXISTS XO_SERVICE_ACCOUNT
    COMMENT = 'Parent role for all service accounts (Airflow, dbt). Provides base permissions for automated pipelines.';

CREATE ROLE IF NOT EXISTS XO_DATA_ANALYST
    COMMENT = 'Parent role for all analysts. Provides base read-only permissions on GOLD layer.';

-- Schemachange Deployer Role (Custom role for database schema management)
CREATE ROLE IF NOT EXISTS XO_SCHEMACHANGE_DEPLOYER
    COMMENT = 'Custom role for schemachange deployments. Can manage schemas/tables within owned databases but cannot create/drop databases. Principle of least privilege for automated deployments.';

-- Data Engineer Child Roles
CREATE ROLE IF NOT EXISTS XO_DATA_ENGINEER_DEV
    COMMENT = 'Data engineer role for DEV environment. Full access to *_DEV databases, read-only to *_PROD.';

CREATE ROLE IF NOT EXISTS XO_DATA_ENGINEER_PROD
    COMMENT = 'Data engineer role for PROD environment. Read-only access to *_PROD for debugging.';

-- Analyst Child Roles (Client-Specific and Internal)
CREATE ROLE IF NOT EXISTS XO_ANALYST_CONDENAST
    COMMENT = 'Analyst role for CondeNast client stakeholders. Read-only access to CND_DB_PROD.GOLD only. External user role.';

CREATE ROLE IF NOT EXISTS XO_ANALYST_WARBYPARKER
    COMMENT = 'Analyst role for Warby Parker client stakeholders. Read-only access to WBP_DB_PROD.GOLD only. External user role.';

CREATE ROLE IF NOT EXISTS XO_ANALYST_EXECUTIVE
    COMMENT = 'Executive analyst role for company leadership. Read-only access to cross-client aggregates in CORE_DB_PROD.GOLD. Internal user role.';

CREATE ROLE IF NOT EXISTS XO_ANALYST_MASTER
    COMMENT = 'Master analyst role for internal senior analysts and BI developers. Full read-only access to GOLD and SILVER across all clients. No BRONZE access (PII protection). Internal user role.';

-- Service Account Child Roles
CREATE ROLE IF NOT EXISTS XO_AIRFLOW_LOADER_DEV
    COMMENT = 'Airflow service account for DEV. Write access to *_DEV.BRONZE (INSERT only, no UPDATE/DELETE).';

CREATE ROLE IF NOT EXISTS XO_AIRFLOW_LOADER_PROD
    COMMENT = 'Airflow service account for PROD. Write access to *_PROD.BRONZE (INSERT only, no UPDATE/DELETE).';

CREATE ROLE IF NOT EXISTS XO_DBT_TRANSFORMER_DEV
    COMMENT = 'dbt service account for DEV. Read-only on *_DEV.BRONZE, full write to *_DEV.SILVER and *_DEV.GOLD.';

CREATE ROLE IF NOT EXISTS XO_DBT_TRANSFORMER_PROD
    COMMENT = 'dbt service account for PROD. Read-only on *_PROD.BRONZE, full write to *_PROD.SILVER and *_PROD.GOLD.';

-- Verify roles created
SHOW ROLES LIKE 'XO_%';

-- Expected output: 15 custom roles
-- (4 parent + 1 schemachange + 2 engineer + 4 analyst + 4 service account)

Checkpoint: Verify you see 15 XO_* roles in the output.

---

Step 6: Grant Database Privileges (SYSADMIN Retains Ownership)

-- ============================================================================
-- STEP 6: GRANT DATABASE PRIVILEGES
-- ============================================================================

USE ROLE SYSADMIN;

-- SYSADMIN retains database ownership (Snowflake best practice)
-- Grant specific privileges to custom roles instead of transferring ownership

-- Grant schemachange deployer role CREATE SCHEMA privilege
-- This allows schemachange to create/manage schemas without owning the database
GRANT CREATE SCHEMA ON DATABASE CND_DB_DEV TO ROLE XO_SCHEMACHANGE_DEPLOYER;
GRANT CREATE SCHEMA ON DATABASE WBP_DB_DEV TO ROLE XO_SCHEMACHANGE_DEPLOYER;
GRANT CREATE SCHEMA ON DATABASE CORE_DB_DEV TO ROLE XO_SCHEMACHANGE_DEPLOYER;
GRANT CREATE SCHEMA ON DATABASE CND_DB_PROD TO ROLE XO_SCHEMACHANGE_DEPLOYER;
GRANT CREATE SCHEMA ON DATABASE WBP_DB_PROD TO ROLE XO_SCHEMACHANGE_DEPLOYER;
GRANT CREATE SCHEMA ON DATABASE CORE_DB_PROD TO ROLE XO_SCHEMACHANGE_DEPLOYER;

-- Grant USAGE to schemachange role (required for schema operations)
GRANT USAGE ON DATABASE CND_DB_DEV TO ROLE XO_SCHEMACHANGE_DEPLOYER;
GRANT USAGE ON DATABASE WBP_DB_DEV TO ROLE XO_SCHEMACHANGE_DEPLOYER;
GRANT USAGE ON DATABASE CORE_DB_DEV TO ROLE XO_SCHEMACHANGE_DEPLOYER;
GRANT USAGE ON DATABASE CND_DB_PROD TO ROLE XO_SCHEMACHANGE_DEPLOYER;
GRANT USAGE ON DATABASE WBP_DB_PROD TO ROLE XO_SCHEMACHANGE_DEPLOYER;
GRANT USAGE ON DATABASE CORE_DB_PROD TO ROLE XO_SCHEMACHANGE_DEPLOYER;

-- Grant database USAGE to service account roles (minimal access)
-- Schema-level grants will be managed by schemachange migrations

-- Airflow loader roles (need to write to BRONZE schemas)
GRANT USAGE ON DATABASE CND_DB_DEV TO ROLE XO_AIRFLOW_LOADER_DEV;
GRANT USAGE ON DATABASE WBP_DB_DEV TO ROLE XO_AIRFLOW_LOADER_DEV;
GRANT USAGE ON DATABASE CORE_DB_DEV TO ROLE XO_AIRFLOW_LOADER_DEV;
GRANT USAGE ON DATABASE CND_DB_PROD TO ROLE XO_AIRFLOW_LOADER_PROD;
GRANT USAGE ON DATABASE WBP_DB_PROD TO ROLE XO_AIRFLOW_LOADER_PROD;
GRANT USAGE ON DATABASE CORE_DB_PROD TO ROLE XO_AIRFLOW_LOADER_PROD;

-- dbt transformer roles (need to read BRONZE, write to SILVER/GOLD)
GRANT USAGE ON DATABASE CND_DB_DEV TO ROLE XO_DBT_TRANSFORMER_DEV;
GRANT USAGE ON DATABASE WBP_DB_DEV TO ROLE XO_DBT_TRANSFORMER_DEV;
GRANT USAGE ON DATABASE CORE_DB_DEV TO ROLE XO_DBT_TRANSFORMER_DEV;
GRANT USAGE ON DATABASE CND_DB_PROD TO ROLE XO_DBT_TRANSFORMER_PROD;
GRANT USAGE ON DATABASE WBP_DB_PROD TO ROLE XO_DBT_TRANSFORMER_PROD;
GRANT USAGE ON DATABASE CORE_DB_PROD TO ROLE XO_DBT_TRANSFORMER_PROD;

-- Data engineer roles (need full access to DEV, read access to PROD)
GRANT ALL PRIVILEGES ON DATABASE CND_DB_DEV TO ROLE XO_DATA_ENGINEER_DEV;
GRANT ALL PRIVILEGES ON DATABASE WBP_DB_DEV TO ROLE XO_DATA_ENGINEER_DEV;
GRANT ALL PRIVILEGES ON DATABASE CORE_DB_DEV TO ROLE XO_DATA_ENGINEER_DEV;
GRANT USAGE ON DATABASE CND_DB_PROD TO ROLE XO_DATA_ENGINEER_PROD;
GRANT USAGE ON DATABASE WBP_DB_PROD TO ROLE XO_DATA_ENGINEER_PROD;
GRANT USAGE ON DATABASE CORE_DB_PROD TO ROLE XO_DATA_ENGINEER_PROD;

-- Analyst roles (read-only access to PROD databases via parent role)
-- Schema-level grants will restrict to GOLD layer only
GRANT USAGE ON DATABASE CND_DB_PROD TO ROLE XO_DATA_ANALYST;
GRANT USAGE ON DATABASE WBP_DB_PROD TO ROLE XO_DATA_ANALYST;
GRANT USAGE ON DATABASE CORE_DB_PROD TO ROLE XO_DATA_ANALYST;

-- Verify database grants
SHOW GRANTS ON DATABASE CND_DB_DEV;
SHOW GRANTS ON DATABASE CORE_DB_PROD;

-- Expected: SYSADMIN has OWNERSHIP, XO_SCHEMACHANGE_DEPLOYER has CREATE SCHEMA + USAGE

Why this follows best practices: - ✅ SYSADMIN owns databases: Standard Snowflake hierarchy (ACCOUNTADMIN → SYSADMIN → custom roles) - ✅ Centralized infrastructure management: All databases owned by one role - ✅ Schemachange has necessary privileges: CREATE SCHEMA + USAGE allows schema/table management - ✅ Engineers retain control: Anyone with SYSADMIN can troubleshoot/fix issues - ✅ No ownership fragmentation: Clear ownership chain, easier auditing - ✅ Still secure: Schemachange cannot create/drop databases (no CREATE DATABASE privilege)

Permission model: - Database ownership (SYSADMIN): Full control over database-level operations - Database privileges (bootstrap): CREATE SCHEMA, USAGE grants for operational roles - Schema ownership (schemachange): Schemas created by schemachange are owned by XO_SCHEMACHANGE_DEPLOYER - Schema/object grants (migrations): Specific privileges granted to service roles

How schemachange creates schemas: 1. Schemachange has CREATE SCHEMA privilege on database 2. When schemachange creates a schema, it becomes the owner of that schema 3. Schemachange can then grant privileges on that schema to other roles 4. SYSADMIN retains database ownership and can intervene if needed

Checkpoint: Verify SYSADMIN owns databases and XO_SCHEMACHANGE_DEPLOYER has CREATE SCHEMA + USAGE.

---

Step 7: Set Up Role Hierarchy

-- ============================================================================
-- STEP 7: ROLE HIERARCHY (GRANTS)
-- ============================================================================

USE ROLE SECURITYADMIN;

-- Grant XO_SECURITY_ADMIN to SECURITYADMIN
GRANT ROLE XO_SECURITY_ADMIN TO ROLE SECURITYADMIN;

USE ROLE SYSADMIN;

-- Grant parent roles to SYSADMIN
GRANT ROLE XO_DATA_ENGINEER TO ROLE SYSADMIN;
GRANT ROLE XO_SERVICE_ACCOUNT TO ROLE SYSADMIN;
GRANT ROLE XO_DATA_ANALYST TO ROLE SYSADMIN;
GRANT ROLE XO_SCHEMACHANGE_DEPLOYER TO ROLE SYSADMIN;

-- Grant child roles to parents (engineer roles)
GRANT ROLE XO_DATA_ENGINEER_DEV TO ROLE XO_DATA_ENGINEER;
GRANT ROLE XO_DATA_ENGINEER_PROD TO ROLE XO_DATA_ENGINEER;

-- Grant child roles to parents (analyst roles)
GRANT ROLE XO_ANALYST_CONDENAST TO ROLE XO_DATA_ANALYST;
GRANT ROLE XO_ANALYST_WARBYPARKER TO ROLE XO_DATA_ANALYST;
GRANT ROLE XO_ANALYST_EXECUTIVE TO ROLE XO_DATA_ANALYST;
GRANT ROLE XO_ANALYST_MASTER TO ROLE XO_DATA_ANALYST;

-- Grant child roles to parents (service account roles)
GRANT ROLE XO_AIRFLOW_LOADER_DEV TO ROLE XO_SERVICE_ACCOUNT;
GRANT ROLE XO_AIRFLOW_LOADER_PROD TO ROLE XO_SERVICE_ACCOUNT;
GRANT ROLE XO_DBT_TRANSFORMER_DEV TO ROLE XO_SERVICE_ACCOUNT;
GRANT ROLE XO_DBT_TRANSFORMER_PROD TO ROLE XO_SERVICE_ACCOUNT;

Checkpoint: Role hierarchy established (parent → child relationships).

---

Step 8: Grant Warehouse Permissions

-- ============================================================================
-- STEP 8: GRANT WAREHOUSE PERMISSIONS
-- ============================================================================

USE ROLE SYSADMIN;

-- XO_DEV_WH: Development warehouse
GRANT USAGE ON WAREHOUSE XO_DEV_WH TO ROLE XO_DATA_ENGINEER_DEV;
GRANT USAGE ON WAREHOUSE XO_DEV_WH TO ROLE XO_AIRFLOW_LOADER_DEV;
GRANT USAGE ON WAREHOUSE XO_DEV_WH TO ROLE XO_DBT_TRANSFORMER_DEV;

-- XO_PROD_WH: Production warehouse (shared across clients)
GRANT USAGE ON WAREHOUSE XO_PROD_WH TO ROLE XO_DATA_ENGINEER_PROD;
GRANT USAGE ON WAREHOUSE XO_PROD_WH TO ROLE XO_AIRFLOW_LOADER_PROD;
GRANT USAGE ON WAREHOUSE XO_PROD_WH TO ROLE XO_DBT_TRANSFORMER_PROD;
GRANT USAGE ON WAREHOUSE XO_PROD_WH TO ROLE XO_SCHEMACHANGE_DEPLOYER;

-- XO_ANALYST_WH: Analyst warehouse (shared across clients)
-- Grant to parent role, all children will inherit
GRANT USAGE ON WAREHOUSE XO_ANALYST_WH TO ROLE XO_DATA_ANALYST;

-- Verify grants
SHOW GRANTS ON WAREHOUSE XO_DEV_WH;
SHOW GRANTS ON WAREHOUSE XO_PROD_WH;
SHOW GRANTS ON WAREHOUSE XO_ANALYST_WH;

Checkpoint: Each warehouse has appropriate role grants.

---

Step 9: Generate RSA Key Pairs for Service Accounts

IMPORTANT: Service accounts use key-pair authentication (not passwords). Snowflake is phasing out password-based auth for service accounts in 2026.

9a. Generate Key Pairs Locally

Run these commands on your local machine to generate RSA key pairs for each service account:

# Create directory for keys
mkdir -p ~/.ssh/snowflake-keys
cd ~/.ssh/snowflake-keys

# Generate key pairs for each service account (2048-bit RSA minimum)
# schemachange
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out schemachange_rsa_key.p8 -nocrypt
openssl rsa -in schemachange_rsa_key.p8 -pubout -out schemachange_rsa_key.pub

# airflow-dev
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out airflow_dev_rsa_key.p8 -nocrypt
openssl rsa -in airflow_dev_rsa_key.p8 -pubout -out airflow_dev_rsa_key.pub

# airflow-prod
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out airflow_prod_rsa_key.p8 -nocrypt
openssl rsa -in airflow_prod_rsa_key.p8 -pubout -out airflow_prod_rsa_key.pub

# dbt-dev
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out dbt_dev_rsa_key.p8 -nocrypt
openssl rsa -in dbt_dev_rsa_key.p8 -pubout -out dbt_dev_rsa_key.pub

# dbt-prod
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out dbt_prod_rsa_key.p8 -nocrypt
openssl rsa -in dbt_prod_rsa_key.p8 -pubout -out dbt_prod_rsa_key.pub

# List generated keys
ls -la

Generated files: - *_rsa_key.p8 - Private keys (keep secure, never commit to git) - *_rsa_key.pub - Public keys (will be assigned to Snowflake users)

9b. Extract Public Keys for Snowflake

For each public key file, extract the key content (removing header/footer and newlines):

# Extract public key for schemachange (remove delimiters, one line)
grep -v "BEGIN PUBLIC" schemachange_rsa_key.pub | grep -v "END PUBLIC" | tr -d '\n'

Repeat for all 5 service accounts and save the output - you'll paste these into the SQL below.

9c. Create Service Account Users in Snowflake

-- ============================================================================
-- STEP 9: CREATE SERVICE ACCOUNT USERS (TYPE=SERVICE with key-pair auth)
-- ============================================================================

USE ROLE SECURITYADMIN;

-- schemachange Service Account (runs database migrations)
CREATE USER IF NOT EXISTS SCHEMACHANGE_SVC
    TYPE = SERVICE
    DEFAULT_ROLE = XO_SCHEMACHANGE_DEPLOYER
    DEFAULT_WAREHOUSE = XO_PROD_WH
    RSA_PUBLIC_KEY = 'PASTE_SCHEMACHANGE_PUBLIC_KEY_HERE'  -- TODO: Paste public key (no delimiters, single line)
    COMMENT = 'Service account for schemachange database migrations. Uses XO_SCHEMACHANGE_DEPLOYER role (NOT SYSADMIN) for security. Can manage schemas/tables but cannot create/drop databases. Uses key-pair authentication.';

GRANT ROLE XO_SCHEMACHANGE_DEPLOYER TO USER SCHEMACHANGE_SVC;

-- Airflow DEV Service Account
CREATE USER IF NOT EXISTS AIRFLOW_DEV_SVC
    TYPE = SERVICE
    DEFAULT_ROLE = XO_AIRFLOW_LOADER_DEV
    DEFAULT_WAREHOUSE = XO_DEV_WH
    RSA_PUBLIC_KEY = 'PASTE_AIRFLOW_DEV_PUBLIC_KEY_HERE'  -- TODO: Paste public key
    COMMENT = 'Airflow service account for DEV environment. Uses key-pair authentication. Loads raw data to *_DEV.BRONZE schemas.';

GRANT ROLE XO_AIRFLOW_LOADER_DEV TO USER AIRFLOW_DEV_SVC;

-- Airflow PROD Service Account
CREATE USER IF NOT EXISTS AIRFLOW_PROD_SVC
    TYPE = SERVICE
    DEFAULT_ROLE = XO_AIRFLOW_LOADER_PROD
    DEFAULT_WAREHOUSE = XO_PROD_WH
    RSA_PUBLIC_KEY = 'PASTE_AIRFLOW_PROD_PUBLIC_KEY_HERE'  -- TODO: Paste public key
    COMMENT = 'Airflow service account for PROD environment. Uses key-pair authentication. Loads raw data to *_PROD.BRONZE schemas.';

GRANT ROLE XO_AIRFLOW_LOADER_PROD TO USER AIRFLOW_PROD_SVC;

-- dbt DEV Service Account
CREATE USER IF NOT EXISTS DBT_DEV_SVC
    TYPE = SERVICE
    DEFAULT_ROLE = XO_DBT_TRANSFORMER_DEV
    DEFAULT_WAREHOUSE = XO_DEV_WH
    RSA_PUBLIC_KEY = 'PASTE_DBT_DEV_PUBLIC_KEY_HERE'  -- TODO: Paste public key
    COMMENT = 'dbt service account for DEV environment. Uses key-pair authentication. Transforms *_DEV.BRONZE to SILVER/GOLD.';

GRANT ROLE XO_DBT_TRANSFORMER_DEV TO USER DBT_DEV_SVC;

-- dbt PROD Service Account
CREATE USER IF NOT EXISTS DBT_PROD_SVC
    TYPE = SERVICE
    DEFAULT_ROLE = XO_DBT_TRANSFORMER_PROD
    DEFAULT_WAREHOUSE = XO_PROD_WH
    RSA_PUBLIC_KEY = 'PASTE_DBT_PROD_PUBLIC_KEY_HERE'  -- TODO: Paste public key
    COMMENT = 'dbt service account for PROD environment. Uses key-pair authentication. Transforms *_PROD.BRONZE to SILVER/GOLD.';

GRANT ROLE XO_DBT_TRANSFORMER_PROD TO USER DBT_PROD_SVC;

-- Verify users created
SHOW USERS LIKE '%_SVC';

-- Expected output: 5 service account users with TYPE=SERVICE

TODO Action Items: - [ ] Generate 5 RSA key pairs using OpenSSL (Step 9a) - [ ] Extract public keys without delimiters (Step 9b) - [ ] Paste public keys into SQL above (replace PASTE_*_PUBLIC_KEY_HERE) - [ ] Run SQL to create service accounts in Snowflake - [ ] Store private keys securely: - SCHEMACHANGE_SVC → GitHub Secrets: SF_SCHEMACHANGE_PRIVATE_KEY - AIRFLOW_DEV_SVC → Airflow Connections (private key field) - AIRFLOW_PROD_SVC → Airflow Connections (private key field) - DBT_DEV_SVC → dbt profiles.yml (private_key_path or private_key_content) - DBT_PROD_SVC → dbt profiles.yml (private_key_path or private_key_content) - [ ] Never commit private keys to git (add *_rsa_key.p8 to .gitignore)

Security Best Practices Applied: - ✅ TYPE=SERVICE: Modern service account type (passwords not supported) - ✅ Key-pair authentication: More secure than passwords, supports rotation - ✅ 2048-bit RSA keys: Meets Snowflake minimum security requirements - ✅ Private key protection: Keys stored in secrets management, not code - ✅ Future-proof: Compliant with Snowflake 2026 authentication requirements - ✅ Least privilege for schemachange: Uses XO_SCHEMACHANGE_DEPLOYER (not SYSADMIN)

Checkpoint: 5 service account users created with key-pair authentication.

---

Step 9d: Verify Service Account Role Grants

CRITICAL: Verify that roles were actually granted to service accounts (not just set as DEFAULT_ROLE).

-- ============================================================================
-- VERIFICATION: Check role grants for service accounts
-- ============================================================================

USE ROLE SECURITYADMIN;

-- Check SCHEMACHANGE_SVC
SHOW GRANTS TO USER SCHEMACHANGE_SVC;
-- Expected: Should see "ROLE XO_SCHEMACHANGE_DEPLOYER" granted

-- Check AIRFLOW_DEV_SVC
SHOW GRANTS TO USER AIRFLOW_DEV_SVC;
-- Expected: Should see "ROLE XO_AIRFLOW_LOADER_DEV" granted

-- Check AIRFLOW_PROD_SVC
SHOW GRANTS TO USER AIRFLOW_PROD_SVC;
-- Expected: Should see "ROLE XO_AIRFLOW_LOADER_PROD" granted

-- Check DBT_DEV_SVC
SHOW GRANTS TO USER DBT_DEV_SVC;
-- Expected: Should see "ROLE XO_DBT_TRANSFORMER_DEV" granted

-- Check DBT_PROD_SVC
SHOW GRANTS TO USER DBT_PROD_SVC;
-- Expected: Should see "ROLE XO_DBT_TRANSFORMER_PROD" granted

-- Alternative: Check all service account grants at once
SELECT
    grantee_name AS user_name,
    role AS granted_role,
    granted_by
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
WHERE grantee_name IN (
    'SCHEMACHANGE_SVC',
    'AIRFLOW_DEV_SVC',
    'AIRFLOW_PROD_SVC',
    'DBT_DEV_SVC',
    'DBT_PROD_SVC'
)
AND deleted_on IS NULL
ORDER BY grantee_name, role;

Troubleshooting:

If role is NOT showing as granted: 1. Check if role was created first (roles must exist before granting):

SHOW ROLES LIKE 'XO_%';

1. Check execution order - You must run Step 5 (create roles) BEFORE Step 9 (create users + grant roles)

2. Re-grant manually if needed:

   USE ROLE SECURITYADMIN;
   GRANT ROLE XO_SCHEMACHANGE_DEPLOYER TO USER SCHEMACHANGE_SVC;
   GRANT ROLE XO_AIRFLOW_LOADER_DEV TO USER AIRFLOW_DEV_SVC;
   GRANT ROLE XO_AIRFLOW_LOADER_PROD TO USER AIRFLOW_PROD_SVC;
   GRANT ROLE XO_DBT_TRANSFORMER_DEV TO USER DBT_DEV_SVC;
   GRANT ROLE XO_DBT_TRANSFORMER_PROD TO USER DBT_PROD_SVC;
   

Important Notes: - DEFAULT_ROLE in CREATE USER does NOT grant the role - it only sets the default - You MUST explicitly GRANT ROLE ... TO USER ... for the user to have access - Only SECURITYADMIN (or role owner) can grant roles

Checkpoint: All service accounts have their roles properly granted.

---

Step 10: Grant Your Personal User Account

-- ============================================================================
-- STEP 10: GRANT ENGINEER ROLE TO YOUR USER
-- ============================================================================

USE ROLE SECURITYADMIN;

-- Replace 'YOUR_USERNAME' with your actual Snowflake username
GRANT ROLE XO_DATA_ENGINEER_DEV TO USER YOUR_USERNAME;
GRANT ROLE XO_DATA_ENGINEER_PROD TO USER YOUR_USERNAME;

-- Optionally set default role
ALTER USER YOUR_USERNAME SET DEFAULT_ROLE = XO_DATA_ENGINEER_DEV;

-- Verify grants
SHOW GRANTS TO USER YOUR_USERNAME;

TODO: Replace YOUR_USERNAME with your actual Snowflake username.

Checkpoint: You now have engineer access to DEV and PROD environments.

---

Step 11: Grant ACCOUNT_USAGE Access for Query History Archival

Critical for cost tracking: This allows Airflow to read query history and archive it to CORE_DB.BRONZE.

-- ============================================================================
-- STEP 11: GRANT ACCOUNT_USAGE ACCESS
-- ============================================================================

USE ROLE ACCOUNTADMIN;

-- Grant Airflow roles access to read query history from SNOWFLAKE database
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE XO_AIRFLOW_LOADER_DEV;
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE XO_AIRFLOW_LOADER_PROD;

-- Verify grant
SHOW GRANTS TO ROLE XO_AIRFLOW_LOADER_DEV;
SHOW GRANTS TO ROLE XO_AIRFLOW_LOADER_PROD;

-- Look for: IMPORTED PRIVILEGES on database SNOWFLAKE

Why this matters: Without this grant, the daily query history archival pipeline will fail, and you'll lose cost tracking data after 7 days.

Checkpoint: Airflow roles can now read from SNOWFLAKE.ACCOUNT_USAGE views.

---

Bootstrap Complete! ✅

You've successfully completed the manual bootstrap setup. Here's what you created:

Summary

Warehouses (3): - ✅ XO_DEV_WH (SMALL, owned by SYSADMIN) - ✅ XO_PROD_WH (SMALL, owned by SYSADMIN) - ✅ XO_ANALYST_WH (SMALL, owned by SYSADMIN)

Databases (6): - ✅ CND_DB_DEV (owned by SYSADMIN) - ✅ CND_DB_PROD (owned by SYSADMIN) - ✅ WBP_DB_DEV (owned by SYSADMIN) - ✅ WBP_DB_PROD (owned by SYSADMIN) - ✅ CORE_DB_DEV (owned by SYSADMIN) - ✅ CORE_DB_PROD (owned by SYSADMIN)

Roles (15): - ✅ XO_SECURITY_ADMIN - ✅ XO_SCHEMACHANGE_DEPLOYER (custom role for schema management) - ✅ XO_DATA_ENGINEER (parent) - ✅ XO_DATA_ENGINEER_DEV - ✅ XO_DATA_ENGINEER_PROD - ✅ XO_DATA_ANALYST (parent) - ✅ XO_ANALYST_CONDENAST (external: CND client stakeholders) - ✅ XO_ANALYST_WARBYPARKER (external: WBP client stakeholders) - ✅ XO_ANALYST_EXECUTIVE (internal: company leadership, aggregates only) - ✅ XO_ANALYST_MASTER (internal: senior analysts, full GOLD/SILVER access) - ✅ XO_SERVICE_ACCOUNT (parent) - ✅ XO_AIRFLOW_LOADER_DEV - ✅ XO_AIRFLOW_LOADER_PROD - ✅ XO_DBT_TRANSFORMER_DEV - ✅ XO_DBT_TRANSFORMER_PROD

Service Accounts (5): - ✅ SCHEMACHANGE_SVC (XO_SCHEMACHANGE_DEPLOYER role - NOT SYSADMIN) - ✅ AIRFLOW_DEV_SVC - ✅ AIRFLOW_PROD_SVC - ✅ DBT_DEV_SVC - ✅ DBT_PROD_SVC

Permissions: - ✅ Database privileges (CREATE SCHEMA, USAGE) to XO_SCHEMACHANGE_DEPLOYER - ✅ Database USAGE grants to service and engineer roles - ✅ Warehouse USAGE grants - ✅ Role hierarchy grants - ✅ ACCOUNT_USAGE access for query history archival - ✅ Your personal user granted engineer roles

---

Next Steps

Now that bootstrap is complete, you can proceed with automated deployment:

Next: Run schemachange Migrations

The schemachange migrations will create: 1. Schemas (BRONZE, SILVER, GOLD in each database) 2. CHANGE_HISTORY schema (automatically created by schemachange on first run) 3. Query history archive table (CORE_DB.BRONZE.QUERY_HISTORY_ARCHIVE) 4. Schema-level permission grants to appropriate roles 5. Object-level permission grants (tables, views)

Important: Schemachange will run as XO_SCHEMACHANGE_DEPLOYER role and can: - ✅ Create schemas (owns schemas it creates) - ✅ Create/modify tables, views, functions within those schemas - ✅ Grant schema/object-level permissions to other roles - ❌ Cannot create new databases (no CREATE DATABASE privilege) - ❌ Cannot drop databases (SYSADMIN owns them) - ❌ Cannot modify database-level settings (SYSADMIN controls this)

Required schema-level grants in migrations:

Each schemachange migration that creates a schema must include grants. Example:

-- Create schema
CREATE SCHEMA IF NOT EXISTS CND_DB_DEV.BRONZE;

-- Grant Airflow write access to BRONZE
GRANT USAGE ON SCHEMA CND_DB_DEV.BRONZE TO ROLE XO_AIRFLOW_LOADER_DEV;
GRANT CREATE TABLE ON SCHEMA CND_DB_DEV.BRONZE TO ROLE XO_AIRFLOW_LOADER_DEV;
GRANT INSERT ON ALL TABLES IN SCHEMA CND_DB_DEV.BRONZE TO ROLE XO_AIRFLOW_LOADER_DEV;
GRANT INSERT ON FUTURE TABLES IN SCHEMA CND_DB_DEV.BRONZE TO ROLE XO_AIRFLOW_LOADER_DEV;

-- Grant dbt read access to BRONZE
GRANT USAGE ON SCHEMA CND_DB_DEV.BRONZE TO ROLE XO_DBT_TRANSFORMER_DEV;
GRANT SELECT ON ALL TABLES IN SCHEMA CND_DB_DEV.BRONZE TO ROLE XO_DBT_TRANSFORMER_DEV;
GRANT SELECT ON FUTURE TABLES IN SCHEMA CND_DB_DEV.BRONZE TO ROLE XO_DBT_TRANSFORMER_DEV;

-- Grant dbt full access to SILVER/GOLD
GRANT USAGE ON SCHEMA CND_DB_DEV.SILVER TO ROLE XO_DBT_TRANSFORMER_DEV;
GRANT ALL PRIVILEGES ON SCHEMA CND_DB_DEV.SILVER TO ROLE XO_DBT_TRANSFORMER_DEV;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA CND_DB_DEV.SILVER TO ROLE XO_DBT_TRANSFORMER_DEV;
GRANT ALL PRIVILEGES ON FUTURE TABLES IN SCHEMA CND_DB_DEV.SILVER TO ROLE XO_DBT_TRANSFORMER_DEV;

-- Grant analysts read access to GOLD only
GRANT USAGE ON SCHEMA CND_DB_DEV.GOLD TO ROLE XO_ANALYST_CONDENAST;
GRANT SELECT ON ALL TABLES IN SCHEMA CND_DB_DEV.GOLD TO ROLE XO_ANALYST_CONDENAST;
GRANT SELECT ON FUTURE TABLES IN SCHEMA CND_DB_DEV.GOLD TO ROLE XO_ANALYST_CONDENAST;

Why grants are in migrations: - ✅ Version controlled with schema changes - ✅ Auditable in CHANGE_HISTORY table - ✅ Repeatable and testable - ✅ Clear separation: bootstrap = infrastructure, migrations = data permissions

See: schemachange-deployment.md for next steps.

---

Verification Queries

Run these to verify everything is set up correctly:

-- 1. Verify warehouses
USE ROLE SYSADMIN;
SHOW WAREHOUSES LIKE 'XO_%';
-- Expected: 3 warehouses

-- 2. Verify databases
SHOW DATABASES LIKE '%_DB_%';
-- Expected: 6 databases

-- 3. Verify database ownership
SHOW GRANTS ON DATABASE CND_DB_DEV;
-- Expected: SYSADMIN has OWNERSHIP

-- 4. Verify roles
SHOW ROLES LIKE 'XO_%';
-- Expected: 15 roles

-- 5. Verify your access
USE ROLE XO_DATA_ENGINEER_DEV;
USE WAREHOUSE XO_DEV_WH;
SELECT CURRENT_ROLE(), CURRENT_WAREHOUSE();
-- Expected: XO_DATA_ENGINEER_DEV, XO_DEV_WH

-- 6. Verify service accounts
USE ROLE SECURITYADMIN;
SHOW USERS LIKE '%_SVC';
-- Expected: 5 users with TYPE = SERVICE

-- 7. Verify warehouse permissions
SHOW GRANTS ON WAREHOUSE XO_PROD_WH;
-- Expected: Multiple roles have USAGE including XO_SCHEMACHANGE_DEPLOYER

-- 8. Verify ACCOUNT_USAGE access
SHOW GRANTS TO ROLE XO_AIRFLOW_LOADER_PROD;
-- Expected: IMPORTED PRIVILEGES on SNOWFLAKE database

-- 9. Verify schemachange role grants
SHOW GRANTS TO ROLE XO_SCHEMACHANGE_DEPLOYER;
-- Expected: CREATE SCHEMA + USAGE on all 6 databases, USAGE on XO_PROD_WH

---

Troubleshooting

Issue: "Insufficient privileges" when creating roles

Solution: Ensure you're using USE ROLE SECURITYADMIN; for role creation.

Issue: "Object already exists" error

Solution: Check if you already ran part of the bootstrap. Use DROP ROLE IF EXISTS ... to clean up and retry.

Issue: Warehouse ownership transfer fails

Solution: Ensure you're using USE ROLE ACCOUNTADMIN; before transferring ownership.

Issue: Can't grant ACCOUNT_USAGE access

Solution: Only ACCOUNTADMIN can grant IMPORTED PRIVILEGES on SNOWFLAKE database.

---

Cost Monitoring

Important: Track actual costs via Snowflake UI and query history archival.

How to monitor warehouse costs:

-- View warehouse credit usage (last 30 days)
SELECT
    warehouse_name,
    SUM(credits_used) as total_credits,
    SUM(credits_used) / 30 as avg_credits_per_day
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
  AND warehouse_name LIKE 'XO_%'
GROUP BY warehouse_name
ORDER BY total_credits DESC;

Expected usage patterns (to be validated with actual data): - XO_DEV_WH: Intermittent use by engineers (low daily credits) - XO_PROD_WH: Batch jobs morning/evening (moderate daily credits) - XO_ANALYST_WH: Dashboard refreshes, ad-hoc queries (low daily credits)

Cost optimization: - All warehouses auto-suspend after 5 minutes of inactivity - Start with SMALL size, scale up only if performance requires it - Monitor query queue times - if increasing, consider scaling warehouse - Use query history archival for per-client cost allocation

Storage costs: Monitor via Snowflake UI → Account → Usage

---

Security Best Practices Applied

✅ Principle of Least Privilege: Each role has minimum permissions needed ✅ Separation of Duties: Different systems have different roles (Airflow vs dbt) ✅ Environment Isolation: DEV and PROD roles are separate ✅ Client Data Isolation: Analyst roles restricted to specific databases ✅ Key-Pair Authentication: All service accounts use TYPE=SERVICE with RSA keys (no passwords) ✅ Warehouse Ownership: SYSADMIN owns warehouses (not ACCOUNTADMIN) ✅ Database Ownership: SYSADMIN owns databases (standard Snowflake hierarchy) ✅ Privilege Grants vs Ownership: Operational roles get specific grants, not ownership ✅ Schemachange Security: CREATE SCHEMA privilege only, cannot create/drop databases ✅ Manual Database Control: Database creation requires engineer approval (not automated) ✅ Centralized Infrastructure: All infrastructure owned by SYSADMIN for easier management ✅ Role Hierarchy: Organized parent/child structure for maintainability

---

Bootstrap complete! You're ready to run schemachange migrations. 🎉