DuckDB: fast SQL analytics on Parquet, CSV and DataFrames without a server

Plenty of analysis work sits awkwardly between tools. The data is too big for comfortable pandas, but setting up a warehouse or a Spark cluster for it feels like overkill. DuckDB fills that gap: an analytical (columnar) SQL database that runs inside your process, like SQLite does for transactional work. There's no server to run, and it can query files where they already are.

Install

pip install duckdb

There is also a standalone command-line shell, and clients for many other languages.

Query files directly

DuckDB can read Parquet, CSV and JSON files as if they were tables:

import duckdb

top = duckdb.sql("""
    SELECT country, count(*) AS orders, round(sum(total), 2) AS revenue
    FROM 'data/orders/*.parquet'
    WHERE created_at >= DATE '2026-01-01'
    GROUP BY country
    ORDER BY revenue DESC
    LIMIT 10
""").df()        # result as a pandas DataFrame

A few things make this fast:

• Columnar execution: only the columns the query uses are read.
• Filter pushdown into Parquet files, so row groups that can't match are skipped.
• Parallelism across all your CPU cores by default.
• Larger-than-memory processing: DuckDB can spill to disk instead of crashing when intermediate results don't fit in RAM.

CSV files work the same way, with schema detection:

duckdb.sql("SELECT * FROM read_csv('events.csv') LIMIT 5").show()

Query DataFrames in place

DuckDB can query a pandas (or Polars, or Arrow) DataFrame by its variable name, without copying it into a database first:

import pandas as pd

events = pd.read_parquet("events.parquet")
summary = duckdb.sql("""
    SELECT user_id, count(*) AS n, max(ts) AS last_seen
    FROM events
    GROUP BY user_id
    HAVING n > 10
""").df()

This is often the quickest way to replace a slow chain of groupby, merge and apply calls with one readable SQL query.

Persist and convert

Use a database file when you want to keep tables between runs:

con = duckdb.connect("analytics.duckdb")
con.sql("CREATE TABLE orders AS SELECT * FROM 'raw/orders.csv'")
con.sql("COPY (SELECT * FROM orders WHERE total > 0) TO 'clean/orders.parquet' (FORMAT parquet)")

That last line is a handy pattern on its own: DuckDB makes a very good CSV-to-Parquet converter and cleaner.

Remote files

With the httpfs extension, DuckDB can read files over HTTPS and from S3-compatible object storage, again reading only the parts of a Parquet file it needs. Credentials are configured through DuckDB's secrets settings; keep them out of your code and notebooks.

Where DuckDB fits

Use DuckDB when	Use something else when
Analysing files on one machine, up to hundreds of GB	Many users write to the same database concurrently (use Postgres)
Speeding up pandas-heavy notebooks and scripts	You need a shared, always-on warehouse for a whole company
Building data pipelines that read and write Parquet	Data is truly huge and already lives in a Spark or warehouse platform
Testing SQL transformations locally before running them elsewhere	You need row-level transactional workloads (orders, payments)

Practical tips

• Prefer Parquet over CSV for anything you query more than once; it's smaller and much faster.
• Use EXPLAIN ANALYZE in DuckDB to see where time goes, just like in Postgres.
• Set PRAGMA threads=... or a memory limit if DuckDB shares a machine with other heavy jobs.
• For dashboards that many people use, DuckDB works best as the engine behind a single service rather than as a shared database file.

Verdict

DuckDB turns a laptop into a capable analytics engine. If your workflow involves Parquet or CSV files, slow pandas code, or local testing of warehouse SQL, it's worth an afternoon of trying, and it may remove the need for heavier infrastructure entirely.