DuckDB for Analytics — A Field Guide

DuckDB is an embedded, in-process OLAP database — the analytical-query equivalent of SQLite. There's no server to run, no cluster to provision: you `import duckdb`, point it at a file (or run entirely in memory), and get a columnar execution engine that can scan and aggregate millions of rows in a single process, often faster than shipping the same query to a hosted warehouse and waiting on network round trips.

Why an in-process columnar engine is fast for analytics

Traditional row-store databases (Postgres, MySQL) read a full row even when a query only needs two of twenty columns. DuckDB stores and scans data column-by-column, so an aggregation over a handful of columns only touches those columns' bytes, and it vectorizes execution — processing batches of values per operation rather than one row at a time. For OLAP-shaped work (`GROUP BY`, `SUM`, large scans with filters) this consistently beats row stores by a wide margin, and because everything runs in-process, there's no serialization or network overhead between the query engine and your application code.

Querying Parquet and CSV without loading them first

The feature that gets DuckDB reached for most is querying files directly with SQL — no ETL step, no load into a table first. `SELECT * FROM 'data/*.parquet'` scans a whole directory of Parquet files as if it were one table, and DuckDB pushes filters and column selection down to the file reader so it only reads the bytes a query actually needs. The same works for CSV, JSON, and — via extensions — data sitting in S3 or other object storage, queried with `s3://` paths directly.

import duckdb

con = duckdb.connect()
result = con.execute("""
    SELECT
        date_trunc('month', order_date) AS month,
        customer_segment,
        sum(amount) AS revenue
    FROM read_parquet('s3://sales-data/orders/*.parquet')
    WHERE order_date >= '2025-01-01'
    GROUP BY 1, 2
    ORDER BY 1, 2
""").fetchdf()

A faster substitute for pandas on the aggregation step

DuckDB integrates directly with pandas and Polars: you can run SQL against an existing DataFrame without exporting it anywhere, and pull results back as a DataFrame with `.fetchdf()` or `.pl()`. For a heavy `groupby`/aggregation step, running it through DuckDB's SQL engine is often noticeably faster than the equivalent pandas operation, especially once the DataFrame is large enough that pandas' single-threaded execution starts to show. It doesn't replace pandas for general data wrangling — it's a fast path for the aggregation-heavy parts.

DuckDB vs. a hosted warehouse

DuckDB is not a replacement for Snowflake, BigQuery, or Redshift when you have many concurrent users, need row-level security and access control, or your dataset exceeds what fits comfortably on one machine's disk and memory. It excels at single-user or single-process analytical workloads: a data scientist's local exploration, a scheduled batch job that aggregates a day's Parquet files, or an application embedding fast analytics without standing up separate warehouse infrastructure. Increasingly, teams use both — DuckDB as the fast local/embedded layer, a cloud warehouse as the shared source of truth.

Wrapping up

DuckDB earns its popularity by being unglamorous: no cluster, no server process, just a fast columnar engine you drop into a Python or CLI workflow to make analytical queries over local or object-stored files fast. It's not competing with a data warehouse — it's removing the warehouse-shaped overhead from the huge share of analytics work that never needed one.