Prod¶

Date: 2025-11-19 Status: RECOMMENDATION Related: snowflake-restructure.md

Question¶

With 8 client databases (CND_DB, WBP_DB, etc.), how should we structure dev/UAT/prod environments?

Snowflake Environment Options¶

Option 1: Database-Level Separation ❌ NOT RECOMMENDED¶

CND_DB_DEV
CND_DB_UAT
CND_DB_PROD

WBP_DB_DEV
WBP_DB_UAT
WBP_DB_PROD

CORE_DB_DEV
CORE_DB_UAT
CORE_DB_PROD

Total: 8 clients × 3 envs = 24 databases

Pros: - Simple isolation - Each env completely independent

Cons: - ❌ Database explosion (24+ databases) - ❌ Naming gets messy (CND_DB_DEV or DEV_CND_DB?) - ❌ Harder to manage permissions - ❌ Triple storage costs - ❌ More schemachange complexity (which databases to deploy?)

Verdict: Too many databases, not scalable

Option 2: Schema-Level Separation ❌ NOT RECOMMENDED¶

CND_DB.DEV_BRONZE
CND_DB.DEV_SILVER
CND_DB.DEV_GOLD
CND_DB.UAT_BRONZE
CND_DB.UAT_SILVER
CND_DB.UAT_GOLD
CND_DB.PROD_BRONZE
CND_DB.PROD_SILVER
CND_DB.PROD_GOLD

Pros: - Same database across environments

Cons: - ❌ Schema explosion (9 schemas per database) - ❌ Mixes dev and prod data in same database (security concern) - ❌ Hard to clone/refresh environments - ❌ dbt gets complicated (multiple schemas per layer)

Verdict: Messy, not recommended

Option 3: Separate Snowflake Accounts ⭐ RECOMMENDED¶

Account: XTENDOPS_DEV
├── CND_DB.BRONZE
├── CND_DB.SILVER
├── CND_DB.GOLD
├── WBP_DB.BRONZE
└── CORE_DB.BRONZE

Account: XTENDOPS_UAT
├── CND_DB.BRONZE
├── CND_DB.SILVER
└── (same structure)

Account: XTENDOPS_PROD
├── CND_DB.BRONZE
├── CND_DB.SILVER
└── (same structure)

Pros: - ✅ Complete isolation (dev can't affect prod) - ✅ Same naming across environments (CND_DB is CND_DB everywhere) - ✅ Security - Different accounts = different credentials - ✅ Resource isolation - Dev compute doesn't affect prod - ✅ Independent scaling - Size warehouses per environment - ✅ Industry standard - This is what most companies do - ✅ Easy promotion - Same database names, just different account - ✅ Clean schemachange - Same migrations, different connection

Cons: - ⚠️ Requires 3 Snowflake accounts (cost, but minimal with dev/uat sizing) - ⚠️ Need to manage 3 sets of credentials - ⚠️ Data refresh requires cloning or reloading

Verdict: This is the industry best practice

Option 4: Single Account + Database Cloning 🤔 ALTERNATIVE¶

Account: XTENDOPS_PROD (only account)

Databases:
├── CND_DB          (production, real data)
├── WBP_DB          (production, real data)
├── CND_DB_DEV      (clone of CND_DB for development)
├── WBP_DB_DEV      (clone of WBP_DB for development)

Use Snowflake cloning:

-- Refresh dev from prod
CREATE OR REPLACE DATABASE CND_DB_DEV CLONE CND_DB;

Pros: - ✅ Single account to manage - ✅ Zero-copy clones (fast, cheap initially) - ✅ Real production data structure in dev

Cons: - ❌ Clones start cheap but diverge over time (storage grows) - ❌ Mixed dev/prod in same account (less isolation) - ❌ Still have database explosion (8 prod + 8 dev = 16) - ⚠️ UAT needs yet another set of clones

Verdict: Better than Option 1, but not as clean as Option 3

RECOMMENDED: Option 3 - Separate Accounts¶

Account Structure¶

Account	Purpose	Compute Size	Data Retention
XTENDOPS_DEV	Development, testing	X-Small warehouses	7 days
XTENDOPS_UAT	User acceptance testing	Small warehouses	30 days
XTENDOPS_PROD	Production	Medium/Large warehouses	90+ days

Database Naming (SAME across all accounts)¶

DEV Account:
├── CND_DB.BRONZE
├── CND_DB.SILVER
├── CND_DB.GOLD
├── WBP_DB.BRONZE
└── CORE_DB.BRONZE

UAT Account:
├── CND_DB.BRONZE      (same names!)
├── CND_DB.SILVER
└── (same structure)

PROD Account:
├── CND_DB.BRONZE      (same names!)
├── CND_DB.SILVER
└── (same structure)

Key Insight: Database names are identical across environments. Only the account changes.

Implementation Strategy¶

1. Schemachange Configuration¶

Single migration folder, multiple connection profiles:

apps/snowflake-bronze/
├── V1.0.0__create_cnd_database.sql    # Same file for all envs
├── V1.0.1__create_wbp_database.sql
└── V1.1.0__create_cnd_schemas.sql

Connection files:

# .config/connections-dev.toml
[default]
account = "xtendops_dev"
user = "dev_deploy_user"
database = "CND_DB"
...

# .config/connections-uat.toml
[default]
account = "xtendops_uat"
user = "uat_deploy_user"
database = "CND_DB"
...

# .config/connections-prod.toml
[default]
account = "xtendops_prod"
user = "prod_deploy_user"
database = "CND_DB"
...

Deploy to each environment:

# Dev
schemachange deploy \
  -f apps/snowflake-bronze \
  --connections-file-path .config/connections-dev.toml \
  -c CND_DB.SCHEMACHANGE.CHANGE_HISTORY

# UAT
schemachange deploy \
  -f apps/snowflake-bronze \
  --connections-file-path .config/connections-uat.toml \
  -c CND_DB.SCHEMACHANGE.CHANGE_HISTORY

# Prod
schemachange deploy \
  -f apps/snowflake-bronze \
  --connections-file-path .config/connections-prod.toml \
  -c CND_DB.SCHEMACHANGE.CHANGE_HISTORY

2. dbt Configuration¶

Single dbt project, multiple profiles:

# apps/dbt-xo-analytics/profiles.yml
xo_analytics:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: xtendops_dev
      database: CND_DB
      schema: SILVER
      warehouse: DEV_XOS_WH

    uat:
      type: snowflake
      account: xtendops_uat
      database: CND_DB      # Same database name!
      schema: SILVER
      warehouse: UAT_XOS_WH

    prod:
      type: snowflake
      account: xtendops_prod
      database: CND_DB      # Same database name!
      schema: SILVER
      warehouse: PROD_XOS_WH

Run dbt:

# Dev
dbt run --target dev

# UAT
dbt run --target uat

# Prod
dbt run --target prod

3. Airflow Configuration¶

Environment-specific variables:

# Airflow Variable: SF_ACCOUNT
# Dev: "xtendops_dev"
# UAT: "xtendops_uat"
# Prod: "xtendops_prod"

from airflow.models import Variable

sf_account = Variable.get("SF_ACCOUNT")
sf_database = "CND_DB"  # Same across all envs

connection = create_engine(
    URL(
        account=sf_account,
        database=sf_database,
        ...
    )
)

4. CI/CD Workflow¶

GitHub Actions - Deploy based on branch:

name: Deploy Snowflake BRONZE Layer

on:
  push:
    branches:
      - develop        # → DEV account
      - uat           # → UAT account
      - main          # → PROD account

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - name: Determine Environment
        id: env
        run: |
          if [[ "${{ github.ref }}" == "refs/heads/main" ]]; then
            echo "environment=prod" >> $GITHUB_OUTPUT
            echo "sf_account=${{ vars.SF_ACCOUNT_PROD }}" >> $GITHUB_OUTPUT
          elif [[ "${{ github.ref }}" == "refs/heads/uat" ]]; then
            echo "environment=uat" >> $GITHUB_OUTPUT
            echo "sf_account=${{ vars.SF_ACCOUNT_UAT }}" >> $GITHUB_OUTPUT
          else
            echo "environment=dev" >> $GITHUB_OUTPUT
            echo "sf_account=${{ vars.SF_ACCOUNT_DEV }}" >> $GITHUB_OUTPUT
          fi

      - name: Deploy to Snowflake
        run: |
          schemachange deploy \
            -f apps/snowflake-bronze \
            --connections-file-path .config/connections-${{ steps.env.outputs.environment }}.toml \
            -c CND_DB.SCHEMACHANGE.CHANGE_HISTORY

Data Refresh Strategy¶

DEV Environment¶

Approach: Clone from prod periodically

-- Weekly refresh (automated)
CREATE OR REPLACE DATABASE CND_DB_DEV_BACKUP CLONE CND_DB_DEV;
DROP DATABASE CND_DB_DEV;

-- Clone from PROD account (requires share or replication)
CREATE DATABASE CND_DB AS CLONE XTENDOPS_PROD.CND_DB;

Alternative: Use sample data or synthetic data in dev

UAT Environment¶

Approach: Clone from prod before each testing cycle

-- Before UAT cycle
CREATE OR REPLACE DATABASE CND_DB CLONE XTENDOPS_PROD.CND_DB;

PROD Environment¶

Approach: Real data from Airflow pipelines

Cost Considerations¶

Storage¶

DEV: Minimal (7-day retention, sample data)
UAT: Moderate (30-day retention, cloned from prod)
PROD: Full (90+ day retention, all historical data)

Compute¶

DEV: X-Small warehouses (~$2/hour when running)
UAT: Small warehouses (~$4/hour when running)
PROD: Medium/Large warehouses (~$8-16/hour when running)

Total Monthly Estimate (assuming 8 client databases): - DEV: ~$100-200/month (minimal usage, small warehouses) - UAT: ~$200-500/month (periodic testing, medium usage) - PROD: ~$2,000-5,000/month (24/7 operations, real workload)

Total: ~$2,500-5,700/month for complete dev/uat/prod isolation

Alternative (Single Account): ~$2,000-4,500/month - Savings: ~$500-1,200/month - Trade-off: Less isolation, mixed dev/prod, more complexity

Recommendation: Separate accounts worth the cost for security and isolation

Migration Strategy¶

Phase 1: Setup DEV Account First (Week 1-2)¶

✅ Create DEV Snowflake account
✅ Deploy schemachange to DEV (CND_DB, WBP_DB, CORE_DB)
✅ Set up dbt with dev target
✅ Create Airflow connection to DEV
✅ Test entire pipeline in DEV

Phase 2: Setup UAT Account (Week 3-4)¶

✅ Create UAT Snowflake account
✅ Deploy schemachange to UAT (same migrations)
✅ Clone databases from DEV or load test data
✅ User acceptance testing

Phase 3: Setup PROD Account (Week 5+)¶

✅ Create PROD Snowflake account (or use existing)
✅ Deploy schemachange to PROD
✅ Cutover from legacy OPERATIONS database

Current State Question¶

Do you already have separate Snowflake accounts?

If YES → Use existing accounts (DEV, UAT, PROD) If NO → Need to create them, or start with single account + cloning

What's your current setup? - Single Snowflake account (OPERATIONS database)? - Multiple accounts already?

Recommended Next Steps¶

If Starting Fresh (Recommended)¶

Create DEV account (start here, safe)
Account: XTENDOPS_DEV or similar
Deploy new structure to DEV first
Test everything
Create UAT account (for stakeholder testing)
Account: XTENDOPS_UAT
Clone structure from DEV
Use existing account as PROD (or create new)
Deploy to PROD only after dev/UAT validated

If Cost-Conscious (Alternative)¶

Use existing account for everything initially
Create CND_DB_DEV, CND_DB_UAT, CND_DB (prod)
Accept database explosion temporarily
Migrate to separate accounts later when proven

Summary¶

Approach	Databases	Accounts	Isolation	Cost	Complexity	Recommended
Separate Accounts	8 per env	3	⭐⭐⭐⭐⭐	$$	Low	✅ YES
Database Suffix	24 total	1	⭐⭐	$	High	❌ No
Schema Suffix	8	1	⭐	$	Very High	❌ No
Cloning	16 total	1	⭐⭐⭐	$$	Medium	🤔 Maybe

Recommendation: - ✅ Separate Snowflake accounts (dev/uat/prod) - ✅ Same database names across all accounts (CND_DB is CND_DB everywhere) - ✅ Same migrations deployed to each account via different connections - ✅ Branch-based deployment (develop → dev, uat → uat, main → prod)

Question for you: 1. Do you already have separate Snowflake accounts (dev/uat/prod)? 2. If not, do you want to create them, or start with single account? 3. What's your budget tolerance for separate accounts?