DuckDB: The Analytical Database That Fits in a Single File

Overview

I have a folder of CSV exports I use for ad-hoc analysis. Twenty files, a few gigabytes total, and for years I loaded them into pandas, hit memory limits, and rewrote things in chunks. Then someone pointed me at DuckDB, and the same analysis dropped from "start a Python script and wait" to "run a SQL query and see results in two seconds."

DuckDB is what SQLite would be if it were designed for analytics instead of transactions. One file, no server, no configuration, but column-oriented storage and a vectorized execution engine that's genuinely fast on analytical queries.

What makes it different from SQLite

The killer feature is querying files directly without loading them. You point DuckDB at a directory of Parquet files and it just runs. No setup step, no schema definition, no memory footprint for the data itself.

Installation

# CLI (single binary)
curl -L https://GitHub.com/duckdb/duckdb/releases/latest/download/duckdb_cli-linux-amd64.zip -o duckdb.zip
unzip duckdb.zip

# Python
pip install duckdb

# Node.js
npm install duckdb

# Or the CLI via homebrew
brew install duckdb

Latest releases at DuckDB's GitHub releases page.

Querying CSV and Parquet directly

-- From the CLI
SELECT
  region,
  SUM(amount) AS total,
  COUNT(*) AS orders
FROM read_csv_auto('sales/*.csv')
WHERE order_date >= '2026-01-01'
GROUP BY region
ORDER BY total DESC;

# From Python
import duckdb

result = duckdb.sql("""
    SELECT
        region,
        SUM(amount) AS total
    FROM read_parquet('data/sales-*.parquet')
    GROUP BY region
    ORDER BY total DESC
""").fetchall()

print(result)

read_csv_auto infers the schema from a sample of rows. read_parquet reads Parquet files with all their column-level metadata — no schema inference needed because Parquet stores types.

The Parquet case is where DuckDB shines. It reads only the columns the query needs, using Parquet's columnar layout and statistics to skip row groups that don't match the filter. On a 50GB dataset where the query touches two columns, you're reading a few hundred MB.

Joining files directly

SELECT
    o.order_id,
    o.amount,
    c.name,
    c.region
FROM read_parquet('orders/*.parquet') o
JOIN read_csv_auto('customers.csv') c
    ON o.customer_id = c.id
WHERE o.order_date >= '2026-01-01'
LIMIT 100;

No data loading, no intermediate tables. DuckDB streams Parquet and CSV as needed and joins on the fly. This is the pattern that replaces a lot of pandas code.

The pandas comparison

The reason DuckDB matters is that pandas doesn't scale. Once your data exceeds RAM, you're rewriting things with chunks, dask, or polars, and the code gets uglier.

# pandas — loads everything, fails at scale
import pandas as pd
df = pd.concat([pd.read_csv(f) for f in files])
result = df[df.amount > 100].groupby("region")["amount"].sum()

# DuckDB — same result, streams, doesn't matter how big
import duckdb
result = duckdb.sql("""
    SELECT region, SUM(amount)
    FROM read_csv_auto('sales/*.csv')
    WHERE amount > 100
    GROUP BY region
""").df()  # returns a pandas DataFrame

The DuckDB version is shorter, faster, and works on datasets that don't fit in memory. It returns a pandas DataFrame at the end, so it fits in existing codebases without a rewrite.

Persistent Databases

For repeated analysis of the same data, a persistent DuckDB file avoids re-parsing CSV every time:

-- Create a database and load data once
CREATE TABLE sales AS
SELECT * FROM read_csv_auto('raw/sales-*.csv');

-- Add indexes on columns you filter by
CREATE INDEX idx_sales_date ON sales(order_date);

-- Analyze
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount)
FROM sales
GROUP BY 1
ORDER BY 1;

The database is a single file. Copy it to another machine and it works, same as SQLite. No server, no dump-and-restore.

The Parquet pipeline

The workflow I've settled on for anything large: convert CSV to Parquet once, then query Parquet forever.

-- Convert CSV to partitioned Parquet
COPY (
    SELECT * FROM read_csv_auto('raw/*.csv')
) TO 'processed/sales' (
    FORMAT PARQUET,
    PARTITION_BY (year, month),
    COMPRESSION ZSTD
);

-- Query with partition pruning
SELECT SUM(amount)
FROM read_parquet('processed/sales/**/*.parquet')
WHERE year = 2026 AND month = 3;

The partitioned layout means DuckDB only reads the files for March 2026. On a multi-year dataset, that's a 90%+ reduction in I/O for time-filtered queries. Parquet with ZSTD compression is typically 5–10x smaller than the original CSV.

Extensions

DuckDB has an extension system that adds functionality on demand:

-- Install and load
INSTALL httpfs;
LOAD httpfs;

-- Query S3 directly
SELECT * FROM read_parquet('s3://my-bucket/data/*.parquet')
WHERE region = 'EU';

-- Query Postgres
INSTALL postgres;
LOAD postgres;
ATTACH 'host=localhost dbname=myapp' AS pg (TYPE postgres);
SELECT * FROM pg.public.users;

The postgres extension is the one that surprised me. You can write a query that joins a local Parquet file with a remote Postgres table, and DuckDB handles the data movement. It's not fast for large Postgres tables, but for lookup tables it's a clean way to enrich local analysis.

When DuckDB is the wrong choice

• Concurrent writes. One writer at a time. If you need multiple processes writing to the same database, use Postgres.
• High-frequency small transactions. DuckDB is optimized for scan-heavy queries, not for inserts of one row at a time. Batch your writes.
• Network-accessible database. There's no server protocol. If multiple machines need to query the same data concurrently, use a real database or put the file on shared storage.
• Anything with strict ACID requirements across multiple clients. DuckDB is transactional within a single process, but it's not designed for multi-client OLTP.

For analytical work on data that fits in a single file — which is more than you'd think — DuckDB replaces a lot of infrastructure. I've stopped reaching for Spark and ClickHouse for datasets under a few hundred GB, and the local development experience is dramatically better.

What I use it for

Three things, mostly:

1. Ad-hoc analysis of log exports. Parsing a few GB of JSON logs, filtering by error type, aggregating by endpoint. Two minutes of work versus an hour setting up an ELK stack.
2. Data pipelines. Convert raw exports to partitioned Parquet, then write a small Python service that queries the Parquet for reports. No database server, no ingestion job.
3. Replacing pandas in scripts. Anywhere I'd write pd.read_csv plus a groupby, DuckDB is shorter and doesn't have memory limits.

The learning curve is essentially zero if you know SQL. If you're doing analytical work in pandas and hitting memory limits, or writing Python loops that could be SQL, DuckDB is a thirty-minute change that pays off for the rest of the project.