# snowflake-cost-mcp

An MCP server that gives Claude (or any MCP-speaking agent) direct, read-only visibility into **Snowflake warehouse cost and query performance** — credit usage by warehouse, the most expensive queries in a window, and heuristic right-sizing recommendations.

## Why this exists (and what it deliberately doesn't do)

Snowflake already ships an official, actively-developed managed MCP server covering schema browsing, Cortex Analyst/Search, and general query execution — and several community servers cover similar ground. This project intentionally does **not** duplicate any of that. It exists for the one thing none of them focus on: **cost attribution and performance right-sizing**, using Snowflake's `ACCOUNT_USAGE` views.

Use this alongside the official Snowflake MCP server, not instead of it.

## Tools

| Tool | What it does |
|---|---|
| `list_warehouses` | Warehouse inventory: size, auto-suspend/resume, running/queued queries |
| `get_warehouse_credit_usage` | Credits consumed per warehouse over a lookback window |
| `find_expensive_queries` | Slowest/costliest successful queries in a window |
| `get_query_detail` | Full detail for one query by `QUERY_ID` |
| `get_warehouse_right_sizing_recommendations` | Heuristic oversized/undersized/no-signal call per warehouse |
| `run_readonly_query` | Ad-hoc `SELECT`-only escape hatch — **disabled by default** |

`ACCOUNT_USAGE` views are eventually consistent (Snowflake documents up to ~45 minutes–a few hours of latency), so treat results as "recent history," not real-time.

## Prerequisites

You need a Snowflake role that can read `ACCOUNT_USAGE`. Create a dedicated, least-privilege role rather than reusing `ACCOUNTADMIN`:

```sql
CREATE ROLE IF NOT EXISTS mcp_cost_monitor;
GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE mcp_cost_monitor;
GRANT USAGE ON WAREHOUSE compute_xs TO ROLE mcp_cost_monitor;
GRANT ROLE mcp_cost_monitor TO USER your_service_user;
```

This role can *read* account usage metadata and nothing else — no write grants anywhere. That's the real security boundary; the in-code SQL guard is a backup, not a substitute for this.

## Setup

### 1. Install

```bash
pip install -e .
```

### 2. Configure authentication (key-pair recommended)

```bash
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
```

```sql
ALTER USER your_service_user SET RSA_PUBLIC_KEY='<contents of rsa_key.pub, header/footer stripped>';
```

Copy `.env.example` to `.env` and fill in `SNOWFLAKE_ACCOUNT`, `SNOWFLAKE_USER`, and `SNOWFLAKE_PRIVATE_KEY_PATH` (pointing at `rsa_key.p8`). Username/password auth is supported as a fallback for local/dev use — see `.env.example`.

### 3. Run it standalone (sanity check)

```bash
snowflake-cost-mcp
```

It will sit waiting on stdio — that's expected; it's meant to be launched by an MCP client, not run interactively.

### 4. Wire it into Claude Desktop / Claude Code

```json
{
  "mcpServers": {
    "snowflake-cost": {
      "command": "snowflake-cost-mcp",
      "env": {
        "SNOWFLAKE_ACCOUNT": "your_account_identifier",
        "SNOWFLAKE_USER": "your_service_user",
        "SNOWFLAKE_PRIVATE_KEY_PATH": "/absolute/path/to/rsa_key.p8",
        "SNOWFLAKE_ROLE": "MCP_COST_MONITOR"
      }
    }
  }
}
```

## Security model (defense in depth)

1. Every built-in tool runs a hardcoded, parameterized SQL template — no string-built SQL from user input, ever.
2. `run_readonly_query` (the only tool that accepts free-form SQL) is **off by default** and, when enabled, is passed through a guard that rejects anything except a plain `SELECT` / `WITH ... SELECT`.
3. The Snowflake role itself should be read-only (see Prerequisites) — that's the boundary that actually matters if there's ever a bug in this server.

## Development

```bash
pip install -e ".[dev]"
pytest -q
ruff check src tests
```

Tests run entirely against a mocked Snowflake connection — no live account needed to develop or run CI.

## Roadmap

- Validate end-to-end against a real Snowflake trial account
- Publish to the public MCP registry
- Add a storage-cost tool (database/table storage spend)
- Optional streamable-HTTP transport for a hosted/enterprise mode

## License

MIT — see [LICENSE](LICENSE).