snowflake-local-dev-loop

name	snowflake-local-dev-loop
description	Configure Snowflake local development with testing, mocking, and fast iteration. Use when setting up dev environment, writing tests against Snowflake, or establishing a fast iteration cycle with SnowSQL and dev warehouses. Trigger with phrases like "snowflake dev setup", "snowflake local development", "snowflake dev environment", "develop with snowflake", "snowflake testing".
allowed-tools	Read, Write, Edit, Bash(npm:*), Bash(pnpm:*), Grep
version	1.5.0
license	MIT
author	Jeremy Longshore <[e-mail]>
tags	["saas","data-warehouse","analytics","snowflake"]
compatibility	Designed for Claude Code

Snowflake Local Dev Loop

Overview

Set up a fast, reproducible local development workflow for Snowflake with separate dev warehouses, mocked tests, and SnowSQL for rapid iteration.

Prerequisites

• Completed snowflake-install-auth setup
• Node.js 18+ or Python 3.9+
• A dedicated dev warehouse (e.g., DEV_WH_XS) with auto-suspend

Instructions

Step 1: Create Dev-Specific Snowflake Objects

-- Run once to set up isolated dev environment
CREATE WAREHOUSE IF NOT EXISTS DEV_WH_XS
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

CREATE DATABASE IF NOT EXISTS DEV_DB;
CREATE SCHEMA IF NOT EXISTS DEV_DB.SANDBOX;

-- Grant to dev role
GRANT USAGE ON WAREHOUSE DEV_WH_XS TO ROLE DEV_ROLE;
GRANT ALL ON DATABASE DEV_DB TO ROLE DEV_ROLE;

Step 2: Project Structure

my-snowflake-project/
├── src/
│   ├── snowflake/
│   │   ├── connection.ts     # Connection wrapper with connectAsync
│   │   ├── queries.ts        # Typed query functions
│   │   └── types.ts          # Row type definitions
│   └── index.ts
├── tests/
│   ├── unit/
│   │   └── queries.test.ts   # Mocked — no Snowflake needed
│   └── integration/
│       └── snowflake.test.ts # Requires SNOWFLAKE_* env vars
├── sql/
│   ├── migrations/           # Versioned DDL scripts
│   │   ├── V001__create_users.sql
│   │   └── V002__add_orders.sql
│   └── seeds/
│       └── dev-data.sql      # Sample data for dev
├── .env.local                # Local secrets (git-ignored)
├── .env.example              # Template for team
└── package.json

Step 3: Connection Wrapper with Async/Await