aptive-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/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 full payload is written to a temp file when it exceeds the default size/line limits.

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:aptive-snowflake-query

Requirements

• Snowflake CLI (snow) installed and on $PATH. Verify with snow --version.
• A claude connection configured in ~/.snowflake/config.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).

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.
• snowflake_describe — Convenience wrapper around DESC TABLE <name>. 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/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/gpt-5.4-mini, update the model: line in the agent file to a model your providers actually serve (e.g. openai/gpt-5.5).

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 tool_call gate only inspects bash calls that match snow sql. Other shells (e.g. invoking SQL through Python) are not intercepted.
• Tool output is truncated to keep the LLM context bounded. The full payload 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/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.