DuckDB is the tool I reach for before I stand up anything heavier. It's an analytics database that runs inside my own process, no server to babysit, and most days it does the job I used to spin up a whole warehouse for. I've pointed it at gigabytes of Parquet on S3, joined a scrappy CSV to a production Postgres table, and used it to back a real semantic layer. Here's where it genuinely shines and where it taps out.
What it actually is
Think SQLite, but built for analytics instead of transactions. DuckDB is an in process, columnar, vectorized SQL engine that ships as a single binary and runs anywhere: your terminal, a Python notebook, a Lambda, even the browser over WASM. It reads Parquet, CSV, JSON, and Arrow straight off disk or object storage with no import step, and it speaks SQL that's close enough to Postgres that you rarely reach for the manual. There's no cluster to size and nothing to keep running overnight. That combination is why it quietly ended up inside half the data tools you already use. It's also the same instinct behind Saiku: keep the heavy machinery out of the way and let people ask questions. If the semantic layer side of that is new to you, Saiku wrote a plain English version over here.
Getting my hands on it
Installing it is almost anticlimactic:
pip install duckdb # or: brew install duckdbThen you can query Parquet sitting on S3 with essentially zero setup:
INSTALL httpfs; LOAD httpfs;
SELECT count(*)
FROM 's3://bucket/events/*.parquet';The trick that made me fall for it is joining across sources in one engine. Local file, live Postgres, same query:
INSTALL postgres; LOAD postgres;
ATTACH 'host=db.internal dbname=app' AS pg (TYPE postgres);
SELECT u.plan, count(*)
FROM read_csv_auto('signups.csv') u
JOIN pg.orders o USING (user_id)
GROUP BY u.plan;No pipeline, no staging table, no waiting. That is the whole pitch, and most days it holds up.
Where it shines, and where it bit me
Where it shines | Where it bit me |
|---|---|
One binary, no server. It runs in your Python process, your terminal, or a function, and disappears when you're done | It's single node. Brilliant to a point, then very large data makes you feel the ceiling |
Reads Parquet, CSV, JSON, and Arrow off disk or S3 with no import step | In process means it lives and dies with your process. It is not a shared server other people connect to |
A vectorized columnar engine that chews through analytics on a laptop | One writer at a time. Reads are happy, concurrent writes are not the use case |
Attaches to Postgres, MySQL, and SQLite in place, so you join across them in one query | It moves fast. Extensions and syntax can shift between releases, so pin your version |
Short version: DuckDB is the best laptop sized analytics engine going, and the moment you need many people hitting the same governed numbers at once, it wants a layer on top rather than more DuckDB.
Wiring it into Saiku
Here's the part DuckDB leaves to you. It will happily run the SQL, but it has no opinion about what "net revenue" means, which of your seventeen date columns is the real one, or who's allowed to see what. That's the job of a semantic layer, and it's exactly where Saiku sits. Point Saiku at DuckDB (or MotherDuck if you want it hosted), define the numbers once, and stop rewriting the same metric in five places. Getting the connection in is a short walk, the DuckDB connection guide is here, and Saiku runs locally the same way DuckDB does:
docker run -p 8080:8080 ghcr.io/spiculedata/saikuDefine one cube, with measures like Net Revenue and dimensions like Plan and Signup Date, and now Excel, your dashboards, and an AI agent all read the same governed numbers off DuckDB. The Excel half is the part people don't believe until they see it: live pivot tables straight onto DuckDB with no exports, which Saiku walks through here. Under the hood it's Apache Calcite, so a new backend is a one line change and not a fork; they wrote up that rebuild in this post. And if you want an AI agent querying DuckDB without hallucinating column names, the typed schema over MCP is documented here.
Maintainer Q&A
This section doubles as the podcast. I sat down with someone who works on DuckDB, and this is the agenda I walked in with. The full answers are in the recorded interview.
The in process model is the whole magic. Where does it stop being the right call, and what's the honest data ceiling on real hardware?
MotherDuck put DuckDB in the cloud. How do you think about local versus hosted without splitting the project in two?
Extensions are exploding. How do you keep the core small while httpfs, iceberg, and friends pull in every direction?
People now ship DuckDB inside other products. What breaks when it stops being a CLI and becomes a library under load?
The next twelve months. What's landing that changes how people reach for it?
Try it yourself
You can have this running in two terminals. DuckDB in one, Saiku in the other, and a live pivot table on your own data before your coffee goes cold. Start a free Saiku trial here and run it end to end.
Full Table Scan is sponsored by Saiku, the open source semantic layer that gives Excel, dashboards, and AI agents the same trusted numbers. Have a look at saiku.bi.
Next issue: Apache Iceberg, from someone who actually runs a lakehouse in anger. Tom's taking that one.
If DuckDB is already in your stack somewhere, hit reply and tell me where it surprised you. I read every one.
Juan