Schemachange Deployment Guide

Date: 2025-11-26 Status: Ready for execution Prerequisite: Bootstrap setup completed (bootstrap-setup.md)

---

Overview

This guide walks you through deploying the Snowflake BRONZE layer infrastructure using schemachange migrations.

What schemachange will create: 1. Databases (CND_DB_DEV, WBP_DB_DEV, CORE_DB_DEV) 2. Schemas (BRONZE, SILVER, GOLD in each database) 3. Query history archive table (CORE_DB.BRONZE.QUERY_HISTORY_ARCHIVE) 4. Database and schema-level permission grants

Deployment environments: - DEV: Deploy by pushing to develop branch or running locally - PROD: Deploy by pushing to main branch (future)

---

Prerequisites Checklist

Before running schemachange, verify:

• Bootstrap setup completed (warehouses, roles, service accounts created)
• Service account passwords saved securely
• GitHub repository secrets configured (if using GitHub Actions)
• Local environment variables set (if running locally)

---

Local Deployment (Test First)

Step 1: Install schemachange

# Ensure you're in the xo-data repo
cd /Users/ocolunga/Developer/xtendops/xo-data

# Install schemachange (if not already installed)
pip install schemachange
# or with uv:
uv tool install schemachange

Step 2: Configure Snowflake Connection

Create a .env file in the repo root (DO NOT commit this file):

# .env (create this file, add to .gitignore)

# Snowflake connection
SNOWFLAKE_ACCOUNT=your_account
SNOWFLAKE_USER=SCHEMACHANGE_SVC
SNOWFLAKE_PASSWORD=your_schemachange_svc_password
SNOWFLAKE_ROLE=SYSADMIN
SNOWFLAKE_WAREHOUSE=XO_PROD_WH

Important: Replace your_account and your_schemachange_svc_password with actual values.

Step 3: Test Connection

# Load environment variables
export $(cat .env | xargs)

# Test Snowflake connection
snowsql -a $SNOWFLAKE_ACCOUNT -u $SNOWFLAKE_USER -r $SNOWFLAKE_ROLE
# Enter password when prompted
# If successful, you'll see: SCHEMACHANGE_SVC#XO_PROD_WH@(no database).(no schema)>
# Exit with: !exit

Step 4: Deploy to DEV (Local Test)

# Deploy to CND_DB_DEV
schemachange deploy \
  -f apps/snowflake-bronze \
  -c CND_DB_DEV.SCHEMACHANGE.CHANGE_HISTORY \
  --create-change-history-table \
  --vars '{"DB_NAME": "CND_DB_DEV", "SF_ROLE": "SYSADMIN", "DB_SUFFIX": "DEV"}' \
  --snowflake-account $SNOWFLAKE_ACCOUNT \
  --snowflake-user $SNOWFLAKE_USER \
  --snowflake-role $SNOWFLAKE_ROLE \
  --snowflake-warehouse $SNOWFLAKE_WAREHOUSE

# Deploy to WBP_DB_DEV
schemachange deploy \
  -f apps/snowflake-bronze \
  -c WBP_DB_DEV.SCHEMACHANGE.CHANGE_HISTORY \
  --create-change-history-table \
  --vars '{"DB_NAME": "WBP_DB_DEV", "SF_ROLE": "SYSADMIN", "DB_SUFFIX": "DEV"}' \
  --snowflake-account $SNOWFLAKE_ACCOUNT \
  --snowflake-user $SNOWFLAKE_USER \
  --snowflake-role $SNOWFLAKE_ROLE \
  --snowflake-warehouse $SNOWFLAKE_WAREHOUSE

# Deploy to CORE_DB_DEV
schemachange deploy \
  -f apps/snowflake-bronze \
  -c CORE_DB_DEV.SCHEMACHANGE.CHANGE_HISTORY \
  --create-change-history-table \
  --vars '{"DB_NAME": "CORE_DB_DEV", "SF_ROLE": "SYSADMIN", "DB_SUFFIX": "DEV"}' \
  --snowflake-account $SNOWFLAKE_ACCOUNT \
  --snowflake-user $SNOWFLAKE_USER \
  --snowflake-role $SNOWFLAKE_ROLE \
  --snowflake-warehouse $SNOWFLAKE_WAREHOUSE

Expected output for each database:

Deploying CND_DB_DEV
  Applying: V1.0.0__create_cnd_database.sql
  Applying: V1.1.0__create_cnd_schemas.sql
  Applying: V1.3.0__create_warehouses.sql (validation only)
  Applying: V1.3.1__create_roles.sql (validation only)
  Applying: V1.3.2__grant_warehouse_permissions.sql (validation only)
  Applying: V1.3.3__grant_database_permissions.sql
  Applying: V1.3.4__grant_cnd_schema_permissions.sql
Successfully deployed

For CORE_DB_DEV, you'll also see:

  Applying: V1.2.0__create_query_history_archive.sql

Step 5: Verify Deployment

-- Log into Snowflake UI or SnowSQL
USE ROLE SYSADMIN;

-- Verify databases created
SHOW DATABASES LIKE '%_DEV';
-- Expected: CND_DB_DEV, WBP_DB_DEV, CORE_DB_DEV

-- Verify schemas in CND_DB_DEV
USE DATABASE CND_DB_DEV;
SHOW SCHEMAS;
-- Expected: BRONZE, SILVER, GOLD, SCHEMACHANGE

-- Verify query history archive table
USE DATABASE CORE_DB_DEV;
USE SCHEMA BRONZE;
SHOW TABLES LIKE 'QUERY_HISTORY_ARCHIVE';
-- Expected: QUERY_HISTORY_ARCHIVE table

-- Verify permissions
USE ROLE XO_DATA_ENGINEER_DEV;
USE WAREHOUSE XO_DEV_WH;
SELECT CURRENT_DATABASE(), CURRENT_SCHEMA(), CURRENT_ROLE();
-- Expected: Should work without errors

-- Try creating a test table in BRONZE
CREATE OR REPLACE TABLE CND_DB_DEV.BRONZE.TEST_TABLE (id INTEGER);
INSERT INTO CND_DB_DEV.BRONZE.TEST_TABLE VALUES (1);
SELECT * FROM CND_DB_DEV.BRONZE.TEST_TABLE;
-- Expected: Should work

-- Cleanup
DROP TABLE CND_DB_DEV.BRONZE.TEST_TABLE;

---

GitHub Actions Deployment

Step 1: Configure GitHub Secrets

Go to your GitHub repository settings → Secrets and variables → Actions

Add the following secrets:

Secret Name	Value	Description
SF_ACCOUNT	your_snowflake_account	Snowflake account identifier
SF_SCHEMACHANGE_USER	SCHEMACHANGE_SVC	Service account username
SF_SCHEMACHANGE_USER_PASSWORD	(password)	Service account password from bootstrap
SF_ROLE	SYSADMIN	Role for schemachange
SF_WAREHOUSE_PROD	XO_PROD_WH	Warehouse for deployments

Note: The workflow uses password authentication. For production, consider using key-pair authentication.

Step 2: Commit Migration Files

# Ensure all migration files are staged
git status

# You should see:
# apps/snowflake-bronze/databases/V1.0.0__create_cnd_database.sql
# apps/snowflake-bronze/databases/V1.0.1__create_wbp_database.sql
# apps/snowflake-bronze/databases/V1.0.2__create_core_database.sql
# apps/snowflake-bronze/schemas/V1.1.0__create_cnd_schemas.sql
# apps/snowflake-bronze/schemas/V1.1.1__create_wbp_schemas.sql
# apps/snowflake-bronze/schemas/V1.1.2__create_core_schemas.sql
# apps/snowflake-bronze/tables/core/V1.2.0__create_query_history_archive.sql
# apps/snowflake-bronze/permissions/V1.3.0-V1.3.6__*.sql
# .claude/ongoing/bootstrap-setup.md
# .claude/ongoing/schemachange-deployment-guide.md

# Add all files
git add apps/snowflake-bronze/
git add .claude/ongoing/

# Commit
git commit -m "feat: add Snowflake BRONZE layer infrastructure with RBAC governance

- Create database/schema structure (CND, WBP, CORE)
- Implement medallion architecture (BRONZE/SILVER/GOLD)
- Add query history archival for cost tracking (7-day workaround)
- Configure RBAC with 12 custom roles and shared warehouses
- Provide bootstrap guide for manual setup
- Add schemachange migrations with environment-aware permissions

Infrastructure is now ready for pipeline deployment."

Step 3: Push to Develop Branch (Deploy to DEV)

# Ensure you're on develop branch
git checkout develop

# Pull latest changes (if working with team)
git pull origin develop

# Merge your changes (or cherry-pick if needed)
git merge your-feature-branch

# Push to develop
git push origin develop

What happens: - GitHub Actions workflow triggers automatically - Deploys to CND_DB_DEV, WBP_DB_DEV, CORE_DB_DEV - Uses SCHEMACHANGE_SVC user with SYSADMIN role - Runs all V1.X.X migrations in order

Step 4: Monitor GitHub Actions

Go to: https://github.com/xtendops/xo-data/actions

Click on the latest workflow run: "Deploy Snowflake BRONZE Layer"

Expected steps: 1. ✅ Checkout Repository 2. ✅ Setup Python 3.9 3. ✅ Determine Environment (DEV) 4. ✅ Configure Snowflake Connection 5. ✅ Install schemachange 6. ✅ Deploy to CND_DB 7. ✅ Deploy to WBP_DB 8. ✅ Deploy to CORE_DB 9. ✅ Deployment Summary

If any step fails, click on it to see error details.

Step 5: Verify Deployment (Same as Local)

Run the verification queries from Step 5 of Local Deployment.

---

Deploying to PROD (Future)

When you're ready to deploy to production:

Step 1: Test Thoroughly in DEV

• All migrations run successfully
• Permissions work as expected
• Airflow can connect and load data to BRONZE
• dbt can transform BRONZE → SILVER → GOLD
• Analysts can query GOLD tables
• Query history archival pipeline works

Step 2: Create PROD Databases

# Checkout main branch
git checkout main

# Merge develop into main (after approval)
git merge develop

# Push to main
git push origin main

What happens: - GitHub Actions workflow triggers - Sets DB_SUFFIX=PROD - Creates: CND_DB_PROD, WBP_DB_PROD, CORE_DB_PROD - Applies same migrations with PROD-specific permissions

Step 3: Create PROD Service Account Credentials

Update Airflow and dbt connections with PROD service accounts:

Airflow: - Connection ID: snowflake_prod - User: AIRFLOW_PROD_SVC - Password: (from bootstrap) - Role: XO_AIRFLOW_LOADER_PROD - Warehouse: XO_PROD_WH

dbt:

# profiles.yml
xo_analytics:
  outputs:
    dev:
      type: snowflake
      account: your_account
      user: DBT_DEV_SVC
      password: "{{ env_var('DBT_DEV_PASSWORD') }}"
      role: XO_DBT_TRANSFORMER_DEV
      warehouse: XO_DEV_WH
      database: CND_DB_DEV
      schema: SILVER

    prod:
      type: snowflake
      account: your_account
      user: DBT_PROD_SVC
      password: "{{ env_var('DBT_PROD_PASSWORD') }}"
      role: XO_DBT_TRANSFORMER_PROD
      warehouse: XO_PROD_WH
      database: CND_DB_PROD
      schema: SILVER

  target: dev  # Default to dev

---

Migration File Structure

apps/snowflake-bronze/
├── databases/
│   ├── V1.0.0__create_cnd_database.sql      # CondeNast DB
│   ├── V1.0.1__create_wbp_database.sql      # Warby Parker DB
│   └── V1.0.2__create_core_database.sql     # Core/shared DB
├── schemas/
│   ├── V1.1.0__create_cnd_schemas.sql       # CND BRONZE/SILVER/GOLD
│   ├── V1.1.1__create_wbp_schemas.sql       # WBP BRONZE/SILVER/GOLD
│   └── V1.1.2__create_core_schemas.sql      # CORE BRONZE/SILVER/GOLD
├── tables/
│   ├── condenast/                           # CND-specific tables (future)
│   ├── warbyparker/                         # WBP-specific tables (future)
│   └── core/
│       └── V1.2.0__create_query_history_archive.sql  # Cost tracking
└── permissions/
    ├── V1.3.0__create_warehouses.sql        # Validation only (manual bootstrap)
    ├── V1.3.1__create_roles.sql             # Validation only (manual bootstrap)
    ├── V1.3.2__grant_warehouse_permissions.sql  # Validation only (manual bootstrap)
    ├── V1.3.3__grant_database_permissions.sql   # DB-level grants
    ├── V1.3.4__grant_cnd_schema_permissions.sql # CND schema grants
    ├── V1.3.5__grant_wbp_schema_permissions.sql # WBP schema grants
    └── V1.3.6__grant_core_schema_permissions.sql # CORE schema grants

Execution order: V1.0.X → V1.1.X → V1.2.X → V1.3.X

---

Troubleshooting

Issue: "Object does not exist or not authorized"

Cause: Bootstrap not completed, or running as wrong role.

Solution:

-- Verify you're using SYSADMIN
SELECT CURRENT_ROLE();  -- Should be SYSADMIN

-- Verify warehouses exist
SHOW WAREHOUSES LIKE 'XO_%';  -- Should see 3 warehouses

-- Verify roles exist
SHOW ROLES LIKE 'XO_%';  -- Should see 12 roles

If missing, complete bootstrap-setup.md first.

Issue: "Insufficient privileges to create database"

Cause: SCHEMACHANGE_SVC user doesn't have SYSADMIN role.

Solution:

USE ROLE SECURITYADMIN;
GRANT ROLE SYSADMIN TO USER SCHEMACHANGE_SVC;

Issue: Migration fails on V1.3.3 with "Role XO_AIRFLOW_LOADER_DEV does not exist"

Cause: Bootstrap not completed.

Solution: Run bootstrap script to create roles first.

Issue: GitHub Actions fails with "Authentication failed"

Cause: GitHub Secrets not configured or incorrect password.

Solution: 1. Verify GitHub Secrets exist in repository settings 2. Check SF_SCHEMACHANGE_USER_PASSWORD matches bootstrap password 3. Test connection locally with same credentials

Issue: "Change history table does not exist"

Cause: First deployment, change history table not created yet.

Solution: schemachange creates it automatically with --create-change-history-table flag. This is normal on first run.

---

Change History Queries

View Deployment History

-- See all migrations applied to CND_DB_DEV
SELECT
    version,
    description,
    script,
    installed_on,
    execution_time
FROM CND_DB_DEV.SCHEMACHANGE.CHANGE_HISTORY
ORDER BY installed_rank DESC;

Check Last Deployment

-- See most recent migration
SELECT
    version,
    description,
    installed_on,
    success
FROM CND_DB_DEV.SCHEMACHANGE.CHANGE_HISTORY
ORDER BY installed_rank DESC
LIMIT 1;

---

Next Steps After Deployment

1. Set up Airflow query history archival pipeline
2. Create BRONZE table DDL - As pipelines are built
3. Set up dbt project - For SILVER and GOLD transformations
4. Update Airflow DAGs - Point to new *_DEV databases
5. Test end-to-end pipeline - Extract → BRONZE → SILVER → GOLD
6. Deploy to PROD - When DEV is validated

---

Deployment Checklist

Pre-Deployment

• Bootstrap completed (warehouses, roles, users)
• Service account passwords saved securely
• GitHub Secrets configured (if using GitHub Actions)
• Local environment variables set (if running locally)

DEV Deployment

• Run schemachange locally to test
• Verify databases created (CND_DB_DEV, WBP_DB_DEV, CORE_DB_DEV)
• Verify schemas created (BRONZE, SILVER, GOLD)
• Verify query history archive table created
• Test engineer access (XO_DATA_ENGINEER_DEV)
• Test service account access (AIRFLOW_DEV_SVC, DBT_DEV_SVC)
• Push to develop branch
• Monitor GitHub Actions workflow
• Verify deployment in Snowflake UI

PROD Deployment (Future)

• Thoroughly test in DEV first
• Update Airflow connections with PROD service accounts
• Update dbt profiles with PROD target
• Merge develop into main
• Push to main branch
• Monitor GitHub Actions workflow
• Verify PROD databases created
• Test PROD service account access
• Create client analyst users (when ready)

---

Deployment complete! Your BRONZE layer infrastructure is ready for data pipelines. 🎉