Most data serving stacks have a database server: PostgreSQL, MySQL, or a managed cloud equivalent. You start the server, the API connects to it over a network socket, queries happen over that connection. For an application where the data is mostly read-only and fits on a single machine, that’s a lot of infrastructure for what you’re getting.
DuckDB is a different model. It’s a library — an in-process database that runs inside your Python application, the same way SQLite does, but designed for analytical workloads rather than transactional ones. It reads Parquet files directly, handles complex SQL including window functions and multi-column aggregations, and materializes fast enough that querying a multi-million-row gold store takes milliseconds. The flight disruption platform uses it as the read layer for the serving API: dbt writes materialized mart tables to a .duckdb file, and FastAPI queries that file directly.
What You Need Coming In
- Basic Python — importing libraries, classes, context managers
- Comfortable with SQL:
SELECT,WHERE,JOIN,GROUP BY - No prior DuckDB knowledge needed
Opening a Read-Only Connection
The serving layer opens the gold DuckDB in read-only mode at startup:
import duckdb
# Read-only: the serving layer is a consumer, not a writer
con = duckdb.connect("path/to/gold.duckdb", read_only=True)read_only=True does two things. First, it prevents the API from accidentally modifying the gold store — if a query somehow contained a DROP TABLE or UPDATE, DuckDB would reject it. Second, it signals intent to anyone reading the code: this connection is a pure consumer of data that another process (the dbt pipeline) produced.
DuckDB also opens Parquet files directly without materialization:
# DuckDB can query Parquet files as if they were tables
result = duckdb.query("SELECT * FROM 'path/to/silver/flights/*.parquet' LIMIT 5")The platform uses this for exploratory analysis during pipeline development — you can run SQL over the raw silver layer without needing to materialize anything first.
Thread Safety in a FastAPI Context
FastAPI runs synchronous route handlers in a thread pool. Multiple requests can be in flight simultaneously, each executing in a different thread. A single DuckDB connection is not thread-safe — two threads writing query state to the same connection simultaneously will corrupt it.
The GoldStore class wraps the connection with a threading lock:
import threading
import duckdb
from contextlib import contextmanager
class GoldStore:
def __init__(self, duckdb_path: str):
self._con = duckdb.connect(duckdb_path, read_only=True)
self._lock = threading.Lock()
@contextmanager
def _cursor(self):
with self._lock:
yield self._con
def close(self) -> None:
with self._lock:
self._con.close()threading.Lock() ensures only one thread uses the connection at a time. The @contextmanager wraps it so calling code can write with self._cursor() as con: rather than managing lock acquire/release manually.
For this application’s query volume — a small demo, not a production service handling thousands of requests per second — one serialized connection is fine. Each query runs in tens of milliseconds, so the serialization overhead is negligible. If the API needed to handle high concurrency, the pattern scales to a small connection pool: create N connections, hand them out to threads as needed, return them on completion.
The Queries
The route reliability query is a straightforward lookup:
def route_reliability(self, origin: str, dest: str) -> dict | None:
with self._cursor() as con:
row = con.execute(
"""
SELECT origin, dest, delay_rate, flights, avg_delay_min
FROM agg_route_reliability
WHERE origin = ? AND dest = ?
""",
[origin, dest],
).fetchone()
carriers = con.execute(
"""
SELECT carrier, delay_rate
FROM agg_route_carrier_reliability
WHERE origin = ? AND dest = ?
ORDER BY flights DESC
""",
[origin, dest],
).fetchall()
if row is None:
return None
return {
"delay_rate": round(float(row[2]), 6),
"flights": int(row[3]),
"by_carrier": [{"carrier": c[0], "delay_rate": round(float(c[1]), 6)} for c in carriers],
}The ? placeholders are parameterized queries — the values from [origin, dest] are passed separately from the SQL string, which prevents SQL injection. DuckDB uses the same ? placeholder style as SQLite.
.fetchone() returns a single row as a tuple, or None if no rows matched. .fetchall() returns a list of tuples. Both are pure Python — no ORM, no schema definition needed for reading.
The airport historical query shows how DuckDB handles multiple queries on the same connection within a lock acquisition:
def airport_historical(self, iata: str) -> dict:
with self._cursor() as con:
overall = con.execute(
"SELECT overall_delay_rate FROM agg_airport_reliability WHERE origin = ?",
[iata],
).fetchone()
by_hour = con.execute(
"SELECT hour, delay_rate FROM agg_airport_hourly "
"WHERE origin = ? ORDER BY hour",
[iata],
).fetchall()
worst = con.execute(
"SELECT dest, delay_rate FROM agg_airport_worst_routes "
"WHERE origin = ? ORDER BY delay_rate DESC",
[iata],
).fetchall()
return {
"overall_delay_rate": float(overall[0]) if overall else None,
"by_hour": [{"hour": int(h[0]), "delay_rate": float(h[1])} for h in by_hour],
"worst_routes": [{"dest": w[0], "delay_rate": float(w[1])} for w in worst],
}Three queries, one lock acquisition. All three run while the lock is held, which means no other thread can sneak in between them. For this use case that’s correct behavior — these three queries are logically one “airport data fetch” and should run as a unit.
Window Functions and QUALIFY
The most complex query in the serving layer generates demo presets — four high-traffic routes with the dominant carrier for each:
rows = con.execute(
"""
SELECT r.origin, r.dest, rc.carrier
FROM agg_route_reliability r
LEFT JOIN agg_route_carrier_reliability rc
ON r.origin = rc.origin AND r.dest = rc.dest
QUALIFY ROW_NUMBER() OVER (
PARTITION BY r.origin, r.dest ORDER BY rc.flights DESC NULLS LAST
) = 1
ORDER BY r.flights DESC
LIMIT ?
""",
[limit],
).fetchall()QUALIFY is a DuckDB (and BigQuery, Snowflake) extension that filters on window function results without a subquery. The equivalent in standard SQL requires a subquery: SELECT * FROM (...) WHERE rn = 1. QUALIFY does it inline.
ROW_NUMBER() OVER (PARTITION BY r.origin, r.dest ORDER BY rc.flights DESC) assigns 1 to the carrier with the most flights on each route. QUALIFY ... = 1 keeps only that row — one row per route, with the dominant carrier. Without this, each route would appear once per carrier, and the caller would have to deduplicate.
NULLS LAST handles routes where rc.carrier is null (no carrier data in the breakdown) — those rows get ranked last rather than first.
Route Distance with MEDIAN
The serving layer also uses DuckDB to look up median route distance from the features table:
row = con.execute(
"""
SELECT median(distance), median(crs_elapsed_time)
FROM fct_flight_features
WHERE origin = ? AND dest = ?
""",
[origin, dest],
).fetchone()MEDIAN() is a first-class aggregate in DuckDB — no percentile approximation, no PERCENTILE_CONT syntax. It returns the actual median over all flights on the route. SQLite doesn’t have a built-in median aggregate; PostgreSQL requires PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY distance). DuckDB’s analytical SQL is significantly closer to what data scientists write in pandas or R.
Why Not SQLite
SQLite is the obvious comparison — also in-process, also zero-server, also widely used for embedding in applications. The differences matter for analytics:
SQLite is designed for transactional workloads: many small reads and writes, row-at-a-time processing, good for storing application state. DuckDB is designed for analytical workloads: a few large reads, column-at-a-time processing, good for aggregating millions of rows. On a query like SELECT AVG(dep_del15) FROM flights GROUP BY origin over 10 million rows, DuckDB is typically 10-100x faster than SQLite because it processes data in columnar batches rather than row by row.
DuckDB also reads Parquet natively, which SQLite doesn’t. For a pipeline that already writes Parquet as its storage format, being able to query that Parquet directly without ETL into another format is a real operational simplification.
The read_only=True connection pattern works the same in both databases. The SQL syntax is similar. The main thing DuckDB adds is analytical query performance and the richer aggregate function set — which are exactly what the flight reliability API needs.
