# pi-snowflake-query

Pi extension for **read-only** Snowflake queries via the team `claude`
connection **plus** a `dbt` tool that wraps the local dbt env
(`run`, `test`, `build`, `compile`, `seed`, `snapshot`, `debug`,
`deps`, `parse`, `clean`, `docs-generate`, `run-operation`, `list`,
`source-freshness`). Ships a `snowflake-analyst` subagent that uses
`openai-codex/gpt-5.4-mini`.

The extension enforces the safety rules from the dbt repo's `CLAUDE.md`:

- Every Snowflake query runs through `snow sql -c claude` (the
  connection is hard-defaulted; override only with
  `SNOWFLAKE_CONNECTION`).
- Only one SQL statement per `snowflake_query` call. No `;`-chained
  DDL/DML smuggling.
- The first SQL keyword must be one of `SELECT`, `WITH`, `SHOW`, `DESC`,
  `DESCRIBE`, `EXPLAIN`, `LIST`. Anything else is rejected before exec.
- A `tool_call` gate blocks direct `bash` invocations of `snow sql` that
  don't use the configured connection or that look destructive.
- `dbt --full-refresh` is opt-in and is **refused** when the selector
  mentions raw tables (matches the "never full-refresh raw tables" rule
  — those models permanently delete their S3 source files).
- All tool output is truncated; the bounded captured output tail is written
  to a temp file when it exceeds the default size/line limits.
- When the `context-mode:build/adapters/pi/extension.js` adapter is active,
  `snowflake_query` is disabled and `ctx_snowflake_query` becomes the only
  available query tool. It uses the same async `snow sql -c claude` execution
  and safety gate, while context-mode captures the tool call and result.

## Install

Bundled with this repo. To run it as a one-off extension:

```bash
pi -e extensions/snowflake-query/snowflake-query.ts
```

To wire it into the standard team runtime, add the same `-e` flag to
`bin/pi-team.sh` or `bin/pi-safe.sh`.

To install as a package elsewhere:

```bash
pi install npm:pi-snowflake-query
```

## Requirements

- Snowflake CLI (`snow`) installed and on `$PATH`. Verify with
  `snow --version`.
- A `claude` connection configured in `~/.snowflake/config.toml` or
  `~/.snowflake/connections.toml` (or whatever name you set via
  `SNOWFLAKE_CONNECTION`). The connection
  should be scoped to a role with **read-only** privileges.
- For the `dbt` tool: either `dbt` on `$PATH` (with your virtualenv
  active), or a wrapper script such as `./.claude/tools/dbt-run.sh`
  that activates the env. Point `DBT_BIN` at whichever you use.

## Configuration

| Env var                      | Default       | Purpose                                                                |
| ---------------------------- | ------------- | ---------------------------------------------------------------------- |
| `SNOWFLAKE_CONNECTION`       | `claude`      | Connection name passed to `snow sql -c`.                               |
| `SNOW_CLI_BIN`               | `snow`        | Override the snow CLI binary (e.g. an absolute path).                  |
| `SNOWFLAKE_QUERY_TIMEOUT_MS` | `120000`      | Per-query timeout in ms. Hard ceiling is 600000 ms (10 min).           |
| `DBT_BIN`                    | `dbt`         | Path to the dbt binary or a wrapper script that activates the venv.    |
| `DBT_PROJECT_DIR`            | process `cwd` | Working directory used for dbt invocations.                            |
| `DBT_TIMEOUT_MS`             | `1800000`     | Per-invocation dbt timeout in ms. Hard ceiling is 3600000 ms (1 hour). |

## Bundled skill

This package ships a Pi Agent Skill named **`pi-snowflake-query`** under
`skills/pi-snowflake-query/SKILL.md`. Load it with the package or invoke it
explicitly with `/skill:pi-snowflake-query` when doing Snowflake/dbt work so
the agent follows the read-only SQL, metadata-first discovery, bounded sampling,
and dbt full-refresh safety rules documented here.

A project-source copy also lives at `../../skills/pi-snowflake-query/SKILL.md`
for this repository's local skill library.

## Tools

- **`snowflake_query`** — Run a single read-only SQL statement. Parameters:
  - `query` (string, required): the SQL.
  - `database`, `schema`, `warehouse` (string, optional): passed to
    `snow sql` to override the connection profile defaults.
  - `format` (`json` | `csv` | `table`, optional): output format. Default `json`.
  - `timeoutMs` (integer, optional): per-call timeout. Capped at 10 min.
- **`ctx_snowflake_query`** — The context-mode-only variant of
  `snowflake_query`. It is enabled only when the context-mode Pi adapter is
  detected; otherwise calls are rejected.
- **`snowflake_describe`** — Convenience wrapper around `DESC TABLE
<name>`. Use this before issuing a SELECT to avoid wasted warehouse
  resumes.
- **`snowflake_create_raw_external_table`** — Creates a new `IF NOT EXISTS`
  external source table for the raw-ingestion pattern. It is limited to `raw`
  and `raw_staging`, the approved stages and file formats, and cannot replace
  or drop existing objects. Create the dbt raw model and schema YAML separately.
- **`dbt`** — Run a dbt subcommand against the configured project. Parameters:
  - `command` (enum, required): `run`, `test`, `build`, `compile`,
    `seed`, `snapshot`, `debug`, `deps`, `parse`, `clean`,
    `docs-generate`, `run-operation`, `list`, `source-freshness`.
  - `select`, `exclude` (string, optional): selectors.
  - `target` (`dev` | `prod`, optional): profiles.yml target.
  - `fullRefresh` (boolean, optional): pass `--full-refresh`. Refused
    when the selector mentions raw tables.
  - `vars` (string, optional): YAML/JSON passed verbatim to `--vars`.
  - `operation`, `operationArgs` (string, optional): required when
    `command` is `run-operation`.
  - `timeoutMs` (integer, optional): per-invocation timeout. Default
    30 min, capped at 1 hour.

## Commands

- **`/snowflake <SQL>`** — Ask Pi to run a read-only query for you via
  `snowflake_query` without composing a full prompt.
- **`/snowflake-conn`** — Print the currently configured Snowflake +
  dbt context (connection, binaries, working dir, timeouts).
- **`/dbt <args>`** — Ask Pi to run a dbt command via the `dbt` tool
  (e.g. `/dbt run --select tag:Aspyn`).

## Specialized agent: `snowflake-analyst`

A read-only persona pairs naturally with these tools. The agent
definition is single-sourced at:

- `.pi/agents/snowflake-analyst.md`

That's the canonical path the team's launchers (`bin/pi-team.sh`,
`bin/pi-safe.sh`) already pick up. The package itself does not ship a
duplicate copy.

Key frontmatter:

```yaml
name: snowflake-analyst
tools: read, grep, find, ls, bash, snowflake_query, snowflake_describe, dbt
model: openai-codex/gpt-5.4-mini
systemPromptMode: replace
```

The persona's job is to inspect Snowflake data, validate dbt model
outputs, run targeted `dbt compile`/`dbt test` checks, and debug
transformations — never to full-refresh raw tables or run destructive
SQL.

> **Model availability:** the extension itself doesn't pin a model; the
> agent frontmatter does. If your Pi install doesn't expose
> `openai-codex/gpt-5.4-mini`, update the `model:` line in the agent file to a
> model your providers actually serve (e.g. `openai-codex/gpt-5.4-mini` for the
> ChatGPT/Codex SSO route).

## Try it

```text
> /snowflake-conn
Snowflake connection: `claude` ... dbt binary: `dbt` ...

> Use snowflake_describe on analytics.dbt.model_aspyn_customers
> Then: SELECT count(*) FROM analytics.dbt.model_aspyn_customers LIMIT 10

> /dbt compile --select model_aspyn_customers
> /dbt test --select model_aspyn_customers --target dev
```

## Security

- The extension executes the local `snow` CLI as a child process. It
  inherits the same credentials and permissions you have configured for
  the `claude` connection.
- The read-only gate is a defense-in-depth check, not a substitute for a
  least-privilege Snowflake role. Always grant the `claude` connection
  the minimum role required.
- The `dbt` tool does **not** use the `claude` Snowflake CLI connection. It
  runs with the credentials and target defined by your dbt profile, and commands
  such as `run`, `build`, `seed`, `snapshot`, and `run-operation` can write to
  Snowflake (including production targets). Treat dbt access as separately
  privileged; use least-privilege profiles and explicit selectors/targets.
- `fullRefresh:true` requires an explicit `select`. Before running, the
  extension resolves it with `dbt ls` and rejects any resolved raw model. This
  prevents indirect graph selectors from full-refreshing raw external tables.
- `snowflake_create_raw_external_table` performs narrowly scoped DDL through
  the pinned `claude` connection. It creates only an external table with
  `IF NOT EXISTS`; it cannot alter or drop existing objects.
- The `tool_call` gate only inspects `bash` calls that match `snow sql`.
  It permits only one inline `-q`/`--query` payload using the configured
  connection; file/stdin SQL and other shells (e.g. Python) are not intercepted.
- Tool output is truncated to keep the LLM context bounded. The bounded
  captured output tail is written to a temp file under `os.tmpdir()`; clean
  those up periodically.

## Development

```bash
npm run typecheck --prefix extensions/snowflake-query
npm run test --prefix extensions/snowflake-query
npm run pack:dry-run --prefix extensions/snowflake-query
pi -e extensions/snowflake-query
pi -e extensions/snowflake-query/snowflake-query.ts
```

The unit tests cover the read-only SQL gate (`_shared/sql-gate.ts`) and
run via `node --test` after a `tsc` step into `.tmp-test/`, mirroring the
`extensions/verifier` test harness.