DuckDB Tutorial: In-Process Analytics SQL

What is DuckDB?

DuckDB is an open-source, in-process SQL database built for analytics. The phrase "in-process" is the key idea: rather than running as a separate server you connect to over a socket, DuckDB is a library that loads directly into your application's process - your Python script, an R session, a Java app, or just the command-line shell. There is nothing to install on a server, no daemon to keep running, and no network round-trip between your code and the query engine.

If that sounds like SQLite, the comparison is deliberate. DuckDB is often described as "SQLite for analytics." SQLite dominates embedded transactional workloads - small, frequent reads and writes of individual rows. DuckDB takes the same embeddable, zero-configuration, single-file philosophy and aims it at the opposite problem: scanning, joining, and aggregating millions or billions of rows. It does this with a column-oriented storage layout and a vectorized execution engine, the same architectural choices that power large analytical warehouses like Snowflake and BigQuery, packaged into a file you can pip install.

The practical result is that a laptop can run analytical queries that used to require a cluster. DuckDB regularly handles datasets larger than available RAM by streaming data and spilling to disk, and it speaks a rich, mostly PostgreSQL-compatible SQL dialect.

How DuckDB works (key concepts)

A few design decisions explain why DuckDB feels different from a traditional database.

Columnar storage. Data is stored and processed column by column rather than row by row. Analytical queries typically touch a handful of columns across many rows - "the average order value by month" never needs the customer's shipping address. A columnar layout reads only the relevant columns and compresses them well, since values in a single column tend to be similar.

Vectorized execution. Instead of processing one row at a time, DuckDB pushes batches (vectors) of around 2,048 values through each operator. This makes good use of CPU caches and lets the engine use modern hardware efficiently, which is where most of the speed comes from.

Query files in place. DuckDB can query CSV, Parquet, and JSON files as if they were tables. You can SELECT directly from a path - local or remote on S3, HTTP, or GCS via the httpfs extension - and DuckDB will only read the columns and row groups a query actually needs. Because of this, you can often skip the load step entirely.

Extensions. Core DuckDB is small; capabilities like httpfs (remote files), json, spatial, and parquet load on demand with INSTALL and LOAD. Many ship pre-bundled and auto-load.

Single-file persistence. A DuckDB database is one file on disk (or purely in memory if you skip a filename). It supports full ACID transactions, so it is a real database, not just a query layer over files.

Installing DuckDB

The standalone command-line client installs on macOS and Linux with the official script:

curl https://install.duckdb.org | sh
# adds the binary at ~/.duckdb/cli/latest/duckdb
export PATH="$HOME/.duckdb/cli/latest:$PATH"
duckdb --version

For the Python client - by far the most common way data engineers use DuckDB - install from PyPI:

pip install duckdb          # Python library

To pin a specific release, append the version (for example pip install duckdb==<version>). The same package works across Linux, macOS, and Windows with no extra system dependencies. R users install with install.packages("duckdb"), and there are official clients for Java, Node.js, Go, Rust, and more.

Hands-on: querying CSV and Parquet directly

Start the shell with no arguments to get an in-memory database, then point SQL straight at a file. DuckDB infers the schema, types, delimiters, and header automatically.

-- inside the duckdb shell
-- query a CSV without importing it
SELECT region, count(*) AS orders, sum(amount) AS revenue
FROM 'orders.csv'
WHERE status = 'shipped'
GROUP BY region
ORDER BY revenue DESC;

-- read a Parquet file (local or s3://...) and only scan two columns
SELECT customer_id, sum(amount) AS lifetime_value
FROM read_parquet('events/*.parquet')
GROUP BY customer_id
HAVING sum(amount) > 1000;

-- persist a derived table into a real DuckDB file
ATTACH 'analytics.duckdb' AS db;
CREATE TABLE db.top_customers AS
SELECT customer_id, sum(amount) AS lifetime_value
FROM read_parquet('events/*.parquet')
GROUP BY customer_id
HAVING sum(amount) > 1000;

Note the glob pattern: events/*.parquet transparently reads and unions an entire partitioned dataset. DuckDB pushes the filters and column projection down into the Parquet reader, so a query over a folder of files touches only the bytes it needs.

The same workflow lives inside Python, which is where DuckDB is most useful for data engineering. It integrates tightly with pandas and Apache Arrow, so you can mix SQL and DataFrames with little or no copying:

import duckdb
import pandas as pd

df = pd.read_csv("orders.csv")

# query the DataFrame by name - DuckDB sees it as a table
result = duckdb.sql("""
    SELECT region, sum(amount) AS revenue
    FROM df
    GROUP BY region
    ORDER BY revenue DESC
""").df()   # return the result as a pandas DataFrame

print(result)

DuckDB scans the pandas DataFrame in place rather than serializing it, so this round-trip stays fast even for large frames. The .df() call (or .arrow(), .fetchall()) controls the output format.

When to use DuckDB

Reach for DuckDB when the work is analytical and the data fits "one machine" - which today means up to hundreds of gigabytes. It is an excellent fit for interactive exploration of CSV and Parquet files, local ETL and transformation steps, powering the compute layer of a notebook, replacing long chains of pandas groupby and merge calls with readable SQL, and running unit-testable transformations in a data pipeline without standing up a warehouse. It is also a strong local development engine for dbt and a fast way to query data sitting in object storage.

It is the wrong tool when you need high-concurrency transactional writes from many clients at once - that is what PostgreSQL or a row-oriented OLTP store is for. It is also not a multi-node distributed system: if a single query genuinely needs a cluster of machines because the dataset is in the multi-terabyte range and heavily concurrent, a warehouse like Snowflake, BigQuery, or a Spark cluster remains the right choice. In practice, DuckDB handles much larger workloads on one machine than many people expect, so try it on a single machine before you set up a cluster.