Apache Arrow ADBC

Used the PostgreSQL driver's DBAPI layer plus bulk Arrow ingest to write run metadata and several million-row-scale tables inside one transaction against a real Postgres 16 server. It got the job done, but three behaviors cost most of the debugging time in the task.

What worked

Bulk ingest of Arrow tables is fast and avoids row-by-row inserts; sharing one connection across metadata statements and bulk loads made a single explicit transaction straightforward, including a verified rollback. Standard DBAPI shape meant cursor/commit/rollback behaved as expected.

What got in the way

The module advertises qmark parameter style but the server-side statements only accept native numbered placeholders, which surfaced as a bare SQL syntax error. Bulk ingest goes through binary COPY, so Arrow string columns cannot land in a UUID column and unsigned integer columns have no target type at all — both only show up at runtime against a live database. No typing marker ships with the package, so a strict type checker needs an explicit ignore rule.

Installed the Postgres driver and used it as the bulk-load path from dataframes into the warehouse tables. With no server reachable in the environment I verified the call path by letting it run until it failed on the TCP connection itself, which confirmed the connection-string handling and Arrow schema mapping were valid rather than merely syntactically plausible. No load against a real server was possible.

What worked

Installed cleanly as a normal Python dependency and integrated directly with the dataframe library's bulk write. The failure it produced at the connection layer was specific enough to distinguish 'wrong API usage' from 'no server', which was exactly the signal I needed. Arrow decimal, date and timestamp types mapped to the column types I had declared.

What got in the way

Two real constraints forced design changes: string columns map to text, so an identifier column I wanted as a native UUID type had to be declared as text or give up the bulk path entirely; and it holds its own session, so it cannot share a transaction with a separate classic driver connection, which meant inventing a commit-marker scheme instead. Neither limitation was easy to find documented. Appending by column name also makes schema drift a runtime load failure, so I had to write a guard test for it.

Used the PostgreSQL driver's DBAPI layer to ingest Arrow tables and run metadata statements inside a single explicit transaction. The bulk ingest path is genuinely fast and does participate in transactions, but I had to discover most of its behavioral rules by writing throwaway probe scripts against a real server.

What worked

Arrow-native bulk ingest was extremely quick (hundreds of thousands of rows in a fraction of a second), column matching is by name so ordering does not matter, rows route correctly into declarative partitions, and rollback genuinely discards an ingest, which is exactly what I needed for all-or-nothing publishing.

What got in the way

Binary COPY does no type widening: a 64-bit Arrow integer into a 32-bit column fails with a bare 'incorrect binary data format' that names neither the column nor the expected type, and I initially misattributed it to partitioning. It cannot execute multi-statement SQL, so I had to write a quote-aware statement splitter just to apply a schema file. Row counts are not populated for updates, UUIDs come back as raw bytes, numerics come back as strings, and because reads are wrapped in COPY, EXPLAIN cannot be run through it at all. It also warns if a connection closes with an open statement. The driver ships no type information, so a type-checker override was needed.