DuckDB: In-Process OLAP Analytics on Parquet, CSV, and S3

Most analytics questions I get asked are one-off and awkward: a stakeholder drops a folder of Parquet exports and wants a grouped total by Friday. The traditional answer is to spin up a warehouse, define a schema, write an ingestion job, and load the data before you can run a single aggregate. For a question that takes ten seconds of SQL to answer, that overhead is absurd.

DuckDB is the tool that removed that overhead for me. It is an in-process analytical database released under the permissive MIT License, and the easiest way to describe it is SQLite for analytics: no server, no daemon, no network hop. You add one library to your process and you have a full SQL engine that reads columnar data at speed. It hit a stable 1.0.0 on June 3, 2024, so its storage format and API are now safe to build on.

In-process, columnar, and vectorized

Two design choices make DuckDB fast for the queries analysts actually run. First, it is columnar: it stores and reads data one column at a time, so a query touching three columns of a fifty-column table only pays for those three. Row-store databases like SQLite or a default Postgres table read the whole row off disk even when you asked for a fraction of it. For scans and aggregates over big tables, that difference is enormous.

Second, DuckDB uses a vectorized execution engine. Instead of pushing rows through the query operators one at a time, it processes them in batches of a few thousand values that fit in CPU cache, which slashes the per-row interpreter overhead that dominates traditional engines. Being in-process matters too: the query runs inside your Python or Node process, so results never cross a socket. There is no server to install, update, or maintain.

Query files directly — no ETL, no schema

The feature that changed how I work is that DuckDB queries files where they sit. A file ending in .parquet can go straight into the FROM clause, and read_csv or read_json_auto do the same for other formats, inferring types automatically. Glob patterns treat a whole folder of files as one table. There is no CREATE TABLE, no COPY, no loading step at all — the file is the table.

-- No load step, no schema, no server. Just point SQL at the file.
SELECT country, COUNT(*) AS orders, SUM(amount) AS revenue
FROM 'sales/2026-*.parquet'
WHERE status = 'paid'
GROUP BY country
ORDER BY revenue DESC
LIMIT 10;

-- CSV and JSON work the same way, with types inferred automatically.
SELECT * FROM read_csv('events.csv');
SELECT * FROM read_json_auto('logs.json');

Reach across the network — remote and S3 data

The httpfs extension extends this model to remote storage. After a one-line INSTALL and LOAD, you can point read_parquet at an https:// URL or an s3:// path and DuckDB streams only the byte ranges it needs rather than downloading the whole object. Credentials live in a secret created with CREATE SECRET, or you can lean on the standard AWS credential chain. This turns an S3 data lake into something you can query from a laptop with plain SQL.

INSTALL httpfs;
LOAD httpfs;

-- Store credentials once as a secret (or use the AWS credential chain).
CREATE SECRET (
  TYPE s3,
  KEY_ID 'AKIAIOSFODNN7EXAMPLE',
  SECRET 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY',
  REGION 'us-east-1'
);

-- Query a remote Parquet file directly; only the needed bytes are fetched.
SELECT device, AVG(latency_ms) AS p_latency
FROM read_parquet('s3://my-bucket/metrics/*.parquet')
GROUP BY device;

Embed it anywhere — Python, Node, and the browser

Because DuckDB compiles down to a header and one implementation file with no external dependencies, it drops into almost any runtime. Python and R clients make it the default for notebook analytics; the official Node.js client ships as the @duckdb/node-api package with native Promise support, letting a backend run analytical SQL without a separate database service. The same engine powers command-line and JVM clients too.

The client that surprises people most is DuckDB-Wasm. DuckDB has been compiled to WebAssembly, so the full engine runs inside any browser on any device — no backend required. A dashboard can fetch a Parquet file and let the user pivot, filter, and aggregate entirely client-side, which is how tools like interactive data explorers ship analytics with zero server cost. The trade-offs are a single thread and a memory ceiling around four gigabytes.

DuckDB versus SQLite versus Postgres

It is easy to mistake DuckDB for a drop-in replacement for the databases you already run. It is not — it is aimed at a different workload. This table lays out where each one fits so you pick by the shape of the query, not by habit.

When to pick DuckDB over Postgres or a warehouse

My rule of thumb is to match the tool to the workload rather than the data size. DuckDB earns its place when the query is analytical and the deployment is single-node. Reach for it when:

• You need to run ad-hoc aggregates over Parquet, CSV, or JSON files without standing up any infrastructure first.
• Your analytics fit comfortably on one machine — DuckDB handles datasets far larger than RAM by spilling to disk, so this covers a lot of ground.
• You want to push analytics into a notebook, a backend job, or the browser without operating a separate database server.
• You are prototyping transformations against a data lake and want warehouse-style SQL without warehouse-style cost or latency.

Stick with Postgres when you need many concurrent writers, transactional integrity, and a long-lived shared database behind an application. Reach for a cloud warehouse like BigQuery or Snowflake when analytics must scale across many nodes or be shared by a whole organization at once. DuckDB fills the wide gap in between, and for the everyday analytics I run, that gap is where most of the work actually lives.