Prompt and context
Several analytics clients need access to different SQL engines. Existing JDBC/ODBC paths create row-column conversions and connection-pool pressure for large results, so the team is considering Arrow Flight SQL for columnar transfer. Design the service from SQL request to Flight data stream, including metadata, result endpoints, authentication, backpressure, cancellation, audit, and isolation.
What the interviewer is testing
- Whether you understand Flight SQL’s boundary above Flight RPC and the Arrow memory format.
- Whether you can distinguish GetFlightInfo, GetSchema, DoGet, DoPut, and DoAction.
- Whether you design query handles, result partitions, flow control, cancellation, and retries.
- Whether you handle SQL authorization, tenant resources, sensitive columns, and audit.
- Whether you explain JDBC/ODBC adapters, capability negotiation, and observability.
Questions to clarify first
- Are clients mainly interactive queries, batch exports, or streaming writes?
- What are result size, concurrent queries, time-to-first-byte, and tenant quotas?
- Do all backends execute Arrow natively, or must the gateway convert results?
- Are OAuth, mTLS, row and column policies, or cross-region access required?
- Which database, memory, and object-storage resources must cancellation reclaim?
A 30-second answer
“I would split the service into authentication and tenant policy, SQL planning, Flight SQL protocol adaptation, and a result-stream gateway. The client calls GetFlightInfo for a query handle and endpoints, then uses DoGet to pull Arrow RecordBatches; metadata uses GetSchema, while writes or parameterized actions use DoPut or Action semantics. The gateway limits scans, concurrency, and retention, propagates downstream backpressure to execution, and supports cancellation. Every handle carries identity, policy, plan version, and audit data; sensitive columns are removed during planning and engine differences are exposed through capability negotiation.”
Step-by-step deep dive
1. Separate protocol and execution boundaries
Flight SQL defines Protobuf commands for SQL metadata, queries, and prepared statements, reusing Flight RPCs such as GetFlightInfo, GetSchema, and DoGet. The gateway owns identity, policy, lifecycle, and flow control; adapters translate a logical plan into engine SQL and Arrow batches.
2. Design the query lifecycle
Authenticate and resolve tenant capabilities before creating an unguessable query handle. GetFlightInfo returns schema, endpoints, and expiry; DoGet reads RecordBatches from those endpoints. Track planned, running, draining, cancelled, failed, and expired states so retries do not create duplicate executions.
{
"queryHandle": "q_7f2a",
"schemaVersion": 3,
"endpoints": [{"ticket": "t_01", "location": "grpc://flight-2"}],
"expiresAt": "2026-08-01T13:00:00Z",
"cancelToken": "c_7f2a"
}3. Handle columnar results and backpressure
The executor produces RecordBatches at a target size, while the gateway controls prefetch and memory watermarks from DoGet consumption. Do not materialize the whole result at the gateway; pause or spill for slow clients. A cross-node endpoint may point to the worker holding a partition, but the gateway still validates ticket, tenant, and expiry.
4. Design cancellation, failure, and retry
Map one cancellation token to database cancel, worker streams, and temporary object-storage files. After a disconnect, only idempotent reads with a handle may retry unconfirmed batches; writes need explicit transaction or Action semantics, not a guessed repeated DoPut. Failure responses expose classified status and trace ID without SQL or sensitive data.
5. Enforce authorization and tenant governance
Authentication may use mTLS, OAuth, or a Flight authorization header, with credentials sent only over TLS. Map users, roles, and tenants to database identity, allowed catalogs, schemas, tables, row filters, column masks, and resource limits. Apply column pruning and parameter binding during planning; never concatenate user SQL into an administrator connection.
6. Build observability and compatibility
Record query handle, tenant, database, plan version, batch count, bytes, first-batch latency, cancel reason, and peak resources. Segment metrics by tenant and engine, and keep raw data out of logs. Offer JDBC/ODBC driver adapters while documenting semantic differences; expose support through GetSqlInfo, GetCatalogs, and related metadata calls.
Example of a strong answer
“A Flight SQL service combines SQL semantics with Arrow columnar streams. The client gets schema, ticket, endpoints, and expiry from GetFlightInfo, then pulls RecordBatches with DoGet. During planning, the gateway enforces identity, tenant, row and column policy, and resource budgets. Results stay streaming, and downstream backpressure reaches execution; slow clients can pause or spill. Cancellation must terminate the database, worker, and temporary files. Idempotent read handles can retry, while writes require explicit transaction semantics. Every query has audit and trace IDs, and capability negotiation handles engine and JDBC/ODBC differences.”
Common mistakes
- Treating Flight SQL as a JSON HTTP API → columnar batches and endpoint semantics are lost → design around Flight SQL RPC lifecycle.
- Caching a complete result at the gateway → large queries exhaust memory → stream batches and propagate backpressure.
- Authenticating only at connection setup → row, column, and tenant policies are missing → enforce policy during planning.
- Restarting every query after disconnect → database load and duplicate writes grow → retry reads by handle and writes by transaction or Action semantics.
- Ignoring capabilities → clients assume every SQL feature exists → negotiate with metadata such as GetSqlInfo.
Follow-ups and responses
Why split GetFlightInfo from DoGet?
GetFlightInfo returns schema, tickets, endpoints, and execution information; DoGet carries the data stream. This allows result partitions on different workers and lets clients read endpoints in parallel or lazily.
How do you limit a slow tenant query?
Set scan-byte, concurrency, memory, and wall-clock quotas during planning, then sample execution and cancel or degrade when limits are exceeded. Meter quotas by tenant and engine so a large tenant cannot starve shared workers.
Can a DoPut retry duplicate writes?
Yes. Writes need a transaction ID, batch sequence, and idempotency constraint, with an explicit commit point and retry result. If commit status is unknown, return a reconciliation state instead of blindly replaying.
Why not let clients connect directly to databases?
Direct access makes shared authorization, auditing, throttling, and cross-engine behavior difficult, and exposes the database network boundary. A Flight SQL gateway centralizes those controls while retaining Arrow-native transport efficiency.