snowflake-multi-env-setup

name	snowflake-multi-env-setup
description	Configure Snowflake across dev, staging, and production with account-level isolation, zero-copy clones, and environment-specific RBAC. Trigger with phrases like "snowflake environments", "snowflake staging", "snowflake dev prod", "snowflake clone", "snowflake environment setup".
allowed-tools	Read, Write, Edit, Bash(aws:*), Bash(gcloud:*), Bash(vault:*)
version	1.5.0
license	MIT
author	Jeremy Longshore <[e-mail]>
tags	["saas","data-warehouse","analytics","snowflake"]
compatibility	Designed for Claude Code

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;
    
     USAGE  DATABASE STAGING_DW  ROLE STAGING_ROLE;
     USAGE   SCHEMAS  DATABASE STAGING_DW  ROLE STAGING_ROLE;
        TABLES  DATABASE STAGING_DW  ROLE STAGING_ROLE;
  ;

 TASK refresh_staging_clone RESUME;