setup-snowflake · diff
git:20260827.7ff2e25 to git:20260828.6faa089
115 added, 206 removed. Audit C to C.
---
name: setup-snowflake
description: >-
- First-time Snowflake setup wizard: install the Snowflake MCP tooling, write connection
- config from the template, and verify a live query round-trip. Use when the user says
- "set up snowflake", "configure the warehouse", "add snowflake credentials", or hits
- authentication/connection errors that suggest Snowflake was never configured. For
- day-to-day remote querying after setup, use connect-snowflake.
+ First-time Snowflake setup wizard for the NATIVE ConnectionManager path (the connection the
+ analyst actually queries through, with auto-logged provenance). Prompts for every connection
+ field, writes credentials to .env, registers the dataset, and VERIFIES the session is live on
+ the warehouse before declaring success. Use when the user says "set up snowflake", "connect to
+ snowflake", "configure the warehouse", or is routed here from /connect-data. For day-to-day
+ remote querying after setup, use connect-snowflake. An optional Snowflake MCP server (for
+ interactive ad-hoc queries) is covered in the appendix.
---
# Skill: Setup Snowflake
## Purpose
- Guided first-time Snowflake setup wizard. Installs dependencies, configures
- the MCP server, collects credentials, writes `.env`, tests the connection,
- explores available data, and gets the user querying.
+ Guided first-time Snowflake setup for the **native `ConnectionManager` path** — the connection the
+ AI Analyst uses for every query, so every result is traced and logged. This wizard collects the
+ connection details, writes credentials safely, registers the dataset, and then **proves the session
+ is actually on the warehouse** (not the local practice copy) before it will report success.
+ This is deliberately native-first. The old MCP server (`snowflake-labs-mcp` via `uvx`) is a separate,
+ optional tool for interactive ad-hoc queries and is kept in the Appendix. The analyst does not query
+ through the MCP, so setting up only the MCP leaves the analyst unconnected. Set up the native path
+ first.
+
## When to Use
- - User says `/setup-snowflake`, "connect to snowflake", "set up snowflake",
- "I have a snowflake account"
+ - `/setup-snowflake`, "set up snowflake", "connect to snowflake", "I have a snowflake account"
- Routed here from `/connect-data` when the user selects Snowflake
- ## Invocation
- `/setup-snowflake` — start the setup wizard
-
- ## Instructions
-
- ### Step 1: Check Existing Configuration
-
- **1a. Check `.env` for credentials:**
- Read `.env` (if it exists) and check for these variables:
- - `SNOWFLAKE_ACCOUNT`
- - `SNOWFLAKE_USER`
- - `SNOWFLAKE_PASSWORD`
-
- **1b. Check MCP server is configured:**
- Read `.mcp.json` (if it exists) and check for a `snowflake` server entry.
-
- **1c. Check `uvx` is installed:**
- Run `which uvx` or check `~/.local/bin/uvx`.
-
- **Decision matrix:**
- - All credentials + MCP configured → skip to Step 5 (test connection)
- - Credentials exist but no MCP config → skip to Step 3 (configure MCP)
- - Missing credentials → continue to Step 2
- - `uvx` not installed → install it in Step 3
-
- ### Step 2: Collect Credentials
- Ask one question at a time:
+ ## Prerequisite: the driver
+ The native path needs `snowflake-connector-python`. Check it and install if missing:
+ ```bash
+ python3 -c "import snowflake.connector; print('driver OK')" || pip install snowflake-connector-python
+ ```
+ (It also ships in the `warehouses` extra: `pip install -e ".[warehouses]"`.)
- 1. **Account Identifier**
- - "What's your Snowflake account identifier?"
- - Help text: "Find it in Snowsight: click your name in the bottom-left →
- click your account name under **Account** → click **View account details** →
- copy the **Account identifier** from the modal. It looks like
- `ORGNAME-ACCOUNTNAME` (e.g., `MYORG-MYACCOUNT`)."
+ ## Step 1: Collect the connection details
+ The repo ships blank, so ask for **every** field, one question at a time. In a class the instructor
+ will read these out; leave each empty until the user gives it. Collect:
+ 1. **Account identifier** — e.g. `ORGNAME-ACCOUNTNAME` (Snowsight: your name, bottom-left, then
+ Account, then View account details).
2. **Username**
- - "What username did you create during Snowflake signup?"
-
- 3. **Password**
- - "Paste your Snowflake password. I'll write it directly to `.env` and
- never display it in the terminal."
-
- **CRITICAL SECURITY RULES:**
- - **Never** echo, print, or log the password in any command
- - **Never** pass the password as a CLI argument (visible in `ps`)
- - **Never** include the password in any output shown to the user
- - **Never** use `Bash` with `echo` or `cat` to write credentials — use the
- `Write` or `Edit` tool only
+ 3. **Password** — "Paste it and I will write it straight to `.env`, never to the terminal."
+ 4. **Warehouse** — the compute warehouse to run on (e.g. `ANALYST_WH`).
+ 5. **Database**
+ 6. **Schema** — default `PUBLIC` if they do not say.
+ 7. **Role** — optional; skip if they do not use one.
+ 8. **A short dataset name** for this connection (used as the dataset id, lowercase-hyphen).
- ### Step 3: Install Dependencies & Configure MCP Server
+ **Credential security (non-negotiable):**
+ - Never echo, print, or log the password; never pass it as a CLI arg (visible in `ps`).
+ - Write secrets only with the **Write/Edit** tool, never `bash echo`/`cat`.
- **3a. Install `uv` (if `uvx` not found):**
- ```bash
- curl -LsSf https://astral.sh/uv/install.sh | sh
+ ## Step 2: Write credentials to `.env`
+ Read any existing `.env` first and preserve other variables. Then set (Write/Edit tool only):
```
- Then verify: `~/.local/bin/uvx --version`
-
- **3b. Write `.env`:**
- 1. If `.env` already exists, read it first to preserve existing variables
- (e.g., `SLACK_TOKEN`, `MOTHERDUCK_TOKEN`)
- 2. Add or update these variables:
- ```
- SNOWFLAKE_ACCOUNT=<account_identifier>
- SNOWFLAKE_USER=<username>
- SNOWFLAKE_PASSWORD=<password>
- SNOWFLAKE_WAREHOUSE=COMPUTE_WH
- ```
- 3. Use the **Write** or **Edit** tool — NOT bash echo/cat
- 4. Confirm: "Credentials saved to `.env`. This file is gitignored and never
- committed."
+ SNOWFLAKE_PASSWORD=<password>
+ ```
+ The password is the one secret that must live in `.env`. Account, user, warehouse, database, schema,
+ and role are not secrets and go in the dataset manifest below (which is gitignored). Confirm:
+ "Password saved to `.env` (gitignored, never committed)."
- **3c. Create `snowflake-mcp-config.yaml`** (if it doesn't exist):
+ ## Step 3: Register the dataset
+ Create `.knowledge/datasets/{id}/` and write `manifest.yaml` from
+ `connection_templates/snowflake.yaml.example`, filling the connection block and referencing the
+ password by env var:
```yaml
- # Snowflake MCP Server Configuration
- # Controls which Snowflake services are available via MCP tools.
- # Auth is handled via .env (SNOWFLAKE_ACCOUNT, SNOWFLAKE_USER, SNOWFLAKE_PASSWORD).
-
- agent_services: []
- search_services: []
- analyst_services: []
-
- other_services:
- object_manager: true
- query_manager: true
- semantic_manager: true
-
- sql_statement_permissions:
- - Select: true
- - Create: false
- - Drop: false
- - Update: false
- - Delete: false
- - Insert: false
+ connection:
+ type: snowflake
+ account: "<account>"
+ warehouse: "<warehouse>"
+ database: "<database>"
+ schema: "<schema>"
+ user: "<username>"
+ password: "$SNOWFLAKE_PASSWORD" # expanded from .env at connect time
+ # role: "<role>" # include only if given
```
-
- Note: SQL permissions are read-only by default for safety. Users can enable
- write permissions later if needed.
-
- **3d. Add Snowflake to `.mcp.json`:**
- 1. Read `.mcp.json` if it exists (preserve other servers)
- 2. Add the `snowflake` server entry:
- ```json
- {
- "mcpServers": {
- "snowflake": {
- "command": "<absolute_path_to_uvx>",
- "args": [
- "snowflake-labs-mcp",
- "--service-config-file",
- "snowflake-mcp-config.yaml",
- "--account",
- "<account_identifier>",
- "--user",
- "<username>",
- "--warehouse",
- "COMPUTE_WH",
- "--password",
- "<password>"
- ]
- }
- }
- }
- ```
- 3. The `command` must be the **absolute path** to `uvx` (e.g.,
- `/Users/<name>/.local/bin/uvx`). Find it with `which uvx` or
- check `~/.local/bin/uvx`.
- 4. All credentials go in `args` with explicit flags (`--account`,
- `--user`, `--warehouse`, `--password`). The env var approach does
- not work — the server ignores `SNOWFLAKE_PASSWORD` from the `env`
- block and requires `--password` as a CLI arg.
- 5. **Do NOT use `--connection-name`** — it requires a Snowflake
- `connections.toml` config file that most users won't have.
-
- ### Step 4: Restart Required
- The MCP server won't be available until Claude Code restarts and loads the
- new `.mcp.json` config.
-
- Tell the user:
- - "Everything is configured. The Snowflake MCP server needs Claude Code to
- restart to pick up the new config."
- - "Exit with `/exit`, then run `claude` again."
- - "When you're back, run `/setup-snowflake` — I'll detect your saved config
- and jump straight to testing the connection."
-
- Stop here — do not proceed to Step 5 in this session.
-
- ### Step 5: Test Connection
- Use the Snowflake MCP `run_snowflake_query` tool to run:
- ```sql
- SELECT CURRENT_ACCOUNT(), CURRENT_USER(), CURRENT_WAREHOUSE(), CURRENT_VERSION()
+ Also create an empty `quirks.md` and `metrics/index.yaml`, and point `.knowledge/active.yaml` at this
+ dataset with the remote opt-in on:
+ ```yaml
+ active_dataset: "{id}"
+ use_remote: true
```
- **If connection succeeds:**
- - Show: account name, username, warehouse, Snowflake version
- - Continue to Step 6.
-
- **If connection fails:**
- - Show the error message
- - Offer to re-enter credentials (go back to Step 2)
- - Common issues to suggest: wrong account identifier format, password typo,
- account not yet activated, warehouse suspended
-
- ### Step 6: Explore Available Data
- Run these queries via the Snowflake MCP:
+ ## Step 4: Verify you are LIVE on Snowflake (hard gate)
+ This is the step the old flow was missing. Connect through `ConnectionManager` and confirm the session
+ is really on the warehouse, not the local DuckDB fallback. Run:
+ ```bash
+ AAP_USE_REMOTE=1 python3 - <<'PY'
+ from helpers.data.connection_manager import ConnectionManager
+ cm = ConnectionManager(dataset_id="{id}")
+ cm.connect()
+ print(cm.test_connection()["message"]) # -> "Live on account ..., warehouse ..."
+ v = cm.verify_remote() # proves snowflake, not the local fallback
+ assert v["remote"], f"NOT LIVE: {v['reason']}"
+ print("Tables:", cm.list_tables()[:25])
+ PY
+ ```
+ - **`v["remote"]` is True** → show the account, warehouse, database.schema, and the table list as
+ proof, then go to Step 5.
+ - **`v["remote"]` is False** → do NOT declare success. The `reason` tells you what to fix:
+ - "resolved to 'duckdb'/'csv', not snowflake" → the remote opt-in did not take. Make sure
+ `AAP_USE_REMOTE=1` is in the **same** shell command as `python3`, and `use_remote: true` is in
+ `active.yaml`.
+ - "not installed" → install `snowflake-connector-python` (Prerequisite above).
+ - an auth/account error → re-collect the offending field (Step 1). Common: wrong account
+ identifier format, password typo, warehouse suspended, account not activated.
- 1. `SHOW DATABASES` — list available databases
- 2. `SHOW SCHEMAS IN SNOWFLAKE_SAMPLE_DATA` — list schemas in the sample data
- 3. `SHOW TABLES IN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1` — list TPC-H tables
+ Never report "connected" on the strength of the manifest file existing. Success means
+ `verify_remote()` returned True.
- Present a summary to the user:
- - "You have access to **N** databases."
- - "The sample data includes **TPC-H** with 8 tables: CUSTOMER, ORDERS,
- LINEITEM, PART, PARTSUPP, SUPPLIER, NATION, REGION."
- - Brief description of what TPC-H models (retail order processing).
+ ## Step 5: Explore and hand off
+ With the connection verified, show what is there and suggest a first question:
+ ```bash
+ AAP_USE_REMOTE=1 python3 -c "from helpers.data.connection_manager import ConnectionManager as C; c=C(dataset_id='{id}'); c.connect(); print(c.list_tables())"
+ ```
+ Tell the user: the analyst now queries this warehouse for every request, and each query is logged for
+ provenance. For day-to-day "am I on live or local?" checks, use `/connect-snowflake`. Remind them that
+ remote is opt-in: `use_remote: true` is set for this dataset, and `AAP_USE_REMOTE=1` in the shell is the
+ belt-and-suspenders guard.
- Suggest a first query: "Try asking me something like: *What are the top 10
- customers by total order value?*"
+ ---
- ### Step 7: Optional — Create Dataset Knowledge
- Offer: "Want me to set up TPC-H as a named dataset so I always know what
- tables and columns are available?"
+ ## Appendix (optional): the Snowflake MCP server
+ Only if the user specifically wants the interactive `snowflake-labs-mcp` query tool in addition to the
+ native path. The analyst does not query through it, so this is not required for setup.
- **If yes:**
- 1. Create `.knowledge/datasets/snowflake-tpch/manifest.yaml`:
+ 1. Install `uv`: `curl -LsSf https://astral.sh/uv/install.sh | sh`, then `~/.local/bin/uvx --version`.
+ 2. Create `snowflake-mcp-config.yaml` with read-only SQL permissions:
```yaml
- dataset_id: snowflake-tpch
- display_name: Snowflake TPC-H (SF1)
- connection_type: snowflake_mcp
- database: SNOWFLAKE_SAMPLE_DATA
- schema: TPCH_SF1
- warehouse: COMPUTE_WH
- notes: >
- TPC-H benchmark dataset at scale factor 1. 8 tables modeling a
- retail order-processing scenario. Read-only sample data provided
- by Snowflake.
+ other_services: {object_manager: true, query_manager: true, semantic_manager: true}
+ sql_statement_permissions:
+ - Select: true
+ - Create: false
+ - Drop: false
+ - Update: false
+ - Delete: false
+ - Insert: false
```
- 2. Generate `schema.md` by querying `INFORMATION_SCHEMA.COLUMNS` for
- each table and formatting with `schema_to_markdown()` or manually
- 3. Create empty `quirks.md` with section headers
- 4. Update `.knowledge/active.yaml` to point to `snowflake-tpch`
- 5. Confirm: "TPC-H is now your active dataset. Ask any question to get started."
-
- **If no:** Skip — the user can run `/connect-data` later.
-
- ## Rules
- 1. Never display passwords or secrets in terminal output or chat
- 2. Always use Write/Edit tool for `.env` — never Bash echo/cat
- 3. Always test the connection before declaring success
- 4. After writing `.mcp.json` or `.env` for the first time, tell the user to
- restart — the MCP server won't load until next session
- 5. Preserve existing `.env` variables when adding Snowflake credentials
- 6. Preserve existing `.mcp.json` servers when adding the Snowflake entry
- 7. Use absolute path for `uvx` in `.mcp.json` `command` field
- 8. Default SQL permissions to read-only (Select only) for safety
+ 3. Add a `snowflake` server to `.mcp.json` (preserve other servers). The `command` is the absolute
+ path to `uvx`; credentials go in `args` as explicit flags (`--account`, `--user`, `--warehouse`,
+ `--password`) because the server ignores the env block. Do NOT use `--connection-name`.
+ 4. Restart Claude Code so the MCP config loads, then query with the MCP `run_snowflake_query` tool:
+ `SELECT CURRENT_ACCOUNT(), CURRENT_WAREHOUSE(), CURRENT_VERSION()`.