Native Asyncio Comes to the Snowflake Python Connector

Version 5.0.0 is the same package you already install, now with an awaitable API built on Universal Core.

If your Python service runs on an event loop, calling Snowflake has meant handing the call to a thread pool so it doesn’t block everything else. In our public preview post we introduced Universal Core; this is the Python feature it made room for. Snowflake Connector for Python 5.0.0 became generally available on October 15. It is still snowflake-connector-python, with the same synchronous API. Check the upgrade guide for behavior changes before switching. The package is a thin Python wrapper around Universal Core, the Rust core shared with the ODBC driver.

The big addition is snowflake.connector.aio: native asyncio support, the feature you’ve asked us for most, going back to a 2017 GitHub issue.

TL;DR

• If you run async Python against Snowflake, install snowflake-connector-python 5.0.0 on Python 3.11 or later.
• Async applications import snowflake.connector.aio and await the calls that talk to Snowflake: connect, execute, fetch, transactions, Arrow, and pandas.
• One event loop can now hold many Snowflake queries in flight next to the rest of your I/O, with no thread pool around the connector.

Asyncio benefits

Most of what an application does with Snowflake is wait. It sends SQL, the warehouse runs it, and the result comes back over HTTPS. Asyncio is built for that: while one coroutine waits on the network, the event loop runs the others. With a native async connector, your application can await Snowflake calls directly, without moving them onto a worker thread.

Web services and APIs. Applications built with FastAPI and other async frameworks can now query Snowflake without blocking the server while they wait for results. Snowflake SQLAlchemy supports SQLAlchemy’s asyncio extension on this connector, so an AsyncSession can run those queries directly.

Fan-out queries. A dashboard tile, a data-quality check, or a metadata crawl often runs a few dozen small, independent statements. Use asyncio.gather or a TaskGroup to overlap independent queries, each on its own cursor. Total time still depends on warehouse capacity and queuing. With TaskGroup, a failure cancels the remaining Python tasks and raises an exception.

Long-running queries. await cursor.execute() waits until the warehouse finishes and leaves the event loop free the whole time. A synchronous execute() holds a thread for that wait. Orchestrators such as Apache Airflow can run the wait on the event loop instead of parking a worker on the connector.

Fully async applications. A codebase that is async end to end can call Snowflake directly. An LLM application is one example: the tool call that queries Snowflake runs in the same event loop as the model call.

What you write

snowflake.connector.aio.connect() takes the same parameters as snowflake.connector.connect(). After connecting, a query is three steps: open a cursor, execute, and fetch.

import asyncio
import snowflake.connector.aio

async def main():
    async with snowflake.connector.aio.connect(connection_name="prod") as conn:
        async with conn.cursor() as cur:
            await cur.execute(
                "SELECT col1, col2 FROM orders WHERE col1 > %s",
                (100,),
            )
            for col1, col2 in await cur.fetchall():
                print(col1, col2)

asyncio.run(main())

async with on the connection opens the session and closes it when the block ends. async with on the cursor closes the cursor. await execute() waits for the query to finish without blocking the event loop. The snowflake.connector.aio module provides async versions of fetchone, fetchmany, executemany, transaction control, and write_pandas — anything using the network is awaitable.

To run two queries at the same time, do those three steps twice. Queries cannot share a cursor. DictCursor provides an async version of its synchronous API as well.

import asyncio
import snowflake.connector.aio
from snowflake.connector.aio import DictCursor

async def fetch_rows(conn, sql):
    async with conn.cursor(DictCursor) as cur:
        await cur.execute(sql)
        return await cur.fetchall()

async def main():
    async with snowflake.connector.aio.connect(connection_name="prod") as conn:
        async with asyncio.TaskGroup() as tg:
            orders = tg.create_task(fetch_rows(conn, "SELECT * FROM orders"))
            users = tg.create_task(fetch_rows(conn, "SELECT * FROM users"))
    print(len(orders.result()), len(users.result()))

asyncio.run(main())

Additionally, both versions share execute_async() method, which starts the query on Snowflake and returns its ID before it finishes.

How it works underneath

snowflake.connector and snowflake.connector.aio call the same implementation. HTTP, authentication, result parsing, and file transfers run as asynchronous tasks on the Rust core’s Tokio runtime.

The difference is how Python waits for those tasks to finish. A synchronous call blocks until the Rust task finishes. An asyncio call schedules the same task and returns control to the event loop. When the task completes, it notifies the loop, and the Python runtime finishes the call.

The synchronous module is still the right choice for scripts, notebooks, and libraries that call the connector from ordinary functions. The asyncio module is for programs that already run an event loop.

The rest of 5.0.0, briefly

pandas for every result. fetch_pandas_all() and fetch_pandas_batches() return a DataFrame for JSON results such as SHOW, DESCRIBE, DDL, PUT, and GET. On 4.x those methods raised NotSupportedError unless the server sent Arrow.

Core Arrow data handling. Result fetches use the nanoarrow converter. Set use_core_arrow=True to use the Universal Core Arrow converter for those fetches. Core Arrow data handling is in public preview and available to all accounts. A later connector version will use it by default.

Try it on an async workload

python -m pip install "snowflake-connector-python==5.0.0"
python -c "import snowflake.connector.aio; print('ready')"

Start with one service, not your whole fleet. Pick one that already runs on an event loop, and move its Snowflake calls from a thread offload to snowflake.connector.aio. Use a virtual environment on Python 3.11 or later, and compare results, types, and errors against your 4.x environment. The asyncio guide covers connections, concurrent cursors, execute_async(), and pandas: Using asynchronous I/O with the Python Connector.

Since preview, asyncio has run on 1.2 million sessions. Thanks to the preview users whose feedback shaped 5.0.0, and to the engineers who built the shared core and both Python APIs. The synchronous and asyncio APIs share the Rust core that came out of that work. The JDBC driver on the same core is now in public preview, so Java users can try it today. Node.js is still in private preview, and that’s where your feedback changes the most. Please share it by opening a GitHub issue or contact Snowflake Support.

Native Asyncio Comes to the Snowflake Python Connector was originally published in Snowflake Builders Blog: Data Engineers, App Developers, AI, & Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.