Skip to content

Snowflake connector

Snowflake is reached over the SQL API v2 (/api/v2/statements) with key-pair JWT authentication — no driver, no SDK, no password. The JWT is minted from your PKCS#8 private key, valid for an hour and re-minted shortly before it expires.

The API is stateless: every request carries its own warehouse, database, schema and role, so USE never persists between statements. Gated by exec-snowflake.

Connection profile

datasources:
  snowflake:
    type: snowflake
    account: ${SNOWFLAKE_ACCOUNT}    # full identifier, e.g. ab12345.us-east-2.aws
    username: ${SNOWFLAKE_USER}
    warehouse: COMPUTE_WH
    role: SYSADMIN                   # optional
    database: ANALYTICS              # session default for two-part names
    schema: PUBLIC                   # optional
    private_key_file: ~/.config/dosi/snowflake_key.p8   # PKCS#8 PEM
    private_key_file_pwd: ${SNOWFLAKE_KEY_PASSPHRASE}   # omit if unencrypted

Parameters

Key Type Required Default Notes
type string yes — snowflake
account string yes — The full account identifier; the endpoint becomes https://<account>.snowflakecomputing.com, and the JWT uses the leading locator.
username string yes — The account the key belongs to.
private_key_file path yes — PKCS#8 PEM. May be PBES2-encrypted.
private_key_file_pwd string no — Passphrase for an encrypted key. Same ${VAR} rules as password, including the plaintext warning.
warehouse string no — Sent with every request.
role string no — Sent with every request.
database string no — Session default, so two-part schema.table names resolve.
schema string no — Session default.
default bool no false See connection profiles.

Parsed and ignored on this connector: password — there is no password auth, key-pair only — plus host, port, uri (rejected outright), sslmode, sslrootcert, catalog, arrow_flight_port, compat_mode.

Authentication

Generate a key pair and register the public key on the Snowflake user, then point private_key_file: at the private half. The JWT's issuer and subject are derived for you from the account locator, the user name and a SHA-256 fingerprint of the public key — nothing else to configure.

Keep the passphrase in the environment (private_key_file_pwd: ${VAR}); a literal one works but warns on stderr.

Limitations

  • No CORR/COVAR_* over a cumulative window frame. Snowflake rejects those with Cumulative window frame unsupported, so such a metric is refused for this dialect.
  • Windowed AVG over integers returns scale-3 fixed decimals, which disagrees with the exact value other engines produce for the same moving average.
  • Multi-partition results are not supported yet — a very large result set fails with Snowflake returned a multi-partition result.
  • Unquoted identifiers come back upper-cased, so column names in output differ in case from DuckDB and the Postgres family.

Dates are handled for you: the API returns DATE as a count of days since the epoch, which the decoder converts back to an ISO date.

Verify the connection

$ dosi query --model model.yaml \
    --metrics revenue --group-by orders.status --execute --connection snowflake

An authentication problem surfaces as an HTTP 401 with Snowflake's own message, which distinguishes an unregistered public key from a wrong account identifier.

Troubleshooting

Message Cause and fix
connection "x" (snowflake) needs account / needs user / needs private_key_file The three mandatory keys. Note the message says user while the profile key is username.
connection "x": cannot read private_key_file <path>: <e> Path or permissions; ~ is not expanded by a shell here, so write the path in full if it fails.
connection "x": bad encrypted key: <e> Wrong private_key_file_pwd, or a key encrypted with a scheme other than PBES2.
connection "x": bad key: <e> Not a PKCS#8 PEM — a PKCS#1 (BEGIN RSA PRIVATE KEY) file lands here; convert it.
HTTP 401: <body> The public key is not registered on the user, or the account identifier is wrong.
Snowflake returned a multi-partition result; not supported yet Narrow the query — add a filter or a LIMIT.
bad Snowflake JSON: <e> The endpoint answered something unexpected, usually a proxy or an outage page.

Reference