Snowflake multi env setup

Snowflake Multi-Environment Setup

Overview

Configure dev/staging/production environments using Snowflake's zero-copy cloning, separate databases, and environment-specific roles and warehouses.

Environment Strategy

Environment	Approach	Data	Warehouse	Cost
Development	Cloned DB, XSMALL WH	Zero-copy clone (refreshed weekly)	DEV_WH_XS	Minimal
Staging	Cloned DB, same-size WH	Zero-copy clone (refreshed daily)	STAGING_WH	Moderate
Production	Source of truth	Real data	PROD_WH (multi-cluster)	Full

Instructions

Step 1: Create Environment Databases with Zero-Copy Cloning

-- Zero-copy clone creates instant copy with no additional storage cost
-- Storage cost only accrues when cloned data diverges from source

-- Clone production to staging (point-in-time)
CREATE DATABASE STAGING_DW CLONE PROD_DW;

-- Clone to dev
CREATE DATABASE DEV_DW CLONE PROD_DW;

-- Clone from a specific point in time (Time Travel)
CREATE DATABASE STAGING_DW CLONE PROD_DW
  AT (TIMESTAMP => '2026-03-21 06:00:00'::TIMESTAMP_NTZ);

-- Refresh clone (drop and re-clone)
-- Schedule this as a task:
CREATE OR REPLACE TASK refresh_staging_clone
  WAREHOUSE = ADMIN_WH
  SCHEDULE = 'USING CRON 0 4 * * * America/New_York'  -- 4 AM ET daily
AS
  BEGIN
    DROP DATABASE IF EXISTS STAGING_DW;
    CREATE DATABASE STAGING_DW CLONE PROD_DW;
    -- Re-grant permissions after clone
    GRANT USAGE ON DATABASE STAGING_DW TO ROLE STAGING_ROLE;
    GRANT USAGE ON ALL SCHEMAS IN DATABASE STAGING_DW TO ROLE STAGING_ROLE;
    GRANT SELECT ON ALL TABLES IN DATABASE STAGING_DW TO ROLE STAGING_ROLE;
  END;

ALTER TASK refresh_staging_clone RESUME;

Step 2: Environment-Specific Warehouses

-- Development: minimal size, aggressive auto-suspend
CREATE WAREHOUSE DEV_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  RESOURCE_MONITOR = dev_monitor;

-- Staging: mirrors production size for realistic testing
CREATE WAREHOUSE STAGING_WH
  WAREHOUSE_SIZE = 'MEDIUM'
  AUTO_SUSPEND = 120
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  RESOURCE_MONITOR = staging_monitor;

-- Production: multi-cluster for concurrency
CREATE WAREHOUSE PROD_WH
  WAREHOUSE_SIZE = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 4
  SCALING_POLICY = 'STANDARD'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  RESOURCE_MONITOR = prod_monitor;

-- Resource monitors per environment
CREATE RESOURCE MONITOR dev_monitor
  WITH CREDIT_QUOTA = 50 FREQUENCY = MONTHLY START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 100 PERCENT DO SUSPEND;

CREATE RESOURCE MONITOR staging_monitor
  WITH CREDIT_QUOTA = 200 FREQUENCY = MONTHLY START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 90 PERCENT DO NOTIFY ON 100 PERCENT DO SUSPEND;

CREATE RESOURCE MONITOR prod_monitor
  WITH CREDIT_QUOTA = 2000 FREQUENCY = MONTHLY START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 75 PERCENT DO NOTIFY ON 90 PERCENT DO NOTIFY ON 100 PERCENT DO SUSPEND;

Step 3: Environment-Specific Roles

-- Dev role: full access to dev DB only
CREATE ROLE DEV_ROLE;
GRANT USAGE ON WAREHOUSE DEV_WH TO ROLE DEV_ROLE;
GRANT ALL ON DATABASE DEV_DW TO ROLE DEV_ROLE;
GRANT ALL ON ALL SCHEMAS IN DATABASE DEV_DW TO ROLE DEV_ROLE;

-- Staging role: read/write staging, read-only prod
CREATE ROLE STAGING_ROLE;
GRANT USAGE ON WAREHOUSE STAGING_WH TO ROLE STAGING_ROLE;
GRANT ALL ON DATABASE STAGING_DW TO ROLE STAGING_ROLE;
GRANT USAGE ON DATABASE PROD_DW TO ROLE STAGING_ROLE;
GRANT SELECT ON ALL TABLES IN DATABASE PROD_DW TO ROLE STAGING_ROLE;

-- Production role: minimal, service-account driven
CREATE ROLE PROD_ETL_ROLE;
GRANT USAGE ON WAREHOUSE PROD_WH TO ROLE PROD_ETL_ROLE;
GRANT ALL ON DATABASE PROD_DW TO ROLE PROD_ETL_ROLE;

-- Hierarchy
GRANT ROLE DEV_ROLE TO ROLE SYSADMIN;
GRANT ROLE STAGING_ROLE TO ROLE SYSADMIN;
GRANT ROLE PROD_ETL_ROLE TO ROLE SYSADMIN;

Step 4: Application Configuration

// src/snowflake/env-config.ts
type Environment = 'development' | 'staging' | 'production';

interface SnowflakeEnvConfig {
  account: string;
  warehouse: string;
  database: string;
  role: string;
}

const ENV_CONFIGS: Record<Environment, SnowflakeEnvConfig> = {
  development: {
    account: process.env.SNOWFLAKE_ACCOUNT!,
    warehouse: 'DEV_WH',
    database: 'DEV_DW',
    role: 'DEV_ROLE',
  },
  staging: {
    account: process.env.SNOWFLAKE_ACCOUNT!,
    warehouse: 'STAGING_WH',
    database: 'STAGING_DW',
    role: 'STAGING_ROLE',
  },
  production: {
    account: process.env.SNOWFLAKE_ACCOUNT!,
    warehouse: 'PROD_WH',
    database: 'PROD_DW',
    role: 'PROD_ETL_ROLE',
  },
};

export function getEnvConfig(): SnowflakeEnvConfig {
  const env = (process.env.NODE_ENV || 'development') as Environment;
  const config = ENV_CONFIGS[env];
  if (!config) throw new Error(`Unknown environment: ${env}`);
  return config;
}

Step 5: Data Masking for Non-Production

-- Mask PII in dev/staging clones
CREATE OR REPLACE MASKING POLICY pii_mask AS (val STRING)
  RETURNS STRING ->
  CASE
    WHEN CURRENT_DATABASE() = 'PROD_DW' THEN val
    ELSE SHA2(val)  -- Hash PII in non-prod
  END;

-- Apply to sensitive columns
ALTER TABLE DEV_DW.SILVER.USERS MODIFY COLUMN email
  SET MASKING POLICY pii_mask;
ALTER TABLE STAGING_DW.SILVER.USERS MODIFY COLUMN email
  SET MASKING POLICY pii_mask;

Error Handling

Issue	Cause	Solution
Clone takes too long	Very large DB	Clone is instant; check for metadata operations
Wrong database context	Environment mismatch	Verify NODE_ENV and connection config
Dev credits exhausted	Resource monitor hit	Increase quota or wait for monthly reset
Clone permissions lost	Grants not re-applied after refresh	Add grants to refresh task

Resources

• Zero-Copy Cloning
• Time Travel
• Masking Policies

Next Steps

For observability setup, see snowflake-observability.