AGENT SKILL

snowflake-local-dev-loop

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".

License 许可
MIT
最近更新
2026-07-13
Stars
★ 1
Forks
0

01 前言 Front Matter

name
snowflake-local-dev-loop
tags
[ "saas", "data-warehouse", "analytics", "snowflake" ]
author
Jeremy Longshore <jeremy@intentsolutions.io>
license
MIT
version
1.0.0
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
compatible-with
claude-code

03 SKILL.md 正文

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

// src/snowflake/connection.ts
import snowflake from 'snowflake-sdk';

// Enable promise-based API
snowflake.configure({ logLevel: 'WARN' });

export function createSnowflakeConnection() {
  return snowflake.createConnection({
    account: process.env.SNOWFLAKE_ACCOUNT!,
    username: process.env.SNOWFLAKE_USER!,
    password: process.env.SNOWFLAKE_PASSWORD!,
    warehouse: process.env.SNOWFLAKE_WAREHOUSE || 'DEV_WH_XS',
    database: process.env.SNOWFLAKE_DATABASE || 'DEV_DB',
    schema: process.env.SNOWFLAKE_SCHEMA || 'SANDBOX',
    role: process.env.SNOWFLAKE_ROLE || 'DEV_ROLE',
  });
}

// Promise wrapper for connection.execute
export function executeQuery(
  conn: snowflake.Connection,
  sqlText: string,
  binds?: any[]
): Promise<any[]> {
  return new Promise((resolve, reject) => {
    conn.execute({
      sqlText,
      binds,
      complete: (err, stmt, rows) => {
        if (err) reject(err);
        else resolve(rows || []);
      },
    });
  });
}

// Promise wrapper for connect
export function connectAsync(
  conn: snowflake.Connection
): Promise<snowflake.Connection> {
  return new Promise((resolve, reject) => {
    conn.connect((err, conn) => {
      if (err) reject(err);
      else resolve(conn);
    });
  });
}

Step 4: Unit Tests with Mocked Snowflake

// tests/unit/queries.test.ts
import { describe, it, expect, vi, beforeEach } from 'vitest';

// Mock the snowflake-sdk module
vi.mock('snowflake-sdk', () => ({
  default: {
    configure: vi.fn(),
    createConnection: vi.fn(() => ({
      connect: vi.fn((cb) => cb(null, { getId: () => 'mock-id' })),
      execute: vi.fn(({ sqlText, complete }) => {
        // Return mock data based on query
        if (sqlText.includes('CURRENT_WAREHOUSE')) {
          complete(null, {}, [{ WAREHOUSE: 'DEV_WH_XS' }]);
        } else if (sqlText.includes('SELECT')) {
          complete(null, {}, [
            { ID: 1, NAME: 'Alice' },
            { ID: 2, NAME: 'Bob' },
          ]);
        } else {
          complete(null, { getNumUpdatedRows: () => 1 }, []);
        }
      }),
      destroy: vi.fn((cb) => cb(null)),
    })),
  },
}));

import { createSnowflakeConnection, executeQuery, connectAsync } from '../../src/snowflake/connection';

describe('Snowflake Queries', () => {
  it('should connect and execute a query', async () => {
    const conn = createSnowflakeConnection();
    await connectAsync(conn);
    const rows = await executeQuery(conn, 'SELECT * FROM USERS');
    expect(rows).toHaveLength(2);
    expect(rows[0].NAME).toBe('Alice');
  });
});

Step 5: Integration Tests (Against Real Snowflake)

// tests/integration/snowflake.test.ts
import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import { createSnowflakeConnection, connectAsync, executeQuery } from '../../src/snowflake/connection';

describe.skipIf(!process.env.SNOWFLAKE_ACCOUNT)('Snowflake Integration', () => {
  let conn: any;

  beforeAll(async () => {
    conn = createSnowflakeConnection();
    await connectAsync(conn);
    // Create temp table for test isolation
    await executeQuery(conn, `
      CREATE TEMPORARY TABLE test_users (
        id INTEGER AUTOINCREMENT, name VARCHAR(100)
      )
    `);
  });

  afterAll(async () => {
    conn?.destroy(() => {});
  });

  it('should insert and query data', async () => {
    await executeQuery(conn,
      'INSERT INTO test_users (name) VALUES (?)', ['TestUser']
    );
    const rows = await executeQuery(conn, 'SELECT * FROM test_users');
    expect(rows.length).toBeGreaterThan(0);
    expect(rows[0].NAME).toBe('TestUser');
  });
});

Step 6: SnowSQL for Quick Iteration

# Install SnowSQL CLI
brew install --cask snowflake-snowsql  # macOS

# Configure named connection
cat >> ~/.snowsql/config << 'EOF'
[connections.dev]
accountname = myorg-myaccount
username = my_user
dbname = DEV_DB
schemaname = SANDBOX
warehousename = DEV_WH_XS
rolename = DEV_ROLE
EOF

# Quick queries
snowsql -c dev -q "SELECT COUNT(*) FROM my_table"

# Run migration scripts
snowsql -c dev -f sql/migrations/V001__create_users.sql

Error Handling

Error Cause Solution
000606: No active warehouse Dev warehouse suspended Set AUTO_RESUME = TRUE on warehouse
Module not found: snowflake-sdk Not installed Run npm install snowflake-sdk
Tests timeout Warehouse resuming from suspend Increase test timeout to 30s, or pre-warm
002003: Object does not exist Wrong database/schema context Check .env.local DB and SCHEMA values

Resources

Next Steps

See snowflake-sdk-patterns for production-ready code patterns.

04 同仓库更多 Skills

jukeyman
jukeyman/jukeyman-skills

zustand-store-creator

Create zustand store creator operations. Auto-activating skill for Frontend Development. Triggers on: zustand store creator, zustand store creator Part of the Frontend Development skill category. Use when working with zustand store creator functionality. Trigger with phrases like "zustand store creator", "zustand creator", "zustand".

★ 1
jukeyman
jukeyman/jukeyman-skills

zero-trust-config-helper

Configure with zero trust config helper operations. Auto-activating skill for Security Advanced. Triggers on: zero trust config helper, zero trust config helper Part of the Security Advanced skill category. Use when configuring systems or services. Trigger with phrases like "zero trust config helper", "zero helper", "zero".

★ 1
jukeyman
jukeyman/jukeyman-skills

zapier-integration-helper

Assist with zapier integration helper operations. Auto-activating skill for Business Automation. Triggers on: zapier integration helper, zapier integration helper Part of the Business Automation skill category. Use when working with APIs or building integrations. Trigger with phrases like "zapier integration helper", "zapier helper", "zapier".

★ 1
jukeyman
jukeyman/jukeyman-skills

yaml-config-validator

Validate yaml config validator operations. Auto-activating skill for DevOps Basics. Triggers on: yaml config validator, yaml config validator Part of the DevOps Basics skill category. Use when configuring systems or services. Trigger with phrases like "yaml config validator", "yaml validator", "yaml".

★ 1
jukeyman
jukeyman/jukeyman-skills

xvary-stock-research

Thesis-driven equity analysis from public SEC EDGAR and market data; /analyze, /score, /compare workflows with bundled Python tools (Claude Code, Cursor, Codex).

★ 1
jukeyman
jukeyman/jukeyman-skills

xss-vulnerability-scanner

Scan xss vulnerability scanner operations. Auto-activating skill for Security Fundamentals. Triggers on: xss vulnerability scanner, xss vulnerability scanner Part of the Security Fundamentals skill category. Use when working with xss vulnerability scanner functionality. Trigger with phrases like "xss vulnerability scanner", "xss scanner", "xss".

★ 1