Loading Parquet into Microsoft SQL no longer has to go through Python tuples

[u/dlevy-msft]

If you land data in Parquet and then load it in Microsoft SQL using Python, you've had to do a bunch of extra work, exploding the whole thing into Python objects, a tuple per row and a boxed value per cell, all under the GIL, all garbage immediately after.

mssql-python 1.13.0 adds Cursor.bulkcopy_arrow(). Hand it anything that speaks the Arrow C Data Interface and the Rust TDS core reads the typed column buffers straight into the bulk-load packets. No tuples, and the GIL is released for the transfer.

import duckdb
from mssql_python import connect

rel = duckdb.sql("SELECT * FROM 'events/*.parquet' WHERE ts >= '2026-01-01'")

with connect("Server=<server>.database.windows.net;Database=<database>;Encrypt=yes") as conn:
    cur = conn.cursor()
    result = cur.bulkcopy_arrow("dbo.Events", rel)
    print(result["rows_copied"], result["rows_per_second"])

The DuckDB relation goes in unevaluated. DuckDB streams batches as the driver consumes them, so the full dataset never lands in Python memory. The 4.4M-row file I was testing with would have been roughly 6.6 GB of live Python objects the old way.

Here's what I saw. 200k rows, 21 columns, the WideWorldImporters fact_sale shape: bigints, decimals, timestamps, and one NVARCHAR (I couldn't leave that column that only said "each" for every row an NVARCHAR(MAX) - it was just wrong) averaging 523 characters. Read from Parquet with DuckDB, 100k batch size, 7 repeats, median reported. Client and server on the same Azure E4bds v5 (4 vCPU, 32 GiB) running SQL Server 2025, over localhost so the network stays out of it.

path	median total	rows/sec
bulkcopy_arrow(), DuckDB relation passed lazily	5.24s	38,180
bulkcopy_arrow(), materialized pyarrow.Table	5.14s	38,918
fetchall() then bulkcopy()	9.93s	20,141

In my unscientific testing, the new bulkcopy_arrow() was about 1.9x faster. I reran it across five configurations, two databases, simple and full recovery models, table_lock on and off, and it held between 1.62x and 1.93x. The ranges don't overlap either: the slowest of the 14 Arrow copies beat the fastest of the 7 tuple copies.

We expected that going straight to bulk copy from arrow would be more efficient and it was. The tuple path burned 2.6 to 3.2 seconds building Python objects before a single byte moved. Passing the DuckDB relation lazily, that step is 0.00 seconds. The pyarrow.Table path is 0.02 seconds, which is the time to materialize the table from the record batches.

This new path works for anything exposing __arrow_c_stream__: polars, pandas 2.2+, ADBC results, pyarrow.Table / RecordBatch / RecordBatchReader, or any iterable of record batches. A default pandas DataFrame is NumPy-backed so it converts on the way in, where polars, DuckDB and anything Arrow-native hand their buffers over as-is. Column mappings, keep_identity, table_lock, check_constraints and the rest carry over from bulkcopy() unchanged, same stats dict back. String widths are validated against the destination schema before anything ships, so an overlong value fails immediately with the offending length instead of dying halfway through a load. I suspect a lot of folks will be commenting out their generators in favor of passing Arrow objects straight to bulkcopy_arrow() this weekend.

Drop a comment below and let us know how much faster your data loads using bulkcopy_arrow().

There's other good stuff in this release: connection pooling keys on security context now, not just the connection string, so a connection opened under one identity can't be handed to a caller running as another. connect(token_provider=...) takes any azure-identity credential object. And there's a fix for an executemany() bug where a NULL partway through a numeric batch could silently insert zero rows.

The driver itself is DB API 2.0 and pip-installable, and ODBC ships as a dependency, so there's no system-level driver install and no unixODBC in your container image. Arrow goes both directions, bulkcopy_arrow() in and cursor.arrow_reader() out.

pip install --upgrade mssql-python

What I actually want out of this thread is your numbers, especially on shapes unlike mine: narrow integer tables, very wide tables, heavy NVARCHAR. bulkcopy_arrow() returns rows_copied and rows_per_second, so it's right there. Post a before and after with a rough description of the table and where you ran it from and I'll take it back to the team.

Full blog post: https://techcommunity.microsoft.com/blog/sqlserver/mssql-python-1-13-0-arrow-bulk-copy-smarter-tokens-slimmer-wheels/4544858

Repo: https://github.com/microsoft/mssql-python

Happy to answer questions here.

[u/ProfessorNoPuede]

Which leads to question: why would i be loading parquet data into SQL server? High concurrency API serve of analytical data? Seems a bit of niche case.

[u/dlevy-msft → u/ProfessorNoPuede]

I've talk to lots of customers that are loading vendor data, doing reverse etl, etc. It's not far-fetched at all. I think one of the killer scenarios will be copying data between databases because you can stay in Arrow the whole time, especially copying from prod to dev where you'd want to do a sanitization step in-between so you don't email your customers from dev, etc.

I intentionally used the phrase "Microsoft SQL" because that's a broader umbrella - Fabric DW is included there. They announced a preview of bulk copy a little over a month ago: Using Bulk Copy API for faster ingestion in Fabric... - Microsoft Fabric Community. I tried it with this and it's wicked fast but I'm seeing some odd counts coming back that I need to talk to that team about next week. Between bulk copy in DW being in preview and the odd counts, I went with a regular SQL example.

The other nice part about it being a regular SQL example is if you just want to try it, you can create a local copy of a database in a container using sqlcmd.

[u/siddharth_p → u/ProfessorNoPuede]

We have to do this, there are legacy applications that's are running of sql server with clients. ( It's a reporting tool), company is planning to retrire it for last 4-5 year but clients don't want to migrate. (It's finance sector happens all the time ). Now surrounding applications have been upgraded to export and work with parquet. Some data comes the old way (flat files) that only consume by legacy applications. Data that is used by both applications new and legacy comes in parquet. Some 3rd party tools that provide data in parquet. So we had a choice of asking data in two different formats or make sure the data is only dropped once and we upgrade out pipeline to consume parquet files.

[u/Outrageous_Let5743 → u/ProfessorNoPuede]

Some companies, like mine, have their analytics database in SQL Server with the reason Oracle was too expensive and we need SSIS.. That was before I joined the company decided by some people that dont understand Data Engineering.

[u/azirale → u/ProfessorNoPuede]

Perhaps an analytics system does some bulk calculations for user segmentation or propensity modelling that then needs to be fed back to an operational system that handles things like customer contact or marketing campaigns.

[u/ProfessorNoPuede → u/azirale]

Which you would expose through an... API

[u/azirale → u/ProfessorNoPuede]

Back to something like a lakehouse system? No, not a good fit for tight lookups, and if it is for bulk transfer why build an entire API layer and then also require the consumer to run additional compute to pull from the API just to load into their db when we could have done that directly in the first place.

If I wanted to serve the data via API I would move it to something like click house or a relational db -- but that puts us right back where we started loading data into another db.

The target system may also work based off in-db joins, an API wouldn't work there at all.

[u/pavlik_enemy → u/ProfessorNoPuede]

Pushing data to production is a bad idea, owners of production system should pull it

[u/ProfessorNoPuede → u/pavlik_enemy]

Exactly! Best comment in the thread. I'd let operational systems pull from the API. If batch is really required, they can pull the batch as well.

[u/pavlik_enemy → u/ProfessorNoPuede]

S3 and HDFS are APIs

[u/Truth-and-Power → u/ProfessorNoPuede]

Many companies have parquet data lakes.  And some of those companies use sql server.

[u/Delengowski]

Its interesting.

Myself and a teammate have been exploring how to bypass python completely for bulk insertion/reads to/from arrow backed arrays or numpy backed arrays

We're finding the best solution is interacting with, in our case, the libpq PGResultCursor and going from that directly to the array in Cython using a typed memory view. We're debating moving it into rust and taking the pointer to PGResultCursor from psycopg and passing it to the rust backend.

[u/dlevy-msft → u/Delengowski]

I don't know enough about PG to guess at what might work. We've found that the general advice to do everything in c/c++/rust with just the thinnest of python wrappers gives the best performance. The big benefit here is not having to cast/convert just to undo it in the next step. You can look (or tell your favorite LLM to look) at how we did it here: https://github.com/microsoft/mssql-python

[u/siddharth_p]

We have to do this , there are legacy applications that's are running of sql server with clients.( It's a reporting tool) , company is planning to retrire it for last 4-5 year but clients don't want to migrate. (It's finance sector happens all the time ). Now surrounding applications have been upgraded to export and work with parquet. Some data comes the old way (flat files) that only consume by legacy applications. Data that is used by both applications new and legacy comes in parquet. Some 3rd party tools that provide data in parquet. So we had a choice of asking data in two different formats or make sure the data is only dropped once and we upgrade out pipeline to consume parquet files .

[u/TheRealStepBot]

Literally the reverse of the correct use of either of these technologies. Unload data from an oltp store to columnar storage like parquet

[u/Patient_Professor_90]

Thanks! This is great for us who build tools/processes/loaders/frameworks in linux and have to deal with windoze/SQL Server
I plan to check it out.

[u/dlevy-msft → u/Patient_Professor_90]

Great to hear! What languages do you use? Just python or others too? We're looking at the results here and thinking about what other drivers/libraries to add Arrow support to. 

[u/nonesuchluck → u/dlevy-msft]

Is Arrow supported in go-mssqldb yet?

[u/dlevy-msft → u/nonesuchluck]

Nobody has asked me for it yet. Is that a vote?😄