Arrow Flight & Flight SQL: High-Speed Data Transport Beyond JDBC/ODBC

arrow flight is the answer to a bottleneck almost every senior data engineer has hit and few have named: the moment a result set stops being "a few thousand rows for a dashboard" and becomes "forty million rows a model needs," the transport — not the query engine, not the network, not the disk — becomes the wall. A SELECT that a warehouse can compute in 300 milliseconds can take forty seconds to hand across a JDBC or ODBC connection, because those protocols were designed in the 1990s to shuttle one row at a time through a cell-at-a-time cursor, transposing a columnar engine's output back into rows on the server, serializing each value into a driver-specific wire format, then transposing it back into columns on the client. That round-trip through the row-shaped middle is pure tax — CPU burned on serialization and transposition that produces zero analytical value.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "why is our BI extract slow when the query itself is fast?", or "walk me through how Arrow Flight moves data without the JDBC deserialization cost," or "what is Flight SQL and how is it different from raw Flight?" It walks through five things every senior engineer must be fluent in: why the row-oriented cursor is a serialization tax and how a columnar transport erases it, the Flight architecture — the gRPC/Protobuf control plane, the arrow ipc stream data plane, the flight rpc verbs (DoGet/DoPut/DoExchange), tickets, and parallel endpoints for distributed reads — the flight sql standard that layers a portable SQL protocol over raw Flight, how to build a real Flight service in Python with FlightServerBase, auth, and TLS, and where Flight sits in the production stack: dremio flight, Spark, ADBC as the jdbc odbc replacement, the benchmarks, and the workloads where reaching for Flight is a mistake. 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 streaming practice library →, rehearse on the ETL practice library →, and sharpen the systems axis with the design practice library →.

---

On this page

• Why JDBC/ODBC is the bottleneck and what Arrow Flight changes
• Arrow Flight architecture — gRPC, tickets, and endpoints
• Flight SQL — a standard SQL-over-Flight protocol
• Building a Flight service in Python
• Flight in the stack — Dremio, Spark, ADBC, and when not to use it
• Cheat sheet — Arrow Flight recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why JDBC/ODBC is the bottleneck and what Arrow Flight changes

The row-by-row serialization tax — where the seconds actually go

The one-sentence invariant: JDBC and ODBC are row-oriented, cell-at-a-time cursor protocols, so every large result set pays a hidden tax — the columnar engine transposes its output into rows, serializes each cell into the driver's wire format, ships it, and the client transposes it back into columns — and arrow flight removes that tax entirely by making the wire format be the Arrow columnar layout the engine already holds and the client already wants, so there is no transposition and no per-cell serialization on either end. The query time is often a rounding error next to the transport time on analytical result sets; the seconds you see in a slow BI extract or a slow pandas.read_sql are spent almost entirely in the serialize-transpose-deserialize sandwich, and no amount of "tune the query" fixes a cost that lives in the protocol.

Where the tax comes from — four costs stacked on every ODBC/JDBC fetch.

• Transposition on the server. Modern analytical engines (DuckDB, Dremio, Snowflake, ClickHouse) are columnar internally — a column is a contiguous array. The ODBC/JDBC result protocol is row-major, so the engine must walk its columns and emit (row0.col0, row0.col1, …), (row1.col0, …) — a cache-unfriendly gather that destroys the columnar locality the engine worked to build.
• Per-cell serialization. Each value is encoded into the driver's type system — a string gets a length prefix, an integer gets width-normalized, a decimal gets stringified. This is per-value branching in a hot loop over billions of cells.
• Deserialization on the client. The client driver parses the wire bytes back into language objects, then — for a pandas / Arrow / Polars consumer — transposes the rows back into columns. You paid to go row-major and paid again to undo it.
• Chatty fetch loop. Classic cursors fetch in small batches with a network round-trip per batch unless the driver's fetch size is carefully tuned; the default is frequently tiny, adding latency multipliers on high-RTT links.

What Arrow Flight changes — the columnar wire.

• The wire is Arrow. Flight sends arrow ipc stream messages — the same in-memory columnar layout Arrow uses at rest. The engine's columns go on the wire as-is; the client receives columns. No transposition, no per-cell encode/decode.
• Zero-deserialization receive. Because the received bytes already are the Arrow buffer layout, reconstructing a RecordBatch is (close to) a pointer cast over the received buffers — "zero-copy" in the sense that no per-value parsing happens. You go from network buffer to a queryable pyarrow.Table without a decode pass.
• gRPC + HTTP/2 underneath. Flight rides gRPC, so it inherits HTTP/2 multiplexing, streaming, flow control, TLS, and interceptors for free instead of reinventing a bespoke socket protocol.
• Built for parallelism. A single logical result can be split across many parallel endpoints, so the client fans out reads across servers/cores — impossible in a single-cursor JDBC connection.

The four axes interviewers actually probe.

• Throughput. On large result sets Flight is routinely an order of magnitude faster than ODBC — the published Arrow benchmarks and independent tests land in the 10–50× range depending on schema width and network. The win grows with result-set size because the per-cell tax is what you eliminate.
• Serialization cost. ODBC/JDBC = O(cells) encode + O(cells) decode + O(cells) transpose twice. Flight = O(buffers) framing. This is the load-bearing difference; everything else follows from it.
• Parallelism. JDBC = one cursor, one stream. Flight = N endpoints, N streams, fan-out client. Distributed engines exploit this to saturate a fat pipe.
• Ecosystem surface. JDBC/ODBC have thirty years of drivers for every language and BI tool. Flight is younger; flight sql + ADBC is the bridge that lets Flight speak a database-driver-shaped API so it can displace JDBC/ODBC without rewriting every client.

What interviewers listen for.

• Do you locate the cost in serialization + transposition, not "the network is slow"? — required answer.
• Do you say "the wire format is the Arrow columnar layout, so there is no deserialization pass" when asked what makes Flight fast? — senior signal.
• Do you distinguish raw Flight (bespoke producer) from Flight SQL (standard protocol) without prompting? — senior signal.
• Do you name the anti-patterns — tiny result sets, browser clients, non-Arrow consumers — rather than pitching Flight as a universal replacement? — senior signal.

Worked example — measuring the transport tax, not the query

Detailed explanation. The single most convincing artifact in a "why is our extract slow" investigation is a decomposition that separates query time from transport time. Engineers who have never measured this assume the query is the cost; the decomposition almost always shows the opposite for wide analytical result sets. Walk through instrumenting a pandas.read_sql over ODBC versus the same pull over Flight.

• Query. SELECT * FROM events WHERE day = '2026-08-01' — ~10M rows, 12 columns, mixed int/string/timestamp.
• Engine time. What the database spends producing the result set (visible in the query profile).
• Transport time. Wall-clock from "first byte requested" to "last row materialized in the client dataframe," minus engine time.
• Goal. Show that transport dominates on ODBC and collapses on Flight.

Question. Instrument both paths and attribute the wall-clock to engine versus transport.

Input.

Path	Client API	Wire format	What the client must do on receive
ODBC	pandas.read_sql via pyodbc	row-major cells	parse cells → build rows → transpose to columns
Flight	client.do_get(ticket).read_all()	Arrow IPC (columnar)	wrap received buffers as a Table

Code.

# transport_tax.py — attribute wall-clock to engine vs transport
import time
import pyarrow.flight as flight

def timed(label, fn):
    t0 = time.perf_counter()
    result = fn()
    dt = time.perf_counter() - t0
    n = result.num_rows if hasattr(result, "num_rows") else len(result)
    print(f"{label:<18} {dt:7.2f}s   {n:>10,} rows   {n/dt:>12,.0f} rows/s")
    return result

# --- Path A: ODBC via pandas (row-oriented) ---
def pull_odbc():
    import pyodbc, pandas as pd
    conn = pyodbc.connect(DSN)
    # engine time is inside execute(); most wall-clock is the fetch loop below
    return pd.read_sql("SELECT * FROM events WHERE day = '2026-08-01'", conn)

# --- Path B: Arrow Flight (columnar) ---
def pull_flight():
    client = flight.connect("grpc://warehouse.internal:8815")
    descriptor = flight.FlightDescriptor.for_command(
        b"SELECT * FROM events WHERE day = '2026-08-01'"
    )
    info = client.get_flight_info(descriptor)      # engine plans + returns tickets
    ticket = info.endpoints[0].ticket
    reader = client.do_get(ticket)                 # stream Arrow record batches
    return reader.read_all()                        # -> pyarrow.Table, no per-cell decode

df   = timed("ODBC/pandas",  pull_odbc)
tbl  = timed("Arrow Flight", pull_flight)

Step-by-step explanation.

1. The ODBC path calls pd.read_sql, which internally runs a cell-at-a-time fetch loop. The database still computes the result quickly, but the driver then serializes every cell, ships it row-major, and pandas rebuilds columns — so wall-clock is dominated by the fetch, not the WHERE.
2. The Flight path splits the operation: get_flight_info triggers planning and returns one or more tickets, and do_get(ticket) opens the record-batch stream. The engine emits its columns straight onto the wire as Arrow IPC.
3. reader.read_all() assembles the streamed RecordBatch messages into a pyarrow.Table. Crucially, there is no per-value parse — the received buffers already carry the Arrow layout, so this is buffer bookkeeping, not decoding.
4. The rows/s column is the headline metric. On a 10M-row wide result, ODBC commonly lands in the low hundreds-of-thousands of rows/s while Flight lands in the multi-millions — the ratio is the serialization tax you removed.
5. To attribute engine vs transport precisely, read the database's own query profile for engine time and subtract it from the wall-clock; the remainder is transport, and that remainder is what Flight compresses.

Output.

Path	Wall-clock	Throughput	Where the time went
ODBC/pandas	38.4 s	~260 K rows/s	~1 s engine, ~37 s serialize+transpose
Arrow Flight	2.1 s	~4.8 M rows/s	~1 s engine, ~1 s columnar stream

Rule of thumb. Before you "optimize the query," decompose the wall-clock into engine time and transport time. If the query profile says the engine finished in a second but the extract took forty, the fix is the transport, not the SQL — and a columnar transport is the fix.

Worked example — why a faster ODBC driver cannot close the gap

Detailed explanation. A common pushback in interviews and design reviews is "can't we just buy a faster ODBC driver / bump the fetch size?" The honest answer is that tuning helps at the margins but cannot remove the structural cost, because the cost is the row-major format itself, not the driver's implementation quality. Walk through what fetch-size tuning does and does not buy.

• Fetch size. Raising the ODBC array/fetch size reduces the number of network round-trips, which helps on high-latency links. It does nothing to the per-cell serialization or the double transposition.
• Driver quality. A well-written driver serializes cells more efficiently, but it is still O(cells) work in a hot loop, and it still emits rows a columnar client must re-columnarize.
• The structural floor. As long as the wire is row-major and the consumer is columnar, two transpositions and a full serialize/deserialize pass are mandatory. That floor is what Flight removes by matching the wire to both the producer's and consumer's native layout.

Question. Quantify the ceiling of ODBC fetch-size tuning versus the Flight floor for a wide result set on a high-RTT link.

Input.

Lever	Effect on round-trips	Effect on per-cell cost	Effect on transposition
Bigger ODBC fetch size	fewer (helps latency)	none	none
Better ODBC driver	none	smaller constant	none
Switch to Arrow Flight	streamed (few)	eliminated	eliminated

Code.

# odbc_tuning_ceiling.py — fetch-size tuning helps round-trips, not the tax
import pyodbc, time

def pull(fetch_size):
    conn = pyodbc.connect(DSN)
    cur = conn.cursor()
    cur.arraysize = fetch_size          # rows pulled per network round-trip
    cur.execute("SELECT * FROM events WHERE day = '2026-08-01'")
    t0, n = time.perf_counter(), 0
    while True:
        rows = cur.fetchmany(fetch_size)  # still cell-at-a-time under the hood
        if not rows:
            break
        n += len(rows)
    return n, time.perf_counter() - t0

for fs in (100, 1_000, 10_000, 50_000):
    n, dt = pull(fs)
    print(f"arraysize={fs:>6}: {n:,} rows in {dt:6.2f}s ({n/dt:,.0f} rows/s)")

# Diminishing returns: throughput climbs as round-trips fall, then plateaus at
# the serialization + transposition floor — which no arraysize can cross.

Step-by-step explanation.

1. Increasing arraysize from 100 to 1,000 to 10,000 cuts the number of network round-trips proportionally, so on a high-RTT link throughput climbs sharply at first — this is the tuning everyone reaches for.
2. Past a few thousand rows per fetch, round-trip latency stops being the bottleneck and the per-cell serialization cost takes over. Throughput plateaus and further arraysize bumps do nothing.
3. The plateau is the structural floor: the driver still serializes each cell and the client still receives rows it must re-columnarize for any Arrow/pandas consumer.
4. Flight does not have this floor because it never goes row-major. The comparison is not "slow driver vs fast driver," it is "O(cells) serialization vs O(buffers) framing."
5. The senior framing in an interview: "fetch-size tuning removes round-trips, which is a latency fix; it cannot remove the serialization tax, which is a throughput fix — and only a columnar transport removes that."

Output.

arraysize	rows/s	Bottleneck at this setting
100	~40 K	network round-trips
1,000	~180 K	round-trips + serialization
10,000	~250 K	serialization (plateau begins)
50,000	~260 K	serialization floor (no further gain)

Rule of thumb. ODBC fetch-size tuning is a latency fix with a hard ceiling at the serialization floor. If your extract is still slow at a 10K+ array size, you have hit the row-major wall — stop tuning the driver and change the transport.

Common beginner mistakes.

• Blaming the query engine for a transport problem. The query profile says 1 second; the extract takes 40. Measure before optimizing.
• Assuming "zero-copy" means no network cost. Flight still sends bytes over the wire — "zero-copy / zero-deserialization" means no per-cell parse on receive, not no I/O.
• Treating Flight as a drop-in for every JDBC use. A single-row primary-key lookup gains nothing; the columnar win is a large-result-set win.
• Forgetting the consumer shape. If the client is a columnar consumer (pandas/Arrow/Polars), the transposition tax is real. If the consumer genuinely wants one row at a time, the calculus changes.

Senior interview question on the transport bottleneck

A senior interviewer often opens with: "Our warehouse query profile shows the query finishing in under a second, but the analytics team's Python extract of the same result takes almost a minute over our ODBC BI driver. Explain where the time is going, prove it, and propose a transport that removes the cost without changing the query."

Solution Using an engine-vs-transport decomposition plus an Arrow Flight cut-over

# diagnose_and_cutover.py
# Step 1 — prove the cost is transport, not engine.
import time, pyodbc
import pyarrow.flight as flight

QUERY = "SELECT * FROM events WHERE day = '2026-08-01'"   # ~10M rows, 12 cols

def engine_time_ms(conn):
    """Ask the DB how long IT spent (read the profile / EXPLAIN ANALYZE)."""
    cur = conn.cursor()
    cur.execute(f"EXPLAIN ANALYZE {QUERY}")
    profile = "\n".join(r[0] for r in cur.fetchall())
    # parse the engine's own reported execution time out of the profile text
    return parse_execution_ms(profile)

def odbc_wallclock_s():
    conn = pyodbc.connect(DSN); cur = conn.cursor()
    cur.arraysize = 50_000
    t0 = time.perf_counter(); cur.execute(QUERY)
    n = 0
    while (batch := cur.fetchmany(50_000)):
        n += len(batch)
    return n, time.perf_counter() - t0

# Step 2 — the cut-over: same SQL, columnar transport.
def flight_wallclock_s():
    client = flight.connect("grpc+tls://warehouse.internal:8815",
                            tls_root_certs=open("ca.pem", "rb").read())
    info   = client.get_flight_info(flight.FlightDescriptor.for_command(QUERY.encode()))
    # Fan across every endpoint the planner returned (parallel by construction).
    tables = [client.do_get(ep.ticket).read_all() for ep in info.endpoints]
    import pyarrow as pa
    table  = pa.concat_tables(tables)
    return table.num_rows

if __name__ == "__main__":
    conn = pyodbc.connect(DSN)
    eng  = engine_time_ms(conn) / 1000.0
    n, odbc = odbc_wallclock_s()
    t0 = time.perf_counter(); rows = flight_wallclock_s(); fl = time.perf_counter() - t0
    print(f"engine        : {eng:6.2f}s")
    print(f"odbc  wall     : {odbc:6.2f}s  (transport ≈ {odbc-eng:5.2f}s)")
    print(f"flight wall    : {fl:6.2f}s  (transport ≈ {fl-eng:5.2f}s)")
    print(f"transport speedup: {(odbc-eng)/max(fl-eng,1e-3):5.1f}x")

Step-by-step trace.

Step	Action	Observation
1	EXPLAIN ANALYZE reports engine time	engine ≈ 0.9 s — the query is not the problem
2	ODBC fetch loop at arraysize=50K	wall ≈ 38 s → transport ≈ 37 s
3	Flight get_flight_info returns endpoints	planner exposes parallelism as tickets
4	Fan do_get across endpoints, concat_tables	wall ≈ 2.1 s → transport ≈ 1.2 s
5	Compare transport-only figures	~30× less time in transport, same SQL

After the decomposition, the number that ends the debate is "transport ≈ 37 s on ODBC vs ≈ 1.2 s on Flight for an identical query the engine ran in under a second." The query was never the bottleneck; the row-major transport was, and swapping it out — without touching the SQL — recovers the engine's real speed.

Output:

Metric	ODBC	Arrow Flight
Engine time	0.9 s	0.9 s
Wall-clock	38.4 s	2.1 s
Transport-only time	~37 s	~1.2 s
Rows/s (end to end)	~260 K	~4.8 M
Change to the SQL	none	none

Why this works — concept by concept:

• Engine-vs-transport decomposition — subtracting the engine's self-reported execution time from the client wall-clock isolates the transport cost. This is the measurement that converts "the extract feels slow" into "37 seconds are spent serializing rows," which is actionable.
• Row-major serialization tax — ODBC must transpose columnar output to rows, serialize each cell, and let the client re-columnarize. That is O(cells) work twice, and it is the entire 37 seconds.
• Columnar wire (Arrow IPC) — Flight sends the engine's columns as-is; the client receives columns. No transposition and no per-cell decode means the transport cost falls to buffer framing over the network.
• Parallel endpoints — fanning do_get across the endpoints the planner returned uses multiple streams instead of one cursor, so a fat network pipe is actually saturated rather than trickled through a single connection.
• Cost — Flight collapses transport from O(cells) serialize/deserialize to O(buffers) framing, turning a ~37 s tax into ~1.2 s. The trade is a younger ecosystem and an Arrow-shaped client; for large analytical result sets that trade is overwhelmingly worth it, and Flight SQL + ADBC closes the ecosystem gap.

Design
Topic — design
System-design problems on data transport and serialization

Practice →

Optimization Topic — optimization Optimization problems on throughput and I/O

Practice →

---

2. Arrow Flight architecture — gRPC, tickets, and endpoints

The control plane plans, the data plane streams — descriptors in, tickets out, DoGet in parallel

The mental model in one line: arrow flight splits every transfer into a gRPC/Protobuf control plane that answers "what result do you want and where can you pick it up" and an Arrow IPC data plane that streams the actual columnar RecordBatch messages — a client describes a result with a FlightDescriptor, the server responds with a FlightInfo containing a schema and one or more tickets paired with endpoints (locations), and the client redeems each ticket with DoGet — often across many endpoints in parallel — so distribution and parallelism are first-class in the protocol rather than bolted on. This descriptor → info → ticket → endpoint indirection is exactly what lets a distributed engine tell one client "your result lives on these five nodes, here are five tickets, go fetch them concurrently."

The two planes.

• Control plane (gRPC + Protobuf). Small, structured metadata messages: descriptors, schemas, tickets, endpoints, actions. This is where planning, auth handshakes, and catalog lookups live. It is cheap and chatty-tolerant because the payloads are tiny.
• Data plane (Arrow IPC over gRPC streaming). The bulk transfer: a server streams FlightData messages, each carrying an Arrow RecordBatch (schema message first, then batches). This is the arrow ipc stream and it is where the throughput lives.

The Flight RPC verbs — the whole protocol surface.

• GetFlightInfo(descriptor) → FlightInfo. "I want the result described by this descriptor; tell me its schema and where to fetch it." Returns tickets + endpoints. This is the planning call.
• DoGet(ticket) → stream<FlightData>. "Redeem this ticket." The server streams record batches. This is the read path.
• DoPut(descriptor, stream<FlightData>) → stream<PutResult>. "Here is a stream of record batches to ingest under this descriptor." This is the write/upload path.
• DoExchange(descriptor, stream<FlightData>) ↔ stream<FlightData>. Full bidirectional streaming — send batches and receive batches on the same call. Used for server-side transforms, joins, and interactive protocols.
• DoAction(action) → stream<Result> / ListActions. The extensibility escape hatch — arbitrary named RPCs (create prepared statement, begin transaction, refresh metadata). Flight SQL is built almost entirely on DoAction + typed descriptors.
• ListFlights(criteria) → stream<FlightInfo> / GetSchema(descriptor). Discovery — enumerate available datasets and fetch a schema without fetching data.

FlightDescriptor, FlightInfo, Ticket, Endpoint, Location — the nouns.

• FlightDescriptor. Names what you want. Two flavors: for_path([...]) (a named dataset, like a table) or for_command(bytes) (an opaque command — a SQL string, a serialized plan, a Flight SQL Protobuf message).
• FlightInfo. The planner's answer: the result schema, the descriptor echoed back, a list of FlightEndpoints, and optional total_records / total_bytes estimates.
• FlightEndpoint. A Ticket plus a list of Locations where that ticket can be redeemed. Multiple endpoints = a partitioned result the client can read in parallel.
• Ticket. An opaque server-issued token (bytes) that identifies one partition/stream to DoGet. The server chooses its meaning — a partition id, a serialized sub-plan, a file offset.
• Location. A gRPC URI (grpc://host:port, grpc+tls://…). If an endpoint's locations are empty, "fetch from the same server you called GetFlightInfo on."

Why the indirection matters — parallel and distributed reads.

• A single query can be planned into N partitions; the server returns N endpoints, each with its own ticket and (possibly) its own location on a different worker node.
• The client fans out DoGet across endpoints concurrently — many streams, many nodes, one logical result — then concatenates. This is parallel endpoints and it is why Flight scales where a single JDBC cursor cannot.
• Because endpoints carry locations, the coordinator can hand reads directly to the workers holding the data, avoiding a funnel through one coordinator socket.

Worked example — a minimal Flight producer and consumer

Detailed explanation. The smallest useful Flight service exposes named datasets: the client lists flights or asks for info by path, then does a DoGet. Building it once makes the descriptor → info → ticket → stream flow concrete. Walk through a server that serves in-memory tables by name and a client that fetches one.

• Server state. A dict of name → pyarrow.Table.
• get_flight_info. Look up the table, return its schema and one endpoint whose ticket is the table name.
• do_get. Decode the ticket to a name, stream the table as record batches via RecordBatchStream.
• Client. Build a for_path descriptor, call get_flight_info, redeem the ticket.

Question. Implement a minimal FlightServerBase that serves named tables and a client that fetches one by name.

Input.

Piece	Responsibility
get_flight_info	resolve name → schema + one endpoint(ticket=name)
do_get	ticket bytes → table → RecordBatchStream
client descriptor	FlightDescriptor.for_path([name])
client read	do_get(ticket).read_all() → pa.Table

Code.

# minimal_flight.py — a named-table Flight server + client
import pyarrow as pa
import pyarrow.flight as flight

class TableServer(flight.FlightServerBase):
    def __init__(self, location, tables):
        super().__init__(location)
        self._tables = tables          # dict[str, pa.Table]
        self._loc = location

    def get_flight_info(self, context, descriptor):
        name = descriptor.path[0].decode()          # for_path([b"events"])
        table = self._tables[name]
        endpoint = flight.FlightEndpoint(
            ticket=flight.Ticket(name.encode()),     # ticket == the table name
            locations=[self._loc],                   # redeem here
        )
        return flight.FlightInfo(
            schema=table.schema,
            descriptor=descriptor,
            endpoints=[endpoint],
            total_records=table.num_rows,
            total_bytes=table.nbytes,
        )

    def do_get(self, context, ticket):
        name = ticket.ticket.decode()
        table = self._tables[name]
        # Stream the table as Arrow record batches — no per-cell serialization.
        return flight.RecordBatchStream(table)

if __name__ == "__main__":
    loc = "grpc://0.0.0.0:8815"
    tables = {
        "events": pa.table({"id": pa.array(range(1_000_000)),
                            "kind": pa.array(["click"] * 1_000_000)}),
    }
    server = TableServer(loc, tables)
    print("serving on", loc)
    server.serve()

# client.py
import pyarrow.flight as flight

client = flight.connect("grpc://localhost:8815")
descriptor = flight.FlightDescriptor.for_path("events")
info = client.get_flight_info(descriptor)
print("schema:", info.schema)
print("rows (estimate):", info.total_records)

reader = client.do_get(info.endpoints[0].ticket)
table = reader.read_all()                 # pyarrow.Table, columnar, no decode pass
print("fetched:", table.num_rows, "rows")

Step-by-step explanation.

1. The server subclasses FlightServerBase and overrides two methods. get_flight_info is the control-plane call: it resolves the descriptor's path to a table and returns a FlightInfo carrying the schema and exactly one endpoint whose ticket is the table name.
2. The endpoint's locations=[self._loc] says "redeem this ticket at this server." With a single node this is the same address the client already called; on a cluster it could point at a worker.
3. do_get is the data-plane call: it decodes the ticket back to a name and returns a RecordBatchStream(table), which streams the table's record batches as Arrow IPC. No cell is ever serialized individually.
4. The client builds a for_path descriptor, calls get_flight_info to learn the schema and get a ticket, then do_get(ticket).read_all() to materialize a pyarrow.Table. The read_all is a columnar assembly, not a parse loop.
5. Even in this two-method toy, the full architecture is visible: descriptor (what) → info (schema + ticket + location) → ticket (redeemable handle) → stream (columnar data). Everything larger — Flight SQL, Dremio — is this flow with richer descriptors and more endpoints.

Output.

serving on grpc://0.0.0.0:8815
schema: id: int64
kind: string
rows (estimate): 1000000
fetched: 1000000 rows

Rule of thumb. Every Flight interaction is descriptor → FlightInfo → ticket → stream. If you can implement get_flight_info and do_get for a named table, you understand the whole protocol; distribution is just "return more endpoints."

Worked example — parallel reads across multiple endpoints

Detailed explanation. The reason Flight scales is that one FlightInfo can carry many endpoints, and the client can redeem their tickets concurrently. Simulate a partitioned result — the server splits a table into shards and returns one endpoint per shard — and a client that fans out DoGet with a thread pool. Walk through the partition-aware server and the fan-out client.

• Server. Split events into 4 shards; get_flight_info returns 4 endpoints, ticket = shard index.
• do_get. Serve the shard named by the ticket.
• Client. Submit one do_get per endpoint to a thread pool, then concat_tables.
• Payoff. Four concurrent Arrow streams instead of one cursor — the network pipe is actually filled.

Question. Return a partitioned FlightInfo and consume it with a parallel fan-out client.

Input.

Component	Behavior
shards	events split into 4 record-batch groups
endpoints	4, one per shard, ticket = b"events:{i}"
client pool	ThreadPoolExecutor(max_workers=4)
assembly	pa.concat_tables([...])

Code.

# parallel_endpoints.py — partitioned FlightInfo + fan-out client
import pyarrow as pa
import pyarrow.flight as flight
from concurrent.futures import ThreadPoolExecutor

N_SHARDS = 4

class ShardedServer(flight.FlightServerBase):
    def __init__(self, location, table):
        super().__init__(location)
        self._loc = location
        # Pre-split the table into N contiguous shards.
        rows = table.num_rows
        step = rows // N_SHARDS
        self._shards = [table.slice(i * step,
                                    step if i < N_SHARDS - 1 else rows - i * step)
                        for i in range(N_SHARDS)]

    def get_flight_info(self, context, descriptor):
        endpoints = [
            flight.FlightEndpoint(
                ticket=flight.Ticket(f"events:{i}".encode()),
                locations=[self._loc],          # could be a per-worker location
            )
            for i in range(N_SHARDS)
        ]
        return flight.FlightInfo(
            schema=self._shards[0].schema,
            descriptor=descriptor,
            endpoints=endpoints,
            total_records=sum(s.num_rows for s in self._shards),
            total_bytes=sum(s.nbytes for s in self._shards),
        )

    def do_get(self, context, ticket):
        idx = int(ticket.ticket.decode().split(":")[1])
        return flight.RecordBatchStream(self._shards[idx])

def fetch_parallel(host):
    client = flight.connect(host)
    info = client.get_flight_info(flight.FlightDescriptor.for_path("events"))

    def read_endpoint(ep):
        # A fresh client per thread avoids sharing one channel across threads.
        c = flight.connect(ep.locations[0].uri.decode()
                           if ep.locations else host)
        return c.do_get(ep.ticket).read_all()

    with ThreadPoolExecutor(max_workers=len(info.endpoints)) as pool:
        parts = list(pool.map(read_endpoint, info.endpoints))
    return pa.concat_tables(parts)

if __name__ == "__main__":
    table = pa.table({"id": pa.array(range(4_000_000))})
    ShardedServer("grpc://0.0.0.0:8815", table)  # serve() in a real run
    result = fetch_parallel("grpc://localhost:8815")
    print("total rows:", result.num_rows)

Step-by-step explanation.

1. The server pre-splits the table into N_SHARDS contiguous slices. In a real distributed engine these shards live on different worker nodes; here they live in one process but the protocol shape is identical.
2. get_flight_info returns one FlightEndpoint per shard, each with a distinct ticket and a location. The list of endpoints is the parallelism the planner is exposing to the client.
3. The client reads the endpoint list and submits one do_get per endpoint to a thread pool. Each redemption opens its own Arrow stream, so four record-batch streams flow concurrently.
4. Each worker thread uses its own FlightClient — Flight channels are not meant to be shared across threads for concurrent do_get, so a client per endpoint (or a small pool of channels) keeps the streams independent.
5. pa.concat_tables(parts) stitches the four shard tables into one logical result. Ordering across endpoints is not guaranteed by the protocol, so if row order matters you sort after assembly or encode order into the ticket.

Output.

Endpoints redeemed	Concurrency	Rows assembled	Effect vs single cursor
4	4 threads	4,000,000	~4 parallel streams fill the pipe

Rule of thumb. Parallelism in Flight is "return more endpoints and fan out do_get." Use one client per concurrent stream, concatenate at the end, and never assume cross-endpoint order — encode ordering into the ticket if you need it.

Common beginner mistakes.

• Sharing one FlightClient across threads for concurrent do_get. Use a client per stream; a single channel serializes the reads.
• Assuming endpoints come back ordered. The protocol makes no ordering promise across endpoints; sort or encode order explicitly.
• Putting large payloads in tickets. A ticket is an opaque handle, not the data — keep it small (an id, an offset, a compact plan).
• Ignoring empty locations. Empty locations means "same server as GetFlightInfo," which is correct for single-node but must be handled when fanning out.

Senior interview question on Flight architecture

A senior interviewer might ask: "A distributed query engine computes a result across five worker nodes. Design the Flight interaction so a single client reads the whole result in parallel, directly from the workers, without funneling every byte through the coordinator. Walk me through the descriptor, the FlightInfo, the tickets, the endpoints, and the client fan-out."

Solution Using coordinator-planned endpoints with per-worker locations

# distributed_flight.py — coordinator plans; workers serve; client fans out.

# --- Coordinator: plans the query into per-worker partitions. ---
import pyarrow as pa
import pyarrow.flight as flight

WORKERS = {                      # partition -> the worker that holds it
    0: "grpc://worker-a.internal:8815",
    1: "grpc://worker-b.internal:8815",
    2: "grpc://worker-c.internal:8815",
    3: "grpc://worker-d.internal:8815",
    4: "grpc://worker-e.internal:8815",
}

class Coordinator(flight.FlightServerBase):
    def get_flight_info(self, context, descriptor):
        sql = descriptor.command.decode()          # for_command(b"SELECT ...")
        plan = plan_query(sql)                       # -> {partition: sub_plan}
        endpoints = [
            flight.FlightEndpoint(
                # ticket carries the serialized sub-plan for THIS partition
                ticket=flight.Ticket(serialize_subplan(plan[p]).encode()),
                locations=[flight.Location.for_grpc_tcp(
                    *host_port(WORKERS[p]))],        # redeem on the worker, not here
            )
            for p in plan
        ]
        return flight.FlightInfo(
            schema=plan_schema(plan),
            descriptor=descriptor,
            endpoints=endpoints,
            total_records=-1,                        # unknown until executed
            total_bytes=-1,
        )

# --- Worker: executes the sub-plan carried in the ticket. ---
class Worker(flight.FlightServerBase):
    def do_get(self, context, ticket):
        subplan = deserialize_subplan(ticket.ticket.decode())
        # Execute locally and stream the resulting batches — no coordinator hop.
        def batches():
            for rb in execute_subplan(subplan):      # generator of RecordBatch
                yield rb
        first = next(batches())
        return flight.GeneratorStream(
            first.schema,
            (rb for rb in [first, *batches()]),
        )

# --- Client: one GetFlightInfo to the coordinator, fan-out DoGet to workers. ---
from concurrent.futures import ThreadPoolExecutor

def run(sql, coordinator="grpc://coordinator.internal:8815"):
    cc = flight.connect(coordinator)
    info = cc.get_flight_info(flight.FlightDescriptor.for_command(sql.encode()))

    def read(ep):
        wc = flight.connect(ep.locations[0].uri.decode())   # talk straight to worker
        return wc.do_get(ep.ticket).read_all()

    with ThreadPoolExecutor(max_workers=len(info.endpoints)) as pool:
        parts = list(pool.map(read, info.endpoints))
    return pa.concat_tables(parts)

Step-by-step trace.

Step	Where	What happens
1	client → coordinator	GetFlightInfo(for_command(sql)) — one small control call
2	coordinator	plans query into 5 partitions, one sub-plan each
3	coordinator → client	FlightInfo with 5 endpoints; each location = the worker holding that partition
4	client → workers (×5)	DoGet(ticket) in parallel, straight to each worker
5	workers	execute sub-plan, stream Arrow batches back — no coordinator relay
6	client	concat_tables assembles the 5 streams into one result

The coordinator only ever moves metadata — descriptors and tickets — never bulk data. The bulk data flows on five independent Arrow streams directly from the workers to the client, so the coordinator is never a throughput bottleneck and the client's network pipe is filled by five concurrent streams.

Output:

Property	Value
Control-plane calls	1 (GetFlightInfo to coordinator)
Data-plane streams	5 (DoGet, one per worker, parallel)
Bytes through coordinator	~0 (metadata only)
Data path	worker → client directly
Assembly	pa.concat_tables on the client

Why this works — concept by concept:

• Descriptor for_command — the client names the result with a SQL command, not a fixed path, so the coordinator is free to plan it into however many partitions the data demands. The descriptor is what, not how.
• Endpoints with per-worker locations — putting each partition's worker URI in the endpoint's Location is what lets the client bypass the coordinator and read directly from the node holding the data. This is the whole point of the endpoint indirection.
• Tickets carry the sub-plan — the ticket is opaque to the client but meaningful to the worker: it holds the serialized sub-plan for that partition, so the worker knows exactly what to execute when the ticket is redeemed.
• GeneratorStream for lazy execution — returning a GeneratorStream lets the worker execute and stream batches lazily rather than materializing the whole partition first, keeping worker memory flat.
• Cost — one metadata round-trip plus N parallel data streams. The coordinator scales because it moves O(partitions) metadata, not O(rows) data; the client scales because it reads N streams concurrently. This is the pattern behind every production distributed Flight service, from Dremio to Spark's Flight connectors.

Streaming
Topic — streaming
Streaming problems on record-batch and partitioned reads

Practice →

Design Topic — design Design problems on distributed read fan-out

Practice →

---

3. Flight SQL — a standard SQL-over-Flight protocol

Flight SQL turns bespoke Flight verbs into a portable database protocol — one driver for every server

The mental model in one line: raw arrow flight gives you the fast transport but leaves the meaning of descriptors, tickets, and actions entirely up to each server — so every producer speaks a bespoke dialect and every client must be written against that specific server, while flight sql standardizes those meanings into a documented protocol of Protobuf command messages (CommandStatementQuery, CommandGetTables, CommandGetCatalogs, prepared-statement and transaction actions) carried over the same Flight verbs, so a single Flight SQL driver can talk to any Flight SQL server the way a single JDBC driver talks to any JDBC database. Flight SQL is to raw Flight what a database wire protocol is to raw sockets: the fast pipe was always there; Flight SQL agrees on what to say through it.

What Flight SQL standardizes on top of raw Flight.

• Query execution. A CommandStatementQuery Protobuf (carrying the SQL text) is packed into a FlightDescriptor.for_command; get_flight_info returns tickets; do_get streams the Arrow result. The client never has to know the server's private descriptor format — the command message is the format.
• Catalog metadata. Standard commands — CommandGetCatalogs, CommandGetDbSchemas, CommandGetTables, CommandGetTableTypes, CommandGetPrimaryKeys — return metadata as Arrow tables with documented schemas. This is what lets a BI tool enumerate your databases and tables generically.
• Prepared statements. ActionCreatePreparedStatementRequest (a DoAction) returns a handle; the client binds parameters as an Arrow batch via DoPut against a CommandPreparedStatementQuery, then do_gets the result. This is server-side prepare + parameter binding, standardized.
• Transactions. ActionBeginTransactionRequest / ActionEndTransactionRequest actions return and consume transaction handles, so multi-statement transactions are part of the protocol, not a per-server bolt-on.
• DDL / updates. CommandStatementUpdate runs non-SELECT statements and returns an affected-row count instead of a stream.

Raw Flight vs Flight SQL — when to use which.

• Reach for raw Flight when you are building a bespoke data service whose "queries" are not SQL — a feature store keyed by entity id, a model-inference endpoint, a partition server. You define the descriptors and tickets because only your server and your client need to agree.
• Reach for Flight SQL when you want a database-shaped interface that arbitrary clients — BI tools, ADBC drivers, JDBC/ODBC shims — can connect to without custom code. The standard buys you the whole client ecosystem.
• The ecosystem payoff. Because Flight SQL is standardized, the ADBC Flight SQL driver, the JDBC-over-Flight-SQL driver, and the ODBC-over-Flight-SQL driver all work against any compliant server — that is what makes Flight a real jdbc odbc replacement rather than a niche.

The client tooling landscape.

• ADBC (Arrow Database Connectivity). The Arrow-native database API; its Flight SQL driver speaks the protocol and returns Arrow directly. This is the recommended Python path and the cleanest JDBC/ODBC replacement.
• JDBC/ODBC drivers over Flight SQL. Official shim drivers let legacy JDBC/ODBC tools connect to a Flight SQL server, getting most of the throughput win without the tool being rewritten.
• The pyarrow.flight FlightSQL client helpers. Lower-level building blocks for constructing the command messages by hand when you need control.

Worked example — executing a query through Flight SQL

Detailed explanation. The cleanest way to use Flight SQL from Python is the ADBC Flight SQL driver, which exposes a standard DB-API interface but returns Arrow. Under the hood it packs a CommandStatementQuery, calls get_flight_info, and fans do_get. Walk through both the high-level ADBC call and the lower-level command construction so the mapping is clear.

• High-level. adbc_driver_flightsql.dbapi.connect(...) → cursor → fetch_arrow_table().
• Low-level. Build a CommandStatementQuery Protobuf, wrap in a for_command descriptor, get_flight_info, do_get.
• Why it matters. ADBC is the ergonomic path; the low-level view shows there is no magic — it is Flight verbs carrying standard command messages.

Question. Run SELECT through Flight SQL via ADBC and, separately, show the equivalent low-level command construction.

Input.

Layer	Call	Returns
ADBC DB-API	cursor.execute(sql); cursor.fetch_arrow_table()	pyarrow.Table
Flight SQL command	CommandStatementQuery{query=sql} in for_command	FlightInfo → stream

Code.

# flight_sql_query.py — the ergonomic ADBC path
import adbc_driver_flightsql.dbapi as flight_sql

# One DSN swap replaces a JDBC/ODBC connection string.
conn = flight_sql.connect(
    "grpc+tls://warehouse.internal:8815",
    db_kwargs={
        "adbc.flight.sql.authorization_header": "Bearer eyJhbGciOi...",
    },
)
cur = conn.cursor()
cur.execute("SELECT id, kind, ts FROM events WHERE day = '2026-08-01'")
table = cur.fetch_arrow_table()          # -> pyarrow.Table, columnar, zero decode
print(table.num_rows, "rows")
cur.close(); conn.close()

# flight_sql_lowlevel.py — what ADBC does under the hood (illustrative)
import pyarrow.flight as flight
from pyarrow.flight import FlightDescriptor

# Flight SQL packs the SQL into a CommandStatementQuery protobuf. The ADBC/Flight
# SQL libraries generate these; shown here as the conceptual payload.
def command_statement_query(sql: str) -> bytes:
    # In practice: build the Flight SQL protobuf message and Any-wrap it.
    from pyarrow.flight import FlightSqlClient  # helper in recent pyarrow
    return FlightSqlClient.pack_statement_query(sql)  # conceptual

client = flight.connect("grpc+tls://warehouse.internal:8815",
                        tls_root_certs=open("ca.pem", "rb").read())
descriptor = FlightDescriptor.for_command(command_statement_query(
    "SELECT id, kind, ts FROM events WHERE day = '2026-08-01'"))
info = client.get_flight_info(descriptor)     # server plans, returns tickets
table = client.do_get(info.endpoints[0].ticket).read_all()
print(table.num_rows, "rows")

Step-by-step explanation.

1. The ADBC path (flight_sql.connect) is deliberately shaped like every other DB-API driver: connect, cursor, execute, fetch. The only unusual method is fetch_arrow_table, which returns columnar Arrow directly instead of a list of row tuples — that is where the transport win is preserved.
2. The connection string is a grpc+tls:// Flight URI and the auth token rides an authorization header. Swapping a JDBC URL for this DSN is often the entire client-side migration.
3. Under the hood, execute packs the SQL into a CommandStatementQuery Protobuf, wraps it in a for_command descriptor, and calls get_flight_info. The server plans the query and returns tickets/endpoints exactly like raw Flight.
4. fetch_arrow_table fans do_get across the returned endpoints and concatenates — so ADBC transparently gives you the parallel-endpoint benefit without you writing the fan-out.
5. The low-level view exists to demystify: Flight SQL is not a new transport, it is agreed-upon command messages over the same Flight verbs. If you understand section 2, Flight SQL is "put a standard Protobuf in the command descriptor."

Output.

1743922 rows

Rule of thumb. Use ADBC's Flight SQL driver as your default client — it gives you the standard DB-API surface, returns Arrow, and handles endpoint fan-out for you. Drop to hand-built command messages only when you need control the driver does not expose.

Worked example — prepared statements and parameter binding

Detailed explanation. Prepared statements in Flight SQL are a two-phase dance: a DoAction creates the statement server-side and returns a handle; parameters are bound by sending an Arrow batch, and the result is fetched by do_get. ADBC exposes this as ordinary parameterized execute. Walk through binding parameters safely — the same injection-avoidance discipline as any driver, now over Flight.

• Create. ActionCreatePreparedStatementRequest{query} → handle + parameter schema.
• Bind. Parameters travel as an Arrow record batch (one row = one parameter set) via DoPut against a CommandPreparedStatementQuery{prepared_statement_handle}.
• Execute. get_flight_info + do_get on the prepared command returns the Arrow result.
• ADBC view. cursor.execute(sql, parameters=...) — the driver runs all three phases.

Question. Run a parameterized Flight SQL query with bound parameters, and show the underlying create → bind → execute phases.

Input.

Phase	Flight SQL mechanism	Payload
create	DoAction(CreatePreparedStatement)	SQL with ? placeholders
bind	DoPut(CommandPreparedStatementQuery)	Arrow batch of parameter values
execute	GetFlightInfo + DoGet	result stream

Code.

# flight_sql_prepared.py — parameterized query via ADBC (safe binding)
import adbc_driver_flightsql.dbapi as flight_sql

conn = flight_sql.connect(
    "grpc+tls://warehouse.internal:8815",
    db_kwargs={"adbc.flight.sql.authorization_header": "Bearer eyJ..."},
)
cur = conn.cursor()

# Parameters are BOUND, never string-formatted — no SQL injection surface.
cur.execute(
    "SELECT id, kind, ts FROM events WHERE day = ? AND kind = ?",
    parameters=("2026-08-01", "purchase"),
)
table = cur.fetch_arrow_table()
print(table.num_rows, "rows for the bound parameters")

# Re-execute with new parameters — the server reuses the prepared plan.
cur.execute(
    "SELECT id, kind, ts FROM events WHERE day = ? AND kind = ?",
    parameters=("2026-08-02", "click"),
)
print(cur.fetch_arrow_table().num_rows, "rows on the second bind")
cur.close(); conn.close()

# what the driver does across the three phases (conceptual)
# 1. create:  handle, param_schema = do_action(CreatePreparedStatement(sql))
# 2. bind:    params = pa.record_batch({"day": ["2026-08-01"], "kind": ["purchase"]})
#             writer, _ = client.do_put(
#                 FlightDescriptor.for_command(PreparedStatementQuery(handle)),
#                 params.schema)
#             writer.write_batch(params); writer.done_writing()
# 3. execute: info = client.get_flight_info(
#                 FlightDescriptor.for_command(PreparedStatementQuery(handle)))
#             table = client.do_get(info.endpoints[0].ticket).read_all()

Step-by-step explanation.

1. The ADBC execute(sql, parameters=...) call is the whole story from the application's view: placeholders (?) plus a tuple of values, exactly like any DB-API driver. Values are bound server-side, so there is no string interpolation and no injection surface.
2. Under the hood, phase 1 issues a DoAction carrying CreatePreparedStatementRequest; the server compiles the statement, returns a handle, and reports the expected parameter schema so the client knows how to shape the bind batch.
3. Phase 2 binds parameters as an Arrow record batch sent via DoPut against a CommandPreparedStatementQuery descriptor holding the handle. Parameters are columnar too — a batch with multiple rows binds multiple parameter sets in one call.
4. Phase 3 calls get_flight_info + do_get on the prepared command; the server executes the compiled plan with the bound parameters and streams the Arrow result.
5. Re-executing with new parameters reuses the server-side prepared plan — the compile cost is paid once, and only the cheap bind + execute phases repeat. This is the same latency win prepared statements give any driver, now with a columnar result.

Output.

Bind	Parameters	Rows returned
1	('2026-08-01', 'purchase')	42,118
2	('2026-08-02', 'click')	991,204

Rule of thumb. Always bind parameters through the prepared-statement path (? + parameters=), never string-format SQL. Flight SQL binds parameters as Arrow batches server-side, giving you injection safety and plan reuse with a columnar result.

Common beginner mistakes.

• String-formatting SQL instead of binding. Flight SQL has real prepared statements — use them; f-strings into SQL are an injection bug.
• Confusing Flight SQL with a new transport. It is command messages over the same Flight verbs; the speed comes from Flight, the portability from the standard.
• Expecting every server to support every command. Servers advertise capabilities; CommandGetTables may be supported while transactions are not. Check the server's advertised feature flags.
• Reaching for raw Flight when you want a database. If arbitrary BI clients must connect, speak Flight SQL so the standard drivers work; don't invent private descriptors.

Senior interview question on Flight SQL

A senior interviewer might ask: "We want our internal query engine to be reachable by BI tools, a Python analytics team, and a legacy Java service that only speaks JDBC — all with the throughput of Arrow. Do we expose raw Flight or Flight SQL, and how does each client connect? Walk me through the protocol choice and the driver story for each consumer."

Solution Using a Flight SQL endpoint fronting the engine with ADBC and JDBC-shim clients

# server_side_choice.py — expose Flight SQL (not raw Flight) so standard drivers work.
# The engine implements the Flight SQL command handlers; clients below are generic.

# --- Python analytics team: ADBC Flight SQL driver (Arrow-native) ---
import adbc_driver_flightsql.dbapi as flight_sql

def python_client():
    conn = flight_sql.connect(
        "grpc+tls://engine.internal:8815",
        db_kwargs={"adbc.flight.sql.authorization_header": "Bearer <token>"},
    )
    cur = conn.cursor()
    cur.execute("SELECT region, sum(amount) FROM sales GROUP BY region")
    return cur.fetch_arrow_table()        # columnar, parallel fan-out handled by ADBC

# --- BI tool: connects via the ODBC-over-Flight-SQL shim driver (config, not code) ---
#   DSN: Driver=Arrow Flight SQL ODBC; Host=engine.internal; Port=8815; UseTLS=1
#   The BI tool issues ordinary SQL; the shim speaks Flight SQL and returns rows.

# --- Legacy Java service: JDBC-over-Flight-SQL driver (URL swap only) ---
#   jdbc:arrow-flight-sql://engine.internal:8815?useEncryption=true
#   Connection c = DriverManager.getConnection(URL, props);
#   ResultSet rs = c.createStatement().executeQuery("SELECT ...");

Decision: expose FLIGHT SQL, not raw Flight.
Reason:   raw Flight would force us to write a custom client for every consumer;
          Flight SQL is a standard, so the ADBC / JDBC / ODBC Flight SQL drivers
          already exist and connect with only config or a DSN swap.

Consumer            Driver                         Client change
------------------  -----------------------------  ---------------------------
Python analytics    ADBC Flight SQL (Arrow-native) new dep, ~DB-API code
BI tool             ODBC-over-Flight-SQL shim      DSN config only
Legacy Java (JDBC)  JDBC-over-Flight-SQL driver    JDBC URL swap only

Step-by-step trace.

Step	Consumer	Mechanism	Throughput characteristic
1	engine	implements Flight SQL command handlers once	one protocol serves all clients
2	Python team	ADBC Flight SQL driver	full columnar + parallel endpoints
3	BI tool	ODBC-over-Flight-SQL shim	most of the win; row API at the very edge
4	Java service	JDBC-over-Flight-SQL driver	URL swap; Arrow under the hood
5	all	one auth + TLS story on the engine	single security surface

By choosing Flight SQL over raw Flight, the engine implements the protocol once and every consumer connects with an off-the-shelf driver — the Python team gets Arrow-native columnar reads, the BI tool and the legacy Java service get most of the throughput win with only a DSN or URL swap, and there is a single auth/TLS surface to secure. Raw Flight would have meant writing and maintaining a bespoke client for each of the three consumers.

Output:

Consumer	Driver	Client-side change	Gets Arrow throughput?
Python analytics	ADBC Flight SQL	add dependency, DB-API code	yes (native)
BI tool	ODBC-over-Flight-SQL shim	DSN config	mostly (row edge)
Legacy Java	JDBC-over-Flight-SQL	JDBC URL swap	mostly (row edge)
Engine team	implement Flight SQL once	one protocol	serves all three

Why this works — concept by concept:

• Flight SQL as a standard — because the command messages are documented and stable, driver authors have already written ADBC/JDBC/ODBC clients. Choosing the standard means you inherit the entire client ecosystem instead of building one.
• ADBC for the Arrow-native path — the Python team gets columnar pyarrow.Table results and transparent endpoint fan-out, preserving the full transport win end to end.
• Shim drivers for legacy consumers — the JDBC/ODBC-over-Flight-SQL drivers let unmodified tools connect; the row-shaped API only appears at the very last hop, so the bulk transfer is still Arrow.
• One server, one security surface — implementing Flight SQL once means a single auth + TLS story on the engine covers every consumer, instead of N bespoke Flight servers each with its own handshake.
• Cost — a modest server-side investment to implement the Flight SQL command handlers, repaid by zero custom-client work per consumer and near-linear read scaling via endpoints. Raw Flight would be cheaper to stand up for one client and far more expensive across many — the crossover favors Flight SQL the moment you have heterogeneous consumers.

ETL
Topic — etl
ETL problems on query interfaces and extraction

Practice →

Design Topic — design Design problems on protocol and driver standardization

Practice →

---

4. Building a Flight service in Python

Subclass FlightServerBase, override four methods, wrap with auth middleware and TLS

The mental model in one line: a production Flight service in Python is a FlightServerBase subclass that overrides at most four methods — get_flight_info (plan), do_get (serve a stream), do_put (ingest a stream), and do_action (everything else) — streams RecordBatches rather than buffering whole tables, and is wrapped by a ServerMiddleware for header/bearer-token auth plus tls_certificates for grpc+tls, so the fast columnar transport arrives with a real security posture instead of an open port. The API surface is small on purpose; the discipline is in streaming (never materialize the whole result), auth (reject unauthenticated calls at the middleware, not in each method), and TLS (encrypt the wire because Arrow buffers are your raw data).

The four methods you actually override.

• get_flight_info(context, descriptor) → FlightInfo. The planner. Resolve the descriptor to a schema and a set of tickets/endpoints. For a single-node service, one endpoint pointing back at yourself; for distribution, many.
• do_get(context, ticket) → FlightDataStream. The read path. Return a RecordBatchStream(table) for a materialized table or a GeneratorStream(schema, batches) to stream lazily and keep memory flat.
• do_put(context, descriptor, reader, writer) → None. The write path. Read incoming RecordBatches from reader and persist them. This is how clients push data into the service.
• do_action(context, action) → iterator<Result>. The extensibility hook — named commands (refresh, compact, create-prepared-statement). list_actions advertises what you support.

Streaming, not buffering — the memory discipline.

• RecordBatchStream(table) streams an already-materialized pyarrow.Table — fine when the result fits in memory.
• GeneratorStream(schema, generator) streams batches produced lazily by a Python generator — the right choice for results larger than memory, since each batch is yielded, sent, and freed.
• The invariant. Never load a 50 GB result into a single pyarrow.Table to serve it; yield batches. The whole point of Flight is streaming columnar data, and the server should stream too.

Auth — reject at the middleware, not in the method.

• ServerMiddleware + ServerMiddlewareFactory. The factory's start_call(info) inspects incoming gRPC headers (e.g. authorization), validates the token, and raises an FlightUnauthenticatedError to reject the call before it reaches your method. Centralizing auth here means every RPC is protected uniformly.
• ClientMiddleware. The client-side mirror injects the authorization: Bearer <token> header on every outgoing call.
• Why not per-method checks? Scattering if not authed: raise across four methods is error-prone; a middleware guarantees no RPC is accidentally left open.

TLS — encrypt the wire.

• Server. Pass tls_certificates=[(cert_bytes, key_bytes)] to FlightServerBase and bind a grpc+tls:// location. Arrow buffers on the wire are your raw data; plaintext is not an option in production.
• Client. Connect with tls_root_certs=<ca_pem> so the client verifies the server, and combine with token auth for "encrypted + authenticated."
• mTLS. For service-to-service, add client certificates for mutual authentication on top of the bearer token.

Worked example — a full FlightServerBase with DoGet and DoPut

Detailed explanation. A service that both serves and ingests named datasets exercises the whole read/write surface. Build a server that holds datasets in memory, streams them on do_get, and accepts uploads on do_put. Walk through the round-trip: a client uploads a table under a name, then another client reads it back.

• State. dict[str, pa.Table].
• do_put. Read all incoming batches from reader, build a table, store under the descriptor's path.
• do_get. Stream the stored table.
• get_flight_info / list_flights. Advertise what is stored.

Question. Implement a read/write Flight service and a client that puts a table and gets it back.

Input.

Method	Input	Effect
do_put	descriptor path + batch stream	store name → table
do_get	ticket (= name)	stream the stored table
get_flight_info	descriptor path	schema + endpoint(ticket=name)

Code.

# rw_flight_server.py — a Flight service that serves AND ingests datasets
import pyarrow as pa
import pyarrow.flight as flight

class DatasetServer(flight.FlightServerBase):
    def __init__(self, location):
        super().__init__(location)
        self._loc = location
        self._store: dict[str, pa.Table] = {}

    # --- write path ---
    def do_put(self, context, descriptor, reader, writer):
        name = descriptor.path[0].decode()
        table = reader.read_all()             # assemble uploaded batches -> Table
        self._store[name] = table
        # optional: writer.write(app_metadata) to ack

    # --- read path ---
    def do_get(self, context, ticket):
        name = ticket.ticket.decode()
        table = self._store[name]
        # Stream lazily so a huge dataset never sits in one buffer twice.
        def batches():
            for batch in table.to_batches(max_chunksize=64 * 1024):
                yield batch
        return flight.GeneratorStream(table.schema, batches())

    # --- planning + discovery ---
    def get_flight_info(self, context, descriptor):
        name = descriptor.path[0].decode()
        table = self._store[name]
        endpoint = flight.FlightEndpoint(flight.Ticket(name.encode()), [self._loc])
        return flight.FlightInfo(table.schema, descriptor, [endpoint],
                                 table.num_rows, table.nbytes)

    def list_flights(self, context, criteria):
        for name, table in self._store.items():
            desc = flight.FlightDescriptor.for_path(name)
            ep = flight.FlightEndpoint(flight.Ticket(name.encode()), [self._loc])
            yield flight.FlightInfo(table.schema, desc, [ep],
                                    table.num_rows, table.nbytes)

if __name__ == "__main__":
    DatasetServer("grpc://0.0.0.0:8815").serve()

# rw_client.py — put a table, then get it back
import pyarrow as pa
import pyarrow.flight as flight

client = flight.connect("grpc://localhost:8815")
table = pa.table({"id": pa.array(range(500_000)),
                  "score": pa.array([0.5] * 500_000)})

# PUT: upload under the name "scores"
desc = flight.FlightDescriptor.for_path("scores")
writer, _ = client.do_put(desc, table.schema)
writer.write_table(table)        # streams record batches to the server
writer.close()

# GET: read it back
info = client.get_flight_info(desc)
got = client.do_get(info.endpoints[0].ticket).read_all()
print("round-tripped rows:", got.num_rows)

Step-by-step explanation.

1. do_put receives a reader streaming the client's uploaded batches. reader.read_all() assembles them into a pyarrow.Table, which the server stores under the descriptor's path name. This is the ingest path — clients push columnar data in.
2. do_get streams the stored table back using GeneratorStream over table.to_batches(max_chunksize=...), so each 64K-row batch is yielded and sent without duplicating the whole table in memory. This is the streaming discipline in action.
3. get_flight_info returns the schema and one endpoint whose ticket is the dataset name — the same descriptor → info → ticket flow from section 2, now backed by uploaded data.
4. list_flights advertises everything stored, so a client can discover datasets without knowing their names in advance — this is how BI tools enumerate a Flight service.
5. On the client, do_put returns a writer; writer.write_table(table) streams the batches and writer.close() finalizes. The subsequent get_flight_info + do_get reads the same data back, proving the full read/write round-trip.

Output.

round-tripped rows: 500000

Rule of thumb. Override do_put for ingest and do_get for serving, and stream with GeneratorStream + to_batches so memory stays flat regardless of dataset size. list_flights makes your service self-describing for generic clients.

Worked example — token-auth middleware and TLS

Detailed explanation. A Flight service on an open plaintext port is a data breach waiting to happen — Arrow buffers are the raw rows. The production posture is a bearer-token ServerMiddleware that rejects unauthenticated calls plus grpc+tls certificates. Walk through a middleware that validates a token header and a client that supplies it over TLS.

• ServerMiddlewareFactory.start_call. Read the authorization header, validate, raise on failure.
• FlightServerBase(middleware=..., tls_certificates=...). Wire the factory and the cert/key.
• Client. ClientMiddleware injects the header; tls_root_certs verifies the server.
• Result. Every RPC is encrypted and authenticated with no per-method code.

Question. Add bearer-token auth and TLS to a Flight service, rejecting calls without a valid token.

Input.

Layer	Mechanism
server auth	ServerMiddlewareFactory.start_call validates authorization
server TLS	tls_certificates=[(cert, key)] + grpc+tls://
client auth	ClientMiddleware.sending_headers injects Bearer
client TLS	tls_root_certs=<ca.pem>

Code.

# secure_flight.py — bearer-token middleware + TLS
import pyarrow.flight as flight

VALID_TOKENS = {"eyJhbGciOi..."}          # in reality: verify a JWT signature

class AuthMiddlewareFactory(flight.ServerMiddlewareFactory):
    def start_call(self, info, headers):
        # gRPC header keys are lowercase; values are lists.
        auth = headers.get("authorization")
        token = auth[0].removeprefix("Bearer ") if auth else None
        if token not in VALID_TOKENS:
            raise flight.FlightUnauthenticatedError("invalid or missing token")
        return AuthMiddleware(token)

class AuthMiddleware(flight.ServerMiddleware):
    def __init__(self, token):
        self.token = token

class SecureServer(flight.FlightServerBase):
    def do_get(self, context, ticket):
        # By the time we get here, auth already passed in the middleware.
        import pyarrow as pa
        return flight.RecordBatchStream(pa.table({"ok": [1]}))

def make_server():
    with open("server.crt", "rb") as c, open("server.key", "rb") as k:
        cert, key = c.read(), k.read()
    return SecureServer(
        location="grpc+tls://0.0.0.0:8815",
        middleware={"auth": AuthMiddlewareFactory()},
        tls_certificates=[(cert, key)],
    )

# secure_client.py — inject the token, verify the server cert
import pyarrow.flight as flight

class TokenClientMiddlewareFactory(flight.ClientMiddlewareFactory):
    def __init__(self, token): self.token = token
    def start_call(self, info): return TokenClientMiddleware(self.token)

class TokenClientMiddleware(flight.ClientMiddleware):
    def __init__(self, token): self.token = token
    def sending_headers(self):
        return {"authorization": f"Bearer {self.token}"}

client = flight.FlightClient(
    "grpc+tls://flight.internal:8815",
    tls_root_certs=open("ca.pem", "rb").read(),
    middleware=[TokenClientMiddlewareFactory("eyJhbGciOi...")],
)
# Every call now carries the token over TLS; a bad token is rejected server-side.
descriptor = flight.FlightDescriptor.for_path("secure")
info = client.get_flight_info(descriptor)
print(client.do_get(info.endpoints[0].ticket).read_all())

Step-by-step explanation.

1. AuthMiddlewareFactory.start_call runs on every incoming RPC before the method body. It reads the lowercase authorization header, strips the Bearer prefix, and validates the token — in production against a JWT signature and claims, not a set literal.
2. On an invalid or missing token it raises FlightUnauthenticatedError, which gRPC turns into an UNAUTHENTICATED status. The call never reaches do_get, so no method needs its own auth check.
3. The server is constructed with middleware={"auth": AuthMiddlewareFactory()} and tls_certificates=[(cert, key)], and bound to a grpc+tls:// location — so the wire is encrypted and every call is authenticated.
4. The client mirrors this: TokenClientMiddleware.sending_headers injects the authorization: Bearer <token> header on every outgoing call, and tls_root_certs makes the client verify the server's certificate against the CA.
5. The result is "encrypted + authenticated" with zero per-method code — the security posture lives entirely in the middleware and TLS config, which is exactly where it should be for uniform enforcement.

Output.

Scenario	Result
valid token over TLS	RPC succeeds, stream returned
missing / bad token	FlightUnauthenticatedError (UNAUTHENTICATED)
plaintext client to TLS server	connection refused / handshake failure

Rule of thumb. Put auth in a ServerMiddleware so every RPC is protected uniformly, always run grpc+tls in production because Arrow buffers are raw data, and inject the client token via ClientMiddleware. Never scatter auth checks across individual methods.

Common beginner mistakes.

• Buffering huge results into one pyarrow.Table to serve. Use GeneratorStream + to_batches and stream; the server should be as streaming as the protocol.
• Auth checks inside each method. One middleware protects everything; per-method checks eventually miss an endpoint.
• Running plaintext gRPC in production. Arrow buffers are your rows; use grpc+tls and verify certs client-side.
• Sharing one server-side table across mutating requests without care. do_put mutates shared state — guard it with a lock or a concurrent-safe store.

Senior interview question on building a Flight service

A senior interviewer might ask: "Design a Python Flight service that serves large query results (bigger than server memory) to authenticated clients over TLS, and also accepts bulk ingest. Walk me through the four methods you override, how you keep memory flat while streaming, and how you enforce auth and encryption without touching each method."

Solution Using GeneratorStream streaming plus middleware auth and TLS

# production_flight.py — streaming, authenticated, encrypted Flight service
import threading
import pyarrow as pa
import pyarrow.flight as flight

# --- auth middleware: validate once, for every RPC ---
class AuthFactory(flight.ServerMiddlewareFactory):
    def __init__(self, verify):  # verify: token -> claims | None
        self._verify = verify
    def start_call(self, info, headers):
        hdr = headers.get("authorization")
        token = hdr[0].removeprefix("Bearer ") if hdr else ""
        claims = self._verify(token)
        if claims is None:
            raise flight.FlightUnauthenticatedError("bad token")
        return _Auth(claims)
class _Auth(flight.ServerMiddleware):
    def __init__(self, claims): self.claims = claims

class WarehouseFlight(flight.FlightServerBase):
    def __init__(self, location, engine, verify, cert, key):
        super().__init__(location,
                         middleware={"auth": AuthFactory(verify)},
                         tls_certificates=[(cert, key)])
        self._loc = location
        self._engine = engine          # runs SQL -> iterator[RecordBatch]
        self._lock = threading.Lock()
        self._staging: dict[str, list] = {}

    # 1) plan
    def get_flight_info(self, context, descriptor):
        sql = descriptor.command.decode()
        schema = self._engine.schema_of(sql)         # plan without executing
        ep = flight.FlightEndpoint(flight.Ticket(sql.encode()), [self._loc])
        return flight.FlightInfo(schema, descriptor, [ep], -1, -1)

    # 2) serve — STREAM, never materialize the whole result
    def do_get(self, context, ticket):
        sql = ticket.ticket.decode()
        batch_iter = self._engine.execute(sql)       # lazy iterator[RecordBatch]
        first = next(batch_iter)
        def gen():
            yield first
            yield from batch_iter
        return flight.GeneratorStream(first.schema, gen())

    # 3) ingest
    def do_put(self, context, descriptor, reader, writer):
        name = descriptor.path[0].decode()
        with self._lock:
            self._staging.setdefault(name, [])
            for chunk in reader:                     # stream batches in
                self._staging[name].append(chunk.data)

    # 4) actions (refresh, compact, ...)
    def do_action(self, context, action):
        if action.type == "commit":
            name = action.body.to_pybytes().decode()
            with self._lock:
                self._engine.load(name, self._staging.pop(name, []))
            yield flight.Result(b"committed")
    def list_actions(self, context):
        return [("commit", "finalize a staged dataset")]

Step-by-step trace.

Step	Method	Memory / security behavior
1	AuthFactory.start_call	validates bearer token on every RPC; rejects before method
2	get_flight_info	plans SQL, returns schema + one ticket — no execution yet
3	do_get	GeneratorStream yields batches lazily → flat memory on huge results
4	do_put	reads incoming batches one at a time into a staging buffer under a lock
5	do_action("commit")	finalizes staged data; extensibility without new RPC verbs
6	TLS certificates	every stream is encrypted on the wire

The service never holds a full result in memory: do_get returns a GeneratorStream that pulls one batch from the engine, sends it, and lets it be freed before the next — so a result far larger than server RAM streams through flat. Auth and TLS live entirely in the middleware and constructor, so the four methods contain only business logic, and every RPC is authenticated and encrypted without a single in-method check.

Output:

Property	Value
Peak server memory on a 50 GB result	~one batch (tens of MB)
Auth enforcement	middleware, every RPC
Wire encryption	grpc+tls, all streams
Ingest path	streamed batches into staged buffer
Extensibility	do_action (commit) — no protocol change

Why this works — concept by concept:

• GeneratorStream lazy serving — returning a generator of RecordBatches lets the engine produce, send, and free one batch at a time, so peak memory is a single batch regardless of total result size. This is the only correct way to serve results larger than RAM.
• Middleware auth — validating the bearer token in start_call protects every RPC uniformly and rejects bad calls before any method runs, so the four methods never carry auth logic and none can be accidentally left open.
• TLS certificates — binding tls_certificates and a grpc+tls location encrypts the Arrow buffers on the wire, which is mandatory because those buffers are the raw data.
• do_put streaming + lock — reading incoming batches one at a time keeps ingest memory flat too, and the lock makes the shared staging buffer safe under concurrent uploads.
• do_action extensibility — commit, refresh, compact ride do_action instead of new protocol verbs, so the service grows capabilities without breaking the Flight contract. list_actions advertises them for discovery.
• Cost — flat O(batch) server memory, one token validation per RPC, TLS handshake per connection. The engineering cost is four small methods plus a middleware; the payoff is a service that streams TB-scale results securely with the full columnar transport win.

Streaming
Topic — streaming
Streaming problems on batch producers and backpressure

Practice →

ETL Topic — etl ETL problems on bulk ingest and serving

Practice →

---

5. Flight in the stack — Dremio, Spark, ADBC, and when not to use it

Where Flight actually lives — the transport hub, its benchmarks, and its anti-patterns

The mental model in one line: arrow flight and flight sql are not a database — they are the transport layer that already sits underneath a growing set of engines (dremio flight exposes a Flight SQL endpoint, Spark has Flight sources/sinks, and ADBC-over-Flight-SQL is the modern jdbc odbc replacement), delivering order-of-magnitude throughput wins on large columnar result sets — and the senior skill is knowing both the win (big analytical extracts, service-to-service columnar transfer, distributed reads) and the envelope where Flight is the wrong tool (tiny result sets, browser clients, non-Arrow consumers, and proxy/load-balancer environments that break long-lived gRPC streams). A candidate who can only pitch the throughput number is junior; the senior candidate names the anti-patterns unprompted.

Where Flight already lives in the stack.

• Dremio. Exposes a Flight SQL endpoint (commonly port 32010); clients connect via the ADBC/JDBC Flight SQL drivers and get parallel, columnar reads straight from Dremio's executors. This is the canonical "point a BI/Python client at a lakehouse engine over Flight" deployment.
• Spark. Flight-based sources and sinks let Spark read from and write to Flight services, and Flight is used to move Arrow batches between Spark and external engines without a row-serialization hop.
• ADBC. Arrow Database Connectivity is the client abstraction; its Flight SQL driver is the recommended replacement for JDBC/ODBC in Arrow-native pipelines — same DB-API shape, Arrow results, endpoint fan-out.
• InfluxDB 3.x / IOx, Ballista, and others. A widening set of query systems speak Flight SQL as their high-throughput client protocol, which is what makes the standard worth learning once.

Where the throughput win comes from — and how big.

• The source of the win. Every benchmark advantage traces back to the same thing: no row transposition and no per-cell serialization. The wider the schema and the larger the result, the bigger the win, because that is exactly the work Flight skips.
• The published range. Arrow's own benchmarks and independent tests report Flight beating ODBC by roughly 10–50× on large result sets; treat the exact multiple as workload-dependent (schema width, network, client language) rather than a fixed number.
• Where it shrinks. On tiny results the fixed gRPC/handshake overhead dominates and the columnar win is negligible — the advantage is asymptotic in result size.

When NOT to use Flight — the envelope.

• Tiny result sets / OLTP point lookups. A single-row primary-key fetch gains nothing; the serialization tax you remove is proportional to result size. Keep JDBC/ODBC (or a normal driver) for chatty small queries.
• Browser / JS clients. Browsers can't speak arbitrary gRPC; you'd need gRPC-Web or a translation layer, which erodes the benefit. A REST/JSON or Arrow-over-HTTP endpoint is usually the pragmatic choice for web front-ends.
• Non-Arrow consumers. If the consumer immediately needs row objects (an ORM hydrating entities, a template renderer), you re-introduce a transposition at the edge and lose much of the win.
• Hostile network middleboxes. Long-lived streaming gRPC calls can be broken by L7 load balancers, idle-timeout proxies, and some corporate egress filters. Flight needs gRPC-aware infrastructure (HTTP/2 end-to-end, generous stream timeouts); plan for it or the streams die mid-transfer.

Interview signals to hit.

• Name the transport-layer framing — Flight is not a database, it is how bytes move — required answer.
• Attribute the benchmark win to no transposition + no per-cell serialization, not "gRPC is fast" — senior signal.
• Name at least two anti-patterns (tiny results, browser clients, non-Arrow consumers, LB/proxy issues) unprompted — senior signal.
• Position ADBC-over-Flight-SQL as the JDBC/ODBC replacement path, and the shim drivers as the migration bridge — senior signal.

Worked example — ADBC Flight SQL as a JDBC/ODBC drop-in

Detailed explanation. The cleanest way to replace a JDBC/ODBC extract is the ADBC Flight SQL driver: the code looks like any DB-API usage, but results come back as Arrow and reads fan across endpoints. Walk through migrating a pandas.read_sql extract to ADBC Flight SQL and note what changes and what does not.

• Before. pandas.read_sql(sql, pyodbc_conn) — row cursor, slow on big results.
• After. ADBC Flight SQL connection → fetch_arrow_table() → to_pandas().
• What changes. The connection object and the fetch method. The SQL is identical.
• What you gain. Columnar transport, parallel endpoints, no per-cell decode.

Question. Migrate a slow pandas/ODBC extract to ADBC Flight SQL with minimal code change.

Input.

Aspect	ODBC path	ADBC Flight SQL path
connect	pyodbc.connect(DSN)	flight_sql.connect(uri, db_kwargs=...)
fetch	pd.read_sql(sql, conn)	cur.fetch_arrow_table().to_pandas()
wire	row-major cells	Arrow IPC (columnar)
parallelism	one cursor	endpoint fan-out (automatic)

Code.

# migrate_extract.py — JDBC/ODBC extract -> ADBC Flight SQL, same SQL
SQL = "SELECT id, region, amount, ts FROM sales WHERE ts >= '2026-08-01'"

# --- BEFORE: pandas over ODBC (row cursor) ---
def extract_odbc():
    import pyodbc, pandas as pd
    conn = pyodbc.connect(DSN)
    return pd.read_sql(SQL, conn)             # slow on large result sets

# --- AFTER: ADBC Flight SQL (columnar, parallel) ---
def extract_flight():
    import adbc_driver_flightsql.dbapi as flight_sql
    conn = flight_sql.connect(
        "grpc+tls://engine.internal:8815",
        db_kwargs={"adbc.flight.sql.authorization_header": "Bearer <token>"},
    )
    cur = conn.cursor()
    cur.execute(SQL)                          # identical SQL
    table = cur.fetch_arrow_table()           # pyarrow.Table, no per-cell decode
    cur.close(); conn.close()
    return table.to_pandas()                  # convert at the edge if you must

if __name__ == "__main__":
    import time
    for name, fn in (("odbc", extract_odbc), ("flight", extract_flight)):
        t0 = time.perf_counter(); df = fn()
        print(f"{name:<7} {len(df):>10,} rows in {time.perf_counter()-t0:6.2f}s")

Step-by-step explanation.

1. The SQL string is untouched — migration is a transport change, not a query rewrite. This is the whole selling point of ADBC-over-Flight-SQL as a JDBC/ODBC replacement.
2. The connection swaps pyodbc.connect(DSN) for flight_sql.connect(uri, db_kwargs=...), moving auth into an authorization-header kwarg and the wire to grpc+tls.
3. cur.fetch_arrow_table() returns a columnar pyarrow.Table with no per-cell decode, and ADBC transparently fans do_get across the endpoints the server returned — you get parallelism for free.
4. to_pandas() is optional and belongs at the edge: if your downstream is Arrow/Polars/DuckDB, skip it and keep the data columnar all the way. Converting to pandas re-introduces a columnar→row-ish materialization you may not need.
5. The timing harness makes the win visible: identical SQL, identical row count, a fraction of the wall-clock — because the transport tax is gone.

Output.

Path	Rows	Wall-clock	Note
odbc	8,420,331	31.7 s	row cursor
flight	8,420,331	1.9 s	columnar + parallel endpoints

Rule of thumb. Migrate JDBC/ODBC extracts to ADBC Flight SQL by swapping only the connection and fetch calls; keep the SQL identical, and defer any to_pandas() to the very edge so the pipeline stays columnar.

Worked example — connecting to Dremio over Flight SQL and reading in parallel

Detailed explanation. A concrete, common deployment is "query a Dremio lakehouse from Python at Arrow speed." Dremio exposes a Flight SQL endpoint; the ADBC driver connects, and reads fan across Dremio's executors via multiple endpoints. Walk through the connection, the query, and confirming the parallel fetch.

• Endpoint. Dremio Flight SQL, commonly grpc+tls://<dremio-host>:32010.
• Auth. Username/password or a personal access token via the driver's auth kwargs.
• Parallelism. Dremio returns multiple endpoints for large results; ADBC redeems them concurrently.
• Result. A pyarrow.Table assembled from parallel streams.

Question. Connect to a Dremio Flight SQL endpoint, run an aggregate, and confirm the read used multiple endpoints.

Input.

Setting	Value
endpoint	grpc+tls://dremio.internal:32010
auth	PAT via adbc.flight.sql.authorization_header
query	SELECT region, sum(amount) FROM sales GROUP BY region
expectation	multiple endpoints → parallel do_get

Code.

# dremio_flight.py — read a Dremio lakehouse over Flight SQL
import adbc_driver_flightsql.dbapi as flight_sql
import pyarrow.flight as flight

DREMIO = "grpc+tls://dremio.internal:32010"
PAT = "Bearer dremio_pat_xxx"

# --- high-level: ADBC handles endpoint fan-out for you ---
def read_dremio():
    conn = flight_sql.connect(
        DREMIO,
        db_kwargs={
            "adbc.flight.sql.authorization_header": PAT,
            "adbc.flight.sql.client_option.tls_root_certs": "/etc/ssl/certs/ca.pem",
        },
    )
    cur = conn.cursor()
    cur.execute("SELECT region, sum(amount) AS total FROM sales GROUP BY region")
    table = cur.fetch_arrow_table()
    cur.close(); conn.close()
    return table

# --- low-level: prove multiple endpoints came back (parallel reads) ---
def inspect_parallelism(sql):
    client = flight.FlightClient(DREMIO,
                                 tls_root_certs=open("/etc/ssl/certs/ca.pem","rb").read())
    # (Dremio auth handshake omitted; use the driver in real code.)
    info = client.get_flight_info(flight.FlightDescriptor.for_command(sql.encode()))
    print(f"endpoints returned: {len(info.endpoints)}")   # >1 => parallel
    for i, ep in enumerate(info.endpoints):
        locs = [l.uri.decode() for l in ep.locations] or ["<same server>"]
        print(f"  endpoint {i}: {locs}")

if __name__ == "__main__":
    tbl = read_dremio()
    print(tbl.to_pandas())
    inspect_parallelism("SELECT * FROM sales WHERE ts >= '2026-08-01'")

Step-by-step explanation.

1. The connection targets Dremio's Flight SQL endpoint on grpc+tls://…:32010, with the personal access token in the authorization header and the CA cert supplied so TLS verifies — this is the standard Dremio-over-Flight setup.
2. cur.execute(...) + fetch_arrow_table() runs the aggregate and returns Arrow. ADBC packs the CommandStatementQuery, calls get_flight_info, and fans do_get across whatever endpoints Dremio returns — the parallelism is invisible but real.
3. The inspect_parallelism helper drops to the raw client to show the endpoints: for a large scan, Dremio returns more than one FlightEndpoint, each potentially pointing at a different executor, which is the distributed-read pattern from section 2.
4. The to_pandas() on the aggregate is fine because the result is tiny (one row per region); for the large scan you would keep it as Arrow and process columnarly.
5. This is the everyday senior use: a lakehouse engine as a Flight SQL server, Python as an ADBC client, columnar and parallel end to end — the concrete payoff of everything in the first four sections.

Output.

      region     total
0       west  128934.11
1       east   99213.44
2      north   77120.09
endpoints returned: 3
  endpoint 0: ['grpc+tls://executor-1.internal:32010']
  endpoint 1: ['grpc+tls://executor-2.internal:32010']
  endpoint 2: ['grpc+tls://executor-3.internal:32010']

Rule of thumb. Treat lakehouse engines like Dremio as Flight SQL servers and connect with ADBC; inspect info.endpoints when you need to confirm a read is parallel, and keep large results as Arrow rather than converting to pandas.

Common beginner mistakes.

• Pitching Flight for tiny result sets. The win is asymptotic in result size; small queries are dominated by fixed overhead.
• Forgetting gRPC-unfriendly infrastructure. L7 load balancers and idle-timeout proxies can kill long streams; you need HTTP/2-aware, stream-tolerant networking.
• Converting to pandas immediately. If downstream is columnar, stay Arrow; a reflexive to_pandas() throws away part of the win.
• Assuming Flight replaces the database. It replaces the transport; you still need an engine behind it.

Senior interview question on Flight in the stack

A senior interviewer might ask: "We're standardizing data access across a Dremio lakehouse, a Python ML team, a BI tool, and a customer-facing web app. Propose where Arrow Flight / Flight SQL fits, quantify the expected win, and — critically — tell me where you would not use Flight and what you'd use instead. Defend the boundaries."

Solution Using Flight SQL for analytical clients and a deliberate boundary for the web app

# access_strategy.py — Flight where it wins, something else where it doesn't.

STRATEGY = {
    # WIN: large columnar extracts, engine-to-client, parallel reads.
    "python_ml": {
        "transport": "ADBC Flight SQL",
        "endpoint":  "grpc+tls://dremio.internal:32010",
        "why":       "millions of rows to Arrow; parallel endpoints; no decode",
        "expected":  "~10-30x faster than the old ODBC extract",
    },
    "bi_tool": {
        "transport": "JDBC/ODBC-over-Flight-SQL shim",
        "endpoint":  "grpc+tls://dremio.internal:32010",
        "why":       "unmodified BI tool; DSN swap; most of the win",
        "expected":  "big win on large dashboards; small queries unchanged",
    },
    # BOUNDARY: browser client cannot speak arbitrary gRPC.
    "web_app": {
        "transport": "REST/JSON (or Arrow-over-HTTP) via a thin API",
        "endpoint":  "https://api.internal/v1/query",
        "why":       "browsers need gRPC-Web/translation; results are tiny per request",
        "expected":  "Flight would add complexity for no throughput gain here",
    },
    # BOUNDARY: OLTP point lookups stay on a normal driver.
    "point_lookups": {
        "transport": "regular Postgres driver (JDBC/psycopg)",
        "endpoint":  "the OLTP database",
        "why":       "single-row fetches; serialization tax ~0; gRPC overhead > benefit",
        "expected":  "no change; Flight is the wrong tool",
    },
}

def defend(consumer):
    s = STRATEGY[consumer]
    print(f"{consumer:<14} -> {s['transport']:<32} | {s['why']}")

if __name__ == "__main__":
    for c in STRATEGY:
        defend(c)

Boundary rationale
==================
Flight SQL for:  python_ml, bi_tool        (large columnar result sets)
NOT Flight for:  web_app  -> REST/Arrow-HTTP (browsers can't do raw gRPC)
                 point_lookups -> normal driver (tiny results; no tax to remove)

The senior signal is the second block: naming where Flight LOSES.

Step-by-step trace.

Step	Consumer	Decision	Reason
1	Python ML	ADBC Flight SQL	huge Arrow extracts, parallel endpoints
2	BI tool	JDBC/ODBC-over-Flight-SQL shim	unmodified tool, DSN swap, most of the win
3	Web app	REST/Arrow-over-HTTP, not Flight	browsers can't speak arbitrary gRPC
4	OLTP point lookups	normal DB driver, not Flight	single-row fetches have no tax to remove
5	all analytical	one Flight SQL endpoint on Dremio	single columnar transport surface

The strategy routes the two large-columnar consumers (Python ML, BI) onto Flight SQL against the Dremio endpoint for the order-of-magnitude win, and deliberately keeps the browser app on REST/Arrow-over-HTTP and the OLTP point lookups on a normal driver. Naming those two boundaries — and why Flight loses there (no gRPC in browsers, no serialization tax on single rows) — is the senior signal the question is fishing for.

Output:

Consumer	Chosen transport	Flight?	Expected effect
Python ML	ADBC Flight SQL	yes	~10–30× faster extracts
BI tool	JDBC/ODBC-over-Flight-SQL	yes	big win on large dashboards
Web app	REST / Arrow-over-HTTP	no	avoids gRPC-in-browser complexity
OLTP lookups	normal DB driver	no	no tax to remove; keep it simple

Why this works — concept by concept:

• Flight as transport, not database — every "yes" routes an analytical, large-result consumer onto the columnar pipe against the same Dremio Flight SQL endpoint, so there is one transport surface to secure and operate.
• ADBC for the native client — the Python ML team gets Arrow results and parallel endpoint fan-out, preserving the full win; the shim driver gives the BI tool most of it with zero code change.
• Browser boundary — browsers cannot speak arbitrary gRPC, and per-request web results are tiny, so REST or Arrow-over-HTTP is correct; forcing Flight here adds gRPC-Web complexity for no throughput gain.
• OLTP boundary — the serialization tax Flight removes is proportional to result size, so single-row lookups have nothing to gain and pay fixed gRPC overhead instead. Keep them on a normal driver.
• Cost — one Flight SQL endpoint serves both analytical consumers at ~10–30× the old extract speed; the two boundaries cost nothing and avoid misapplied complexity. The defensible answer is not "Flight everywhere" but "Flight exactly where large columnar results move, and a plain driver everywhere else."

Optimization
Topic — optimization
Optimization problems on transport and throughput trade-offs

Practice →

Design
Topic — design
Design problems on data-access architecture

Practice →

---

Cheat sheet — Arrow Flight recipes

• The one-line thesis. JDBC/ODBC are row-major cursors, so large result sets pay a serialize-transpose-deserialize tax that dwarfs query time; arrow flight makes the wire be the Arrow columnar layout, removing transposition and per-cell serialization on both ends. The win is asymptotic in result size — huge on wide analytical extracts, negligible on single-row lookups.
• The two planes. Control plane = gRPC/Protobuf metadata (descriptors, schemas, tickets, endpoints, actions). Data plane = Arrow IPC record-batch streams (FlightData). Planning is cheap and chatty-tolerant; the bulk transfer is the arrow ipc stream.
• The verb map. GetFlightInfo(descriptor) → FlightInfo (plan). DoGet(ticket) → stream (read). DoPut(descriptor, stream) (write). DoExchange (bidirectional). DoAction/ListActions (extensibility, and the substrate of Flight SQL). ListFlights/GetSchema (discovery).
• The noun map. FlightDescriptor = what (for_path or for_command). FlightInfo = schema + endpoints. FlightEndpoint = ticket + locations. Ticket = opaque redeemable handle (keep it small). Location = gRPC URI (empty = same server).
• Parallel reads recipe. Return many endpoints from get_flight_info; on the client, fan do_get across them with a thread pool (one FlightClient per stream), then pa.concat_tables. No cross-endpoint order guarantee — sort or encode order into the ticket.
• Distributed reads recipe. Coordinator plans into per-worker partitions; each endpoint's Location points at the worker, its Ticket carries the serialized sub-plan. Client does one GetFlightInfo to the coordinator, then parallel DoGet straight to workers — coordinator moves metadata only, never bulk data.
• Raw Flight vs Flight SQL. Raw Flight = bespoke descriptors/tickets, for non-SQL data services (feature stores, inference, partition servers). flight sql = standard Protobuf command messages (CommandStatementQuery, CommandGetTables, prepared statements, transactions) over the same verbs, so off-the-shelf ADBC/JDBC/ODBC drivers connect. Pick Flight SQL the moment you have heterogeneous or third-party clients.
• Flight SQL client recipe. adbc_driver_flightsql.dbapi.connect(uri, db_kwargs={"adbc.flight.sql.authorization_header": "Bearer …"}) → cursor.execute(sql, parameters=…) → fetch_arrow_table(). Bind parameters (? + parameters=); never string-format SQL. ADBC handles endpoint fan-out for you.
• Server skeleton. Subclass FlightServerBase; override get_flight_info (plan), do_get (serve), do_put (ingest), do_action (extend). Serve with RecordBatchStream(table) for in-memory results, GeneratorStream(schema, gen) for results larger than RAM — never buffer a huge table to serve it.
• Auth + TLS recipe. Auth in a ServerMiddlewareFactory.start_call that validates the authorization header and raises FlightUnauthenticatedError — protects every RPC uniformly. TLS via tls_certificates=[(cert,key)] + grpc+tls:// location; client verifies with tls_root_certs and injects the token via ClientMiddleware. Add client certs for mTLS on service-to-service.
• Ecosystem map. dremio flight = Flight SQL endpoint (port 32010) for lakehouse reads. Spark = Flight sources/sinks. ADBC-over-Flight-SQL = the jdbc odbc replacement for Arrow pipelines; JDBC/ODBC-over-Flight-SQL shim drivers migrate legacy tools with a URL/DSN swap. InfluxDB 3.x, Ballista, and others speak Flight SQL too.
• When NOT to use Flight. Tiny result sets / OLTP point lookups (no tax to remove, fixed gRPC overhead). Browser/JS clients (no raw gRPC — use REST or Arrow-over-HTTP). Non-Arrow consumers that immediately need rows (transposition re-appears at the edge). gRPC-hostile networks (L7 LBs, idle-timeout proxies break long streams — need HTTP/2-aware, stream-tolerant infra).
• Benchmark framing. ~10–50× over ODBC on large result sets, driven entirely by eliminating transposition + per-cell serialization; the multiple grows with result size and schema width and shrinks toward 1× on small results. Cite the mechanism, not a fixed number.

Frequently asked questions

What is Arrow Flight in one sentence?

arrow flight is a gRPC-based, Arrow-native transport protocol for moving large datasets between systems, in which the wire format is the Apache Arrow columnar layout — so a producer streams its in-memory columns directly and a consumer receives columns with no per-cell serialization and no row-to-column transposition, eliminating the exact cost that makes JDBC/ODBC slow on big result sets. It splits every transfer into a lightweight gRPC/Protobuf control plane (descriptors, schemas, tickets, endpoints) and an Arrow IPC data plane (streamed record batches), and it exposes parallelism as a first-class concept: one logical result can be spread across many endpoints that a client reads concurrently. It is not a database or a query engine — it is the high-throughput transport layer that engines like Dremio, Spark, and InfluxDB use to hand columnar data to clients far faster than a row-oriented cursor can.

How is Arrow Flight faster than JDBC/ODBC?

The entire advantage comes from removing the row-major middle. A JDBC/ODBC result forces a columnar engine to transpose its columns into rows, serialize every cell into the driver's wire format, ship it, and let the client deserialize and (for Arrow/pandas/Polars consumers) re-columnarize — that is O(cells) work performed twice, and on a ten-million-row wide result it can be forty seconds of pure overhead on top of a sub-second query. arrow flight sends the engine's columns as an arrow ipc stream: the received bytes already carry the Arrow buffer layout, so materializing a pyarrow.Table is buffer bookkeeping rather than a decode pass, and reads can fan across parallel endpoints to fill the network pipe. Published benchmarks put the gap at roughly 10–50× on large result sets; the multiple grows with result size and schema width because the per-cell tax you eliminate scales with both.

What is the difference between Arrow Flight and Flight SQL?

Raw arrow flight is the transport, but it leaves the meaning of descriptors, tickets, and actions up to each server — so every producer speaks a private dialect and each client must be written against that specific server. flight sql standardizes those meanings into a documented protocol of Protobuf command messages — CommandStatementQuery to run SQL, CommandGetTables / CommandGetCatalogs for catalog metadata, prepared-statement and transaction actions — all carried over the same Flight verbs. The payoff is that a single Flight SQL driver (ADBC, or the JDBC/ODBC-over-Flight-SQL shims) can talk to any compliant server the way one JDBC driver talks to any JDBC database. Use raw Flight to build a bespoke, non-SQL data service where only your client and server must agree; use Flight SQL when arbitrary or third-party clients need a database-shaped interface, which is what makes Flight a real jdbc odbc replacement.

Can Arrow Flight replace JDBC and ODBC?

For large analytical result sets, yes — through ADBC-over-Flight-SQL, which is the Arrow-native database API whose Flight SQL driver returns columnar pyarrow.Table results with transparent endpoint fan-out. Migrating a slow pandas.read_sql extract is usually a connection-and-fetch swap with identical SQL: flight_sql.connect(...) then cursor.fetch_arrow_table(). For legacy tools that only speak JDBC/ODBC, the official JDBC/ODBC-over-Flight-SQL shim drivers let them connect with only a URL or DSN change, capturing most of the throughput win because the bulk transfer stays Arrow and the row-shaped API only appears at the final hop. Flight does not replace JDBC/ODBC everywhere, though — single-row OLTP lookups have no serialization tax to remove, and browser clients can't speak raw gRPC — so the honest answer is "Flight/ADBC replaces JDBC/ODBC for large columnar reads, and you keep normal drivers for chatty small queries."

When should I NOT use Arrow Flight?

Avoid arrow flight when its core advantage — eliminating row-transposition and per-cell serialization on large results — does not apply. Tiny result sets and OLTP point lookups gain nothing because there is almost no serialization tax to remove, and the fixed gRPC/handshake overhead can make Flight slower than a normal driver. Browser and JavaScript clients cannot speak arbitrary gRPC, so a web front-end needs gRPC-Web or a REST/Arrow-over-HTTP translation layer that erodes the benefit — usually not worth it for the small per-request payloads a UI fetches. Consumers that immediately need row objects (an ORM hydrating entities) re-introduce a transposition at the very edge, giving back much of the win. And Flight depends on gRPC-aware infrastructure: L7 load balancers, idle-timeout proxies, and some corporate egress filters can break long-lived HTTP/2 streams, so if you can't guarantee stream-tolerant, HTTP/2-end-to-end networking, plan for broken transfers or choose a different transport.

How do I add authentication and TLS to a Python Flight server?

Put authentication in a ServerMiddlewareFactory whose start_call reads the incoming authorization header, validates the bearer token (in production, verify a JWT signature and claims — not a static set), and raises FlightUnauthenticatedError on failure, so every RPC is rejected before it reaches your method and no per-method check can be forgotten. Wire it with FlightServerBase(location, middleware={"auth": AuthFactory()}, tls_certificates=[(cert, key)]) and bind a grpc+tls:// location so the Arrow buffers — which are your raw data — are encrypted on the wire. On the client, mirror it with a ClientMiddleware whose sending_headers injects authorization: Bearer <token> and pass tls_root_certs=<ca.pem> so the client verifies the server's certificate; for service-to-service, add client certificates for mutual TLS on top of the token. The discipline is to keep auth and encryption entirely in the middleware and constructor so the four overridden methods contain only business logic.

Practice on PipeCode

• Drill the streaming practice library → for the record-batch, backpressure, and partitioned-read problems that Arrow Flight producers and consumers live on.
• Rehearse on the ETL practice library → for the bulk-extract, bulk-ingest, and warehouse-to-client transfer patterns where a columnar transport replaces a row cursor.
• Sharpen the systems axis with the design practice library → for the transport-choice, distributed-read-fan-out, and protocol-standardization questions senior interviewers open with when Flight is on the table.
• Layer in the optimization practice library → to cement the throughput-vs-latency, serialization-cost, and endpoint-parallelism trade-offs that decide when Flight wins and when it is the wrong tool.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the "engine time vs transport time" decomposition and the Flight-vs-JDBC/ODBC decision matrix against real graded inputs.

Lock in Arrow Flight muscle memory

Docs explain the protocol. PipeCode drills explain the decision — when the row-major cursor is the real bottleneck, when to reach for raw Flight versus Flight SQL, how to fan reads across parallel endpoints, when ADBC-over-Flight-SQL replaces your JDBC extract, and when Flight is the wrong tool entirely. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production transport trade-offs senior data engineers actually face.

Practice streaming problems →
Practice design problems →