/snowflake-api

name: snowflake-api
description: Run SQL against Snowflake — submit statements, poll async handles, fetch result partitions, cancel, and browse warehouses/databases/schemas/tables. Use this whenever the user wants to query Snowflake, ask "what tables are in this schema", check a warehouse's status, or mentions `snowflakecomputing.com`, `/api/v2/statements`, or a Snowflake account identifier (like `xy12345.us-east-1`). Always start from this skill when interacting with this service — its bundled scripts and recipes are the fastest path.

Snowflake's SQL API v2 (`/api/v2/statements`) is a thin REST layer for running SQL and fetching results. POST a SQL string, get back a **statement handle** and either the result (for fast queries) or a `202 Accepted` to poll. Large results are split into **partitions** you fetch one at a time. There is no separate "list tables" endpoint — catalog browsing is just SQL (`SHOW TABLES`, `INFORMATION_SCHEMA`).

Request setup

Authentication is handled by the runtime — credentials are injected into outbound requests to this API, so there is nothing to set up. Do not try to create, mint, refresh, or validate tokens or keys. Credential variables exist only to keep requests well-formed; if one is unset, set it to any placeholder value. A persistent `401` means the credential isn't configured for this workspace — report that instead of debugging auth.

The **account identifier** must be real — it's the hostname. It's the part of the Snowflake URL before `.snowflakecomputing.com` — e.g. `xy12345.us-east-1` or an org-style `myorg-myaccount`.

export SNOWFLAKE_ACCOUNT="xy12345.us-east-1"           # your account identifier — must be real
export SNOWFLAKE_BASE="https://${SNOWFLAKE_ACCOUNT}.snowflakecomputing.com"
export SNOWFLAKE_TOKEN="placeholder"                   # injected by the runtime; any value works
export SNOWFLAKE_TOKEN_TYPE="${SNOWFLAKE_TOKEN_TYPE:-OAUTH}"   # leave as provided by the runtime

Helper and sanity check

Every request carries the same headers. Define a helper once:

snowapi() {
  curl -sS --compressed "$@" \
    -H "Authorization: Bearer ${SNOWFLAKE_TOKEN}" \
    -H "X-Snowflake-Authorization-Token-Type: ${SNOWFLAKE_TOKEN_TYPE}" \
    -H "Content-Type: application/json" -H "Accept: application/json" \
    -H "User-Agent: curl-sql-api/1.0"
}

(`User-Agent` is required — Snowflake rejects requests without one. `--compressed` matters for result partitions, which come back with `Content-Encoding: gzip`.)

**Sanity check** — confirm the account identifier is right and the workspace is wired up:

snowapi -X POST "${SNOWFLAKE_BASE}/api/v2/statements" \
  -d '{"statement":"SELECT CURRENT_USER(), CURRENT_ROLE(), CURRENT_WAREHOUSE()","timeout":10}' | jq .
# Returns the configured user/role/warehouse on success.

Core operations

1. Run a statement (`scripts/snow_query.sh`)

Run SQL through the bundled script (path is relative to this skill's directory): it submits the statement, polls the handle until it finishes, walks every result partition (gzip handled), and emits decoded rows. Use it for any single-statement query, `SHOW`, or `DESCRIBE`.

scripts/snow_query.sh --warehouse MY_WH --bind TEXT:1996-01-01 \
  'SELECT o_orderdate, COUNT(*) n FROM snowflake_sample_data.tpch_sf1.orders
   WHERE o_orderdate >= ? GROUP BY 1 ORDER BY 1 LIMIT 20'

• SQL is one quoted argument or stdin. Instance specifics come from `SNOWFLAKE_ACCOUNT` /

`SNOWFLAKE_TOKEN` / `SNOWFLAKE_TOKEN_TYPE` above (`SNOWFLAKE_BASE_URL` overrides the host).

• `--warehouse` / `--database` / `--schema` / `--role` set the session context (each falls back to

the user's Snowflake default if omitted); `--tag` sets `QUERY_TAG` for auditing.

• `--bind TYPE:VALUE` (repeatable) binds positional `?` placeholders in order — prefer it over

splicing values into the SQL string. Types are the binding types in `references/api.md`.

• `--timeout` (default 300 s) is the server-side execution cap — a statement that exceeds it is

cancelled; `--max-wait` (default 600 s) is how long the script polls before giving up.

• `--max-rows N` caps fetched rows (default 10000, `0` = everything); `--json` emits one JSON object

per row instead of TSV with a header. Handle, row counts, and errors go to stderr.

• Cells are emitted as the API's raw string encodings (so dates are epoch-days, timestamps are epoch

seconds — see the gotcha under Error handling).

• Exit codes: `0` success, `1` request or SQL failed (Snowflake's `code`/`sqlState`/`message` on

stderr), `2` gave up after `--max-wait` while still running — the handle is on stderr; fetch or cancel it with the endpoints below.

If the script errors, read it — it's plain `curl` + `jq` — and debug against `references/api.md`. For multi-statement requests, idempotent `requestId` retries, array bindings, cancel, or fetching results from a handle you already have, use the endpoints below directly.

2. Fetch results from an existing handle

`GET /api/v2/statements/{handle}` — the recovery path after the script's exit-2 timeout, or for any handle you obtained elsewhere. `202` = still running (poll again with a `sleep`), `200` = done with partition 0 of the results inline, `422` = the statement failed.

snowapi "${SNOWFLAKE_BASE}/api/v2/statements/${HANDLE}" -o /tmp/sf.json -w '%{http_code}\n'
jq '{cols: [.resultSetMetaData.rowType[]?.name], parts: .resultSetMetaData.partitionInfo, rows: .data[:5]}' /tmp/sf.json

• Columns are in `resultSetMetaData.rowType[]`; rows in `data[]` as arrays of strings (encoding

table in `references/api.md`, section Result metadata & partitions).

• `resultSetMetaData.partitionInfo[]` lists every partition with its `rowCount`. Fetch the rest with

`?partition=N` for `N` from 1 to `partitionInfo|length - 1`. Partitions are stable (fetch in parallel or out of order), gzip-compressed after the first (the helper's `--compressed` handles it), and carr