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 ```sql -- 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 DATABA...