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 uses the official snowflake-sdk driver. It tests [connections.<SNOWFLAKE_CONNECTION>] in connections.toml before trying SNOWFLAKE_* environment credentials.
• Only one SQL statement per query-tool call. No ;-chained DDL/DML smuggling. snowflake_analytics_query additionally confines relation access to analytics.dbt.
• 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).
• Snowflake query rows never enter the agent context. Every query writes its complete result plus a shape index to an isolated local directory and returns only paths, shape metadata, and local inspection examples. dbt output remains bounded and spills to a temp file when it exceeds the context 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:pi-snowflake-query

Requirements

• A [connections.claude] profile in ~/.snowflake/connections.toml (or whatever name you set via SNOWFLAKE_CONNECTION) with account, user, and password. The driver tests this profile first, then falls back to SNOWFLAKE_ACCOUNT, SNOWFLAKE_USER, and SNOWFLAKE_PASSWORD.
• For the dbt tool: Python with dbt-core and the selected adapter installed. The extension auto-detects it (see below); set DBT_PYTHON only to override.

Configuration

Env var	Default	Purpose
SNOWFLAKE_CONNECTION	claude	Profile name in connections.toml to test before environment fallback.
SNOWFLAKE_CONFIG_PATH	~/.snowflake/connections.toml	Override the profile file path.
SNOWFLAKE_QUERY_TIMEOUT_MS	120000	Per-query timeout in ms. Hard ceiling is 600000 ms (10 min).
DBT_PYTHON	auto-detected	Python executable with dbt-core and the selected adapter installed.
DBT_PROJECT_DIR	auto-detected	Working directory used for dbt invocations.
DBT_PROFILES_DIR	auto-detected	Passed to dbt-core as --profiles-dir.
DBT_TIMEOUT_MS	1800000	Per-invocation dbt timeout in ms. Hard ceiling is 3600000 ms (1 hour).

dbt environment auto-detection

All three dbt settings are resolved from the filesystem at startup, so the dbt tool works with no DBT_* variables set. An explicit env var always wins, and /snowflake-conn prints each resolved value with the rule that produced it.

Python interpreter — first match wins:

1. DBT_PYTHON.
2. $VIRTUAL_ENV/bin/python, if a dbt entrypoint sits beside it.
3. <project>/.venv, <project>/venv, or <project>/env, same check.
4. The dbt executable on $PATH, resolved through symlinks, a shell wrapper's DBT_BIN=... assignment, or a console script's shebang.
5. python3.

Ordering matters when a machine has several dbt installs: an activated virtualenv wins over whatever dbt is on $PATH, so source .venv/bin/activate is how you pin a project's exact dbt-core/dbt-snowflake releases. /snowflake-conn prints the resolved versions so a mis-detected environment is visible before you run anything.

Project directory — DBT_PROJECT_DIR, else the nearest ancestor of the cwd containing dbt_project.yml, else the project a dbt wrapper on $PATH declares (DEFAULT_PROJECT_DIR= or --project-dir, accepted only when that path really is a dbt project), else a sibling data-dbt checkout found from a cwd ancestor, else the cwd. So the tool works from any subdirectory of the project and from another repo in the Aptive workspace. When nothing resolves to a dbt project the dbt tool fails immediately with that path named, rather than passing a doomed invocation to dbt.

Profiles directory — DBT_PROFILES_DIR, else ~/.dbt when it holds a profiles.yml, else dbt's own default. The override of dbt's default matters: dbt prefers ./profiles.yml when the project has one, and a checked-in project profiles.yml is usually a credential-less CI profile, which fails at connect time with 251006: Password is empty. Developer credentials live in ~/.dbt. Pass the dbt tool's profilesDir parameter for a one-off override.

Config file

Auto-detection cannot know which dbt target you want, or which targets your profile defines. Copy snowflake-query.example.yaml to snowflake-query.yaml beside the extension for those, plus any path the detector gets wrong:

target: claude
targets:
  - dev
  - prod
  - claude
python: ~/aptive-data/data-dbt/.venv/bin/python

Every key is optional and unset keys stay auto-detected, so an empty file behaves like no file. target supplies the target when a dbt tool call does not name one; targets restricts what the tool accepts, so a typo is rejected before dbt connects. SNOWFLAKE_QUERY_CONFIG points at a different path, and snowflake-query.yaml is gitignored.

Precedence for the three paths is env var, then this file, then detection: a per-invocation override beats persistent config, which beats a guess.

The format is a flat map of scalars plus one list, with ~ expanded — no nesting, anchors, or multi-line strings. Unknown keys and malformed lines are reported by line number and fail the dbt tool rather than being silently dropped.

Bundled skills

This package ships two Pi Agent Skills:

• pi-snowflake-query at skills/pi-snowflake-query/SKILL.md for general read-only Snowflake and dbt work.
• diagnose-aptive-dbt-errors at skills/diagnose-aptive-dbt-errors/SKILL.md for evidence-based Aptive dbt failure diagnosis using this extension's snowflake_analytics_query, snowflake_global_query, snowflake_describe, and dbt tools.

Load them with the package or invoke /skill:pi-snowflake-query or /skill:diagnose-aptive-dbt-errors explicitly. The package's skills/**/* manifest includes each skill and its references.

Tools

• snowflake_analytics_query — Run a single read-only SELECT or WITH query for analytics.dbt dbt models. Metadata commands, table functions, and IDENTIFIER() are rejected so their alternate object-reference grammars cannot escape the analytics schema restriction. A bare model name such as model_marketing_sms_audiences is rewritten to analytics.dbt.model_marketing_sms_audiences; analytics.<model> is rewritten the same way. Relations outside analytics.dbt are rejected. It accepts query, warehouse, format, outputDir, and timeoutMs.
• snowflake_global_query — Run a single read-only SQL statement against other Snowflake schemas, including SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY. It accepts query, optional database, schema, warehouse, format, outputDir, and timeoutMs.
• snowflake_query — Deprecated compatibility alias for snowflake_global_query.
• snowflake_describe — Convenience wrapper around DESC TABLE <name>. Use this before issuing a SELECT to avoid wasted warehouse resumes. It accepts an optional outputDir.
• 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.

Local query output

All Snowflake query tools stream complete rows to a unique pi-snowflake-* directory under os.tmpdir(). Pass outputDir to snowflake_analytics_query, snowflake_global_query, snowflake_query, or snowflake_describe to choose a different parent directory. Each result directory contains:

• rows.jsonl by default, or rows.csv/rows.tsv when explicitly selected;
• index.json with format, encoding, row count, column count, column names, byte count, and data filename. Column names come from the Snowflake Statement's result metadata, so columns/columnCount are populated even for a zero-row result.

rows.tsv fields and header names are backslash-escaped so tabs, carriage returns, newlines, and literal backslashes in Snowflake values survive the round trip losslessly: a literal backslash becomes \\, a tab becomes \t, a carriage return becomes \r, and a newline becomes \n (each a two-character escape sequence, not the raw control character). Quotes are not TSV delimiters and are left as-is.

The tool result contains no row values. It reports the full result-directory path and shape index, then includes commands such as:

DIR='/tmp/pi-snowflake-example' jq . "$DIR/index.json"
DIR='/tmp/pi-snowflake-example' jq -s '.[0:10]' "$DIR/rows.jsonl"

When @hypabolic/pi-hypa resolves in the running Pi installation, the result also includes a bounded hypa_read example that reads only index.json. It does not suggest loading result rows into agent context.

• 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_analytics_query, snowflake_global_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 Python: `python3` ...

> Use snowflake_analytics_query: SELECT count(*) FROM model_marketing_sms_audiences LIMIT 10
> It executes: SELECT count(*) FROM analytics.dbt.model_marketing_sms_audiences LIMIT 10

> Use snowflake_global_query: SELECT query_id, error_code FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE query_id = '01c6b3c0-0421-9310-0025-6403b4bc4f0a'

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

Security

• The extension uses the official snowflake-sdk Node.js driver. It tests the configured connections.toml profile before trying SNOWFLAKE_* environment credentials.
• The read-only gate is a defense-in-depth check, not a substitute for a least-privilege Snowflake role. Always grant both credential sources the minimum role required.
• The dbt tool does not use the Snowflake driver 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.
• Snowflake query rows are persisted locally instead of entering the LLM context. Result directories may contain sensitive data. Restrict permissions on custom output parents and clean result directories periodically. dbt output remains bounded and writes an oversized captured tail under os.tmpdir().

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.