Duckdb

DuckDB — embedded columnar OLAP, no server

DuckDB is an in-process analytical (OLAP) database: it links into your process like SQLite, but stores data column-by-column and vectorizes execution for aggregates, joins, and window functions. There is no server, no port, no daemon — you pip install duckdb (or drop one CLI binary) and query. Its killer move is reading Parquet/CSV/JSON/Arrow in place, without a load step, so a folder of files becomes a table.

The fact that drives every routing decision below: DuckDB is one writer, many readers, single process. It is brilliant for analysis on one machine and wrong for multi-user serving or transactional app writes.

Latest stable is v1.5.3 (released 2026-05-20). Pin the LTS line (v1.4.x; v1.4.4 LTS shipped 2026-01-26) for anything long-lived — LTS gets ~1 year of patches and a stable storage format. Use 1.5.x for greenfield exploration.

Is DuckDB the tool? (decide first)

The two you will confuse most: DuckDB vs ClickHouse is embedded-single-node vs server-distributed — under ~10GB on one box DuckDB usually wins; pick ClickHouse when many people query concurrently. DuckDB vs SQLite is same niche, opposite workload — both embedded single-file, but SQLite is row-store OLTP and DuckDB is column-store OLAP. Don't run your app's writes through DuckDB.

Install & version

bashCopy code
# Python (replacement-scan + relational API)
pip install 'duckdb==1.4.4'        # LTS, for long-lived projects — stable storage format + patches
pip install duckdb                  # current stable, for greenfield exploration

# CLI (single static binary)
curl https://install.duckdb.org | sh   # or: brew install duckdb
duckdb -version

Why pin LTS for production: the on-disk .duckdb format and extension ABI are stable within an LTS line, so a routine upgrade won't strand a persisted database or break an installed extension mid-project.

Query files directly — the killer feature

Do not load a file into pandas just to query it. Point DuckDB at the path and let it scan only the columns and row groups it needs (Parquet metadata pushdown). A bare string path is a replacement scan, so FROM 'data/*.parquet' works without naming a reader.

pythonCopy code
import duckdb

# BAD: read the whole file into RAM, then aggregate in pandas
import pandas as pd
df = pd.read_parquet("sales/")          # pulls every column of every file into memory
out = df.groupby("region")["amount"].sum()

# GOOD: scan in place, only the two needed columns ever touch memory
out = duckdb.sql("""
    FROM 'sales/*.parquet'
    SELECT region, sum(amount) AS revenue
    GROUP BY ALL
    ORDER BY revenue DESC
""").df()

Readers and globs you will actually use:

sqlCopy code
SELECT * FROM read_parquet('s3://bkt/y=*/m=*/*.parquet', filename = true);  -- glob + source col
SELECT * FROM read_csv_auto('events.csv');         -- sniff delimiter/types/header
SELECT * FROM read_csv('raw.csv', header = false, types = {'id': 'BIGINT'});  -- when sniffing is wrong
SELECT * FROM read_json_auto('logs/*.ndjson');     -- newline-delimited or array JSON

filename = true adds a filename column — essential when a glob mixes partitions and you need to know which file a row came from.

In-memory vs persistent

pythonCopy code
con = duckdb.connect()                    # in-memory: default, gone when the process exits
con = duckdb.connect("analytics.duckdb")  # single file, created if absent; extension is not significant

Persist when: the dataset is reused across runs, an intermediate result is larger than RAM (DuckDB spills to the file), or you are curating a dataset to share. Otherwise stay in-memory — it is the fast path and needs no cleanup. The whole database is one file; copy it to move the database.

Python / dataframe interop

In-scope pandas/Polars/Arrow frames are queryable by variable name — that is a replacement scan, no registration needed. The relational API is lazy; nothing executes until you materialize.

pythonCopy code
import duckdb, pandas as pd
orders = pd.read_parquet("orders.parquet")        # ordinary frame in local scope

rel = duckdb.sql("FROM orders SELECT region, sum(amount) AS rev GROUP BY ALL")  # lazy, by name
rel.df()        # -> pandas        rel.pl()       # -> Polars
rel.arrow()     # -> Arrow table   rel.fetchall() # -> list[tuple]

Use a single connection per thread, never share one cursor across threads. Full client surface — parameterized queries, relational operators, NumPy/torch round-trips, threading rules — is in references/python-and-interop.md.

Friendly SQL — use the dialect

DuckDB's dialect removes the boilerplate that makes analytics SQL tedious. Prefer it in DuckDB-only code.

sqlCopy code
FROM events SELECT count(*);                 -- FROM-first: pipe-friendly, valid on its own
SELECT * EXCLUDE (raw_payload) FROM events;  -- everything but the noisy column
SELECT * REPLACE (lower(email) AS email) FROM users;  -- transform one column, keep the rest
SELECT region, sum(amount) FROM sales GROUP BY ALL;   -- no restating non-aggregates
SELECT * FROM sales ORDER BY ALL;            -- deterministic order without listing columns
SELECT COLUMNS('amount_.*') FROM sales;      -- regex over column names
SELECT 1, 2, 3,                              -- trailing commas are legal

GROUP BY ALL / ORDER BY ALL are the biggest wins: add a column to the SELECT and the grouping follows automatically, so the two clauses can't drift out of sync.

Remote + lakehouse data

Read from S3/GCS/HTTP without downloading first: load httpfs and store credentials in a secret.

sqlCopy code
INSTALL httpfs; LOAD httpfs;
CREATE SECRET s3 (TYPE s3, PROVIDER credential_chain);  -- picks up env/role creds
SELECT region, sum(amount) FROM read_parquet('s3://bkt/sales/*.parquet') GROUP BY ALL;

Iceberg, Delta, and DuckLake (DuckDB's own SQL-catalog lakehouse format) are read via extensions. Secret config, hive-partition globs, and the lakehouse one-liners live in references/remote-and-lakehouse.md.

Export & handoff

sqlCopy code
COPY (SELECT region, sum(amount) AS rev FROM 'sales/*.parquet' GROUP BY ALL)
  TO 'summary.parquet' (FORMAT parquet);

COPY sales TO 'out/' (FORMAT parquet, PARTITION_BY (year, month));  -- hive-partitioned dataset

Parquet is the default handoff: it keeps types and reads straight back into DuckDB, Spark, pandas, or ClickHouse. Use PARTITION_BY so downstream readers can prune partitions.

Outgrowing DuckDB

DuckDB scales up (more RAM/threads, out-of-core spill), not out. Tune within one box:

sqlCopy code
PRAGMA threads = 8;            -- match cores
PRAGMA memory_limit = '12GB';  -- cap RAM; the rest spills to the temp dir / database file

Hand off when you hit the single-process wall: concurrent writers or a multi-user query API or an always-on service → clickhouse-analytics. Want managed/shared/hybrid DuckDB without running infra → MotherDuck (managed DuckDB-as-a-service; ATTACH 'md:') — the scale-out escape hatch, not the default.

Anti-patterns

Verify

Run scripts/verify.sh from anywhere. It runs a tiny self-contained smoke test — prefers the duckdb CLI, falls back to python3 -c "import duckdb" — that runs an aggregate and a read_csv_auto over a generated file and asserts a known scalar, proving the documented commands execute on your installed version. If neither the CLI nor the Python module is present it prints SKIP and exits 0. No network.

Project grounding (02-DOCS + CLAUDE.md)

In a project with a 02-DOCS/ layer (the harness wiki), record this project's DuckDB decisions — version pin, file layout, persistent vs in-memory, remote/secret setup — in 02-DOCS/wiki/stack/duckdb.md and index it in 02-DOCS/wiki/index.md (the Knowledge map; root CLAUDE.md keeps only a short pointer to it). Read it first on every use and keep choices consistent. No 02-DOCS/? Skip silently. Conventions are recorded, never gated.