snowflake-rest

The Problem

The official snowflake-connector-python is 50+ MB with C extensions. In serverless environments (AWS Lambda, Cloud Functions), that means bloated deployment packages, slow cold starts, and painful dependency management. If all you need is to run queries via Snowflake’s REST-based SQL API, you’re paying an enormous overhead for features you’ll never use.

Using the SQL API directly isn’t trivial either - it requires JWT generation with RSA key fingerprinting, async polling for long-running queries, multi-partition result pagination, manual type coercion (everything comes back as strings), and a binding limitation where only the first statement in a transaction supports parameter bindings.

What I Built

A focused Python client that handles all the SQL API complexity internally and exposes a clean interface:

from snowflake_rest import SnowflakeClient

client = SnowflakeClient(
    account="myorg-myaccount",
    user="SVC_USER",
    private_key_path="~/.snowflake/rsa_key.p8",
    database="MY_DB",
    warehouse="COMPUTE_WH",
)

rows = client.query("SELECT * FROM users WHERE status = ?", ["active"])

Under 100 KB installed. No C dependencies. Works anywhere Python runs.

Key Design Decisions

• JWT auth with automatic refresh: Computes the SHA-256 fingerprint of the public key DER, generates short-lived JWTs, and caches/refreshes them transparently. The user never touches token logic.
• Transparent async promotion: Snowflake returns 202 for long-running queries. The client automatically polls the status URL until completion - callers just see results.
• Safe transaction escaping: Snowflake’s multi-statement API only supports bindings on the first statement. For transactions, the library safely escapes and inlines values for subsequent statements, handling the edge cases (NULL, booleans, timestamps, strings with quotes).
• Automatic type coercion: Uses result set metadata to convert strings back to proper Python types (int, Decimal, datetime, bool, etc.). Opt-out available.
• Streaming with lazy partition fetching: For large result sets, rows are yielded one at a time with partitions fetched on demand - constant memory usage regardless of result size.

Features

• Multiple query styles: query, query_one, query_scalar, query_column, query_stream
• Result mapping to dataclasses and Pydantic models
• Transaction support with session variables
• Batch insert with configurable batch size
• Async query submission and polling
• COPY INTO export helper
• Pandas DataFrame integration
• CLI for ad-hoc queries (table, CSV, JSON output)
• Configurable retries on transient errors (429, 502, 503, 504)
• Query profiling hooks

Performance

Query speeds are comparable to the official connector, but connection setup is faster and more consistent. Install size is 40 MB (with deps) vs 117 MB. In serverless where cold starts dominate, this is the difference that matters.

Building It

I built snowflake-rest because the official Snowflake connector is absurdly heavy for what most serverless workloads actually need.

The problem is simple: snowflake-connector-python pulls in 50+ MB of C extensions. On AWS Lambda or Cloud Functions, that’s a cold start tax you pay on every invocation. If all you’re doing is running queries, there’s no reason to ship a native binary just to talk to a REST API.

Snowflake has a SQL API - a proper HTTP endpoint that accepts SQL and returns JSON. But using it directly is surprisingly painful. You need to:

1. Load your RSA private key, compute the SHA-256 fingerprint of the public key’s DER encoding
2. Generate a JWT with a very specific issuer format (ACCOUNT.USER.SHA256_FINGERPRINT)
3. Cache and refresh tokens before expiry
4. Handle 202 responses (async promotion) by polling a status URL
5. Parse paginated results across multiple partitions
6. Coerce every value from strings back to proper types using result metadata
7. Deal with the binding limitation in multi-statement transactions

That’s easily 800-1,000 lines of boilerplate before you write a single business query. So I wrapped it all up.

The hard part: transactions

The trickiest piece was transaction support. Snowflake’s multi-statement API has a quirk: parameter bindings (? placeholders) only work on the first statement. Every subsequent statement in the transaction needs values inlined directly into the SQL.

This means you need safe escaping that handles every edge case - NULL, booleans, timestamps, strings with embedded quotes, numeric types. Get it wrong and you have SQL injection in your own client library. I ended up building an escaping layer that maps Python types to their Snowflake SQL literals, with extensive test coverage for the weird cases.

What it looks like now

from snowflake_rest import SnowflakeClient

client = SnowflakeClient.from_env()

# Simple query
rows = client.query("SELECT * FROM users WHERE status = ?", ["active"])

# Transaction
with client.transaction() as tx:
    tx.set("now_ts", "CURRENT_TIMESTAMP()")
    tx.execute("UPDATE users SET updated=$now_ts WHERE id=?", [42])
    tx.execute("INSERT INTO audit (action, ts) VALUES (?, $now_ts)", ["update"])

# Streaming for large results
for row in client.query_stream("SELECT * FROM events"):
    process(row)

It’s on PyPI - pip install snowflake-rest. Under 100 KB, three dependencies (requests, PyJWT, cryptography), works everywhere Python does.