Snowflake Tool

SnowflakeTool

Execute SQL against Snowflake via the SQL REST API (POST /api/v2/statements). The tool handles authentication, session context injection, and single-statement dispatch.

Source: src/tools/snowflake.rs

Authentication

Two methods are supported. Key-pair JWT is preferred for accounts that enforce MFA.

Key-pair JWT (preferred)

Added in v3.9.0 (tools#62).

The tool mints an RS256 JWT and sends it as a Bearer token with the X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT header. The Snowflake password/MFA login flow is bypassed entirely.

JWT shape:

Claim	Value
iss	<ACCOUNT_UPPER>.<USER_UPPER>.SHA256:<base64(SHA256(public-key DER))>
sub	<ACCOUNT_UPPER>.<USER_UPPER>
iat	current Unix timestamp
exp	iat + 300 (5 minutes)

Both account and user are uppercased. The region segment (e.g. .us-east-1 in myaccount.us-east-1.snowflakecomputing.com) is dropped from the account identifier — Snowflake expects only the base account name.

The SHA256 fingerprint is computed over the DER encoding of the public key, then base64-encoded (standard, not URL-safe).

Required config fields for JWT auth:

• private_key — PEM-encoded RSA private key (used to sign the JWT).
• public_key — PEM-encoded RSA public key (used to compute the fingerprint in the iss claim). Added in v3.9.0.

Dependencies: jsonwebtoken 9 + pem 3 (added in v3.9.0).

Password auth (legacy fallback)

Uses Snowflake's /api/v2/login-request endpoint with user + password. Not usable on accounts that enforce MFA or require key-pair authentication.

Required config fields for password auth:

• user
• password

Config reference

tool: snowflake
config:
  account: NDCFGPC-MI21697        # Snowflake account identifier (region segment dropped internally)
  user: NOETL                      # Snowflake username
  # --- key-pair JWT auth (preferred) ---
  private_key: |
    -----BEGIN RSA PRIVATE KEY-----
    ...
    -----END RSA PRIVATE KEY-----
  public_key: |
    -----BEGIN PUBLIC KEY-----
    ...
    -----END PUBLIC KEY-----
  # private_key_passphrase: ""    # optional; needed if the private key is encrypted
  # --- password auth (legacy fallback) ---
  # password: "..."
  # --- optional session context ---
  warehouse: SNOWFLAKE_LEARNING_WH
  role: SYSADMIN
  database: MY_DB
  schema: PUBLIC
  # --- SQL ---
  command: "SELECT current_version()"
  # command_b64: "<base64>"       # alternative: base64-encoded SQL
  # commands: ["stmt1", "stmt2"]  # alternative: list of statements (each dispatched separately)

All config fields can carry {{ template }} expressions resolved by the worker before dispatch.

Field	Required	Notes
account	Yes	Snowflake account identifier.
user	Yes	Snowflake username (uppercased internally).
private_key	Yes (JWT)	PEM RSA private key for JWT signing.
public_key	Yes (JWT)	PEM RSA public key for the JWT fingerprint. Added v3.9.0.
private_key_passphrase	No	Decrypt an encrypted private key.
password	Yes (password)	Used only when private_key is absent.
warehouse	No	Injected into the request body as session context. Added v3.9.2.
role	No	Injected into the request body. Added v3.9.2.
database	No	Injected into the request body (omitted for CREATE/DROP DATABASE). Added v3.9.2.
schema	No	Injected into the request body (omitted for CREATE/DROP DATABASE). Added v3.9.2.
command	One of these	SQL string. Split on ; — each segment is dispatched as a separate request. Added v3.9.2.
command_b64	One of these	Base64-encoded SQL.
commands	One of these	List of SQL strings; each dispatched separately.

SQL API behaviour — known constraints

Three constraints were hit during the v3.9.x work and are documented here so future callers do not re-discover them.

USE is not supported (code 391911)

The Snowflake SQL REST API rejects USE DATABASE, USE SCHEMA, USE WAREHOUSE, and USE ROLE statements with HTTP 400 / error code 391911.

Fix: pass warehouse, database, schema, and role in the request body as session parameters (added in v3.9.2). Do not try to set context via USE statements in a command: block.

One statement per request (code 000008)

The SQL REST API runs exactly one statement per POST /api/v2/statements request. A multi-statement string (e.g. CREATE TABLE foo (...); INSERT INTO foo ...) returns HTTP 400 / error code 000008.

Fix: as of v3.9.2 the tool splits a command: string on ; and dispatches each non-empty segment as a separate request. Statements that naturally contain ; in string literals should use the commands: list form instead.

User-Agent is required (code 391903)

The Snowflake SQL REST API returns HTTP 400 / error code 391903 when the User-Agent header is absent. reqwest sends no User-Agent by default.

Fix: added in v3.9.1 — the tool sets a User-Agent on the HTTP client at construction time.

Result envelope

A successful query returns:

{
  "data": {
    "columns": ["COL1", "COL2"],
    "rows": [["val1", "val2"]],
    "row_count": 1
  }
}

Non-SELECT statements (DDL, DML) return {"data": {"affected_rows": N}}.

Worker credential-alias mapping

When a playbook step uses auth: "{{ sf_credential }}", the worker's auth_alias.rs SNOWFLAKE_FIELD_MAP maps credential fields into the tool config before dispatch:

Credential field	Tool config field
sf_account	account
sf_user	user
sf_password	password
sf_private_key	private_key
sf_public_key	public_key
sf_private_key_passphrase	private_key_passphrase
sf_warehouse	warehouse
sf_role	role
sf_database	database
sf_schema	schema

The sf_public_key → public_key mapping was added in worker#83 (v3.9.0 adoption).

Related

• Home — noetl-tools overview and tool-kinds table.
• noetl/worker wiki — noetl-executor-adoption — how the worker dispatches through the registry.
• noetl/ai-meta#98 — regression baseline migration (Snowflake validation session).
• noetl/ai-meta#99 — transfer tool credential-alias gap (follow-up).
• src/tools/snowflake.rs
• repos/worker/src/auth_alias.rs