Single Account Strategy - Prove Value First¶

Date: 2025-11-19 Status: RECOMMENDED - Pragmatic approach for budget constraints Related: snowflake-environments.md

Situation¶

✅ Single Snowflake account (existing)
✅ Need to prove ROI before getting budget for separate accounts
✅ Want to test new architecture safely
✅ Keep current production (OPERATIONS) running

RECOMMENDED: Phased Approach¶

Phase 1: DEV Only (Week 1-6) - START HERE¶

Create development databases in existing account:

Existing Account (e.g., XTENDOPS_PROD)
├── OPERATIONS          ← Current prod (KEEP RUNNING)
├── TRAINING_DB         ← Current training (KEEP RUNNING)
│
└── NEW (Development):
    ├── CND_DB_DEV.BRONZE
    ├── CND_DB_DEV.SILVER
    ├── CND_DB_DEV.GOLD
    ├── WBP_DB_DEV.BRONZE
    ├── WBP_DB_DEV.SILVER
    ├── WBP_DB_DEV.GOLD
    └── CORE_DB_DEV.BRONZE

Why this approach: - ✅ Zero risk to production (OPERATIONS untouched) - ✅ Minimal cost (small dev warehouses, limited data) - ✅ Prove the architecture (test dbt, new structure) - ✅ Build confidence (stakeholders see it working) - ✅ No commitment (can delete if doesn't work out)

Phase 2: Parallel Production (Week 7-10) - After Dev Proven¶

Add production databases (still same account):

Existing Account
├── OPERATIONS          ← Legacy prod (parallel run)
├── TRAINING_DB
│
├── DEV:
│   ├── CND_DB_DEV
│   └── WBP_DB_DEV
│
└── PROD (NEW):
    ├── CND_DB_PROD.BRONZE
    ├── CND_DB_PROD.SILVER
    ├── CND_DB_PROD.GOLD
    ├── WBP_DB_PROD.BRONZE
    └── CORE_DB_PROD.BRONZE

Parallel run for 2-4 weeks: - Both OPERATIONS and CND_DB_PROD running - Compare results, validate accuracy - Build cost comparison data for stakeholders

Phase 3: Prove ROI (Week 11-12)¶

Collect metrics: - ✅ Query performance: dbt vs dynamic tables - ✅ Compute costs: New vs legacy - ✅ Storage costs: BRONZE/SILVER/GOLD vs STAGING/ODS/AGGREGATE - ✅ Development velocity: dbt testing, faster iterations - ✅ Data quality: dbt tests catching issues

Present to stakeholders: - Cost savings/increases - Performance improvements - Faster feature development - Better data quality

Phase 4: Migrate to Separate Accounts (Later) - After Budget Approval¶

When ROI proven and budget approved:

Before (Single Account):
XTENDOPS_PROD account:
├── CND_DB_DEV
└── CND_DB_PROD

After (Separate Accounts):
XTENDOPS_DEV account:
└── CND_DB          ← Same name! (drop _DEV suffix)

XTENDOPS_PROD account:
└── CND_DB          ← Same name! (drop _PROD suffix)

Migration is EASY: - Same database name (CND_DB) - Same migrations (just different connection) - Just change connection config - No code changes needed

Database Naming Convention (Temporary)¶

Format: `{CLIENT_CODE}_DB_{ENV}`¶

Development:
├── CND_DB_DEV
├── WBP_DB_DEV
└── CORE_DB_DEV

Production (later):
├── CND_DB_PROD
├── WBP_DB_PROD
└── CORE_DB_PROD

Why DEV suffix (not DEV prefix): - Groups by client: CND_DB_DEV, CND_DB_PROD appear together in database list - Easier to drop suffix later when migrating to separate accounts - Alphabetically sorted by client

Security Isolation (Single Account)¶

Role-Based Access Control¶

-- Development roles
CREATE ROLE DEV_DEVELOPER;
CREATE ROLE DEV_ANALYST;

-- Production roles
CREATE ROLE PROD_DEVELOPER;
CREATE ROLE PROD_ANALYST;

-- Grant development access
GRANT USAGE ON DATABASE CND_DB_DEV TO ROLE DEV_DEVELOPER;
GRANT USAGE ON DATABASE WBP_DB_DEV TO ROLE DEV_DEVELOPER;

-- Grant production access (restricted)
GRANT USAGE ON DATABASE CND_DB_PROD TO ROLE PROD_DEVELOPER;
GRANT SELECT ON ALL TABLES IN SCHEMA CND_DB_PROD.GOLD TO ROLE PROD_ANALYST;

-- Ensure separation
REVOKE USAGE ON DATABASE CND_DB_PROD FROM ROLE DEV_DEVELOPER;  -- Devs can't access prod
REVOKE USAGE ON DATABASE CND_DB_DEV FROM ROLE PROD_ANALYST;    -- Analysts don't need dev

Network Policies (Optional)¶

-- Restrict production database access to specific IPs
CREATE NETWORK POLICY PROD_ONLY_POLICY
  ALLOWED_IP_LIST = ('YOUR_OFFICE_IP', 'AIRFLOW_IP');

ALTER DATABASE CND_DB_PROD SET NETWORK_POLICY = PROD_ONLY_POLICY;

Separate Warehouses¶

-- Development warehouses (auto-suspend aggressively)
CREATE WAREHOUSE DEV_XOS_WH
  WAREHOUSE_SIZE = 'X-SMALL'
  AUTO_SUSPEND = 60        -- 1 minute
  AUTO_RESUME = TRUE;

-- Production warehouses (stay active longer)
CREATE WAREHOUSE PROD_XOS_WH
  WAREHOUSE_SIZE = 'SMALL'
  AUTO_SUSPEND = 300       -- 5 minutes
  AUTO_RESUME = TRUE;

Migration Files (Account-Agnostic)¶

Use Jinja Variables¶

-- V1.0.0__create_cnd_database.sql
CREATE DATABASE IF NOT EXISTS {{ DB_NAME }}
    COMMENT = 'CondeNast client database - BRONZE/SILVER/GOLD layers';

-- V1.1.0__create_cnd_schemas.sql
USE DATABASE {{ DB_NAME }};

CREATE SCHEMA IF NOT EXISTS BRONZE;
CREATE SCHEMA IF NOT EXISTS SILVER;
CREATE SCHEMA IF NOT EXISTS GOLD;

Deploy with Variables¶

# Development
schemachange deploy \
  -f apps/snowflake-bronze \
  --connections-file .config/connections.toml \
  -c CND_DB_DEV.SCHEMACHANGE.CHANGE_HISTORY \
  --vars '{"DB_NAME": "CND_DB_DEV"}'

# Production (later)
schemachange deploy \
  -f apps/snowflake-bronze \
  --connections-file .config/connections.toml \
  -c CND_DB_PROD.SCHEMACHANGE.CHANGE_HISTORY \
  --vars '{"DB_NAME": "CND_DB_PROD"}'

# Separate account (future)
schemachange deploy \
  -f apps/snowflake-bronze \
  --connections-file .config/connections-prod-account.toml \
  -c CND_DB.SCHEMACHANGE.CHANGE_HISTORY \
  --vars '{"DB_NAME": "CND_DB"}'

Same migration files work everywhere!

Cost Optimization for Development¶

1. Small Warehouses¶

-- Use X-Small for dev
CREATE WAREHOUSE DEV_XOS_WH
  WAREHOUSE_SIZE = 'X-SMALL'     -- ~$2/hour (vs $4+ for small)
  AUTO_SUSPEND = 60               -- Suspend after 1 min idle

2. Limited Data Retention¶

-- Keep only recent data in dev
CREATE TABLE CND_DB_DEV.BRONZE.GLADLY_CONVERSATIONS (
    ...
    _LOADED_AT TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

-- Add retention policy (automated cleanup)
-- Only keep last 30 days in dev
DELETE FROM CND_DB_DEV.BRONZE.GLADLY_CONVERSATIONS
WHERE _LOADED_AT < DATEADD(DAY, -30, CURRENT_TIMESTAMP());

3. Sample Data Strategy¶

Option A: Load sample data only (1-10% of prod)

# Airflow DAG for dev - sample only
if environment == 'dev':
    df = df.sample(frac=0.1)  # 10% sample

Option B: Clone from production periodically

-- Weekly refresh: Clone prod → dev (zero-copy initially)
CREATE OR REPLACE DATABASE CND_DB_DEV
    CLONE OPERATIONS
    DATA_RETENTION_TIME_IN_DAYS = 7;

Folder Structure (Single Account)¶

Same structure works for both approaches¶

apps/snowflake-bronze/
├── databases/
│   ├── V1.0.0__create_cnd_database.sql        # Uses {{ DB_NAME }}
│   ├── V1.0.1__create_wbp_database.sql
│   └── V1.0.2__create_core_database.sql
│
├── schemas/
│   ├── V1.1.0__create_cnd_schemas.sql         # Uses {{ DB_NAME }}
│   └── V1.1.1__create_wbp_schemas.sql
│
├── tables/
│   ├── condenast/
│   │   └── V1.2.0__cnd_gladly_conversations.sql
│   └── core/
│       └── V1.2.20__core_bamboohr_employees.sql
│
└── permissions/
    ├── V1.3.0__create_roles_dev.sql
    └── V1.3.1__create_roles_prod.sql

CI/CD Strategy (Single Account)¶

Branch-Based Deployment¶

# .github/workflows/snowflake-bronze-deploy.yml
name: Deploy Snowflake BRONZE Layer

on:
  push:
    branches:
      - develop        # → *_DEV databases
      - main          # → *_PROD databases (later)

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - name: Determine Environment
        id: env
        run: |
          if [[ "${{ github.ref }}" == "refs/heads/main" ]]; then
            echo "db_suffix=PROD" >> $GITHUB_OUTPUT
          else
            echo "db_suffix=DEV" >> $GITHUB_OUTPUT
          fi

      - name: Deploy CND_DB
        run: |
          schemachange deploy \
            -f apps/snowflake-bronze \
            --connections-file .config/connections.toml \
            -c CND_DB_${{ steps.env.outputs.db_suffix }}.SCHEMACHANGE.CHANGE_HISTORY \
            --vars '{"DB_NAME": "CND_DB_${{ steps.env.outputs.db_suffix }}"}'

      - name: Deploy WBP_DB
        run: |
          schemachange deploy \
            -f apps/snowflake-bronze \
            --connections-file .config/connections.toml \
            -c WBP_DB_${{ steps.env.outputs.db_suffix }}.SCHEMACHANGE.CHANGE_HISTORY \
            --vars '{"DB_NAME": "WBP_DB_${{ steps.env.outputs.db_suffix }}"}'

Migration Path to Separate Accounts¶

When budget approved:

Step 1: Create New Accounts¶

# Create DEV account
snowsql -c dev_account
CREATE WAREHOUSE DEV_XOS_WH ...;

Step 2: Update Connection Files¶

# NEW: .config/connections-dev-account.toml
[default]
account = "xtendops_dev"      # Different account
database = "CND_DB"           # No _DEV suffix!
...

Step 3: Redeploy (Same Migrations!)¶

# Same migration files, just different connection + DB name
schemachange deploy \
  -f apps/snowflake-bronze \
  --connections-file .config/connections-dev-account.toml \
  -c CND_DB.SCHEMACHANGE.CHANGE_HISTORY \
  --vars '{"DB_NAME": "CND_DB"}'  # No suffix!

Step 4: Update dbt/Airflow¶

# dbt profiles.yml - just change account
xo_analytics:
  outputs:
    dev:
      account: xtendops_dev     # Changed
      database: CND_DB          # No suffix!

Code changes: Minimal (just connection config)

Recommended Rollout Plan¶

Week 1-2: Setup DEV Infrastructure¶

✅ Create CND_DB_DEV, WBP_DB_DEV, CORE_DB_DEV
✅ Deploy BRONZE/SILVER/GOLD schemas
✅ Set up permissions (DEV roles)
✅ Create DEV warehouses (X-Small)

Week 3-4: Build & Test¶

✅ Create BRONZE tables (pilot: CondeNast)
✅ Set up dbt project
✅ Create SILVER models
✅ Create GOLD models
✅ Load sample/test data

Week 5-6: Validate¶

✅ Run queries, compare with legacy
✅ Test Airflow integration
✅ Stakeholder demos
✅ Collect metrics (performance, costs)

Week 7-10: Pilot in Production (If Approved)¶

✅ Create CND_DB_PROD, WBP_DB_PROD
✅ Deploy same migrations
✅ Parallel run with OPERATIONS
✅ Validate results

Week 11-12: ROI Analysis¶

✅ Cost comparison (new vs legacy)
✅ Performance metrics
✅ Present to stakeholders
✅ Get budget for separate accounts (if needed)

Summary¶

Current Situation¶

Single Snowflake account
Need to prove value first
Budget constraints

Recommended Approach¶

✅ Phase 1: Create *_DEV databases in existing account (low risk, low cost)
✅ Phase 2: Add *_PROD databases after dev proven (still same account)
✅ Phase 3: Collect metrics, prove ROI
✅ Phase 4: Migrate to separate accounts when budget approved

Key Benefits¶

✅ No new accounts needed (start today)
✅ Zero risk to production (OPERATIONS untouched)
✅ Easy migration later (same migrations, just connection change)
✅ Security through RBAC (roles separate dev from prod)
✅ Prove value first (collect metrics for stakeholders)

Total Databases¶

Now: 2 (OPERATIONS, TRAINING_DB)
Phase 1 (Dev): +3 (CND_DB_DEV, WBP_DB_DEV, CORE_DB_DEV) = 5 total
Phase 2 (Prod): +3 (CND_DB_PROD, WBP_DB_PROD, CORE_DB_PROD) = 8 total
Manageable for a single account

Next Steps¶

START HERE: 1. Create CND_DB_DEV and WBP_DB_DEV only 2. Build and test in dev 3. Prove it works 4. Decide on prod rollout

Ready to create the migration files?