API Review: Apache Arrow Support in Bulk Copy

[gargsaumya]

Apache Arrow Support in Bulk Copy

Issue #570

We are working on native Apache Arrow support for bulk copy and would like to align on the public API design before implementation.
The primary question is whether Arrow ingestion should be exposed as:

• A dedicated bulkcopy_arrow() method, or
• An enhancement to the existing bulkcopy() API that accepts Arrow sources in addition to row iterators.

---

Motivation

Today, Cursor.bulkcopy() accepts an iterable of Python tuples or Row objects. Users who already hold data in an Arrow-based format (PyArrow, Polars, DuckDB, etc.) must first materialize that data into Python objects before bulk loading.

Allowing Arrow batches to be passed directly through the Arrow C Data Interface would eliminate that per-row materialization overhead. This is purely additive functionality.

---

Option A: Dedicated bulkcopy_arrow()

cur.bulkcopy_arrow(
    table_name,
    source,
    *,
    batch_size=0,
    timeout=30,
    column_mappings=None,
    keep_identity=False,
    ...
)
# source:
#   pyarrow.Table
#   pyarrow.RecordBatch
#   pyarrow.RecordBatchReader
#   __arrow_c_stream__ producers
#   __arrow_c_array__ producers
#   iterable[RecordBatch]

Pros

• Explicit and easy to discover.
• Clear separation between row-based and Arrow-based ingestion.
• Allows Arrow-specific validation and error messages.

Cons

• Introduces a second bulk-copy API to document, maintain, and evolve.
• Risks duplication if both methods ultimately support the same options and execution path.

---

Option B: Extend bulkcopy() to Accept Arrow Sources

Allow bulkcopy() to accept either a traditional row iterator or an Arrow source.

cur.bulkcopy(table_name, data, ...)
# data:
#   iterable[tuple | Row]
#   Arrow source

Pros

• Keeps a single bulk-copy entry point.
• Minimizes API surface area.
• Existing options (column_mappings, batch_size, keep_identity, etc.) remain unchanged.
• Treats Arrow as simply another supported input format rather than a separate operation.

Cons

• Implicit, type-dependent dispatch. One method runs two different paths (columnar Arrow vs. row tuples) based on input inspection - convenient, but less explicit.
• Raises questions around ambiguous inputs (awkward for lazy generators, see below).

---

Design Considerations

Column Mapping

column_mappings is already optional in bulkcopy() and defaults to ordinal mapping. The same behavior can naturally apply to Arrow sources, so column mapping alone does not justify a separate API.

Input Ambiguity

If we unify the API, we need to distinguish:

iterable[tuple]

from

iterable[RecordBatch]

Potential approaches include:

• Inspecting the first element.
• Supporting Arrow only through concrete Arrow types and Arrow C Data Interface protocols.
• Requiring an explicit opt-in flag. This feels like the main design tradeoff for a unified API.

Performance

Performance should not influence the API choice. Both approaches would use the same underlying implementation and differ only in the public surface area.

---

Open Questions

1. Do you prefer a dedicated bulkcopy_arrow() API or a unified bulkcopy() that also accepts Arrow sources?
2. If we unify the API, is implicit input detection acceptable, or would an explicit opt-in mechanism be preferable?
3. From a discoverability perspective, would you naturally look for bulkcopy_arrow(), or expect bulkcopy() to work directly with Arrow objects?

I'd appreciate feedback before implementation begins.

cc: @saurabh500 @dlevy-msft-sql @sumitmsft

[saurabh500]

Adding @amachanic @jschuba for any feedback.

[saurabh500]

Adding @oscarrobertson who had opened this issue #551

[azhangd]

Q1: I'd go with bulkcopy_arrow(). I'd rather the schema-aware behavior live in its own method than be a sometimes on branch of bulkcopy(). ADBC also went the same way with adbc_ingest, including a mode arg to create/append.

Q3: bulkcopy_arrow(), personally. A named method I can find by searching "arrow" beats having to read and check if bulkcopy() takes it.

[djouallah]

i don't care either way, just give us arrow :)

[saurabh500]

Overloading the existing bulkcopy API makes sense. This is because the two APIs only differ in the input type, but all other functionality is the same.
Also its a nice value add for the current API to simply accept Arrow iterable recordbatch. Less cognitive overload for the users.

[oscarrobertson]

I'm with @djouallah :D (also agree with @azhangd )

For users this really makes no difference, especially given how new this lib is, it's tough to have a strong opinion either way. If I were a maintainer I'd probably lean towards bulkcopy_arrow just because it avoids irritating python design issues that you've highlighted and gives you a bit more future proofing if you end up needing more arrow specific parameters.

[saurabh500]

This reasoning resonates with me, even though I had voted to overload bulkcopy API

[azhangd]

I voted for bulkcopy_arrow(), but I don’t feel strongly about it. Both seem reasonable.

[jschuba]

I vote for one method: bulkcopy(). That method should take anything that looks reasonably table-like and copy it into the database.

As a user, if a library has multiple methods that do the same thing and only differ by their input type, I find this annoying. I usually end up writing my own little wrapper function that takes any valid object and dispatches it to the appropriate library method. bulkcopy() should just do this natively.

However, I agree with the other comment that if the methods actually differ meaningfully (eg the arrow path takes more/different arguments) then they should be different functions.

[amachanic]

At first I was Team bulkcopy but after giving it some thought I'm leaning more and more toward bulkcopy_arrow. The input validation, specific error handling, etc, all seem like big enough pieces that a separate path will make sense. And the potential for future Arrow-specific params really seals the deal; Arrow seems to still be very actively in development and who knows what new enhancements will be added there that might be useful to SQL Server bulk copying?

[gargsaumya]

Thank you all for the thoughtful feedback here. After an internal discussion and weighing the suggestions raised on this thread, we've decided to go with the dedicated bulkcopy_arrow() API (Option A).
We're actively working on this feature now and will follow up on this thread as implementation progresses.

Thanks again, everyone. 🙂

[sumitmsft]

Although, already discussed with Saumya internally - felt like adding my view point here.

To me, dedicated bulkcopy_arrow() is safer because Arrow inputs have different validation rules (schema, nullability, type coercion) than tuple-row inputs, so errors can be precise instead of mixed. A separate API gives cleaner compatibility/versioning promises as Arrow protocol support evolves independently from existing bulkcopy() behavior. From future integration perspective which other reviewers have already written about, I feel bulkcopy_arrow() makes more sense.