snowflake-api

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 carry only data — no metadata.
• Results are retained for 24 hours after the statement finishes; past that the handle returns 404.

3. Cancel a running statement

snowapi -X POST "${SNOWFLAKE_BASE}/api/v2/statements/${HANDLE}/cancel" | jq .

Cancellation is best-effort. If the statement already finished, this is a no-op.

4. Idempotent submits with requestId

If a POST might be retried (flaky network, timeout), pass a client-generated UUID as requestId. A retry with the same requestId and retry=true returns the original statement's handle instead of running it again.

RID=$(uuidgen)
snowapi -X POST "${SNOWFLAKE_BASE}/api/v2/statements?requestId=${RID}" -d '{"statement":"INSERT INTO t VALUES (1)","warehouse":"MY_WH"}'
# On timeout, retry:
snowapi -X POST "${SNOWFLAKE_BASE}/api/v2/statements?requestId=${RID}&retry=true" -d '{"statement":"INSERT INTO t VALUES (1)","warehouse":"MY_WH"}'

5. Run multiple statements in one request

Separate with ; and set MULTI_STATEMENT_COUNT to the exact count (or 0 for "any number"). Returns a parent handle plus per-child handles.

snowapi -X POST "${SNOWFLAKE_BASE}/api/v2/statements" -d '{
  "statement": "CREATE TEMP TABLE tmp AS SELECT 1 AS x; SELECT * FROM tmp;",
  "warehouse": "MY_WH",
  "parameters": {"MULTI_STATEMENT_COUNT": "2"}
}' | jq '{parent: .statementHandle, children: .statementHandles, msg: .message}'

Fetch each child's results via its own handle. BEGIN/COMMIT/ROLLBACK, USE, ALTER SESSION, and temporary table/stage creation are only supported inside a multi-statement request — as a single statement they fail with a 422. Bindings are not supported in multi-statement requests.

6. Browse the catalog (databases, schemas, tables, columns)

There are no REST catalog endpoints — use SHOW/DESCRIBE, which run as ordinary statements. A failure (no such object, no privilege) comes back as 422 with {code, message, sqlState} and no data key — guard for it once, then assume .data on subsequent calls:

snowapi -X POST "${SNOWFLAKE_BASE}/api/v2/statements" -d '{"statement":"SHOW DATABASES","timeout":20}' \
  | jq -r 'if .data then (.data[] | @tsv) else "error \(.code // "?"): \(.message // .)" end'

Swap the statement for any of: SHOW SCHEMAS IN DATABASE MY_DB, SHOW TABLES IN SCHEMA MY_DB.PUBLIC, DESCRIBE TABLE MY_DB.PUBLIC.EVENTS, SHOW WAREHOUSES LIKE 'MY_WH' (column 2 = state: STARTED/SUSPENDED/RESUMING/SUSPENDING).

For predictable column sets across versions, query INFORMATION_SCHEMA instead — e.g. SELECT table_name, row_count, bytes FROM my_db.information_schema.tables WHERE table_schema='PUBLIC' or SELECT column_name, data_type, is_nullable FROM my_db.information_schema.columns WHERE table_schema='PUBLIC' AND table_name='EVENTS' ORDER BY ordinal_position.

To label rows by column name, zip resultSetMetaData.rowType[].name with each data[] row: jq '[.resultSetMetaData.rowType[]?.name] as $c | .data[] | [$c,.] | transpose | map({key:.[0],value:.[1]}) | from_entries'.

Pagination

Pagination is partitions, not cursors. resultSetMetaData.partitionInfo is an array of {"rowCount", "uncompressedSize"} entries; loop indices, no next-token. Each partition is bounded to keep response sizes manageable (roughly a few thousand rows or ~10 MB uncompressed).

Rate limits

Snowflake doesn't publish a fixed requests-per-second cap on the SQL API. The limits you actually hit are:

• Concurrent statements per warehouse (MAX_CONCURRENCY_LEVEL, default 8) — excess queries queue inside Snowflake, not at the HTTP layer, so you won't get a 429; they'll just sit in QUEUED state.
• 429 Too Many Requests does happen if you hammer the poll endpoint. Back off and respect Retry-After if present.
• Credential lifetime — the runtime supplies and renews the credential. A persistent 401 means the configured credential needs fixing — report it rather than trying to re-authenticate.

Poll with a sleep, not a hot loop — and keep timeout in the POST reasonably high so most queries return inline without polling at all.

Error handling

Snowflake returns the HTTP status plus a JSON body with code, sqlState, message, and statementHandle. The code is a Snowflake-specific numeric string (e.g. "390302").

• 200 — Finished, results inline. Read data[]. Check resultSetMetaData.partitionInfo for more pages.
• 202 — Still running (or submitted async). Poll GET .../statements/{handle}. Body carries statementStatusUrl and message.
• 400 — Malformed request. Read message. Common causes: missing Content-Type, bad JSON, wrong binding index (bindings are 1-indexed strings: "1", "2", one per ?).
• 401 — Credential missing or rejected. Check SNOWFLAKE_ACCOUNT points at the right account and the token/type headers are present. If it persists, the credential isn't configured for this workspace — report it.
• 404 — Handle not found. Results are retained 24 h; past that the handle is gone. Or the handle is wrong.
• 408 — Statement exceeded the request's timeout and was cancelled. Raise (or omit) timeout and resubmit.
• 422 — Statement failed (SQL error, cancelled). message and sqlState carry the SQL error. Don't retry blindly.
• 429 — Rate limited. Back off per Retry-After.
• 503 / 504 — Transient. Retry with backoff. For POSTs, retry with the same requestId + retry=true to stay idempotent.

Gotchas worth calling out:

• Everything is uppercase by default. Unquoted identifiers resolve uppercased. SHOW TABLES IN schema my_db.public and MY_DB.PUBLIC are the same; "my_db"."public" is not.
• Bind placeholders in the SQL are ?, and bindings are a 1-indexed string-keyed object, not an array: {"1": {...}, "2": {...}}. Values are always strings regardless of type. See references/api.md (Bindings) for the type list and array-binding form.
• data[] values are all strings, and dates/timestamps are not ISO — date is days since epoch ("18262"), timestamp_* is epoch seconds with a 9-decimal fraction. Parse against resultSetMetaData.rowType[].type; the full per-type encoding table is in references/api.md (Result metadata & partitions).
• Warehouse is required for anything that scans data. Metadata-only statements (most SHOW commands, DESCRIBE) don't need one, but SELECT does. A missing warehouse fails with a clear message. Queries on a suspended warehouse auto-resume it (startup delay), and each resume incurs a 60 s billing minimum (per warehouse start, not per query) — batch small lookups where you can.

Going deeper

references/api.md has the fuller reference — the complete request body fields, the resultSetMetaData shape, all the parameters session options, multi-statement and child-handle mechanics, the monitoring endpoints (/api/v2/statements/{handle} status values), and notes on binding types and VARIANT/ARRAY handling. Read it when you need the exact shape for something not covered above.