What is DuckDB?

DuckDB is an in-process, columnar SQL database built for analytics (OLAP). It runs inside your application — a Python process, a CLI session, a Node script — the same way SQLite runs inside an application, except SQLite is optimized for transactional (OLTP) workloads and DuckDB is optimized for scans, aggregations, and joins over large columns of data.

There’s no server to install, no port to open, and no cluster to provision. You import duckdb, run SQL, and get results back — often against files (Parquet, CSV, JSON) that never need to be loaded into a “real” database at all.

Why It Exists

Most analytical work happens on a single laptop or a single container, not a 100-node cluster. Spinning up Snowflake, BigQuery, or Spark for a few million rows is overkill: network round-trips, warehouse spin-up time, and cluster overhead dominate the actual query time. DuckDB targets exactly this gap — fast, embedded, zero-ops analytics on datasets from a few megabytes up to hundreds of gigabytes on a single machine.

It was created at CWI (the Dutch national research institute for mathematics and computer science) by Hannes Mühleisen and Mark Raasveldt, the same lab that produced MonetDB, and is now maintained by DuckDB Labs and a large open-source community.

Core Design Ideas

• Columnar storage — data is stored and processed column-by-column, so aggregations like SUM(amount) only touch the amount column, not entire rows.
• Vectorized execution — operators process data in batches (“vectors”) of a few thousand values at a time instead of one row at a time, which keeps the CPU pipeline full and cache-friendly.
• In-process — DuckDB is a library (a single .so/.dll/.dylib or Python wheel), not a client-server system. There’s no network protocol between your code and the database engine.
• ACID transactions — despite being embedded, DuckDB supports full transactions, so concurrent writers within a process are still safe.
• Zero-copy data exchange — DuckDB can query Pandas DataFrames, Polars DataFrames, and Arrow tables directly, without copying data into its own storage first.

What DuckDB Is Good At

• Ad-hoc analysis and exploration over CSV/Parquet/JSON files, with no ETL step
• Feature engineering and data prep inside Python/R data science workflows
• Fast local development and testing for pipelines that will eventually run on Snowflake/BigQuery/Spark
• Embedded analytics inside applications (dashboards, CLI tools, notebooks)
• Querying and joining data that lives across multiple file formats and cloud object storage

What It’s Not For

DuckDB is not a replacement for a multi-user, always-on production data warehouse serving hundreds of concurrent analysts, nor for high-throughput transactional (OLTP) systems like an application’s primary Postgres database. For those, look at Snowflake, BigQuery, or a hosted variant like MotherDuck (which layers a shared, always-on service on top of DuckDB’s engine).

A First Query

-- Install the CLI, then:
D SELECT 'Hello, DuckDB' AS greeting, 21 * 2 AS answer;
┌────────────────┬────────┐
│    greeting    │ answer │
│    varchar     │ int32  │
├────────────────┼────────┤
│ Hello, DuckDB  │     42 │
└────────────────┴────────┘

Or query a Parquet file on disk without creating a table at all:

SELECT country, COUNT(*) AS orders
FROM 'sales_2026.parquet'
GROUP BY country
ORDER BY orders DESC;

The rest of this guide walks through installation, SQL fundamentals, working with files, integrating with Python, and tuning performance for larger datasets.