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
CREATE DATABASE STAGING_DW CLONE PROD_DW;
CREATE DATABASE DEV_DW CLONE PROD_DW;
CREATE DATABASE STAGING_DW CLONE PROD_DW
AT (TIMESTAMP => '2026-03-21 06:00:00'::TIMESTAMP_NTZ);
CREATE OR REPLACE TASK refresh_staging_clone
WAREHOUSE = ADMIN_WH
SCHEDULE = 'USING CRON 0 4 * * * America/New_York'
AS
BEGIN
DROP DATABASE IF EXISTS STAGING_DW;
CREATE DATABASE STAGING_DW CLONE PROD_DW;
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
CREATE WAREHOUSE DEV_WH
WAREHOUSE_SIZE = 'XSMALL'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED = TRUE
RESOURCE_MONITOR = dev_monitor;
CREATE WAREHOUSE STAGING_WH
WAREHOUSE_SIZE = 'MEDIUM'
AUTO_SUSPEND = 120
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED = TRUE
RESOURCE_MONITOR = staging_monitor;
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;
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
CREDIT_QUOTA FREQUENCY MONTHLY START_TIMESTAMP IMMEDIATELY
TRIGGERS DO NOTIFY DO SUSPEND;
RESOURCE MONITOR prod_monitor
CREDIT_QUOTA FREQUENCY MONTHLY START_TIMESTAMP IMMEDIATELY
TRIGGERS DO NOTIFY DO NOTIFY DO SUSPEND;
Step 3: Environment-Specific Roles
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;
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;
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;
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
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 (): {
env = (process.. || ) ;
config = [env];
(!config) ();
config;
}
Step 5: Data Masking for Non-Production
CREATE OR REPLACE MASKING POLICY pii_mask AS (val STRING)
RETURNS STRING ->
CASE
WHEN CURRENT_DATABASE() = 'PROD_DW' THEN val
ELSE SHA2(val)
END;
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
Next Steps
For observability setup, see snowflake-observability.
