Verdict: install it, point it at your CSVs and Parquet files, and let it do the analytics your app database dreads — but do not mistake it for that app database. DuckDB is an in-process, columnar SQL engine you pip install (or drop in as a single CLI binary) with no server, no daemon, and no CREATE USER ceremony. It reads a CSV or Parquet file directly — SELECT ... FROM 'sales.csv' — and chews through GROUP BY aggregates fast enough that you stop reaching for pandas. The catch is what it’s built for: reads and one writer, not your app’s concurrent transactions. We ran it on real files and left every surprise in — including one where the tool was smarter than the warning we’d written down.
DuckDB is free and open source (MIT), maintained by DuckDB Labs and the DuckDB Foundation. We have no relationship with the project and nothing to sell. This one started on our sister site’s Data Warehousing: Build a Dimensional Star Schema in SQL quest — they build the star schema; we’re here to tell you which corner of the tool bites. Everything below was captured on a real Ubuntu 24.04 box running DuckDB 1.5.4 (both the duckdb Python package and the CLI binary).
What it’s for, and who it’s for
If you have a CSV — sales export, log dump, a Parquet file someone handed you — and you want to run real SQL against it without standing up Postgres, DuckDB is the answer. It’s for analysts, data engineers, and anyone who’s written pandas.read_csv(...).groupby(...) and wished it were a GROUP BY. It is OLAP: built for scanning columns and aggregating millions of rows. It is not OLTP — it is not the database behind your web app’s checkout flow, and the moment you treat it like one it will tell you so (we’ll get there).
The good part: no server, and it queries the file in place
There is no install ceremony. The Python package is one line, and then you’re querying a CSV from memory:
$ pip install duckdb
Successfully installed duckdb-1.5.4
import duckdb
print("duckdb", duckdb.__version__)
rows = duckdb.sql("SELECT region, count(*) AS n FROM 'sales.csv' "
"GROUP BY region ORDER BY n DESC").fetchall()
print(rows)
duckdb 1.5.4
[('East', 2), ('West', 2), ('North', 1)]
Note what’s not happening: there’s no connection string, no port, no server process. It’s a library running inside your Python process. Check for yourself — nothing is listening:
$ pgrep -a duckdb || echo "no duckdb process running — it's a library, not a daemon"
no duckdb process running — it's a library, not a daemon
The CLI is the same engine as a single binary, and it queries a file with no CREATE TABLE, no COPY ... FROM, no import step at all:
$ duckdb -c "SELECT region, count(*) AS orders, round(sum(amount),2) AS total \
FROM 'sales.csv' GROUP BY region ORDER BY total DESC;"
┌─────────┬────────┬────────┐
│ region │ orders │ total │
│ varchar │ int64 │ double │
├─────────┼────────┼────────┤
│ West │ 2 │ 380.75 │
│ North │ 1 │ 200.0 │
│ East │ 2 │ 170.75 │
└─────────┴────────┴────────┘
The filename is the table. That’s the whole pitch, and it delivers.
It’s fast, and it stays fast on Parquet
We generated a 100 MB CSV — 5,000,000 rows — and ran a GROUP BY straight off the file, no import:
$ time duckdb -c "SELECT region, count(*) AS n, round(sum(amount),2) AS total \
FROM 'big.csv' GROUP BY region ORDER BY region;"
┌─────────┬─────────┬──────────────┐
│ region │ n │ total │
│ varchar │ int64 │ double │
├─────────┼─────────┼──────────────┤
│ East │ 1249615 │ 312327360.64 │
│ North │ 1249540 │ 312661081.13 │
│ South │ 1249088 │ 312189251.05 │
│ West │ 1251757 │ 312960476.56 │
└─────────┴─────────┴──────────────┘
real 0m0.380s
Five million rows parsed and aggregated in 0.38 seconds — the parsing is most of that time. Write the same data to Parquet (columnar, compressed) and query that, and the parse cost mostly vanishes:
$ duckdb -c "COPY (SELECT * FROM 'big.csv') TO 'big.parquet' (FORMAT parquet);"
$ time duckdb -c "SELECT region, count(*) AS n FROM 'big.parquet' \
GROUP BY region ORDER BY region;"
real 0m0.025s
0.025 seconds off the Parquet — fifteen times faster than the CSV, and the file is 44 MB instead of 100 MB. If you’re going to query the same dump more than twice, convert it once. That’s the workflow DuckDB is built for.
The dealbreaker: it’s OLAP, and it has one writer
Here’s the line you must not cross. DuckDB is single-process for writes. One connection can hold the database file open read-write; a second process that tries to write gets locked out cold. We opened a write transaction from one process and, while it was open, tried to insert from another:
# process 1 (Python): BEGIN; INSERT INTO t VALUES (2); -- holds the lock
# process 2 (CLI), meanwhile:
$ duckdb shop.db -c "INSERT INTO t VALUES (3);"
IO Error: Could not set lock on file "/tmp/ddtest/shop.db": Conflicting lock is
held in /usr/bin/python3.12 (PID 7489).
See also https://duckdb.org/docs/stable/connect/concurrency
That is not a bug — it’s the design. A web app has many workers writing concurrently; hand them all a DuckDB file and they’ll spend the day fighting over that lock. This is the sentence that decides whether DuckDB is your tool: reads scale, writers do not. Concurrent readers are fine — open the file read-only from as many processes as you like:
$ duckdb -readonly shop.db -c "SELECT count(*) FROM t;" # process A
┌──────────────┐
│ count_star() │
│ 2 │
└──────────────┘
$ duckdb -readonly shop.db -c "SELECT sum(x) FROM t;" # process B, same time
┌────────┐
│ sum(x) │
│ 3 │
└────────┘
So the shape is: one writer, many readers, built to scan. For an analytics job, a notebook, an ETL step, an embedded reporting engine — perfect. For the database behind a live app with concurrent checkouts — that’s Postgres, and it isn’t close.
The type sniffer eats a ZIP code — but not when you’d expect
We came in expecting the classic footgun: read_csv_auto sees a column of digits, decides it’s an integer, and turns 02134 into 2134. So we fed it a small CSV with a leading-zero ZIP and braced for the leading zero to vanish:
$ duckdb -c "SELECT zip, typeof(zip) FROM 'sales.csv' LIMIT 3;"
┌─────────┬─────────────┐
│ zip │ typeof(zip) │
│ varchar │ varchar │
├─────────┼─────────────┤
│ 02134 │ VARCHAR │
│ 90210 │ VARCHAR │
│ 02134 │ VARCHAR │
└─────────┴─────────────┘
VARCHAR. The zero survived. The sniffer saw 02134 in its sample, recognized that a leading zero means “this is a string, not a number,” and kept it as text. Credit where it’s due — that’s smarter than the warning we’d written down, and if the offending value is anywhere near the top of your file, you’re fine.
The trap is more insidious than “it always mangles ZIPs.” The sniffer only reads a sample from the head of the file (about 20k rows by default). If every value it samples looks like a plain integer, it commits to BIGINT — and any leading-zero value further down gets coerced silently. We built exactly that file: 30,000 rows of ordinary 5-digit numbers, then one real Massachusetts ZIP past the sample window:
$ duckdb -c "SELECT typeof(zip) AS t, count(*) FROM 'bigzip.csv' GROUP BY t;"
┌─────────┬──────────────┐
│ t │ count_star() │
│ BIGINT │ 30001 │
└─────────┴──────────────┘
$ duckdb -c "SELECT id, zip FROM 'bigzip.csv' WHERE id=30000;"
┌───────┬───────┐
│ id │ zip │
│ int64 │ int64 │
├───────┼───────┤
│ 30000 │ 2134 │ ← 02134 became 2134
└───────┴───────┘
There it is: 02134 → 2134, the leading zero eaten, and no error — the column is an integer now and the data is quietly wrong. This is the failure to fear, and it’s the one that’s invisible in testing, because your test file is small enough that the bad row is always in the sample. The fix is to never let the sniffer guess on a column that’s secretly a code. Either pin the type:
$ duckdb -c "SELECT id, zip FROM read_csv('bigzip.csv', \
columns={'id':'INTEGER','zip':'VARCHAR'}) WHERE id=30000;"
┌───────┬─────────┐
│ id │ zip │
│ 30000 │ 02134 │ ← preserved
└───────┴─────────┘
…or make it sample the whole file so it can’t miss the leading-zero row:
$ duckdb -c "SELECT typeof(zip) FROM read_csv('bigzip.csv', sample_size=-1) LIMIT 1;"
┌─────────────┐
│ VARCHAR │
└─────────────┘
sample_size=-1 scans everything, so accuracy costs you a full pass; explicit columns= costs you nothing but the typing. On any column that’s an identifier wearing a number’s clothes — ZIPs, phone numbers, account IDs, SKUs with check digits — declare it VARCHAR and move on.
The other ceiling: a join wider than RAM
DuckDB spills to disk when a query outgrows memory, so it won’t fall over the instant you exceed RAM the way an in-memory-only tool would. But “spills to disk” means “gets slow,” and a hash join across two genuinely huge tables can turn a snappy aggregate into a grinding one. It’s still a single machine — there’s no cluster to scale onto. If your working set is comfortably bigger than one box’s memory and you need it fast, that’s the boundary where a distributed warehouse (or plain Postgres with the right indexes for a transactional shape) earns its keep.
What made us close the tab
Nothing — it stays, and it’s the first thing we reach for on a loose CSV now. But go in with the real expectations:
- It’s OLAP, not OLTP. One writer, many readers, built to scan. A second process that tries to write gets an
IO Error: Could not set lock. That’s the design, not a bug — don’t put it behind a concurrent app. - The type sniffer samples the head of the file. A leading-zero code (ZIP, account ID) in the sample is kept as text; the same code past the sample window gets silently coerced to an integer and mangled — no error. Pin
columns={'zip':'VARCHAR'}or passsample_size=-1on anything that’s a code, not a quantity. - It’s one machine. A join wider than RAM spills to disk and slows down; there’s no cluster. Big-and-fast at the same time is where a distributed warehouse still wins.
- Convert to Parquet if you’ll query twice. 0.38 s off the CSV, 0.025 s off the Parquet, and a smaller file. The columnar format is where the speed lives.
When it goes wrong: the day a report’s totals look almost right but an ID column doesn’t join to anything, check whether the sniffer turned a code into an integer and ate a leading zero. It won’t raise an error — the number is a perfectly valid number, only the wrong one. The tell is a column that should be text showing up as BIGINT in the schema. DESCRIBE SELECT * FROM 'yourfile.csv'; before you trust the join, and pin the type the moment you see it guess wrong.