ADBC (Arrow Database Connectivity): The Modern Replacement for ODBC/JDBC

adbc — Arrow Database Connectivity — is the columnar-first database API that finally removes the tax every senior data engineer has silently paid for two decades: the cost of dragging a columnar result set through a row-oriented driver only to reassemble the columns again on the other side. Every analytical query your stack runs — a SELECT scanning a hundred million rows out of Snowflake, a DuckDB aggregation, a ClickHouse rollup, a BigQuery Storage read — produces data that is already columnar on the wire, and every consumer you feed it into — pandas, Polars, Apache DataFusion, a Parquet writer — wants it columnar. The two ends agree. It is the driver in the middle, ODBC or JDBC, that insists on tearing the columns apart into rows and handing you one cell at a time, forcing a transpose on the way out and a transpose on the way back in.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "why is arrow database connectivity faster than ODBC for a columnar warehouse," or "walk me through the ADBC three-layer model," or "you have a pyodbc pipeline pulling 40 GB of Snowflake results a day — how would you cut the CPU in half without changing the SQL?" It covers the five things that matter: why ODBC/JDBC's row model hurts columnar workloads and what an arrow native driver fixes, the ADBC architecture — driver manager, drivers, Arrow result streams — and how its handle hierarchy compares to ODBC's, the shipped drivers (PostgreSQL, SQLite, Snowflake, flight sql adbc) sitting behind one dbapi 2.0 surface, the Python recipes for fetch_arrow_table, streaming, adbc_ingest bulk-load, and transactions, and the ADBC vs ODBC/JDBC vs Flight SQL decision matrix with a concrete migration path. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the data-processing practice library →, and sharpen the pipeline axis with the ETL practice library →.

---

On this page

• Why ODBC/JDBC hurts columnar workloads and what ADBC fixes
• ADBC architecture — the three-layer model
• ADBC drivers in practice — Postgres, SQLite, Snowflake, Flight SQL
• Using ADBC from Python — fetch_arrow_table, ingest, transactions
• ADBC vs ODBC/JDBC vs Flight SQL, migration and interview
• Cheat sheet — ADBC recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why ODBC/JDBC hurts columnar workloads and what ADBC fixes

Row-oriented driver APIs impose a transpose tax on columnar data — ADBC keeps the result set columnar from wire to consumer

The one-sentence invariant: ODBC and JDBC are row-oriented client APIs designed in an era when both the database and the consumer were row-oriented, so when you point them at a columnar database whose results are already Arrow-shaped and feed the output into a columnar consumer like pandas or Polars, the driver transposes columns→rows on the way out and your consumer transposes rows→columns on the way in — two full transposes plus per-cell object boxing that adbc deletes by keeping the data in Apache Arrow's columnar layout the entire way. The database agrees the data is columnar; the analytics library agrees the data is columnar; only the 1992-vintage driver in the middle disagrees, and that disagreement is pure overhead you have been paying without a line item for it.

What "row-oriented API" actually means.

• ODBC's contract. You call SQLFetch to advance to the next row, then read each column with SQLGetData / bound buffers. The API's atomic unit is one row; there is no "give me the whole amount column" call. A million-row result is a million SQLFetch iterations from the consumer's point of view.
• JDBC's contract. ResultSet.next() advances a cursor one row at a time; getInt, getString, getObject pull one cell. getObject boxes every scalar into a java.lang.Object, so a 100M-cell result allocates 100M boxed objects the garbage collector must later reclaim.
• The shared assumption. Both APIs assume the natural iteration order is row by row. That was correct for OLTP row stores and record-at-a-time application code. It is exactly wrong for analytical scans feeding vectorised compute.

What "columnar" changes about the cost.

• Columnar databases already serialize columns. Snowflake, BigQuery (Storage API), ClickHouse, DuckDB, Redshift, and Databricks all store and often transmit data column-by-column — increasingly as Arrow batches on the wire. The bytes arriving at your driver are contiguous column chunks.
• The ODBC/JDBC driver must transpose them to rows. To satisfy SQLFetch/ResultSet.next(), the driver reassembles those column chunks into row tuples — a scatter write across memory that destroys cache locality. This is the transpose tax, half of it.
• Then your consumer transposes back. pandas.read_sql walks the row cursor and rebuilds columns; a NumPy array or Arrow table is column-major. So the rows the driver just built are immediately torn apart into columns again — the second half of the transpose tax, plus a Python object per cell for dynamically typed rows.

Where the CPU actually goes.

• Per-value boxing. In Python, every ODBC cell becomes a PyObject (an int, str, Decimal); in Java, every JDBC getObject cell becomes a boxed Object. For a 100M-cell result that is 100M allocations on each side of the fence.
• Cache-hostile scatter. Transposing column chunks into row tuples writes one field here, one field there — the opposite of the sequential column writes a columnar layout wants. Memory bandwidth, not the query, becomes the bottleneck.
• Type round-trips. ODBC's C type system and JDBC's java.sql.Types are not the database's types and not Arrow's types, so values are coerced twice: DB type → driver type → Python/Java type, then again into the Arrow/NumPy type your consumer needs.

What ADBC keeps instead.

• Arrow all the way. ADBC's result-set type is an Arrow stream (ArrowArrayStream over the Arrow C Data Interface). The driver hands you Arrow record batches; pandas/Polars/DataFusion consume Arrow record batches. No transpose, on either side.
• Zero-copy handoff. Because both the driver and the consumer speak the Arrow C Data Interface, the buffers are passed by pointer, not re-serialized. A fetch_arrow_table() that feeds Polars can be a pointer move, not a copy.
• One API across databases. ADBC is a single client API (AdbcDatabase/AdbcConnection/AdbcStatement, and a Python dbapi 2.0 layer on top) that any conforming driver implements — so the same code targets Postgres, SQLite, Snowflake, or Flight SQL by swapping the driver, exactly like ODBC's promise but columnar.

What interviewers listen for.

• Do you name the transpose tax — "columnar source → row API → columnar consumer = two transposes" — without prompting? — senior signal.
• Do you say "ADBC's result set is an Arrow stream" rather than "ADBC is faster"? — required answer.
• Do you distinguish API from wire protocol — ADBC/ODBC are client APIs, Flight SQL is a wire protocol — before the follow-up asks? — senior signal.
• Do you note that ADBC still works against row stores like Postgres, it just doesn't get the free lunch a columnar source gives? — senior signal.
• Do you describe the win as "eliminating per-cell boxing and the double transpose," not as vague "less overhead"? — required answer.

Worked example — count the transposes in an ODBC→pandas pipeline

Detailed explanation. The clearest way to internalise the transpose tax is to trace a single pandas.read_sql call against a columnar warehouse and mark every place the data changes shape. Most engineers assume read_sql is one hop; it is four.

• Hop 1 — the warehouse serializes columns. Snowflake's result chunks are columnar (Arrow, in the Python connector's fast path).
• Hop 2 — the ODBC driver transposes to rows. To satisfy the row cursor API, columns become row tuples.
• Hop 3 — the DBAPI cursor yields Python row tuples. fetchmany returns lists of tuples of boxed Python objects.
• Hop 4 — pandas transposes back to columns. The DataFrame constructor rebuilds column arrays from those row tuples.

Question. For a result of R rows × C columns pulled from a columnar warehouse through ODBC into a pandas DataFrame, count the transposes and the number of Python objects allocated, then state what ADBC changes.

Input.

Stage	Shape after this stage	Cost
Warehouse wire	columnar (Arrow chunks)	0 (already columnar)
ODBC driver	rows	transpose #1 (columns → rows)
DBAPI cursor	list of row tuples	R×C boxed Python objects
pandas DataFrame	columnar	transpose #2 (rows → columns)

Code.

# The row-oriented path: columnar source, row API, columnar consumer
import pandas as pd
import pyodbc  # row-oriented ODBC DBAPI

conn = pyodbc.connect(DSN)  # Snowflake / any columnar warehouse via ODBC
sql = "SELECT id, amount, ts, region FROM fact_sales WHERE ts >= '2026-01-01'"

# read_sql walks a ROW cursor: SQLFetch loop under the hood,
# builds R tuples of C boxed objects, then transposes to columns.
df = pd.read_sql(sql, conn)   # 2 transposes + R*C boxed objects

# The Arrow-native path: columnar source, columnar API, columnar consumer
import adbc_driver_snowflake.dbapi as snowflake

conn = snowflake.connect(SNOWFLAKE_URI)
with conn.cursor() as cur:
    cur.execute(sql)
    table = cur.fetch_arrow_table()   # pyarrow.Table, columnar, no transpose
    df = table.to_pandas()            # column-to-column, cheap / often zero-copy

Step-by-step explanation.

1. In the pyodbc path, pd.read_sql opens a DBAPI row cursor. Even though Snowflake's result chunks are columnar, the ODBC layer transposes them into rows so fetchmany can return tuples — that is transpose #1.
2. Each cell in each tuple is a boxed Python object (int, Decimal, str, datetime). For an R-row × C-column result that is R×C allocations the interpreter must create and later free.
3. pandas then constructs column arrays from those row tuples, tearing the rows back apart into columns — transpose #2. The data has now been columnar → rows → columnar, having started and ended in the shape it wanted.
4. In the adbc path, fetch_arrow_table() returns the driver's Arrow batches directly as a pyarrow.Table. There is no row stage, so there is no transpose and no R×C per-cell boxing — the columns stay contiguous.
5. table.to_pandas() is a column-to-column conversion; for Arrow-backed dtypes it can be zero-copy, and even for NumPy-backed dtypes it is one pass per column rather than a rebuild from row tuples.

Output.

Metric	ODBC → pandas	ADBC → pandas
Transposes	2	0
Per-cell Python objects	R×C	~0 (columnar buffers)
Type coercions per value	2 (DB→C→Py)	1 (DB→Arrow)
Handoff to consumer	rebuild from tuples	column arrays / zero-copy
Bottleneck	memory bandwidth + GC	network / decode

Rule of thumb. Any time the source is columnar (Snowflake, DuckDB, ClickHouse, BigQuery, Redshift) and the consumer is columnar (pandas, Polars, Arrow, Parquet), a row-oriented driver taxes you twice. Reach for the arrow native driver and the transpose tax disappears — the SQL does not change, only the driver import.

Worked example — the JDBC getObject boxing cost

Detailed explanation. On the JVM the transpose tax shows up as garbage-collector pressure. A JDBC ResultSet iterated with getObject allocates one boxed Object per cell; at analytical scale that is the dominant cost, and it is invisible until you profile allocations. The Arrow-native answer on the JVM is the ADBC Java driver (or Arrow Flight SQL's JDBC-to-Arrow bridge), which materialises Arrow VectorSchemaRoot batches instead of per-cell objects.

• The symptom. A Spark/Trino-adjacent batch job spends 40% of wall-clock in GC while pulling a wide result over JDBC.
• The cause. getObject (and even typed getters into an Object[] row) box every scalar; a 50M-row × 12-column result is 600M short-lived objects.
• The fix. Materialise Arrow batches — one IntVector/Float8Vector per column per batch — so the allocation count drops from R×C scalars to O(batches × C) vectors.

Question. Quantify the allocation count for a 50M-row × 12-column pull under row-oriented JDBC versus Arrow-batch materialisation, and explain why GC time collapses.

Input.

Parameter	Value
Rows (R)	50,000,000
Columns (C)	12
JDBC batch fetch size	10,000
Arrow batch size	65,536 rows

Code.

// Row-oriented JDBC: one boxed Object per cell
try (ResultSet rs = stmt.executeQuery(sql)) {
    while (rs.next()) {                 // R iterations
        for (int c = 1; c <= 12; c++) {
            Object v = rs.getObject(c);  // R*C boxed allocations
            consume(v);
        }
    }
}
// Allocations ~ R*C = 600,000,000 boxed scalars

// Arrow-native (ADBC / Flight SQL JDBC-Arrow): one vector per column per batch
try (ArrowReader reader = adbcStatement.executeQuery()) {
    while (reader.loadNextBatch()) {            // ~R/65536 batches
        VectorSchemaRoot root = reader.getVectorSchemaRoot();
        // 12 typed column vectors, contiguous, no per-cell boxing
        Float8Vector amount = (Float8Vector) root.getVector("amount");
        for (int i = 0; i < root.getRowCount(); i++) {
            double a = amount.get(i);           // primitive read, no allocation
        }
    }
}
// Allocations ~ batches * C = (50M/65536)*12 ≈ 9,156 vectors

Step-by-step explanation.

1. The JDBC loop calls rs.next() R times and getObject R×C times. Each getObject returns a reference type, so a primitive double is autoboxed into a Double on the heap — 600M short-lived allocations for this result.
2. Those 600M objects are almost all garbage immediately after consume(v), so they flood the young generation and trigger minor collections continuously; the profiler attributes ~40% of wall-clock to GC.
3. The Arrow path loads batches of 65,536 rows. Each batch is a VectorSchemaRoot holding 12 typed column vectors backed by off-heap contiguous buffers — not per-cell objects.
4. Reading amount.get(i) returns a primitive double straight from the vector's buffer with no allocation. The allocation count drops from R×C scalars to roughly (R / batch_size) × C vectors — about four orders of magnitude fewer.
5. With almost nothing to collect, GC time collapses and the job becomes bound by network and decode rather than by the allocator — the same structural win ADBC gives Python via fetch_arrow_table.

Output.

Metric	Row-oriented JDBC	Arrow-batch (ADBC)
Heap allocations	~600,000,000	~9,156
GC share of wall-clock	~40%	negligible
Per-cell type	boxed Object	primitive in vector
Memory locality	scattered	contiguous columns
Dominant cost	allocator + GC	network + decode

Rule of thumb. On the JVM the transpose tax is spelled "garbage collection." If a JDBC-fed analytical job spends double-digit percentages in GC, the fix is Arrow-batch materialisation via ADBC, not a bigger heap — you are treating a symptom of per-cell boxing, and a bigger heap just defers the collection.

Common beginner mistakes

• Thinking ADBC is only for columnar databases. ADBC has a first-class PostgreSQL driver; it works fine against row stores. You simply do not get the "source is already columnar" free lunch — the win there is one clean API and Arrow output, not a transpose you avoided.
• Confusing ADBC with Arrow Flight SQL. ADBC is a client API; Flight SQL is a wire protocol. They compose — ADBC has a Flight SQL driver — but they are different layers, and conflating them is the fastest way to sound junior in an interview.
• Assuming pandas.read_sql is "one hop." It is a row cursor underneath; against a columnar warehouse it pays both transposes. Reaching for fetch_arrow_table() is the fix, not tuning chunk size.
• Believing ODBC-to-Arrow bridges are free. Tools like turbodbc and arrow-odbc do produce Arrow output, but they still receive rows from the ODBC driver and transpose to Arrow internally — better than pandas' row path, still not the end-to-end columnar path ADBC gives.

SQL interview question on the columnar transpose tax

A senior interviewer might ask: "Your team runs pandas.read_sql against Snowflake through ODBC and pulls roughly 40 GB of result data a day into a feature pipeline. Profiling shows the box is CPU-bound in the driver and pandas, not in Snowflake. Explain what is happening in driver terms and show the change you would make — without touching the SQL — to cut CPU and memory."

Solution Using an Arrow-native driver to remove the double transpose

# BEFORE — row-oriented ODBC path: 2 transposes + per-cell boxing
import pandas as pd
import pyodbc

def load_features_odbc(dsn: str, sql: str) -> pd.DataFrame:
    conn = pyodbc.connect(dsn)
    # read_sql walks a row cursor; Snowflake's columnar chunks are
    # transposed to rows, boxed into tuples, then transposed back to
    # columns by the DataFrame constructor.
    return pd.read_sql(sql, conn)   # CPU-bound in driver + pandas

# AFTER — ADBC Arrow-native path: 0 transposes, columnar end to end
import adbc_driver_snowflake.dbapi as snowflake
import pyarrow as pa

def load_features_adbc(uri: str, sql: str) -> pa.Table:
    with snowflake.connect(uri) as conn:
        with conn.cursor() as cur:
            cur.execute(sql)
            # Driver hands back Snowflake's Arrow batches directly.
            # No row stage exists, so there is no transpose and no
            # R*C Python-object allocation.
            return cur.fetch_arrow_table()

# Downstream stays columnar; convert only at the last moment if needed.
table = load_features_adbc(SNOWFLAKE_URI, FEATURE_SQL)
# Feed Polars zero-copy, or pandas one-pass-per-column:
import polars as pl
frame = pl.from_arrow(table)          # zero-copy from Arrow
# df = table.to_pandas()              # if a pandas consumer truly needs it

Step-by-step trace.

Input	ODBC path	ADBC path
Snowflake result (columnar)	received as columns	received as columns
Driver stage	transpose → rows	none (stays Arrow)
Cursor materialisation	R×C boxed tuples	Arrow record batches
Consumer stage	pandas rebuilds columns	zero-copy to Polars / one-pass to pandas
Net shape changes	columnar→rows→columnar	columnar→columnar

1. The profiler pointing at the driver and pandas — not Snowflake — is the tell: the query is fine; the cost is the row round-trip. That immediately rules out "tune the warehouse" answers.
2. Swapping pyodbc for adbc_driver_snowflake.dbapi keeps the SQL byte-for-byte identical; only the import and the fetch call change.
3. fetch_arrow_table() returns the driver's Arrow batches as a pyarrow.Table. The row stage that boxed R×C objects and forced two transposes never runs.
4. Handing the Arrow table to Polars via pl.from_arrow is a zero-copy pointer move; if a legacy consumer needs pandas, to_pandas() is a column-wise conversion rather than a rebuild-from-tuples.
5. Because the whole path is now columnar, the box stops being CPU-bound in the driver and pandas; the remaining time is network transfer and Arrow decode, which is where the time should go.

Output:

Metric	Before (ODBC)	After (ADBC)
Transposes per query	2	0
Per-cell Python objects	R×C	~0
CPU location	driver + pandas	network + decode
Peak memory	rows + columns held	columnar buffers only
SQL changed	—	none

Why this works — concept by concept:

• Transpose tax — a columnar source feeding a columnar consumer through a row API pays for two shape changes and per-cell boxing; removing the row stage removes both at once. This is the entire performance thesis of adbc.
• Arrow C Data Interface — ADBC results are exposed as an ArrowArrayStream, a stable ABI for handing Arrow buffers across a library boundary by pointer. That is what makes the driver→consumer handoff zero-copy rather than a re-serialize.
• fetch_arrow_table — the DBAPI Arrow extension that returns a pyarrow.Table directly, bypassing the row-tuple materialisation fetchall would do. It is the single most load-bearing call in an ADBC pipeline.
• Driver swap, not query rewrite — because the SQL is unchanged and only the client driver differs, the migration risk is confined to the connection and fetch layer, which is what makes this a safe, high-leverage change.
• Cost — CPU drops from O(R×C) boxing + two O(R×C) transposes to O(R×C) decode once, and memory drops from holding both a row copy and a column copy to holding columnar buffers alone. Net: roughly halved CPU and memory for large columnar pulls, with zero SQL change.

Data Processing
Topic — data-processing
Columnar / Arrow data-processing problems

Practice →

SQL Topic — sql SQL result-set and scan problems

Practice →

---

2. ADBC architecture — the three-layer model

Driver manager on top, per-database drivers below, Arrow result streams in between — one API, swappable backends

The mental model in one line: adbc is a three-layer architecture — a thin adbc driver manager that dynamically loads drivers by name, a set of per-database drivers that each implement the same abstract ADBC C API, and Arrow result streams that carry query output back up as columnar record batches — with a handle hierarchy (AdbcDatabase → AdbcConnection → AdbcStatement) that mirrors ODBC's environment/connection/statement handles but returns Arrow instead of rows. The genius is boring on purpose: it is ODBC's proven "one API, many drivers, a manager that loads them" shape, re-cut so the result type is an Arrow stream and the ABI is the Arrow C Data Interface.

Layer 1 — the driver manager.

• What it does. Loads a driver shared library (.so/.dylib/.dll) by name or path at runtime and exposes the ADBC API to your application, so your code links against the manager, not against any specific database. In Python this is the adbc_driver_manager package.
• The ODBC parallel. This is exactly ODBC's Driver Manager (unixODBC/iODBC/the Windows DM) — the indirection layer that lets one binary talk to many databases. ADBC copies the pattern; it does not reinvent it.
• Why it matters. You can swap Postgres for Snowflake by changing which driver the manager loads, not by recompiling your application. It also means driver vendors ship a single shared library that every ADBC-speaking language can load.

Layer 2 — the drivers.

• What they are. Per-database implementations of the abstract ADBC C API. adbc_driver_postgresql and adbc_driver_sqlite are written in C/C++; adbc_driver_snowflake and adbc_driver_flightsql are written in Go and exposed through a C ABI shim.
• Why the language is invisible to you. Because every driver presents the same C ABI, a Go driver and a C driver are interchangeable from the manager's point of view. Your Python code cannot tell — and should not care — that the Snowflake driver is Go underneath.
• What a driver must implement. Connecting, preparing and executing statements, binding parameters, returning results as an ArrowArrayStream, metadata introspection (GetObjects, GetTableSchema), and transaction control (Commit/Rollback, autocommit toggle).

Layer 3 — the Arrow result streams.

• The result type is a stream, not a cursor. Executing a query yields an ArrowArrayStream — a pull-based sequence of Arrow RecordBatches over the Arrow C Data Interface, plus a rows_affected count for writes.
• Zero-copy by construction. The C Data Interface passes Arrow buffers by pointer with a release callback, so the driver and consumer share memory rather than re-serializing — the mechanism behind fetch_arrow_table's cheapness.
• Streaming, not buffering. Because it is a stream, you can consume batch-by-batch and never hold the whole result in memory — essential for results larger than RAM.

The handle hierarchy — and its ODBC/JDBC analogues.

• AdbcDatabase. Holds shared, connection-independent configuration (driver name, URI, credentials, pool-wide options). ODBC analogue: the environment handle SQLHENV. JDBC analogue: the DataSource/DriverManager config.
• AdbcConnection. A single session/transaction context created from a database handle. ODBC analogue: SQLHDBC. JDBC analogue: java.sql.Connection.
• AdbcStatement. A prepared or ad-hoc statement you set SQL/substrait on, bind parameters to, and execute to get an Arrow stream. ODBC analogue: SQLHSTMT. JDBC analogue: PreparedStatement.
• Why the parallel is deliberate. Anyone who has written ODBC or JDBC already knows this shape; ADBC's contribution is not a new object model but the columnar result type hung off the familiar one.

What interviewers listen for.

• Do you name all three layers — manager, drivers, Arrow streams — unprompted? — senior signal.
• Do you map the handle hierarchy to ODBC's SQLHENV/SQLHDBC/SQLHSTMT? — senior signal.
• Do you say the result is an ArrowArrayStream over the C Data Interface, not "a DataFrame"? — required answer.
• Do you note that drivers can be written in any language (Go for Snowflake/Flight SQL) behind one C ABI? — senior signal.
• Do you explain that the manager loads drivers dynamically, so swapping databases is a config change? — required answer.

Worked example — the low-level driver-manager statement lifecycle

Detailed explanation. The high-level DBAPI layer hides the three handles, but knowing the raw lifecycle is what lets you reason about pooling, options, and errors. Walk the AdbcDatabase → AdbcConnection → AdbcStatement → ArrowArrayStream path once, at the low level, so the DBAPI convenience layer stops being magic.

• Database handle. Configure driver + URI; this is process-wide, shareable config.
• Connection handle. One per session; carries the transaction context.
• Statement handle. Set the SQL, execute, get back a stream + row count.
• Stream. Import as a pyarrow.RecordBatchReader; read batches.

Question. Execute SELECT against SQLite using the raw adbc_driver_manager handles (no DBAPI layer) and materialise the result as a pyarrow.Table.

Input.

Handle	ADBC type	ODBC analogue	Lifetime
Database	AdbcDatabase	SQLHENV	process / pool-wide
Connection	AdbcConnection	SQLHDBC	one session
Statement	AdbcStatement	SQLHSTMT	one query (reusable)
Result	ArrowArrayStream	row cursor	one execution

Code.

# Low-level ADBC: the three handles, no DBAPI sugar
import adbc_driver_sqlite
import adbc_driver_manager
import pyarrow

# Layer 1+2: the manager loads the SQLite driver via its entrypoint.
db = adbc_driver_manager.AdbcDatabase(
    driver=adbc_driver_sqlite._driver_path(),  # shared library path
    uri="file:example.db",
)

# Layer: connection (one session / transaction context)
conn = adbc_driver_manager.AdbcConnection(db)

# Layer: statement — set SQL, execute, get an Arrow stream back
stmt = adbc_driver_manager.AdbcStatement(conn)
stmt.set_sql_query("SELECT id, region, amount FROM fact_sales")
stream, rows_affected = stmt.execute_query()

# Layer 3: import the ArrowArrayStream via the C Data Interface (zero-copy)
reader = pyarrow.RecordBatchReader._import_from_c(stream.address)
table = reader.read_all()          # pyarrow.Table, columnar

print(table.num_rows, table.column_names)

# Explicit teardown (DBAPI does this for you)
stmt.close(); conn.close(); db.close()

Step-by-step explanation.

1. AdbcDatabase(driver=..., uri=...) is the manager loading a driver shared library and stamping it with connection-independent config. This handle is safe to share across connections and threads — it is where a pool would live.
2. AdbcConnection(db) opens one session from that database handle. It carries the transaction state; two connections from the same database are independent sessions.
3. AdbcStatement(conn) is the per-query handle. set_sql_query stages the SQL; execute_query runs it and returns a (stream, rows_affected) pair — the stream for reads, the count for writes.
4. execute_query hands back a raw ArrowArrayStream capsule. pyarrow.RecordBatchReader._import_from_c(stream.address) adopts it over the C Data Interface — a pointer handoff, not a copy — yielding a normal PyArrow reader.
5. reader.read_all() drains the stream into a pyarrow.Table; for a huge result you would instead iterate for batch in reader: to stay streaming. Finally the three handles are closed in reverse order — the exact bookkeeping the DBAPI layer automates.

Output.

Observation	Value
Handles created	3 (db, conn, stmt)
Result type	ArrowArrayStream → RecordBatchReader
Copy on import	none (C Data Interface pointer)
Materialised as	pyarrow.Table
DBAPI equivalent	conn.cursor().execute(...).fetch_arrow_table()

Rule of thumb. You will write DBAPI code 95% of the time, but know the three-handle lifecycle: it is how you reason about connection pooling (share the AdbcDatabase), per-session options (set on the AdbcConnection), and statement reuse (keep the AdbcStatement, rebind parameters). The DBAPI layer is sugar over exactly these handles.

Worked example — swapping the backend by changing only the driver

Detailed explanation. The architectural payoff of the manager layer is that "which database" becomes a configuration value, not a code fork. Demonstrate it by writing one function that runs the same query against any ADBC backend, selecting the driver by name.

• The fixed part. The SQL, the fetch call, the Arrow handling — identical across databases.
• The variable part. Which driver module supplies connect, and the connection string format.
• The lesson. ADBC gives you ODBC's "swap the DSN" portability, but the output is Arrow, so the downstream code is portable too.

Question. Write a run(engine, ...) dispatcher that executes the same SQL against SQLite, PostgreSQL, or Snowflake by driver name and always returns a pyarrow.Table.

Input.

engine	driver module	connection argument
sqlite	adbc_driver_sqlite.dbapi	":memory:" or file path
postgres	adbc_driver_postgresql.dbapi	postgresql://… URI
snowflake	adbc_driver_snowflake.dbapi	user:pass@acct/db/schema?...

Code.

import importlib
import pyarrow as pa

# One dispatcher; the driver name is the only thing that varies.
_DRIVERS = {
    "sqlite":    "adbc_driver_sqlite.dbapi",
    "postgres":  "adbc_driver_postgresql.dbapi",
    "snowflake": "adbc_driver_snowflake.dbapi",
}

def run(engine: str, target: str, sql: str) -> pa.Table:
    dbapi = importlib.import_module(_DRIVERS[engine])  # manager loads driver
    with dbapi.connect(target) as conn:
        with conn.cursor() as cur:
            cur.execute(sql)
            return cur.fetch_arrow_table()             # Arrow, every backend

# Identical call site, three backends:
t1 = run("sqlite",   ":memory:",                    "SELECT 1 AS x")
t2 = run("postgres", "postgresql://app@db/warehouse","SELECT count(*) FROM orders")
t3 = run("snowflake","user:pw@acct/ANALYTICS/PUBLIC?warehouse=WH",
                                                     "SELECT count(*) FROM orders")

Step-by-step explanation.

1. _DRIVERS maps a friendly engine name to the driver's DBAPI module path. Choosing a backend is now a dictionary lookup, not a branch in the query code.
2. importlib.import_module asks the ADBC manager to load the selected driver's shared library the first time it is used; subsequent calls reuse it.
3. dbapi.connect(target) returns a DBAPI connection regardless of engine — the connection-string format differs per database, but the returned object honours the same interface.
4. cur.execute(sql) and cur.fetch_arrow_table() are identical across all three backends because they are defined by the ADBC DBAPI contract, not by any one driver.
5. Every call returns a pyarrow.Table, so the downstream pipeline — Polars, DataFusion, a Parquet write — is written once and reused for all three engines. That is the "one API, many backends" promise made concrete.

Output.

Call	Backend	Return type
run("sqlite", …)	SQLite (C driver)	pyarrow.Table
run("postgres", …)	PostgreSQL (C driver)	pyarrow.Table
run("snowflake", …)	Snowflake (Go driver)	pyarrow.Table
Code paths	1 (shared)	1 (shared)

Rule of thumb. Design your data-access layer to depend on the ADBC DBAPI surface, not on a specific driver. Then "add a new warehouse" is a one-line entry in a driver map, and every consumer downstream already speaks Arrow. Portability is the architecture's whole point — build to it.

Common beginner mistakes

• Recreating the AdbcDatabase per query. The database handle is process-wide config and is the natural home for a connection pool; recreating it per query throws away the loaded driver and any pooling. Create it once.
• Treating the result like a rewindable cursor. An ArrowArrayStream is forward-only and single-pass. If you need the data twice, read it into a pyarrow.Table once and reuse the table.
• Assuming all drivers support every option. Options like statement timeouts, bulk-ingest modes, or specific auth flows are driver-specific. Check the driver's docs; the manager exposes a uniform API but drivers implement different subsets.
• Forgetting the Go drivers are still one shared library. Snowflake and Flight SQL drivers are Go, but you install and load them exactly like the C drivers — there is no separate Go runtime to manage from Python.

Design interview question on the ADBC layering

A senior interviewer might ask: "Design the data-access layer for a service that must read from Postgres today and add Snowflake and a Flight SQL endpoint next quarter, feeding a Polars pipeline. Explain the ADBC layers you would lean on, where a connection pool lives, and why the downstream code does not change when you add a backend."

Solution Using the driver-manager layer as a swappable backend seam

# A backend-agnostic gateway built on the ADBC three-layer model.
import importlib
from contextlib import contextmanager
import pyarrow as pa
import polars as pl

class ArrowGateway:
    """One seam over many ADBC drivers; downstream sees only Arrow."""

    _DRIVERS = {
        "postgres":  "adbc_driver_postgresql.dbapi",
        "snowflake": "adbc_driver_snowflake.dbapi",
        "flightsql": "adbc_driver_flightsql.dbapi",
    }

    def __init__(self, engine: str, target: str, **db_kwargs):
        self._dbapi = importlib.import_module(self._DRIVERS[engine])
        self._target = target
        self._db_kwargs = db_kwargs
        # NOTE: the AdbcDatabase (loaded driver + config) is created once
        # inside connect(); a pool would cache connections keyed on target.

    @contextmanager
    def connect(self):
        conn = self._dbapi.connect(self._target, **self._db_kwargs)
        try:
            yield conn
        finally:
            conn.close()

    def query_arrow(self, sql: str, params=None) -> pa.Table:
        with self.connect() as conn:
            with conn.cursor() as cur:
                cur.execute(sql, params)
                return cur.fetch_arrow_table()   # ArrowArrayStream -> Table

    def query_polars(self, sql: str, params=None) -> pl.DataFrame:
        return pl.from_arrow(self.query_arrow(sql, params))  # zero-copy


# Today:
pg = ArrowGateway("postgres", "postgresql://app@db/warehouse")
df = pg.query_polars("SELECT region, sum(amount) AS rev FROM orders GROUP BY region")

# Next quarter — same downstream code, new backend:
sf = ArrowGateway("snowflake", "user:pw@acct/ANALYTICS/PUBLIC?warehouse=WH")
df2 = sf.query_polars("SELECT region, sum(amount) AS rev FROM orders GROUP BY region")

Step-by-step trace.

Layer	Role in the gateway	Swappable?
Driver manager	importlib.import_module loads the driver	yes (engine name)
AdbcDatabase config	connect(target, **db_kwargs)	yes (target)
AdbcConnection	self.connect() context manager	per call / poolable
AdbcStatement	cur.execute(sql, params)	per query
Arrow stream	fetch_arrow_table() → Polars	never changes

1. The gateway depends only on the ADBC DBAPI surface; the concrete driver is chosen by the engine key, so "which database" is data, not a code branch.
2. The driver manager (via import_module) loads the correct shared library — C for Postgres, Go for Snowflake/Flight SQL — behind one uniform API, so the caller cannot tell the languages apart.
3. connect() yields an AdbcConnection; this is the natural pooling boundary — a real deployment caches these per target rather than opening one per query.
4. query_arrow executes on an AdbcStatement and returns the Arrow stream materialised as a pyarrow.Table; query_polars wraps it zero-copy.
5. Adding Snowflake or Flight SQL is a new ArrowGateway(engine, target) construction — the query_polars consumers, the SQL, and the Polars pipeline are untouched because they all sit above the Arrow seam.

Output:

Requirement	How the design meets it
Postgres today	ArrowGateway("postgres", …)
Snowflake + Flight SQL later	new engine key, same API
Pool location	around AdbcConnection in connect()
Downstream stability	consumers see only pyarrow.Table/Polars
Code forks per backend	0

Why this works — concept by concept:

• Driver manager seam — loading drivers by name turns the choice of database into configuration, so the gateway has exactly one place that knows about backends and everything above it is backend-agnostic.
• Handle hierarchy — mapping AdbcDatabase/AdbcConnection/AdbcStatement onto config/pool/query tells you precisely where to cache: share the database, pool the connection, reuse the statement.
• Arrow result seam — because every backend returns an ArrowArrayStream, the consumer contract is "you get Arrow," which is stable across Postgres, Snowflake, and Flight SQL alike.
• Zero-copy to Polars — pl.from_arrow adopts the Arrow buffers by pointer, so the gateway adds no serialization tax between the driver and the compute engine.
• Cost — the abstraction costs one dictionary lookup and one module import per engine; it buys O(1) cost to add a backend and O(0) changes downstream, which is the entire reason the three-layer model exists.

Design
Topic — design
Data-access layer design problems

Practice →

Data Processing Topic — data-processing Arrow record-batch streaming problems

Practice →

---

3. ADBC drivers in practice — Postgres, SQLite, Snowflake, Flight SQL

One DBAPI 2.0 surface, many backends — the shipped drivers and where the Flight SQL driver fits

The mental model in one line: the ADBC ecosystem ships production drivers for PostgreSQL, SQLite, Snowflake, DuckDB, BigQuery, and Flight SQL, all presenting the same dbapi 2.0 (PEP 249) surface with Arrow extensions, so you learn connect / cursor / execute / fetch_arrow_table once and reuse it against every backend — and the flight sql adbc driver is special because it speaks a wire protocol (Arrow Flight SQL over gRPC) rather than a native database protocol, which lets one driver talk to any server that implements Flight SQL. Learn the surface, swap the import.

The shipped drivers.

• PostgreSQL (adbc_driver_postgresql). A C/C++ driver over libpq. Postgres is a row store, so results are assembled into Arrow by the driver — you still win the clean API and Arrow output, and bulk COPY-based ingest.
• SQLite (adbc_driver_sqlite). A C driver over the SQLite C API; ideal for tests, local files, and :memory: fixtures. The fastest way to prototype ADBC code with zero server.
• Snowflake (adbc_driver_snowflake). A Go driver using Snowflake's Arrow result format natively — this is where the transpose-tax win is largest, because Snowflake already hands back Arrow batches.
• Flight SQL (adbc_driver_flightsql). A Go driver speaking Arrow Flight SQL over gRPC; it targets any Flight SQL server (Dremio, InfluxDB 3.x, Ceramic-style services, DataFusion-based engines) without a database-specific driver.
• Others. DuckDB ships an ADBC entry point, BigQuery has an ADBC driver, and the driver roster grows because the API is an Apache Arrow subproject with a stable ABI.

The DBAPI 2.0 compatibility layer.

• PEP 249 conformance. Each driver's .dbapi submodule exposes connect(), Connection, Cursor, execute, executemany, fetchone/fetchmany/fetchall, commit/rollback — so existing DBAPI-shaped code and tools recognise it.
• Arrow extensions on top. Beyond PEP 249, cursors add fetch_arrow_table(), fetch_record_batch() (a streaming RecordBatchReader), fetchallarrow(), and connections add adbc_ingest(), adbc_get_table_schema(), adbc_get_objects().
• Why this matters for adoption. Libraries that accept a "DBAPI connection" — pandas' read_sql, SQLAlchemy in some modes, Polars' read_database — accept an ADBC connection, so you can drop it in and then switch hot paths to the Arrow calls.

Flight SQL: the driver vs the protocol.

• Flight SQL is a wire protocol. It defines how a client and server exchange SQL requests and Arrow results over Arrow Flight (gRPC + Arrow IPC). It is a server-side thing a database implements.
• The Flight SQL ADBC driver is a client. It is one ADBC driver that speaks that protocol, so any Flight-SQL-enabled server is reachable through the same ADBC API you use for Postgres.
• Why the pairing is powerful. A vendor that implements Flight SQL server-side instantly gets ADBC, JDBC (via a Flight-SQL JDBC driver), and columnar transport "for free" — one protocol, many clients. ADBC-over-Flight-SQL is the cleanest columnar remote path in the stack.

What interviewers listen for.

• Do you distinguish "the Flight SQL driver (client)" from "Flight SQL the protocol (server)"? — senior signal.
• Do you note that Snowflake gets the biggest transpose-tax win because its results are already Arrow? — senior signal.
• Do you say the drivers share a dbapi 2.0 surface with Arrow extensions, so code is portable? — required answer.
• Do you know Postgres/SQLite drivers are C, Snowflake/Flight SQL are Go, behind one ABI? — senior signal.
• Do you mention that an ADBC connection is a drop-in DBAPI connection for tools like pandas.read_sql? — required answer.

Worked example — identical DBAPI code across four backends

Detailed explanation. The strongest proof that ADBC drivers share a surface is to run byte-identical cursor code against four different databases, changing only the connect line. This is the portability that ODBC promised for rows and ADBC delivers for columns.

• SQLite — a local file/memory fixture.
• Postgres — a URI connection to a row store.
• Snowflake — an account/warehouse connection to a columnar warehouse.
• Flight SQL — a gRPC endpoint to any Flight-SQL server.

Question. Show one fetch_top_regions(conn) function that works unchanged across all four drivers, and the four connect lines that feed it.

Input.

Backend	import	connect argument
SQLite	adbc_driver_sqlite.dbapi	":memory:"
Postgres	adbc_driver_postgresql.dbapi	postgresql://app@db/warehouse
Snowflake	adbc_driver_snowflake.dbapi	user:pw@acct/ANALYTICS/PUBLIC?warehouse=WH
Flight SQL	adbc_driver_flightsql.dbapi	grpc+tls://sql.example.com:443

Code.

import pyarrow as pa

# Backend-agnostic: depends only on the ADBC DBAPI + Arrow surface.
def fetch_top_regions(conn, n: int = 5) -> pa.Table:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT region, SUM(amount) AS revenue "
            "FROM fact_sales GROUP BY region "
            "ORDER BY revenue DESC LIMIT ?",   # placeholder style varies per DB
            (n,),
        )
        return cur.fetch_arrow_table()

# --- SQLite (placeholder: ?) ---
import adbc_driver_sqlite.dbapi as sqlite
with sqlite.connect(":memory:") as c:
    bootstrap_sqlite(c)                 # create + seed fact_sales for the demo
    print(fetch_top_regions(c))

# --- PostgreSQL (placeholder: $1) ---
import adbc_driver_postgresql.dbapi as postgres
with postgres.connect("postgresql://app@db/warehouse") as c:
    print(fetch_top_regions_pg(c))     # same body, $1 instead of ?

# --- Snowflake (placeholder: ?) ---
import adbc_driver_snowflake.dbapi as snowflake
with snowflake.connect("user:pw@acct/ANALYTICS/PUBLIC?warehouse=WH") as c:
    print(fetch_top_regions(c))

# --- Flight SQL (placeholder: ?) ---
import adbc_driver_flightsql.dbapi as flight_sql
from adbc_driver_flightsql import DatabaseOptions
with flight_sql.connect(
    "grpc+tls://sql.example.com:443",
    db_kwargs={DatabaseOptions.AUTHORIZATION_HEADER.value: "Bearer <token>"},
) as c:
    print(fetch_top_regions(c))

Step-by-step explanation.

1. fetch_top_regions touches only conn.cursor(), cur.execute(), and cur.fetch_arrow_table() — all defined by the ADBC DBAPI contract, so its body is genuinely backend-independent.
2. The one portability wrinkle is placeholder syntax: it follows the database's native convention — ? for SQLite/Snowflake/Flight SQL, $1 for PostgreSQL — because ADBC binds parameters through the native driver rather than rewriting SQL.
3. Each connect line differs only in the driver import and the connection-string format; the Flight SQL case additionally passes a db_kwargs auth header because it is a remote gRPC endpoint.
4. Every backend returns a pyarrow.Table from fetch_arrow_table(), so whatever consumes the result — printing here, Polars/pandas in production — is written once.
5. The Snowflake and Flight SQL calls get the largest speedups because their servers emit Arrow natively; SQLite and Postgres return correct Arrow too, assembled by the driver from row data.

Output.

Backend	Placeholder	Result type	Arrow origin
SQLite	?	pyarrow.Table	driver-assembled
PostgreSQL	$1	pyarrow.Table	driver-assembled
Snowflake	?	pyarrow.Table	server-native Arrow
Flight SQL	?	pyarrow.Table	server-native Arrow

Rule of thumb. Write your query functions against cursor + fetch_arrow_table, and isolate the two things that legitimately vary per backend — the connect string and the parameter placeholder style — at the edges. Everything else is portable, which is the whole reason to standardise on the dbapi 2.0 surface.

Worked example — an ADBC connection as a drop-in for read_database

Detailed explanation. Because an ADBC connection satisfies the DBAPI contract, high-level libraries accept it directly — and Polars specifically fast-paths ADBC connections through Arrow, so you get columnar transport without calling the Arrow methods yourself. This is the gentlest possible migration: hand your existing tools an ADBC connection.

• pandas. pd.read_sql(sql, adbc_conn) works (row path), but cur.fetch_arrow_table().to_pandas() is the fast path.
• Polars. pl.read_database(sql, adbc_conn) detects ADBC and pulls Arrow batches — columnar end to end with a one-line call.
• The point. You can adopt ADBC without rewriting your data layer on day one, then move hot queries to explicit Arrow calls.

Question. Load a query into Polars two ways — via read_database on an ADBC connection, and via explicit fetch_arrow_table — and explain why both stay columnar.

Input.

Path	Call	Transport
High-level	pl.read_database(sql, conn)	Arrow (ADBC fast path)
Explicit	pl.from_arrow(cur.fetch_arrow_table())	Arrow (zero-copy)

Code.

import polars as pl
import adbc_driver_postgresql.dbapi as postgres

SQL = "SELECT region, amount, ts FROM fact_sales WHERE ts >= '2026-01-01'"

with postgres.connect("postgresql://app@db/warehouse") as conn:
    # Path A — Polars talks to the ADBC connection directly and pulls Arrow.
    df_a = pl.read_database(query=SQL, connection=conn)

    # Path B — explicit Arrow fetch, then zero-copy adopt into Polars.
    with conn.cursor() as cur:
        cur.execute(SQL)
        df_b = pl.from_arrow(cur.fetch_arrow_table())

assert df_a.schema == df_b.schema

Step-by-step explanation.

1. pl.read_database(query=SQL, connection=conn) inspects the connection, recognises it as ADBC, and requests Arrow batches instead of walking a row cursor — so even the high-level call is columnar.
2. Polars builds its columns directly from the Arrow batches; there is no row-tuple intermediate and no per-cell boxing, unlike the same call against a pyodbc connection.
3. Path B is explicit: cur.fetch_arrow_table() returns a pyarrow.Table, and pl.from_arrow adopts its buffers by pointer — a genuine zero-copy handoff.
4. Both paths end in an identical Polars schema and data because both moved Arrow buffers; the difference is only how much you spelled out.
5. This is the practical migration ladder: swap pyodbc/psycopg2 for the ADBC driver, keep your read_database calls (instant columnar win), then move the hottest queries to explicit fetch_arrow_table/streaming for maximum control.

Output.

Path	Lines of code	Copies	Result
read_database	1	0 (Arrow fast path)	Polars DataFrame
explicit Arrow	3	0 (zero-copy)	Polars DataFrame
schemas equal	—	—	yes

Rule of thumb. Start migrations by handing your existing read_database/read_sql calls an ADBC connection — Polars will already go columnar. Then graduate the hot paths to explicit fetch_arrow_table or fetch_record_batch when you want streaming or want to keep the Arrow table around for multiple consumers.

Common beginner mistakes

• Using %s placeholders everywhere. ADBC binds through the native driver, so placeholders follow the database: $1 for Postgres, ? for SQLite/Snowflake/Flight SQL. Copy-pasting %s from psycopg2 will error.
• Expecting one driver package to cover all databases. Each backend is a separate pip package (adbc-driver-postgresql, adbc-driver-snowflake, …). Install the ones you need plus adbc-driver-manager.
• Pointing the Flight SQL driver at a native protocol. The Flight SQL driver only speaks Flight SQL; it cannot talk to a plain Postgres port. Use the Postgres driver for Postgres and the Flight SQL driver only for Flight-SQL servers.
• Ignoring db_kwargs auth options. Remote drivers (Snowflake, Flight SQL) take auth and session options via db_kwargs/typed option enums, not by stuffing everything into the URI. Read the driver's option list.

SQL interview question on driver portability

A senior interviewer might ask: "You maintain a reporting library that today runs only against Snowflake via the Python connector. Product wants the same reports to run against a Dremio (Flight SQL) endpoint and a local SQLite fixture for tests. Show how ADBC lets you keep one query layer and what genuinely has to vary per backend."

Solution Using one DBAPI query layer over three ADBC drivers

# reporting/gateway.py — one query layer, three ADBC backends.
import importlib
import pyarrow as pa

# Per-backend: driver module + native placeholder token.
_BACKENDS = {
    "snowflake": ("adbc_driver_snowflake.dbapi", "?"),
    "flightsql": ("adbc_driver_flightsql.dbapi", "?"),
    "sqlite":    ("adbc_driver_sqlite.dbapi",    "?"),
}

class Reports:
    def __init__(self, backend: str, target: str, **db_kwargs):
        mod, self.ph = _BACKENDS[backend]
        self._dbapi = importlib.import_module(mod)
        self._target, self._db_kwargs = target, db_kwargs

    def _q(self, sql: str, params=()) -> pa.Table:
        with self._dbapi.connect(self._target, **self._db_kwargs) as conn:
            with conn.cursor() as cur:
                cur.execute(sql, params)
                return cur.fetch_arrow_table()

    def revenue_by_region(self, since: str, top: int) -> pa.Table:
        # Placeholder token is injected so the same SQL string works everywhere.
        sql = (
            f"SELECT region, SUM(amount) AS revenue FROM fact_sales "
            f"WHERE ts >= {self.ph} GROUP BY region "
            f"ORDER BY revenue DESC LIMIT {self.ph}"
        )
        return self._q(sql, (since, top))


# Production Snowflake:
Reports("snowflake", "user:pw@acct/ANALYTICS/PUBLIC?warehouse=WH") \
    .revenue_by_region("2026-01-01", 10)

# Dremio / Flight SQL:
from adbc_driver_flightsql import DatabaseOptions
Reports("flightsql", "grpc+tls://dremio.example.com:443",
        db_kwargs={DatabaseOptions.AUTHORIZATION_HEADER.value: "Bearer <t>"}) \
    .revenue_by_region("2026-01-01", 10)

# Local SQLite test fixture:
Reports("sqlite", ":memory:").revenue_by_region("2026-01-01", 10)

Step-by-step trace.

Concern	Varies per backend?	Where it lives
Driver module	yes	_BACKENDS map
Connection string	yes	target / db_kwargs
Placeholder token	yes	self.ph, injected into SQL
Query text (shape)	no	one revenue_by_region
Fetch + result type	no	fetch_arrow_table → Arrow

1. _BACKENDS centralises the only two truly backend-specific facts: which driver to load and which placeholder token the database uses.
2. Reports.__init__ loads the chosen driver via the manager (import_module) and stores the placeholder so query methods can build portable SQL.
3. revenue_by_region writes the SQL once, injecting self.ph so the identical string binds correctly whether the backend wants ? (all three here) — and if Postgres joined, only the token map changes to $1-style generation.
4. _q runs the statement and returns Arrow; every report method reuses it, so the fetch path is written once.
5. The three call sites differ only in construction — backend name, target, optional auth — while the report logic, SQL shape, and Arrow-consuming downstream are shared. Tests run against :memory: SQLite with the same code that hits Snowflake in production.

Output:

Backend	Construction	Report code reused
Snowflake (prod)	account/warehouse URI	yes
Flight SQL (Dremio)	gRPC URI + auth header	yes
SQLite (tests)	":memory:"	yes
Query layer forks	—	0

Why this works — concept by concept:

• DBAPI 2.0 surface — because every driver honours PEP 249 plus the Arrow extensions, one query method built on cursor/execute/fetch_arrow_table is valid against all of them.
• Native parameter binding — ADBC binds parameters through the driver, so the only SQL-level variance is the placeholder token, which the gateway injects rather than hard-coding.
• Flight SQL client driver — reaching Dremio needs no Dremio-specific library; the Flight SQL driver speaks the protocol, so a new columnar server is just another connect target.
• SQLite as a fixture — a zero-server :memory: backend gives fast, hermetic tests that exercise the exact same code path as production, closing the "tested against a mock, broke against the real driver" gap.
• Cost — the abstraction is a dict lookup and a token substitution per query; it buys a single reporting library that runs against a warehouse, a Flight SQL engine, and a test fixture with zero query forks — the payoff the shared driver surface exists to give.

SQL
Topic — sql
SQL portability and parameter-binding problems

Practice →

ETL Topic — etl ETL multi-source extraction problems

Practice →

---

4. Using ADBC from Python — fetch_arrow_table, ingest, transactions

connect → execute → fetch_arrow_table / stream → adbc_ingest → commit — the everyday ADBC Python workflow

The mental model in one line: day-to-day adbc in Python is a small, memorable surface — connect(...) for a session, cursor.execute(sql, params) with native placeholders, fetch_arrow_table() for a whole columnar result or fetch_record_batch() to stream one that is bigger than RAM, adbc_ingest(table, mode=...) to bulk-load an Arrow table back into the database, and commit()/rollback() because autocommit is off by default — and every result is Arrow, so the handoff to Polars/pandas/Parquet is zero-copy or one pass. Five calls carry almost every pipeline.

Connecting and executing.

• connect. driver.dbapi.connect(target, **db_kwargs) returns a DBAPI Connection. Use it as a context manager so the session and handles close deterministically.
• cursor.execute(sql, params). Runs a statement; params is a sequence bound via the native placeholder style ($1 Postgres, ? SQLite/Snowflake/Flight SQL). executemany(sql, rows) binds many parameter sets.
• Prepared reuse. Keep the cursor and call execute repeatedly with different params to reuse the prepared statement — the DBAPI convenience over an AdbcStatement.

Fetching results — whole or streamed.

• fetch_arrow_table(). Drains the result into a pyarrow.Table. Best when the result fits in memory and you want a reusable columnar object.
• fetch_record_batch(). Returns a pyarrow.RecordBatchReader you iterate batch-by-batch — the memory-safe path for results larger than RAM.
• DBAPI fallbacks. fetchone/fetchmany/fetchall still work for row-shaped needs, and fetch_df() returns pandas directly — but the Arrow calls are the point.

Bulk-loading with adbc_ingest.

• What it does. cursor.adbc_ingest(table_name, arrow_obj, mode=...) writes a pyarrow.Table/RecordBatchReader into the database in a bulk path (e.g. Postgres COPY), far faster than row-by-row INSERT.
• The modes. create (fail if exists), append (must exist), replace (drop + recreate), create_append (create if missing, then append). Pick per idempotency needs.
• Why it is fast. It pushes Arrow columns into the database's native bulk-load channel, avoiding the executemany per-row round trips that dominate naive loaders.

Transactions.

• Autocommit is off by default. Per DBAPI, an ADBC connection opens a transaction implicitly; you must conn.commit() to persist, conn.rollback() to discard.
• Toggling autocommit. For fire-and-forget DDL or single statements, conn.adbc_connection.set_autocommit(True) (driver-dependent) commits each statement.
• Scope. Wrap a multi-statement unit (e.g. adbc_ingest + a metadata update) in one transaction so it commits atomically or not at all.

What interviewers listen for.

• Do you reach for fetch_record_batch for larger-than-RAM results instead of fetch_arrow_table? — senior signal.
• Do you know autocommit is off by default and that you must commit()? — required answer.
• Do you use adbc_ingest for bulk load rather than executemany INSERT loops? — senior signal.
• Do you keep data Arrow until the last moment and convert once at the edge? — required answer.
• Do you name the native placeholder styles correctly per driver? — senior signal.

Worked example — stream a larger-than-RAM result and write Parquet

Detailed explanation. The wrong way to handle a 200 GB result is fetch_arrow_table() — it buffers everything. The right way is fetch_record_batch(), which yields a RecordBatchReader you can pump straight into a Parquet writer, keeping only one batch in memory at a time.

• The problem. Result is far larger than RAM; a full materialisation OOMs.
• The tool. fetch_record_batch() → pyarrow.RecordBatchReader.
• The sink. pyarrow.parquet.ParquetWriter, fed batch by batch.

Question. Export a multi-hundred-GB query result to partitioned Parquet using constant memory.

Input.

Parameter	Value
Result size	~200 GB (won't fit in RAM)
Fetch method	fetch_record_batch()
Batch size	driver default (~tens of MB)
Sink	ParquetWriter (streaming)

Code.

import adbc_driver_postgresql.dbapi as postgres
import pyarrow as pa
import pyarrow.parquet as pq

SQL = "SELECT id, region, amount, ts FROM fact_sales WHERE ts >= $1"

with postgres.connect("postgresql://app@db/warehouse") as conn:
    with conn.cursor() as cur:
        cur.execute(SQL, ("2020-01-01",))
        reader: pa.RecordBatchReader = cur.fetch_record_batch()  # streaming

        writer = None
        rows = 0
        for batch in reader:                 # one batch in memory at a time
            if writer is None:               # open with the stream's schema
                writer = pq.ParquetWriter("sales.parquet", reader.schema,
                                          compression="zstd")
            writer.write_batch(batch)
            rows += batch.num_rows
        if writer is not None:
            writer.close()

print(f"exported {rows} rows at constant memory")

Step-by-step explanation.

1. cur.fetch_record_batch() returns a RecordBatchReader that pulls from the driver lazily; nothing is materialised yet, so memory stays flat regardless of the 200 GB total.
2. The for batch in reader loop draws one Arrow RecordBatch at a time — tens of MB — so peak memory is one batch plus the Parquet writer's buffers, not the whole result.
3. The ParquetWriter is opened lazily with reader.schema on the first batch, guaranteeing the file schema matches the Arrow stream exactly, including nullability and types.
4. writer.write_batch(batch) appends the Arrow batch directly to Parquet — columnar in, columnar out, no transpose and no row intermediate anywhere in the path.
5. When the stream is exhausted the writer is closed, flushing the footer. The whole 200 GB export ran in constant memory because both the ADBC source and the Parquet sink are batch-streaming and Arrow-native.

Output.

Metric	Value
Peak memory	~one batch (tens of MB)
Result size handled	~200 GB
Transposes	0 (Arrow throughout)
Sink	streaming Parquet (zstd)
Failure mode avoided	OOM from full materialisation

Rule of thumb. If a result might exceed RAM, never call fetch_arrow_table(); call fetch_record_batch() and stream batches into your sink. The Arrow RecordBatchReader composes with Parquet writers, Polars' streaming engine, and dataset writers — all batch-native, all constant-memory.

Worked example — bulk-load an Arrow table with adbc_ingest

Detailed explanation. The classic slow loader is an executemany INSERT loop — one round trip per batch of rows, plus per-row parameter binding. adbc_ingest instead pushes an Arrow table through the database's bulk channel (Postgres COPY), which is often an order of magnitude faster and stays columnar until the wire.

• The anti-pattern. cursor.executemany("INSERT ...", rows) — row-oriented, chatty.
• The tool. cursor.adbc_ingest("target", arrow_table, mode="create_append").
• The atomicity. Wrap ingest + any bookkeeping in one transaction and commit().

Question. Load a computed pyarrow.Table of daily aggregates into Postgres idempotently and atomically.

Input.

Parameter	Value
Source	pyarrow.Table (in-memory aggregates)
Target table	daily_region_revenue
Mode	create_append (create if missing, then append)
Atomicity	ingest + watermark update in one txn

Code.

import adbc_driver_postgresql.dbapi as postgres
import pyarrow as pa

aggregates = pa.table({
    "day":     pa.array(["2026-08-01", "2026-08-02"]),
    "region":  pa.array(["EMEA", "EMEA"]),
    "revenue": pa.array([148230.55, 151002.10]),
})

with postgres.connect("postgresql://app@db/warehouse") as conn:
    with conn.cursor() as cur:
        # Bulk path (Postgres COPY under the hood), not row-by-row INSERT.
        n = cur.adbc_ingest("daily_region_revenue", aggregates,
                            mode="create_append")
        # Same transaction: advance a load watermark so it is all-or-nothing.
        cur.execute(
            "INSERT INTO load_watermarks(table_name, loaded_through) "
            "VALUES ($1, $2) "
            "ON CONFLICT (table_name) DO UPDATE SET loaded_through = EXCLUDED.loaded_through",
            ("daily_region_revenue", "2026-08-02"),
        )
    conn.commit()          # autocommit is OFF; nothing persists until here

print(f"ingested {n} rows")

Step-by-step explanation.

1. aggregates is a pyarrow.Table produced upstream (from a fetch_arrow_table + Polars aggregation, say); it stays columnar right up to the ingest call.
2. cur.adbc_ingest("daily_region_revenue", aggregates, mode="create_append") creates the table if it does not exist, then appends the rows through Postgres COPY — one bulk transfer, not two INSERT round trips per row.
3. mode="create_append" makes the load idempotent-friendly for first-run/steady-state: the table is created once, appended thereafter, so the same code works on an empty and a populated warehouse.
4. The watermark INSERT ... ON CONFLICT runs in the same transaction, so "data loaded" and "watermark advanced" commit together — a crash between them cannot leave the watermark ahead of the data.
5. conn.commit() is mandatory because autocommit is off; until it runs, neither the ingested rows nor the watermark are visible. A raised exception before commit rolls back both, preserving atomicity.

Output.

Aspect	executemany INSERT	adbc_ingest
Transport	row-by-row	bulk COPY, columnar
Round trips	many	one
Typical speed	baseline	~5–10× faster
Atomic with watermark	possible	yes (one txn)
Data shape	rows	Arrow to the wire

Rule of thumb. For any load bigger than a handful of rows, use adbc_ingest with the mode that matches your idempotency story (create_append for "make it exist then add"), and put the ingest plus its bookkeeping in one transaction with an explicit commit(). Row-by-row executemany is for tiny writes only.

Common beginner mistakes

• Forgetting to commit(). Autocommit is off; a script that ingests and exits without commit() silently persists nothing. This is the single most common ADBC surprise.
• Calling fetch_arrow_table() on huge results. It buffers the entire result. Use fetch_record_batch() and stream when the result may exceed RAM.
• Loading with executemany INSERT. It works but is slow and chatty; adbc_ingest uses the bulk channel and stays columnar. Reach for it by default.
• Converting to pandas too early. Every early to_pandas() throws away Arrow's zero-copy advantages. Keep the pyarrow.Table/Polars frame and convert once, at the last consumer that truly needs pandas.

Data-processing interview question on a memory-safe export

A senior interviewer might ask: "A nightly job reads a 120 GB Snowflake result and today OOMs because it calls fetch_arrow_table().to_pandas(). Rewrite the read-and-write so it runs in constant memory, keeps the data columnar, and writes compressed Parquet — and explain the transaction/commit story for the small bookkeeping table it also updates."

Solution Using streamed record batches into Parquet with a committed watermark

import adbc_driver_snowflake.dbapi as snowflake
import adbc_driver_postgresql.dbapi as postgres
import pyarrow as pa
import pyarrow.parquet as pq

READ_SQL = "SELECT id, region, amount, ts FROM fact_sales WHERE ts >= ?"

def export_constant_memory(sf_uri: str, pg_uri: str, since: str, path: str) -> int:
    rows = 0
    # 1) Stream the columnar result from Snowflake — never fully materialised.
    with snowflake.connect(sf_uri) as sf:
        with sf.cursor() as cur:
            cur.execute(READ_SQL, (since,))
            reader = cur.fetch_record_batch()          # RecordBatchReader
            writer = None
            for batch in reader:                       # one batch in memory
                if writer is None:
                    writer = pq.ParquetWriter(path, reader.schema,
                                              compression="zstd")
                writer.write_batch(batch)
                rows += batch.num_rows
            if writer:
                writer.close()

    # 2) Record the load atomically in a Postgres bookkeeping table.
    with postgres.connect(pg_uri) as pg:
        with pg.cursor() as c:
            c.execute(
                "INSERT INTO exports(path, row_count, loaded_through) "
                "VALUES ($1, $2, $3)",
                (path, rows, since),
            )
        pg.commit()          # autocommit OFF — persist the bookkeeping row
    return rows

Step-by-step trace.

Step	Memory held	Why
fetch_record_batch()	0 (lazy)	reader pulls on demand
loop for batch in reader	1 batch	stream, not buffer
ParquetWriter.write_batch	1 batch + footer	columnar sink
Snowflake result total	~120 GB	never fully in RAM
Postgres bookkeeping	1 row	separate txn, committed

1. The OOM came from fetch_arrow_table().to_pandas() buffering all 120 GB and then transposing to pandas. Swapping to fetch_record_batch() makes the source lazy — the fix's root cause.
2. Each iteration holds exactly one Arrow RecordBatch; peak memory is one batch plus the Parquet writer's buffers, independent of the 120 GB total.
3. The ParquetWriter opens with reader.schema, so the file's columns and types mirror the Snowflake result precisely, and write_batch appends columnar data with no row detour.
4. Snowflake's server-native Arrow means the batches arrive already columnar; the entire read→write path is transpose-free.
5. The bookkeeping INSERT runs on a separate Postgres connection and is only durable after pg.commit() — autocommit is off, so the explicit commit is what makes "this export happened" a fact.

Output:

Metric	Before	After
Peak memory	~120 GB (OOM)	~one batch
Data shape	Arrow→pandas (transpose)	Arrow throughout
Output	(crashed)	zstd Parquet
Bookkeeping durability	n/a	committed row
Result size ceiling	RAM-bound	unbounded (streamed)

Why this works — concept by concept:

• fetch_record_batch streaming — returning a RecordBatchReader instead of a full table makes the source pull-based, so memory is bounded by one batch and the result size ceiling is removed.
• Server-native Arrow (Snowflake) — because Snowflake emits Arrow, every batch is columnar on arrival; there is no transpose to pay on the read side.
• Streaming Parquet sink — ParquetWriter.write_batch accepts Arrow batches directly, so the write side is columnar too and composes with the streaming reader for constant memory.
• Explicit commit for bookkeeping — autocommit is off, so the export-record row is durable only after commit(); wrapping it separately keeps the heavy export and the tiny bookkeeping cleanly staged.
• Cost — memory drops from O(result) to O(batch), CPU drops by deleting the Arrow→pandas transpose entirely, and the job's result-size ceiling goes from "fits in RAM" to "fits on disk" — the exact properties a nightly bulk export needs.

Data Processing
Topic — data-processing
Streaming and constant-memory processing problems

Practice →

ETL Topic — etl Bulk-load and ingest ETL problems

Practice →

---

5. ADBC vs ODBC/JDBC vs Flight SQL, migration and interview

Two client APIs and one wire protocol — pick ADBC for columnar, keep ODBC/JDBC for the long tail, ride Flight SQL for remote

The mental model in one line: adbc and ODBC/JDBC are all client APIs your application links against, while Arrow Flight SQL is a wire protocol a server implements — so the real comparison is ADBC (columnar, Arrow-native) versus ODBC/JDBC (row-oriented, universal-but-transposing) for the client, with Flight SQL sitting underneath as the transport ADBC can ride to reach a remote columnar server. You do not choose "ADBC or Flight SQL"; you choose ADBC as your API and, optionally, Flight SQL as the wire it travels on.

API vs protocol — the axis people conflate.

• ADBC — client API, columnar. An abstract API returning Arrow streams. It is what your code calls. It can be backed by a native driver (Postgres) or by a wire protocol (Flight SQL).
• ODBC / JDBC — client APIs, row-oriented. Also what your code calls, but their result model is rows, so columnar sources pay the transpose tax. Their strength is a two-decade driver ecosystem covering nearly every database on earth.
• Flight SQL — wire protocol. How a client and server exchange SQL and Arrow over gRPC. It is a server capability; clients reach it via the Flight SQL ADBC driver or a Flight-SQL JDBC driver.
• How they compose. ADBC-over-Flight-SQL is the clean columnar remote path; ODBC-over-anything transposes; Flight SQL without an ADBC/JDBC client is just a protocol with no ergonomics.

When ADBC wins.

• Columnar source + columnar consumer. Snowflake/DuckDB/ClickHouse/BigQuery → pandas/Polars/Arrow: the transpose tax is real money and ADBC deletes it.
• Large result movement. Bulk exports, feature pipelines, warehouse-to-lake copies — Arrow streaming plus adbc_ingest beat row APIs on CPU and memory.
• Polyglot backends behind one API. You want one data-access layer over Postgres, Snowflake, and a Flight SQL engine.

When ODBC/JDBC still wins.

• The long tail of databases. A niche or legacy database with a mature ODBC/JDBC driver but no ADBC driver — ODBC's ecoystem breadth is unmatched.
• Row-shaped OLTP hot paths. Fetching a single row by primary key, where there is no columnar advantage and existing ODBC/JDBC code is battle-tested.
• Deep existing integration. A BI tool or framework that only speaks ODBC/JDBC; rip-and-replace is not worth it until a columnar bottleneck justifies it.

The migration playbook (pyodbc/turbodbc/JDBC → ADBC).

• Swap the driver, keep the SQL. Replace the connect import; the SQL text is unchanged (adjust placeholder tokens per driver).
• Move the hot fetch to Arrow. Change pd.read_sql(...)/cursor.fetchall() to cursor.fetch_arrow_table() or fetch_record_batch().
• Keep data columnar downstream. Feed Polars/Arrow directly; convert to pandas only at the final edge that needs it.
• Adopt adbc_ingest for loads. Replace executemany INSERT loops with the bulk Arrow path.
• Verify and measure. Confirm row counts and schemas match, then measure the CPU/memory drop to justify the change.

What interviewers listen for.

• Do you say "ADBC and ODBC are APIs; Flight SQL is a protocol" crisply? — required answer.
• Do you note ADBC can ride on Flight SQL as its transport? — senior signal.
• Do you name when ODBC/JDBC still wins (driver long tail, OLTP point reads)? — senior signal.
• Do you describe migration as "swap driver, keep SQL, move fetch to Arrow"? — required answer.
• Do you tie the win back to the transpose tax and per-cell boxing, not vague "speed"? — senior signal.

Worked example — the three-way decision matrix

Detailed explanation. The artifact to keep in your head is a compact matrix that separates the two client APIs from the one protocol and states the deciding property for each. Build it for a team standardising its data access.

• Axis 1 — kind: client API vs wire protocol.
• Axis 2 — result model: columnar (Arrow) vs row.
• Axis 3 — reach: native driver vs gRPC remote.
• Axis 4 — ecosystem: breadth of existing drivers.

Question. Fill the matrix and give the one-line "pick this when" for ADBC, ODBC/JDBC, and Flight SQL.

Input.

Property	ADBC	ODBC / JDBC	Flight SQL
Kind	client API	client API	wire protocol
Result model	columnar (Arrow)	row	columnar (Arrow)
Transpose tax	none	yes	none
Reach	native or via Flight SQL	native drivers	gRPC remote
Ecosystem breadth	growing	largest	server-side, growing

Code.

Pick-one cheat card
===================

ADBC        -> your client API when source and/or consumer is columnar,
               or you want one API over many backends returning Arrow.
ODBC/JDBC   -> your client API when you need a database that only has an
               ODBC/JDBC driver, or for row-shaped OLTP point reads where
               there is no columnar win and the existing code is proven.
Flight SQL  -> the wire protocol a server exposes; reach it with the ADBC
               Flight SQL driver (or a Flight-SQL JDBC driver). Not an
               API you 'choose instead of' ADBC — it's what ADBC rides on
               to talk to a remote columnar engine.
Compose     -> ADBC-over-Flight-SQL = clean columnar remote path.
               ODBC-over-columnar    = transpose tax.

Step-by-step explanation.

1. The first split is kind: ADBC and ODBC/JDBC are things your code calls; Flight SQL is a thing a server speaks. Getting this axis right dissolves most "ADBC vs Flight SQL" confusion.
2. The result-model axis decides the transpose tax: ADBC and Flight SQL are columnar, ODBC/JDBC are row — so a columnar workload pays a tax only on the ODBC/JDBC row.
3. The reach axis shows ADBC's flexibility: it can use a native driver (Postgres) or ride Flight SQL to a remote engine, whereas ODBC/JDBC always use a native driver.
4. The ecosystem axis is ODBC/JDBC's remaining moat — decades of drivers for obscure databases — which is exactly why they persist for the long tail.
5. The composed reading is the senior takeaway: choose ADBC as the API for columnar work, let it ride Flight SQL when the server is remote and columnar, and keep ODBC/JDBC for databases without an ADBC driver or for row-shaped point reads.

Output.

Situation	Pick
Snowflake/DuckDB → Polars	ADBC
Remote Flight-SQL engine (Dremio)	ADBC over Flight SQL
Niche DB with only an ODBC driver	ODBC
OLTP single-row point read	ODBC/JDBC (fine as-is)
One API over mixed backends	ADBC

Rule of thumb. Say the sentence "ADBC and ODBC are APIs, Flight SQL is a wire" out loud before answering any comparison question. The matrix falls out of that distinction, and it prevents the most common junior mistake of treating a protocol and an API as interchangeable options.

Worked example — migrate a pyodbc feature loader to ADBC

Detailed explanation. Take a real, ordinary pyodbc loader — read from a warehouse, transform, write back — and convert it to ADBC one edge at a time. The SQL never changes; the fetch and the load do.

• Read. pd.read_sql → fetch_arrow_table().
• Transform. pandas → Polars (or keep pandas, but from Arrow).
• Write. executemany INSERT → adbc_ingest.
• Commit. add the explicit commit() autocommit-off requires.

Question. Show the before/after of a warehouse-to-warehouse feature loader migrated from pyodbc to ADBC and state exactly what changed.

Input.

Stage	Before (pyodbc)	After (ADBC)
Read	pd.read_sql	cur.fetch_arrow_table()
Compute	pandas	Polars (from Arrow)
Write	executemany INSERT	cur.adbc_ingest(...)
Commit	autocommit on (DSN)	explicit conn.commit()

Code.

# BEFORE — pyodbc: row cursor in, row inserts out
import pyodbc, pandas as pd

src = pyodbc.connect(SRC_DSN)
df = pd.read_sql("SELECT user_id, amount, ts FROM events", src)      # transpose
feat = df.groupby("user_id")["amount"].sum().reset_index()

dst = pyodbc.connect(DST_DSN, autocommit=True)
cur = dst.cursor()
cur.executemany("INSERT INTO features(user_id, total) VALUES (?, ?)",  # row-by-row
                list(feat.itertuples(index=False, name=None)))

# AFTER — ADBC: Arrow in, bulk ingest out
import adbc_driver_snowflake.dbapi as snowflake
import adbc_driver_postgresql.dbapi as postgres
import polars as pl

with snowflake.connect(SRC_URI) as src:
    with src.cursor() as cur:
        cur.execute("SELECT user_id, amount, ts FROM events")
        table = cur.fetch_arrow_table()                # columnar, no transpose

feat = (pl.from_arrow(table)                           # zero-copy
          .group_by("user_id")
          .agg(pl.col("amount").sum().alias("total")))

with postgres.connect(DST_URI) as dst:
    with dst.cursor() as c:
        c.adbc_ingest("features", feat.to_arrow(), mode="create_append")  # bulk
    dst.commit()                                       # autocommit OFF

Step-by-step explanation.

1. The read swaps pd.read_sql (row cursor + double transpose) for cur.fetch_arrow_table() — same SQL, but the result now arrives as a columnar pyarrow.Table.
2. The transform moves to Polars via pl.from_arrow(table), a zero-copy adoption; the aggregation runs on Arrow-backed columns with no pandas boxing.
3. The write replaces the executemany INSERT loop with c.adbc_ingest("features", feat.to_arrow(), mode="create_append"), pushing Arrow columns through Postgres COPY in one bulk transfer.
4. Because ADBC connections default to autocommit off, the explicit dst.commit() replaces the pyodbc autocommit=True DSN flag — the one behavioural change to remember.
5. Net: the SQL string is byte-identical, the transposes and per-cell boxing are gone on read, the load is bulk instead of chatty, and the only new line is the commit().

Output.

Aspect	Before	After
Read transposes	2	0
Compute engine	pandas	Polars (Arrow)
Write path	row-by-row INSERT	bulk COPY ingest
Commit	implicit (DSN flag)	explicit commit()
SQL changed	—	none

Rule of thumb. Migrate at the edges: read (fetch_arrow_table), write (adbc_ingest), and commit (commit()). The query text and business logic stay put, which keeps the diff small and the review honest — and the measured CPU/memory drop is what justifies rolling it out further.

Common beginner mistakes

• Framing it as "ADBC vs Flight SQL." They are different layers; the real client-API choice is ADBC vs ODBC/JDBC, with Flight SQL as an optional transport ADBC uses.
• Migrating everything at once. Move one pipeline, measure, then expand. A big-bang swap hides which change bought the win and complicates rollback.
• Assuming ADBC always beats ODBC. For a niche database with only an ODBC driver, or a row-shaped point read, ODBC/JDBC is the right call. ADBC's edge is columnar movement, not universality.
• Leaving early to_pandas() in place after migrating. If you swap to ADBC but immediately convert to pandas, you keep the row-shaped consumer and forfeit half the win. Push the conversion to the last edge.

Design interview question on choosing and migrating the driver layer

A senior interviewer might ask: "Your platform reads from Snowflake and a legacy database that only has an ODBC driver, and writes features back to Postgres. Design the driver strategy: which client API per source, how ADBC and ODBC coexist, where Flight SQL might enter, and the migration order that de-risks the rollout."

Solution Using ADBC for the columnar paths and ODBC only for the long-tail source

# platform/access.py — ADBC where it pays, ODBC where it must.
import adbc_driver_snowflake.dbapi as snowflake
import adbc_driver_postgresql.dbapi as postgres
import pyarrow as pa
import polars as pl
import pyodbc                      # kept ONLY for the legacy no-ADBC-driver source

def read_snowflake(uri: str, sql: str) -> pa.Table:
    with snowflake.connect(uri) as c:              # columnar: ADBC
        with c.cursor() as cur:
            cur.execute(sql)
            return cur.fetch_arrow_table()

def read_legacy_odbc(dsn: str, sql: str) -> pa.Table:
    # No ADBC driver exists for this DB, so ODBC is correct here.
    # Localise the transpose tax to this one function.
    import pandas as pd
    with pyodbc.connect(dsn) as c:
        return pa.Table.from_pandas(pd.read_sql(sql, c))

def write_features(uri: str, table_name: str, arrow_tbl: pa.Table) -> int:
    with postgres.connect(uri) as c:               # columnar load: ADBC ingest
        with c.cursor() as cur:
            n = cur.adbc_ingest(table_name, arrow_tbl, mode="create_append")
        c.commit()
        return n

# Both sources normalise to pyarrow.Table, so the pipeline is uniform:
sf   = read_snowflake(SF_URI, "SELECT user_id, amount FROM events")
leg  = read_legacy_odbc(LEGACY_DSN, "SELECT user_id, score FROM risk")
feat = (pl.from_arrow(sf).join(pl.from_arrow(leg), on="user_id", how="left")
          .group_by("user_id").agg(pl.col("amount").sum().alias("total"),
                                    pl.col("score").max().alias("risk")))
write_features(PG_URI, "features", feat.to_arrow())

Step-by-step trace.

Source / sink	API chosen	Why
Snowflake (columnar)	ADBC	server-native Arrow, no transpose
Legacy DB (ODBC-only)	ODBC → Arrow	no ADBC driver exists
Postgres (write)	ADBC adbc_ingest	bulk columnar load
Pipeline shape	Arrow / Polars	both sources normalise to Arrow
Flight SQL	future option	if legacy grows a Flight SQL server

1. Snowflake is columnar with server-native Arrow, so ADBC is the obvious client — fetch_arrow_table avoids the transpose tax entirely for the heavy source.
2. The legacy database has only an ODBC driver, so ODBC is genuinely correct there; the design localises its transpose tax inside read_legacy_odbc and immediately normalises the output to pyarrow.Table.
3. Both readers return Arrow, so the join/aggregate pipeline is uniform Polars regardless of how each source was read — the ODBC quirk does not leak upward.
4. The write uses adbc_ingest for a bulk columnar load into Postgres with the mandatory explicit commit(), keeping the load fast and the write path Arrow-native.
5. The migration order de-risks the rollout: convert the highest-volume columnar path (Snowflake read + Postgres write) to ADBC first for the biggest measured win, leave the low-volume legacy source on ODBC, and revisit it only if it later exposes a Flight SQL endpoint — at which point it becomes another ADBC target.

Output:

Decision	Outcome
Columnar source/sink	ADBC (Arrow, no transpose)
ODBC-only legacy source	ODBC, tax localised
Uniform pipeline	Arrow/Polars throughout
Migration order	heavy columnar paths first
Flight SQL	reserved as future ADBC transport

Why this works — concept by concept:

• API-per-source — choosing ADBC for columnar sources and ODBC only where no ADBC driver exists puts each workload on the right client API instead of forcing one tool everywhere.
• Tax localisation — confining the unavoidable ODBC transpose to one function that immediately emits Arrow keeps the rest of the pipeline columnar and uniform.
• Arrow as the normalisation layer — because both readers return pyarrow.Table, the join/aggregate/write code is written once and is agnostic to how each source was read.
• Bulk ingest with commit — adbc_ingest plus explicit commit() gives a fast, atomic columnar write, matching the columnar read for an end-to-end Arrow path on the paths that matter.
• Cost — the design pays ODBC's transpose tax on only the low-volume legacy source, gets the columnar win on the high-volume paths, and adds backends by function, not by rewrite — an O(1)-to-extend layer that spends effort exactly where the volume is.

Design
Topic — design
Driver-strategy and migration design problems

Practice →

SQL
Topic — sql
SQL extraction and load-pattern problems

Practice →

---

Cheat sheet — ADBC recipes

• What ADBC is. adbc (Arrow Database Connectivity) is a columnar client API whose result type is an Arrow stream. It sits beside ODBC/JDBC as a client API — not beside Flight SQL, which is a wire protocol. Mental model: "ODBC's shape, Arrow's result."
• Install matrix. pip install adbc-driver-manager pyarrow plus the backends you need: adbc-driver-postgresql, adbc-driver-sqlite, adbc-driver-snowflake, adbc-driver-flightsql, adbc-driver-bigquery. DuckDB exposes an ADBC entry point of its own.
• Connect one-liners. SQLite: adbc_driver_sqlite.dbapi.connect(":memory:"). Postgres: adbc_driver_postgresql.dbapi.connect("postgresql://u@h/db"). Snowflake: adbc_driver_snowflake.dbapi.connect("user:pw@acct/DB/SCHEMA?warehouse=WH"). Flight SQL: adbc_driver_flightsql.dbapi.connect("grpc+tls://host:443", db_kwargs={DatabaseOptions.AUTHORIZATION_HEADER.value: "Bearer <t>"}).
• Placeholder styles. PostgreSQL uses $1, $2; SQLite, Snowflake, and Flight SQL use ?. ADBC binds via the native driver, so the token follows the database — never %s.
• Fetch whole result. with conn.cursor() as cur: cur.execute(sql, params); table = cur.fetch_arrow_table() → a pyarrow.Table, columnar, reusable.
• Stream large results. reader = cur.fetch_record_batch() returns a pyarrow.RecordBatchReader; for batch in reader: sink.write_batch(batch) for constant memory. Use this whenever the result may exceed RAM.
• Zero-copy to compute. Polars: pl.from_arrow(cur.fetch_arrow_table()) or pl.read_database(sql, conn). pandas: cur.fetch_arrow_table().to_pandas(). Keep it Arrow until the last consumer.
• Bulk-load (ingest). cur.adbc_ingest("target", arrow_table, mode="create_append"); modes are create, append, replace, create_append. Uses the DB's bulk channel (Postgres COPY) — far faster than executemany INSERT.
• Transactions. Autocommit is OFF by default — call conn.commit() to persist, conn.rollback() to discard. Toggle with conn.adbc_connection.set_autocommit(True) (driver-dependent). Wrap ingest + bookkeeping in one transaction for atomicity.
• Metadata introspection. conn.adbc_get_table_schema("t") → pyarrow.Schema; conn.adbc_get_objects(...) for catalogs/schemas/tables; conn.adbc_get_info() for driver/vendor info. Uniform across drivers.
• Handle hierarchy (low level). AdbcDatabase (config, share it / pool it) → AdbcConnection (session/txn) → AdbcStatement (query) → ArrowArrayStream. Maps to ODBC SQLHENV/SQLHDBC/SQLHSTMT. The DBAPI layer is sugar over these.
• Migration recipe. Swap the driver import (SQL unchanged, fix placeholder tokens), change read_sql/fetchall → fetch_arrow_table/fetch_record_batch, keep data in Arrow/Polars downstream, replace executemany INSERT with adbc_ingest, add the explicit commit(), then measure the CPU/memory drop.
• When NOT to use ADBC. A database that has only an ODBC/JDBC driver (no ADBC driver yet), or a row-shaped OLTP point read with no columnar advantage — keep ODBC/JDBC there and localise it.

Frequently asked questions

What is ADBC in one sentence?

adbc (Arrow Database Connectivity) is a columnar-first, vendor-neutral client API for databases whose result type is an Apache Arrow stream rather than a row cursor, so a query against any conforming driver returns Arrow record batches you can hand to pandas, Polars, DataFusion, or a Parquet writer zero-copy — eliminating the "transpose tax" that ODBC and JDBC impose when a columnar database's results are forced through a row-oriented API and then rebuilt into columns by a columnar consumer. It is an Apache Arrow subproject with a stable C ABI and a three-layer design — a adbc driver manager that loads per-database drivers, the drivers themselves (PostgreSQL, SQLite, Snowflake, Flight SQL, and more), and Arrow result streams — plus a Python dbapi 2.0 layer so existing DBAPI-shaped code recognises it. The elevator pitch is "ODBC's one-API-many-drivers shape, but the result is Arrow."

ADBC vs ODBC/JDBC — when do I pick each?

Pick adbc whenever the source is columnar (Snowflake, DuckDB, ClickHouse, BigQuery, Redshift) and/or the consumer is columnar (pandas, Polars, Arrow, Parquet), because ODBC/JDBC's row model forces two transposes — columns→rows in the driver, rows→columns in the consumer — plus a Python/Java object per cell, all of which the arrow native driver deletes by staying columnar. Pick ODBC or JDBC when you need a database that only ships an ODBC/JDBC driver (their ecosystem breadth is unmatched after two decades) or for row-shaped OLTP hot paths like a single-row primary-key lookup where there is no columnar advantage and the existing code is proven. The two are both client APIs, so the choice is genuinely "columnar Arrow API" versus "universal row API," and a healthy platform often runs both — ADBC on the high-volume analytical paths, ODBC on the long-tail sources.

Is ADBC a wire protocol like Flight SQL?

No — and this is the distinction interviewers probe hardest. adbc is a client API your application code calls (like ODBC/JDBC), whereas Arrow Flight SQL is a wire protocol a database server implements to exchange SQL and Arrow results over gRPC. They are different layers and they compose rather than compete: ADBC ships a flight sql adbc driver that speaks the Flight SQL protocol, so "ADBC over Flight SQL" is the clean columnar path to a remote Flight-SQL server (Dremio, InfluxDB 3.x, DataFusion-based engines). You never choose "ADBC or Flight SQL"; you choose ADBC as your API and optionally Flight SQL as the wire it rides on. Saying "ADBC and ODBC are APIs, Flight SQL is a wire" out loud is the fastest way to sound senior on this topic.

Does ADBC work with row-oriented databases like Postgres?

Yes — ADBC has a first-class PostgreSQL driver (over libpq) and a SQLite driver, and both work perfectly against row stores. The difference is where the win comes from: against a columnar source like Snowflake, ADBC avoids a transpose the row API would have imposed, so the speedup is large; against a row store like Postgres, the driver assembles the rows into Arrow for you, so you get the clean dbapi 2.0 API, Arrow output for a zero-copy handoff to Polars/pandas, and a fast adbc_ingest bulk-load path (Postgres COPY), but not a transpose you dodged — the source was row-shaped to begin with. In short: ADBC is a strict ergonomics-and-ingest upgrade for row stores and a strict performance upgrade for columnar stores.

Is ADBC DBAPI 2.0 compatible?

Yes — each Python driver exposes a .dbapi submodule that conforms to PEP 249 (connect, Connection, Cursor, execute, executemany, fetchone/fetchmany/fetchall, commit/rollback), which is why tools that accept a "DBAPI connection" — pandas.read_sql, polars.read_database, and many ORMs in raw-connection mode — accept an ADBC connection directly. On top of the dbapi 2.0 baseline, ADBC cursors add Arrow extensions (fetch_arrow_table(), fetch_record_batch(), fetchallarrow()) and connections add adbc_ingest(), adbc_get_table_schema(), and adbc_get_objects(). The practical consequence is a gentle migration: hand your existing DBAPI-shaped code an ADBC connection for an immediate columnar win via read_database, then move the hot queries to the explicit Arrow calls. Note that autocommit is off by default, so remember the explicit commit().

Can ADBC replace pyodbc in an existing pipeline?

For columnar workloads, almost always — and the migration is deliberately small. Swap the pyodbc.connect import for the ADBC driver's dbapi.connect (the SQL text is unchanged; only adjust placeholder tokens, ? vs $1), replace pandas.read_sql/cursor.fetchall() with cursor.fetch_arrow_table() or fetch_record_batch() for streaming, keep the data in Arrow/Polars downstream instead of converting to pandas early, and replace any executemany INSERT loop with adbc_ingest. Add the explicit conn.commit() that autocommit-off requires, verify row counts and schemas match, then measure — typical results are roughly halved CPU and memory on large columnar pulls because you deleted the double transpose and the per-cell boxing. The one place to keep pyodbc is a database that has no ADBC driver yet; there, localise the ODBC call in one function that returns Arrow so the rest of the pipeline stays columnar.

Practice on PipeCode

• Drill the SQL practice library → for the result-set, parameter-binding, and extraction problems that ADBC pipelines live and die on.
• Rehearse on the data-processing practice library → for the Arrow record-batch, streaming, and constant-memory problems that make fetch_record_batch second nature.
• Sharpen the pipeline axis with the ETL practice library → for the bulk-ingest, warehouse-to-lake, and multi-source extraction patterns ADBC is built to accelerate.
• Layer in the design practice library → for the driver-strategy, migration-order, and one-API-many-backends topology questions senior interviewers open with when ADBC is on the table.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the ADBC-vs-ODBC/JDBC-vs-Flight-SQL decision matrix against real graded inputs.

Lock in ADBC muscle memory

Docs explain the API. PipeCode drills explain the decision — when the transpose tax is real money, when `fetch_record_batch` beats `fetch_arrow_table`, when `adbc_ingest` retires an executemany loop, when ODBC still deserves to stay. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face across columnar drivers and Arrow-native pipelines.

Practice data-processing problems →
Practice SQL problems →