DuckDB for In-Process Analytics

DuckDB is to OLAP what SQLite is to OLTP: a full analytical SQL engine that runs inside your process — no server, no cluster, a single dependency. It executes vectorized, parallel, columnar query plans over Parquet, CSV, and dataframes, and it has quietly become one of the highest-leverage open-source tools in the data stack: most "big data" workloads are under a terabyte, and a laptop-class machine running DuckDB handles them interactively.

Querying Parquet and CSV files directly

DuckDB treats files as tables — no load step:

import duckdb

con = duckdb.connect()            # in-memory; pass a path to persist
res = con.sql("""
    SELECT user_id, sum(amount) AS total
    FROM 'events/2026-*.parquet'          -- glob over partitioned files
    WHERE country = 'DE'
    GROUP BY user_id
    ORDER BY total DESC LIMIT 100
""").df()                          # straight into a pandas DataFrame

Parquet reads benefit from projection and predicate pushdown — only the needed columns and row groups are touched — so selective queries over hundreds of gigabytes of partitioned Parquet return in seconds. The CSV reader deserves special mention: its dialect sniffing is the most robust in the open-source ecosystem, and read_csv with automatic type inference frequently succeeds on files pandas needed hand-tuning to parse. The httpfs extension extends all of this to S3/GCS URLs directly.

Zero-copy interchange with pandas, Polars, and Arrow

Inside a Python process, DuckDB queries dataframes in place by name — registered automatically from local variables — and returns results as pandas, Polars, or Arrow with zero or near-zero copies:

orders = pl.read_parquet("orders.parquet")     # a Polars frame
top = con.sql("SELECT region, avg(margin) FROM orders GROUP BY region").pl()

This makes the pragmatic division of labor: dataframe API for row-wise munging and library handoffs, SQL for joins, window functions, and aggregations where SQL is simply the clearer language. Teams that adopt DuckDB rarely debate SQL-vs-dataframes again — they use both in the same function.

Larger-than-memory execution

DuckDB spills to disk: joins, sorts, and aggregations that exceed RAM degrade gracefully rather than OOM-killing the process (set memory_limit and a temp_directory explicitly in constrained environments). A 200 GB join on a 32 GB machine completes — slower, but it completes. This out-of-core capability plus multicore vectorized execution is what lets a single node replace small Spark clusters for batch transformation jobs, with an enormous simplification dividend: no JVM, no cluster config, no serialization boundary.

Replacing glue scripts and powering local pipelines

Common deployment patterns, roughly in adoption order:

Notebook accelerator — the duckdb pip install as a faster, memory-lighter engine for joins/aggregations mid-analysis.
ETL glue replacement — pipeline steps that were 200-line pandas scripts become 20-line SQL COPY (SELECT ...) TO 'out.parquet' transforms, testable and readable.
dbt + DuckDB — the dbt-duckdb adapter runs a full dbt project against local or S3 Parquet — warehouse-grade transformation workflow with zero warehouse cost, popular for development environments and modest production loads.
Lakehouse reader — extensions for Iceberg and Delta let DuckDB serve as a query layer over open table formats (see Data Lakehouse).
Application embedding — analytics features inside an app (dashboards over the customer's own data) without shipping a database server; also compiles to WASM for fully in-browser analytics.

Persistence, concurrency, and the .duckdb file

A DuckDB database file can be attached by either one read-write process or many read-only processes — not both at once; within the single read-write process, multiple threads and connections write concurrently under MVCC. It is a single-node embedded engine, not a multi-writer server. The idiomatic architecture keeps data in Parquet on object storage and treats DuckDB instances as disposable compute; the .duckdb file is a cache or working store, not the system of record. That framing sidesteps nearly every concurrency question.

When you actually need a warehouse

Move up to Snowflake/BigQuery/ClickHouse (or keep them) when you need: many concurrent writers or a serving layer for hundreds of simultaneous dashboard users; centralized governance, RBAC, and audit across an organization; or genuinely multi-terabyte hot working sets. The frequent anti-pattern is the reverse: standing up warehouse infrastructure for a single-team, sub-100 GB workload that one process on one machine handles interactively. Start embedded; scale out when a concrete constraint — not a default assumption — forces it.