snowflake-sql-api

A lightweight, pure-Python client for Snowflake's SQL API v2 (the POST /api/v2/statements REST endpoint).

Status: pre-1.0. The API may still change between minor versions.

Why

The official snowflake-connector-python is ~75 MB installed (150 MB with pandas/pyarrow) and ships a compiled extension that slows container builds and inflates cold starts (its import alone is ~1.5 s versus ~0.3 s for this client). For serverless and AWS Lambda workloads that only need to run SQL, that is a lot of weight.

snowflake-sql-api talks to Snowflake's SQL API over plain HTTP (httpx), so it stays small and cold-starts quickly. Core dependencies: httpx, PyJWT, cryptography.

Features

• Synchronous and asynchronous clients with the same surface
• Keypair (JWT) authentication, including encrypted private keys
• Result type coercion (NUMBER/Decimal, dates/timestamps, VARIANT, and more)
• Multi-partition result handling for large result sets
• Lazy row streams and bounded row batches, with CSV and JSONL exports
• Query helpers, DML, batch insert, async statement submission
• A small CLI for ad-hoc queries
• Optional pandas ([pandas]) and typed-row ([pydantic]) helpers, kept out of the default install

Install

pip install snowflake-sql-api
# optional extras
pip install "snowflake-sql-api[pandas]"
pip install "snowflake-sql-api[pydantic]"

Quick start

from snowflake_sql_api import SnowflakeClient

client = SnowflakeClient(
    account="myorg-myaccount",
    user="MY_USER",
    private_key_path="/path/to/rsa_key.p8",
)

rows = client.query("SELECT id, name FROM users WHERE active = ?", [True])
for row in rows:
    print(row["ID"], row["NAME"])

Async:

from snowflake_sql_api import AsyncSnowflakeClient

async with AsyncSnowflakeClient.from_env() as client:
    rows = await client.query("SELECT current_timestamp()")

CLI:

snowflake-sql-api query "SELECT current_version()"

Stream large results or export them without collecting every row:

with SnowflakeClient.from_env() as client:
    for batch in client.query_batches("SELECT id, amount FROM sales", batch_size=500):
        process(batch)

    with open("sales.csv", "w", encoding="utf-8", newline="") as output:
        count = client.export_csv("SELECT id, amount FROM sales", output)

Streaming fetches completed results one server partition at a time. Memory scales with the largest decoded partition plus the application batch. See streaming results and exports for row streams, async usage, serialization, and partial-output behavior.

Prior art and alternatives

snowflake-sql-api is not the first REST-based Snowflake client. If it does not fit your needs, one of these might:

How snowflake-sql-api differs: pure Python over httpx, with no dependency on the official connector (small install, fast cold start), paired sync and async clients, keypair auth, type coercion, partition handling, and a CLI as tested first-class behavior.

Note: the similarly named snowflake-sql-api-async and snowflake-sql-api-client are separate, unaffiliated projects.

Acknowledgements

The API design is inspired by pps-19012/snowflake-rest by Pushpendra Singh. This is a clean-room implementation (no code copied); credit for the original design and API shape goes to its author.

License

MIT