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:

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:

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

`. Use this before issuing a SELECT to avoid wasted warehouse resumes.

• 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:

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

> /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.
• 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

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.