# Snowflake MCP Server

A Model Context Protocol server that gives Claude direct access to a Snowflake
account. Four tools, stdio transport, runs locally on macOS.

Built fresh, but the design choices come from an earlier eight-server MCP
platform. What carried over is at the bottom.

## Tools

| Tool | What it does |
|---|---|
| `snow_get_schema` | Tables, views, and columns, generated live from `INFORMATION_SCHEMA` and cached for ten minutes |
| `snow_query` | Read-only queries, row bounded, returned as a markdown table |
| `snow_execute` | Anything that changes data or objects: DML, DDL, `COPY INTO`, `GRANT`, `USE` |
| `snow_log` | Reads this server's own query log |

`snow_query` accepts `SELECT`, `WITH`, `SHOW`, `DESCRIBE`, `LIST`, `EXPLAIN`.
Everything else goes to `snow_execute`, which is annotated `destructiveHint: true`
so Claude treats it differently and Claude Desktop can prompt before it runs.

There is no permission blocklist. Your Snowflake role is the boundary. A guard
rail list exists in `sql_guard.py` and is off by default; set
`SNOWFLAKE_MCP_GUARDRAILS=1` to refuse `DROP DATABASE`, `DROP SCHEMA`,
`ALTER ACCOUNT` and similar. Worth turning on the day this stops being a lab.

## Setup

### 0. Put this somewhere local

Do not run it from OneDrive, Dropbox, or iCloud Drive. Claude Desktop launches
`server.py` as a child process at startup, and a cloud-synced file that has been
dehydrated to a placeholder cannot be read, so the server silently fails to
start. The `.venv` and the SQLite log make it worse: thousands of files to sync,
and a WAL database that sync clients corrupt.

`~/Developer` is the macOS convention:

```bash
mkdir -p ~/Developer
mv "/Users/Shubham/Library/CloudStorage/OneDrive-HarrisburgUniversity/Arabella Stuff/claude/snowflake-mcp" ~/Developer/
cd ~/Developer/snowflake-mcp
```

(The quotes are required, that path has spaces in it.)

Then `git init` and push it. A synced folder is not a backup for code.

### 1. Install

```bash
cd ~/Developer/snowflake-mcp
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
```

If you use a virtualenv, the `command` in your Claude Desktop config must be the
**venv's** Python, not `python3`. Claude Desktop does not activate environments,
so a bare `python3` launches the system interpreter, which has none of these
packages installed and fails with `ModuleNotFoundError: No module named 'mcp'`:

```
"command": "/Users/YOU/Developer/snowflake-mcp/.venv/bin/python3"
```

Confirm the path with `which python3` while the venv is active.

No ODBC driver is needed. `snowflake-connector-python` speaks HTTPS directly,
which is why this runs cleanly on macOS.

### 2. Find your account identifier

In a Snowflake worksheet:

```sql
SELECT CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME() AS account;
```

Use that value for `SNOWFLAKE_ACCOUNT`, lowercase, with underscores replaced by
hyphens. It is the identifier, not the full URL.

### 3. Pick an auth method

Password is fastest to start with and fails if your user requires MFA. Key pair
is what you want once this is running regularly, because there is no browser
prompt and no password in a config file:

```bash
mkdir -p ~/.snowflake
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out ~/.snowflake/rsa_key.p8 -nocrypt
openssl rsa -in ~/.snowflake/rsa_key.p8 -pubout -out ~/.snowflake/rsa_key.pub
chmod 600 ~/.snowflake/rsa_key.p8
```

Then register the public key, pasting the body without the BEGIN and END lines:

```sql
ALTER USER <your_user> SET RSA_PUBLIC_KEY='MIIBIjANBgkq...';
```

Leaving every credential unset falls back to browser SSO, which opens a tab on
the first query after each restart.

See `.env.example` for the full list of settings.

### 4. Register with Claude Desktop

Open `~/Library/Application Support/Claude/claude_desktop_config.json` and merge
in the `snowflake` block from `claude_desktop_config.snippet.json`. Use absolute
paths. Then quit and reopen Claude Desktop, because MCP servers load once at
startup.

### 5. Check it

Ask Claude: *what tables are in my Snowflake account?*

It should call `snow_get_schema` and come back with your session details and an
object list. If a database is not configured it lists the databases your role
can see instead.

## Running the tests

```bash
python3 -m pytest tests/ -q
```

68 tests, no Snowflake connection needed. The `TestRegressionsFromTheFabricPlatform`
class pins three bugs that were verified in the earlier codebase; each test fails
against that implementation and passes against this one.

## The query log

Every call to `snow_query`, `snow_execute`, and `snow_get_schema` is recorded
with latency, row count, outcome, referenced objects, and whatever `meta` tags
the caller supplied. Two files under `logs/`:

- `query_log.jsonl` is append-only and authoritative
- `query_log.db` is SQLite in WAL mode, indexed for aggregate questions

Read it from Claude with the `snow_log` tool, or from a shell:

```bash
python3 observability.py stats
python3 observability.py recent 20
python3 observability.py failures 20
python3 observability.py sessions 20
python3 observability.py session <session-id>
python3 observability.py rebuild     # regenerate SQLite from the JSONL
```

`snow_log mode=cost` goes further and joins the logged `query_id` values to
Snowflake's own `INFORMATION_SCHEMA.QUERY_HISTORY`, so you get bytes scanned,
partitions pruned, rows produced, and cloud credits per query. It uses the
table function rather than `ACCOUNT_USAGE`, which lags up to 45 minutes.

Because the JSONL is the source of truth, a corrupt database is a rebuild rather
than a data loss.

### The `meta` parameter

Every tool takes an optional `meta` JSON string:

```json
{"trigger":"user","source":"cowork","intent":"revenue-lookup","session":"cowork-20260803-a1b"}
```

Without it the log tells you a query ran. With it the log tells you who asked for
it, from which Claude surface, toward what end, and which other calls belonged to
the same conversation. Malformed input degrades to `"unknown"` rather than
failing the call, because a tagging problem should never cost you a query.

## One server covers the whole account

Coming from Fabric, the instinct is one server per warehouse. That was forced by
the platform: each Fabric warehouse is its own SQL endpoint with its own
connection string, so five warehouses meant five servers.

Snowflake does not work that way. You connect once to an account, and database,
schema, warehouse, and role are session context rather than separate endpoints.
A single connection reaches every database your role can see, as long as names
are fully qualified:

```sql
SELECT * FROM SALES_DB.PUBLIC.ORDERS;
SELECT * FROM MARKETING_DB.WEB.SESSIONS;
```

So one server entry, one connection, the entire account.

`SNOWFLAKE_DATABASE` and `SNOWFLAKE_SCHEMA` only set defaults so unqualified
names resolve. Leave them empty and everything still works with qualified names,
and `snow_get_schema` will list the databases your role can see. Set them when
you spend most of your time in one place.

The one reason to run a second entry is a **different role**, since role is
fixed per connection. Point a second `mcpServers` key at the same `server.py`
with a different `SNOWFLAKE_ROLE`, and give it a distinct name so Claude can
tell them apart:

```json
"snowflake-readonly": {
  "command": "/Users/YOU/Developer/snowflake-mcp/.venv/bin/python3",
  "args": ["/Users/YOU/Developer/snowflake-mcp/server.py"],
  "env": { "SNOWFLAKE_ROLE": "ANALYST_RO", "...": "..." }
}
```

Warehouse is not a reason to split. Switch it in-session with
`USE WAREHOUSE <name>` through `snow_execute`.

## Files

```
server.py           tool definitions and MCP wiring
connection.py       auth, retry, health check, error mapping
sql_guard.py        validation and row limiting
observability.py    query log, also a CLI
tests/              pytest suite
```

## What carried over from the previous platform

**Few tools.** That platform started with six tools per server and consolidated
to two. Tool count is the main driver of selection mistakes. This one has four
and three of them are the data path.

**Schema first.** Without a schema tool, the model explores `INFORMATION_SCHEMA`
by hand across several round trips before it can answer anything, and every one
of those wakes your warehouse. One call replaces the exploration. It is generated
here rather than hand-written, because a hand-written schema doc goes stale
silently and a generated one cannot.

**Errors that carry a next step.** `connection.py` maps common Snowflake failures
to what to do about them. "Invalid identifier" becomes a pointer at
`snow_get_schema`. A suspended warehouse says so and names the fix.

**Log everything.** The old platform logged every call to SQLite and reviewed it
weekly, which is how misrouted queries and redundant work got found. Same idea
here, minus the routing layer, since there is only one server to route to.

## What did not carry over, deliberately

**The keyword blocklist.** The old `validate_sql_readonly` scanned raw SQL for
write keywords. It rejected `WHERE account_name = 'Create Health'` and
`WHERE notes LIKE '%delete%'`, and a write hidden in a comment would have slipped
past it. This replaces it with an allowlist on the leading keyword, applied after
comments and string literals are blanked.

**Regex row limiting.** The old `add_top_clause` injected `TOP` into the first
`SELECT` it matched. Inside a CTE that is the inner query, so the outer result
came back unbounded. It also skipped limiting entirely whenever the substring
"TOP" appeared anywhere, including in the word "Laptop". Here the whole statement
is wrapped as a subquery, which is correct for CTEs and set operations because
the parser handles the nesting instead of a regex.

**f-string docstrings.** Python does not treat an f-string as a docstring, so
`__doc__` is `None` and FastMCP ships the tool with no description at all. Five
tools in the old `_base.py` were written that way. There is a test here asserting
all four tools have real descriptions.